What's wrong with Postgres?

March 22, 2020
Administration PostgreSQL Troubleshooting

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 logfile 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 logfile 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 are 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:

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

How to find your Postgres logfile

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 logfile 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!

Where do my Postgres settings come from ?

April 19, 2020
Hacking PostgreSQL Administration

Debian Default Logging Explained

April 4, 2020
Hacking PostgreSQL Debian

Can't connect to Postgres

March 30, 2020
Administration PostgreSQL Troubleshooting