Backup, Security & Maintenance

Backups

# Backup
pg_dump -U app_user -d app_db > backup.sql

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

Roles & Privileges

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;

Maintenance: VACUUM & ANALYZE

Postgres uses MVCC, which leaves old row versions behind. VACUUM cleans them up and ANALYZE updates planner stats.

VACUUM (ANALYZE) users;