Backups, Migrations, Monitoring, and Operations

Back up and restore data, manage schema changes, inspect health, and understand routine maintenance.

Backups with pg_dump and Restore

A backup is useful only if it can be restored and meets recovery requirements. pg_dump creates a logical backup of a database, and pg_restore can restore custom-format dumps. Physical backups and continuous archiving are used for different recovery objectives, including point-in-time recovery. Choose a backup strategy based on data size, recovery point objective (RPO), recovery time objective (RTO), and operational constraints.

Store backups securely, protect them from accidental deletion or ransomware, and test restoration regularly. A successful backup command is not proof that the backup is complete or recoverable.

Create and restore a custom-format dump

pg_dump -Fc -d app_db -f app_db.dump
createdb app_db_restore
pg_restore -d app_db_restore app_db.dump

💡 The custom format supports selective and parallel restore options. Supply connection details and credentials securely in real deployments, and test the restored database before relying on the backup.

Key Takeaways

Choose backups based on RPO and RTO.
Logical and physical backups serve different recovery strategies.
Regularly test restores, not just backup creation.

Knowledge Check

What proves that a backup is operationally useful?

Schema Migrations and Safe Deployments

Schema migrations are versioned changes to database structure or data. A migration tool records which changes have been applied and helps environments move consistently between versions. Prefer small, reviewable migrations and plan for deployments where old and new application versions may briefly run at the same time.

For large tables, adding a column with a safe default, building indexes concurrently, and backfilling data may require staged deployments. Some DDL operations acquire locks, and CREATE INDEX CONCURRENTLY has transaction restrictions. Test migrations against realistic data volumes and have a recovery plan for failed changes.

Add a nullable column in a migration

ALTER TABLE customers
ADD COLUMN preferred_name TEXT;

-- Backfill in a controlled, application-aware step.
UPDATE customers
SET preferred_name = name
WHERE preferred_name IS NULL;

💡 For a large production table, a backfill may need to be batched to limit lock duration, WAL generation, and replication lag. Add constraints in a deployment plan appropriate to table size and application compatibility.

Key Takeaways

Version and review schema changes.
Plan for old and new application versions during rollout.
Large-table migrations need lock, WAL, and replication awareness.

Knowledge Check

Why might a large-table backfill be performed in batches?

VACUUM, ANALYZE, and Monitoring

PostgreSQL uses MVCC and creates new row versions for updates and deletes. VACUUM helps reclaim space for reuse and prevents transaction ID wraparound; autovacuum performs routine maintenance. ANALYZE gathers statistics used by the query planner. VACUUM FULL rewrites a table and takes a stronger lock, so it is not a routine substitute for ordinary vacuum.

Monitor connections, slow queries, locks, replication lag, disk growth, cache behavior, and autovacuum activity. Use pg_stat_activity, pg_stat_statements when installed, logs, and execution plans to diagnose issues. Establish baselines before tuning; a single metric rarely explains the entire performance picture.

Inspect active sessions

SELECT pid, usename, state, wait_event_type, wait_event,
       query_start, query
FROM pg_stat_activity
WHERE datname = current_database()
ORDER BY query_start NULLS LAST;

💡 The view provides information about server processes and their current activity. Access to query text and other details can depend on privileges and server configuration.

Key Takeaways

Autovacuum handles routine maintenance in most installations.
ANALYZE updates planner statistics.
Monitor workload patterns and diagnose with evidence before tuning.

Knowledge Check

What is a primary purpose of ANALYZE?

Backups with pg_dump and Restore

Schema Migrations and Safe Deployments

VACUUM, ANALYZE, and Monitoring

STAY UPDATED WITH BYTES!