Maintenance

PostgreSQL Maintenance Backup Strategies

bash

# pg_dump - Logical backup
pg_dump -h localhost -U postgres -d myapp > myapp_backup.sql
# pg_dump with compression
pg_dump -h localhost -U postgres -d myapp | gzip > myapp_backup.sql.gz
# pg_dumpall - All databases
pg_dumpall -h localhost -U postgres > all_databases_backup.sql
# pg_basebackup - Physical backup
pg_basebackup -h localhost -U postgres -D /backup/postgres/base -P -W

Restore Operations

bash

# Restore from pg_dump
psql -h localhost -U postgres -d myapp < myapp_backup.sql
# Restore compressed backup
gunzip -c myapp_backup.sql.gz | psql -h localhost -U postgres -d myapp
# Restore all databases
psql -h localhost -U postgres -f all_databases_backup.sql

Vacuum and Analyze

sql

-- Manual vacuum
VACUUM ANALYZE;
-- Vacuum specific table
VACUUM ANALYZE table_name;
-- Full vacuum (requires exclusive lock)
VACUUM FULL table_name;
-- Autovacuum configuration
autovacuum = on
autovacuum_max_workers = 3
autovacuum_vacuum_threshold = 50
autovacuum_analyze_threshold = 50

Index Maintenance

sql

-- Index statistics
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan;
-- Rebuild index
REINDEX INDEX index_name;
-- Rebuild all indexes on table
REINDEX TABLE table_name;