PostgreSQL Connection Failure
Sometimes, computers have a life of their own. You shut down your laptop and on the next boot, you are unable to connect to a PostgreSQL 12 database.
Opening the installed Postgres.app, I notice it displays
stale postmaster.pid file error.
What the heck is the postmaster.pid file? Turns out that the postmaster.pid is a lock file used to prevent two instances of the same PostgreSQL server from running on the same data-directory.
While the server is running, its PID is stored in the file postmaster.pid in the data directory. This is used to prevent multiple server instances from running in the same data directory and can also be used for shutting down the server.
This is the first time I am encountering such an error. I click on the Start button to force a re-start and I get back a dialog with the following message: The data directory contains a postmaster.pid file, which usually means that the server is already running. When the server crashes or is killed, you have to remove this file before you can restart the server. Make sure that the database process is definitely not running anymore, otherwise your data directory will be corrupted.
Click on OK button to close the dialog window.
Resolving the Problem
- Open Postgres.app
- Click on Server Settings…
- Click on Show button next to the Data Directory. This should open the data directory of your PostgreSQl installation.
- Locate the postmaster.pid file
- Delete the postmaster.pid file. Right click on the file and select Move to Bin
- After deleting the file, opening or making the Postgres GUI app window have focus will change the error message from
stale postmaster.pid fileto
Not running. Click Start button on Postgres GUI app to start PostgreSQL server.
Why not let PostgreSQL auto remove a stale postmaster.pid file? This would make a user manage a PostgreSQL database without knowing its internal workings. Pros and cons of auto removing a stale postmaster.pid exist and balancing between these two is not easy. Therefore, as at the time of this writing, the auto removal of the file remains an open issue and the user’s responsibility.