PostgreSQL: managing the postmaster PID

mac os xpostgresqlpsql

I am running PostgreSQL on my localhost on MacOS. Every once in a while (~ every 5 times I reboot the computer), I face the following error when I try to connect to my local database instance:

could not connect to server: Connection refused
    Is the server running on host "localhost" (::1) and accepting
    TCP/IP connections on port 5432?
could not connect to server: Connection refused
    Is the server running on host "localhost" (127.0.0.1) and accepting
    TCP/IP connections on port 5432?

When this happens, I have gotten used to the practice of opening the terminal, then running

 rm -f /usr/local/var/postgres/postmaster.pid

which allows me to connect to the database. According to the documentation, having an old .PID file in the data directory "confuses" postgres and so it must be removed.

My question is, why must be this done intermittently and can one automate the removal of old .pid files ? Please note that brew services restart postgres does not resolve the issue – only the removal of the old .pid file works. Any advice here would be greatly appreciated!

Best Answer

The postmaster.pid file is one of the measure that PostgreSQL takes to prevent starting multiple database servers on the same data directory at the same time (which would cause data corruption).

If you start the PostgreSQL server and there is a postmaster.pid file present, PostgreSQL checks if there is a process running with the process with the process ID from the PID file. If not, PostgreSQL will ignore the obviously stale file and start anyway. Otherwise, it will refuse to start.

The correct solution is to see that the database is shut down cleanly when you turn off the machine. A clean shutdown will remove postmaster.pid. This will also save you the time to perform crash recovery on the PostgreSQL database.

Related Question