Backup, Security & Maintenance

Backups with pg_dump

pg_dump creates a logical backup of a single database. It is consistent — it captures a snapshot at a point in time, even while the database is live. pg_dumpall backs up all databases plus server-level objects like roles.

# SQL-format dump (human-readable, default)
pg_dump -U app_user -d app_db -f backup.sql

# Custom-format dump (compressed, fastest to restore)
pg_dump -U app_user -d app_db -Fc -f backup.dump

# Backup specific tables only
pg_dump -U app_user -d app_db -t users -t orders -Fc -f partial.dump

# Restore from SQL dump
psql -U app_user -d app_db < backup.sql

# Restore from custom format (faster, supports parallel restore)
pg_restore -U app_user -d app_db -j 4 backup.dump

# Backup all databases + roles
pg_dumpall -U postgres -f full_cluster.sql

Roles and Least-Privilege Access

Never let your application connect as a superuser. Create dedicated roles with only the permissions needed.

-- Read-only role (for reporting, read replicas)
CREATE ROLE readonly;
GRANT CONNECT ON DATABASE app_db TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
-- Ensure future tables are also readable
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO readonly;

-- Application role (can read and write, but not drop tables)
CREATE ROLE app_rw;
GRANT CONNECT ON DATABASE app_db TO app_rw;
GRANT USAGE ON SCHEMA public TO app_rw;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_rw;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO app_rw;

-- Create a login user and assign the role
CREATE USER app_service WITH PASSWORD 'strong-password';
GRANT app_rw TO app_service;

Connection Security

# pg_hba.conf: control who can connect from where
# Format: type  database  user  address  method
local   all         postgres             peer
host    app_db      app_service  0.0.0.0/0  scram-sha-256

# Enforce SSL for all remote connections
# In postgresql.conf:
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file  = 'server.key'

# Connection string with SSL required
DATABASE_URL=postgresql://app_service:pw@host/app_db?sslmode=require

Maintenance: VACUUM and ANALYZE

Postgres uses MVCC: instead of modifying rows in place, it creates new row versions and marks old ones as dead. Over time, dead row versions (called "bloat") accumulate. VACUUM reclaims this space and ANALYZE updates the query planner's statistics.

-- Manual vacuum + analyze (usually handled by autovacuum)
VACUUM (ANALYZE, VERBOSE) users;

-- VACUUM FULL: reclaims disk space but locks the table — use during maintenance windows
VACUUM FULL orders;

-- Check table and index bloat
SELECT relname, n_dead_tup, n_live_tup, last_autovacuum
FROM   pg_stat_user_tables
ORDER  BY n_dead_tup DESC
LIMIT  10;

Autovacuum handles routine vacuuming automatically. Monitor pg_stat_user_tables to verify it is keeping up.