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!
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!
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:
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
log_destination: the default is
log_directory: the default is
logmeaning logs will be sent to the
log_filename: the default is
Now, you need to interpret these results to understand where your logfile 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
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!