What's wrong with Postgres?

This post is part of several blog posts (at least two!) about Troubleshooting Postgres. Last time, we found out if Postgres was running and how to start it. Please find here all the troubleshooting posts.

Here is my checklist of troubleshooting Postgres:

[] Try to start Postgres (if this works, stop here) [] Locate the log file [] Look at the messages in Postgres logfile

To start Postgres, please refer to Is Postgres running.

Locating Postgres logfile 🔗

For Debian and friends 🔗

Debian-based distributions have these wonderful Perl wrappers that extend Postgres administration tool. Use them!

pg_lsclusters

So, for me, I’ll find this:

root@ubuntu:/home/vagrant# pg_lsclusters
Ver Cluster Port Status Owner    Data directory              Log file
12  main    5432 online postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log
12  test    5433 online postgres /var/lib/postgresql/12/test /var/log/postgresql/postgresql-12-test.log

Your log file is simply indicated there. Go find out why Postgres is not happy with you!

For Red Hat and friends 🔗

Let’s say it straight: it won’t be easy, depending on how you (mis)configured Postgres. If Postgres is running, you can always query it like that:

postgres=# select pg_current_logfile();

But, if you’re reading that post, you might be in a situation where Postgres refuses to start and you have no idea where your log file is.

To find your Postgres logfile, you need to find out how Postgres was configured, regarding logs. Maybe you simply don’t have a logfile at all!

Finding $PGDATA 🔗

Red Hat and Red Hat-based distribution use systemctl to control Postgres. So the best thing you have to do is to ask directly systemctl to know where the config file is:

systemctl cat postgresql-12.service | grep "PGDATA" | grep "Environment"

Here’s my example:

[root@centos7 vagrant]# systemctl cat postgresql-12.service | grep "PGDATA" | grep "Environment"
Environment=PGDATA=/var/lib/pgsql/12/data/

Finding your logfile 🔗

Now that you have found your $PGDATA, you’ll be able to look at your Postgres log settings to find out where your Postgres log is located.

You’ll need to find out the value of the following parameters:

• [] logging_collector
• [] log_destination
• [] log_directory
• [] log_filename

Here’s one way to get them all:

sed -e 's/#.*$//' /var/lib/pgsql/12/data/postgresql.conf | grep -E "logging_collector|log_destination|log_directory|log_filename"

If some of the parameters don’t show up, it simply means that they’re not set and the default value is taken instead.

Here are the default values for each one:

• logging_collector: the default is off
• log_destination: the default is stderr
• log_directory: the default is log meaning logs will be sent to the log directory inside $PGDATA
• log_filename: the default is postgresql-%Y-%m-%d_%H%M%S.log

Now, you need to interpret these results to understand where your log file is. This schema might help you:

For example, if you had these results:

[root@centos7 vagrant]# sed -e 's/#.*$//' /var/lib/pgsql/12/data/postgresql.conf | grep -E "logging_collector|log_destination|log_directory|log_filename"
log_destination = 'stderr'
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%a.log'

It means you’ll find your Postgres logfile in $PGDATA/log and the name of the log file will be postgresql-Sun.log, as today is Sunday.

Now, go look at your logfile. Postgres is pretty good at telling you what’s wrong. Be particularly attentive to the few last lines!