PostgreSQL 101

The following cheat list assumes that you have installed PostgreSQL as documented.

Start psql as DB admin

$ sudo -u postgres psql
postgres=#

Tip

psql indicates that you have a role with superuser permission with “=#” in the prompt. For example, postgres=#.

Determine PostgreSQL version

select version();

Start, stop, restart PostgreSQL

sudo systemctl start postgresql
sudo systemctl stop postgresql
sudo systemctl restart postgresql

Tip

Maybe the change that you want to apply only needs a reload that did not cause a downtime.

List existing databases

sudo -u postgres psql -c "\l"

Backup and restore a single database

Backup to compressed file:

sudo -u postgres bash -c "pg_dump somedb | gzip > /tmp/somedb_backup.gz"

The target file must be in a writable location for the postgres user, e.g. /tmp/.

Restore a compressed file:

sudo -u postgres bash -c "gunzip -c /tmp/somedb_backup.gz | psql"

Backup and restore a cluster

To backup and compress the entire contents of a cluster, use pg_dumpall instead:

sudo -u postgres bash -c "pg_dumpall | gzip > /tmp/pg_backup.gz

The target file must be in a writable location for the postgres user, e.g. /tmp/.

To restore a compressed backup:

sudo -u postgres bash -c "gunzip -c /tmp/pg_backup.gz | psql"

Inspect a cluster

The command pg_lsclusters gives you important information about known PostgreSQL clusters. (Typically you will have only one.)

The output will tell you the version, network port, status, data directory and log file.

$ pg_lsclusters
Ver Cluster Port Status Owner    Data directory              Log file
12  main    5432 down   postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log

For example, if the status is down, see the log file for the reason.

Next   Previous