[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!
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 postgreswill only look at processes owned by the user
-fwill look at the pattern in the whole command line, not only the process name
-awill display the whole command line instead of only the process number
--will allow a pattern that begins by
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.
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,
/var/lib/pgsql/12/data/ in my CentOS host whereas
/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
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
-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
4 is to say we want the 4th line and the
p is to mean we want to print
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
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 :
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).