Backups, Maintenance & Monitoring

Back up regularly and test restores — an untested backup is not a backup. pg_dump -Fc creates a compressed, flexible dump of one database; pg_restore loads it. For large production systems, use continuous archiving / point-in-time recovery (built into most managed services).

Change schemas through versioned migration files (with tools such as Flyway, Alembic, Prisma or plain numbered .sql files), applied the same way in every environment. Avoid long table locks in production: add indexes with CREATE INDEX CONCURRENTLY.

Because of MVCC, updates and deletes leave dead row versions; autovacuum cleans them up and refreshes statistics — keep it on. Each connection uses server memory, so put a pooler such as PgBouncer in front of busy apps. Watch active and long-running queries, table bloat and cache hit ratio.

TerminalShell

# Back up one database (custom compressed format)
pg_dump -h localhost -U postgres -Fc -d school -f school_$(date +%F).dump

# Restore into a fresh database
createdb -h localhost -U postgres school_restore
pg_restore -h localhost -U postgres -d school_restore --no-owner school_2026-10-04.dump

# Plain SQL dump of the schema only (useful for code review)
pg_dump -h localhost -U postgres --schema-only -d school > schema.sql

monitoring.sqlSQL

-- Build an index without blocking writes (cannot run inside a transaction block)
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_payments_paid_at ON payments (paid_at);

-- Currently running queries, longest first
SELECT pid, now() - query_start AS running_for, state, left(query, 80) AS query
FROM pg_stat_activity
WHERE state <> 'idle' AND pid <> pg_backend_pid()
ORDER BY running_for DESC NULLS LAST;

-- Cancel or terminate a runaway query (replace 12345 with a real pid)
-- SELECT pg_cancel_backend(12345);
-- SELECT pg_terminate_backend(12345);

-- Dead rows and last (auto)vacuum per table
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 5;

-- Largest tables including indexes
SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 5;

-- Cache hit ratio (aim for > 99% on a warmed-up OLTP database)
SELECT round(sum(blks_hit) * 100.0 / nullif(sum(blks_hit) + sum(blks_read), 0), 2) AS cache_hit_pct
FROM pg_stat_database;

-- Manual maintenance after a big data load
VACUUM (ANALYZE) exam_entries;

Runs in your browser · PostgreSQL

Key points

Automate backups and regularly test restoring them.
Apply schema changes through versioned migrations; use CONCURRENTLY for indexes in production.
Keep autovacuum on, pool connections, and monitor pg_stat_activity and table statistics.

Exercise

Back up your school database with pg_dump -Fc, restore it into school_restore, and verify row counts match with a query on both. Then write a numbered migration (002_add_guardians.sql) that adds a guardians table safely.

Show solution

Try the exercise yourself first — then compare your approach with this one.

Back up with pg_dump -Fc, restore into a new database, then compare row counts table by table. The migration is a numbered, re-runnable file: it uses IF NOT EXISTS, runs in a transaction, and adds the index without blocking writes on a busy server.

TerminalShell

pg_dump -h localhost -U postgres -Fc -d school -f school.dump
createdb -h localhost -U postgres school_restore
pg_restore -h localhost -U postgres -d school_restore --no-owner school.dump

for t in students subjects results payments; do
  a=$(psql -h localhost -U postgres -d school -Atc "SELECT count(*) FROM $t")
  b=$(psql -h localhost -U postgres -d school_restore -Atc "SELECT count(*) FROM $t")
  echo "$t: $a vs $b $([ "$a" = "$b" ] && echo OK || echo MISMATCH)"
done

002_add_guardians.sqlSQL

BEGIN;

CREATE TABLE IF NOT EXISTS guardians (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    student_id bigint NOT NULL REFERENCES students (id) ON DELETE CASCADE,
    full_name  text   NOT NULL,
    phone      text   NOT NULL CHECK (phone ~ '^0[67][0-9]{8}$'),
    relation   text   NOT NULL DEFAULT 'parent'
);

COMMIT;

-- Outside the transaction: build the index without blocking writes
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_guardians_student ON guardians (student_id);

Runs in your browser · PostgreSQL

Key points

Exercise

Check your understanding