PostgreSQL Admin

Below is a running list of admin tasks I’ve had to use to maintain my database.

Restart PostgreSQL

A good thing to do every now and then. This can clean up stuck transactions and flush out old write-ahead log (WAL) files.

$ sudo systemctl restart postgresql

Check for long running transactions.

SELECT pid, state, query, now() - query_start AS runtime
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY runtime DESC
;

If a query is found to be running for hours or even days, it may be holding back WAL file cleanup’s.

Set WAL file retention and archiving

Open the PostgreSQL config file

$ sudo vim /etc/postgresql/15/main/postgresql.conf

Locate and set these values:

wal_keep_size = 512MB     # default is 0; prevents unbounded WAL growth
max_wal_size = 1GB        # limit WAL file growth
min_wal_size = 80MB

If you’re not using WAL archiving or replication:

archive_mode = off
archive_command = ''

Then restart to force PostgreSQL to re-read config file.

$ sudo systemctl reload postgresql

Vacuum the database

This SQL command cleans up dead rows left behind by updates and deletes.

VACUUM
;

Full vacuum to reclaim disk space

VACUUM FULL
;

Same as above but with a verbose output

VACUUM VERBOSE
;