Backup and Production
pg_dump, connection pooling, VACUUM, ANALYZE, and production configuration
Skills taught: pg.pg-dump, pg.vacuum, pg.connection-pooling, pg.production-config
Backups with pg_dump
# Dump a database to SQL text file
pg_dump -h localhost -U postgres myapp > myapp_backup.sql
# Compressed backup (preferred for large databases)
pg_dump -h localhost -U postgres -F c myapp > myapp_backup.dump
# Restore from SQL file
psql -h localhost -U postgres -d myapp_restore < myapp_backup.sql
# Restore from compressed dump
pg_restore -h localhost -U postgres -d myapp_restore myapp_backup.dump
# Automated daily backups (crontab -e)
# 0 2 * * * pg_dump -U postgres myapp | gzip > /backups/myapp_$(date +\%Y\%m\%d).sql.gz
# Dump only the schema (no data)
pg_dump --schema-only -h localhost -U postgres myapp > schema.sql
# Dump only data (no schema)
pg_dump --data-only -h localhost -U postgres myapp > data.sql
VACUUM and ANALYZE
-- VACUUM reclaims storage from dead rows (MVCC cleanup)
-- autovacuum runs this automatically — manual VACUUM rarely needed
VACUUM users;
VACUUM VERBOSE users; -- shows statistics
VACUUM FULL users; -- reclaims disk space (locks table — use sparingly)
-- ANALYZE updates query planner statistics
-- Run after bulk inserts/updates for accurate query plans
ANALYZE users;
ANALYZE; -- analyze all tables
-- Check autovacuum status
SELECT
schemaname,
tablename,
last_vacuum,
last_autovacuum,
last_analyze,
n_dead_tup
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;
Production configuration checklist
- Use a connection pooler — PgBouncer between your app and PostgreSQL. Each Postgres connection is a process (~5-10MB). 500 app connections → 500 Postgres processes without pooling.
- Set max_connections — default 100. Increase to 200-400 with PgBouncer pooling. Formula: available RAM / 10MB per connection.
- Enable pg_stat_statements — query performance tracking. Add to postgresql.conf: shared_preload_libraries = 'pg_stat_statements'
- Configure shared_buffers — set to 25% of system RAM. The most impactful memory setting.
- Regular backups — pg_dump or continuous WAL archiving (point-in-time recovery). Test restores regularly.
- Use TIMESTAMPTZ everywhere — set timezone = 'UTC' in postgresql.conf so NOW() returns UTC.
- Enable SSL — ssl = on in postgresql.conf. Require SSL in pg_hba.conf for remote connections.
Frequently Asked Questions
How often should I run pg_dump?
For production: daily minimum, with automated rotation keeping 7-30 days of backups. Store backups off-server (S3, a different data center). For critical data, consider continuous WAL archiving (point-in-time recovery) so you can restore to any point in time, not just a daily snapshot.