PostgreSQL tips and tricks

postgresql-tips.md

PostgreSQL Tips and Tricks

Checking to see if PostgreSQL is running:

Running these commands should tell you if postgres is running or not.

Ubuntu Linux:

sudo service postgresql status

macOS

brew services

Starting PostgreSQL

You can usually use a restart to start it blindly. Or replace 'restart' with 'start' if you know it's stopped

Ubuntu Linux

sudo service postgresql restart

macOS

brew services restart postgres

Cleaning up the postmaster.pid file.

Sometimes if your computer restarts without shutting down cleanly, postgres will leave behind a postmaster.pid file, which makes postgres think it's running when it's not actually running.

Finding the location of the file:

Running this command will tell you the directory where postgres is storing it's files.

pg_conftool show data_directory

Deleting the pid file.

Take the directory from the pg_conftool command and then use it to remove the postmaster.pid file.

For example if the path was /var/lib/postgresql/12/main

rm -v /var/lib/postgresql/12/main/postmaster.pid

On Ubuntu Linux, you may have to be root to remove this file, so prepend the command with sudo like this:

sudo rm -v /var/lib/postgresql/12/main/postmaster.pid

Once the file is removed, start up postgres and it should be good to go.

Checking the port postgresql is listening on

PostgreSQL normally listens on port 5432.

If you want to see if it's listening you can run this unix command to list all listening ports on your computer.

sudo lsof -nP -iTCP -sTCP:LISTEN

You should see something like this in the output somewhere:

postgres  942081        postgres    3u  IPv4 4819961      0t0  TCP 127.0.0.1:5432 (LISTEN)

If it doesn't show up, try restarting postgres.