Appendices

Appendix B. PostgreSQL Installation and Command Reference

The PostgreSQL side of Chapter 14, as a reference card: install routes, server control, psql, configuration, backup and restore, maintenance, and monitoring commands — PostgreSQL 16 and later.

Installation

Server control and connection

sudo systemctl start | stop | restart | status postgresql   # systemd units
pg_ctl -D /var/lib/postgresql/16/main start | stop | reload  # direct (as postgres user)
psql -h host -p 5432 -U user -d university                  # flags
psql "postgresql://user@host:5432/university"                 # connection URI
sudo -u postgres psql                                        # local peer-auth first contact

Environment defaults: PGHOST, PGPORT, PGUSER, PGDATABASE, PGPASSWORD (or ~/.pgpass).

psql meta-commands

Roles, users, privileges

CREATE ROLE name [LOGIN] [PASSWORD '...'] [SUPERUSER | CREATEDB | CREATEROLE | REPLICATION];
ALTER ROLE name PASSWORD '...' | LOGIN | ...;
DROP ROLE name;
GRANT role TO role;                        -- group membership (inherited)
GRANT SELECT, INSERT ON table TO role;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO role;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO role;
GRANT EXECUTE ON FUNCTION name(args) TO role;
REVOKE ... FROM role | PUBLIC;

Configuration (postgresql.conf / pg_hba.conf)

pg_hba.conf line shape: local db user peer / host db user CIDR scram-sha-256 / hostssl ... — reload with pg_ctl reload or SELECT pg_reload_conf();. ALTER SYSTEM SET name = value; writes without editing files.

Backup and restore

pg_dump -Fc university > u.dump                  # custom (compressed, selective)
pg_dump -Fd university -j 4 -f dumpdir/         # directory, parallel
pg_dump -f u.sql university                     # plain SQL
pg_dumpall --globals-only > globals.sql         # roles/tablespaces
pg_restore -d university -j 4 u.dump [-t table]
psql -d university -f u.sql
pg_basebackup -D basebackup/                    # physical base for PITR/replication

PITR: restore base, recovery.signal, restore_command, recovery_target_time, start.

Maintenance and monitoring

VACUUM [FULL] [ANALYZE] [table];            -- reclaim (FULL: exclusive, rare)
ANALYZE [table];                            -- refresh statistics
REINDEX TABLE table;
SELECT * FROM pg_stat_activity;             -- who is doing what (wait events included)
SELECT * FROM pg_stat_user_tables;          -- seq/index scans, n_dead_tup
SELECT * FROM pg_stat_database;             -- blks hit/read, deadlocks
SELECT query, calls, mean_exec_time FROM pg_stat_statements
    ORDER BY mean_exec_time DESC LIMIT 10;  -- top queries (extension: CREATE EXTENSION pg_stat_statements)
SELECT pg_size_pretty(pg_database_size('university'));

Replication: SELECT * FROM pg_stat_replication; (lag). Wraparound horizon: datfrozenxid ages in pg_database.

Common troubleshooting