Is Postgres running ?

[2020-03-22 Edit: I got several tips from friends showing me better ways to test if Postgres was running that I decided to edit this post to make it better. I compiled all these tips to come up with my upgraded way to see if Postgres is running. Thanks to @tapoueh, I found a rock-solid way to find out the port your instance is using (if your Postgres is up and running). Please, keep sending me tips, my friends: it makes me bigger!]

These troubled times made me work harder than before to finish my ongoing missions and after that, I found myself somehow left with little to do and that’s kind of disturbing. So, why not take this time to write down that article born into my brain this morning while eating breakfast?

I’m on Postgres slack and Telegram group, more rarely on IRC. I often see the same question, again and again: “Postgres doesn’t work, can you help me?”. No, we can’t. There’s too little information there, we can’t do anything for you.

So, I decided to create this little troubleshooting guide for Postgres running on Linux (for Windows, some tips will be accurate, some won’t be). Today, the first part: Is Postgres running?

How to check if Postgres is running? 🔗

Whatever you do, you need to know if Postgres is running (or if several Postgres are running at the same time). There are several ways to find out, here is the one I customized with all the good tips my friends sent me:

pgrep -u postgres -fa -- -D

Should I be ashamed I didn’t even know that pgrep existed yesterday? I don’t think so. If I was ashamed of everything I don’t know, I would be afraid to learn and I certainly don’t want that!

So pgrep will look at processes and will try to grep on it. Here is the options I added to make it look as I wanted:

• -u postgres will only look at processes owned by the user postgres
• -f will look at the pattern in the whole command line, not only the process name
• -a will display the whole command line instead of only the process number
• -- will allow a pattern that begins by - (like our -D)

So, normaly you should have one or several lines like there:

[root@centos7 vagrant]# pgrep -u postgres -fa -- -D
2424 /usr/pgsql-12/bin/postmaster -D /var/lib/pgsql/12/data/

But if you have several instances running, you’ll find several lines like there:

root@ubuntu:/home/vagrant# pgrep -fa -- -D |grep postgres
4499 /usr/lib/postgresql/12/bin/postgres -D /var/lib/postgresql/12/main -c config_file=/etc/postgresql/12/main/postgresql.conf
6378 /usr/lib/postgresql/12/bin/postgres -D /var/lib/postgresql/12/test -c config_file=/etc/postgresql/12/test/postgresql.conf

If you have several Postgres running at the same time, maybe you’re not trying to connect to the right instance?

If you have no line at all, it means Postgres is not started.

How to connect to the right Postgres instance 🔗

Each Postgres instance uses a port to connect to. By default, the 5432 port is used. You need to find which port is in use for each instance to connect to the right one.

Finding the $PGDATA directory` 🔗

You’ll probably find your $PGDATA directory in the path indicated after the -D option you’ll see in the pgrep command you issued earlier. So, in my case, $PGDATA is /var/lib/pgsql/12/data/ in my CentOS host whereas it’s /var/lib/postgresql/12/main and /var/lib/postgresql/12/test for the instances running on my Ubuntu host.

Finding your port 🔗

Once you’re located your $PGDATA directory, you will find a postmaster.pid file. The 4th line will give you the port for that instance. So, you might want to try that command to get directly your port number.

sed -n 4p <$PGDATA>/postmaster.pid

So, for my CentOS host, this command will be:

sed -n 4p /var/lib/pgsql/12/data/postmaster.pid

The -n flag will ensure we get only the line we’re asking for The sed command will get rid of any comment and the grep command will look for the port word. The 4 is to say we want the 4th line and the p is to mean we want to print it.

How to start Postgres 🔗

There again are several ways to start Postgres. If you’re running a Debian-based distribution, you should find the Perl wrappers pretty handy.

For Debian or Debian-based (including Ubuntu) Linux distributions 🔗

First, issue pg_lsclusters to find out your cluster name:

$ 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

To start that particular cluster, you just need to issue:

pg_ctlcluster <Version> <Name> start

So, in our example, it will be:

pg_ctlcluster 12 main start

For Systemd distributions 🔗

If your Linux distribution uses Systemd, you can use it also. To do that, you’ll need the service name. You can find it by issuing this simple command (I assumed your service has been enabled as advised in Postgres download and install page):

systemctl list-unit-files | grep enabled | grep postgres

For example, my CentOS answered me :

postgresql-12.service                         enabled

So, to start that service, you just need to do:

systemctl start postgresql-12

That was sooooo easy! Next time, we’ll see what to do when Postgres refuses to start! (Yes, it happens, most of the time because you messed up, but that’s another story).