PostgreSQL High Availability and Disaster Recovery
On this page
The TaskFlow design exercise
Try this yourself before reading the walkthrough. Set aside an hour.
You're building the database for TaskFlow, a project-management SaaS used by many separate companies (tenants). Requirements:
- Schema. Tenants, users, projects and tasks, with keys and constraints that stop bad data.
- Isolation. No tenant may ever read or write another tenant's rows, even through an application bug or an ad hoc query.
- Scale.
taskswill grow by millions of rows a month. Old tasks are archived after two years. - Access. The application connects with a role that has only what it needs.
- Availability. If the primary server dies, service should be back within a minute, losing no committed data.
- Recovery. If someone runs a destructive query, you must be able to restore to the minute before it.
- Monitoring. List what you'd alert on.
Write your answer, then compare it with the walkthrough below.
Multi-tenant schema with composite foreign keys
The key decision is putting tenant_id on every tenant-owned table and into the keys themselves. That lets foreign keys enforce tenant boundaries too. A task can't point at a project or user from another tenant, because the foreign key includes tenant_id.
CREATE TABLE tenants (
tenant_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL
);
CREATE TABLE users (
tenant_id bigint NOT NULL REFERENCES tenants,
user_id bigint GENERATED ALWAYS AS IDENTITY,
email text NOT NULL,
full_name text NOT NULL,
PRIMARY KEY (tenant_id, user_id),
UNIQUE (tenant_id, email)
);
CREATE TABLE projects (
tenant_id bigint NOT NULL REFERENCES tenants,
project_id bigint GENERATED ALWAYS AS IDENTITY,
name text NOT NULL,
status text NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'archived')),
PRIMARY KEY (tenant_id, project_id)
);
CREATE TABLE tasks (
tenant_id bigint NOT NULL,
task_id bigint GENERATED ALWAYS AS IDENTITY,
project_id bigint NOT NULL,
assignee_id bigint,
title text NOT NULL,
status text NOT NULL DEFAULT 'open' CHECK (status IN ('open', 'in_progress', 'done')),
created_at timestamptz NOT NULL DEFAULT now(),
PRIMARY KEY (tenant_id, task_id, created_at),
FOREIGN KEY (tenant_id, project_id) REFERENCES projects,
FOREIGN KEY (tenant_id, assignee_id) REFERENCES users
) PARTITION BY RANGE (created_at);
CREATE TABLE tasks_2026_10 PARTITION OF tasks FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');
CREATE TABLE tasks_2026_11 PARTITION OF tasks FOR VALUES FROM ('2026-11-01') TO ('2026-12-01');
CREATE INDEX ON tasks (tenant_id, project_id, status);
CREATE INDEX ON tasks (tenant_id, assignee_id) WHERE status <> 'done';How each requirement is met:
- Constraints (Day 5): CHECKs on status values, email unique per tenant, NOT NULL wherever a value is required.
- Partitioning (Day 28):
tasksis split by month, so archiving two-year-old tasks means detaching and dropping a partition. The primary key includescreated_atbecause a partitioned table's unique keys must contain the partition key. - Indexes (Days 11 and 23): they match the real queries, "a project's tasks by status" and "my open tasks".
tenant_idcomes first because every query filters on it. The partial index skips finished tasks, which are most of the table.
Now add the application role and the RLS policies (Day 29). A short loop applies the same policy to each table:
CREATE ROLE taskflow_app LOGIN PASSWORD 'change-me';
GRANT SELECT, INSERT, UPDATE, DELETE ON users, projects, tasks TO taskflow_app;
GRANT SELECT ON tenants TO taskflow_app;
DO $$
DECLARE t text;
BEGIN
FOREACH t IN ARRAY ARRAY['users', 'projects', 'tasks'] LOOP
EXECUTE format('ALTER TABLE %I ENABLE ROW LEVEL SECURITY', t);
EXECUTE format($p$CREATE POLICY tenant_isolation ON %I
USING (tenant_id = current_setting('app.tenant_id')::bigint)
WITH CHECK (tenant_id = current_setting('app.tenant_id')::bigint)$p$, t);
END LOOP;
END;
$$;The app role isn't a superuser and doesn't own the tables, so the policies apply to it. It can't create tables or change the schema. A separate migration role does that (Day 21).
Testing tenant isolation
Don't trust the design until you've tried to break it as the application role:
INSERT INTO tenants (name) VALUES ('Acme'), ('Globex');
INSERT INTO users (tenant_id, email, full_name) VALUES (1, 'ana@acme.test', 'Ana'), (2, 'gus@globex.test', 'Gus');
INSERT INTO projects (tenant_id, name) VALUES (1, 'Launch'), (2, 'Secret plan');
INSERT INTO tasks (tenant_id, project_id, assignee_id, title, created_at)
VALUES (1, 1, 1, 'Write docs', '2026-10-08'), (2, 2, 2, 'Hide plan', '2026-10-08');
SET ROLE taskflow_app;
BEGIN;
SELECT set_config('app.tenant_id', '1', true); -- per transaction, safe behind PgBouncer
SELECT t.title, u.full_name AS assignee
FROM tasks t
LEFT JOIN users u ON u.tenant_id = t.tenant_id AND u.user_id = t.assignee_id;
-- Write docs | Ana (Globex's task is invisible)
SELECT count(*) FROM projects; -- 1
INSERT INTO tasks (tenant_id, project_id, assignee_id, title, created_at)
VALUES (1, 1, 2, 'Assign to Gus', '2026-10-09');
-- ERROR: violates foreign key constraint "tasks_tenant_id_assignee_id_fkey"
ROLLBACK;
RESET ROLE;Note the last test. RLS alone wouldn't have caught it, because the row's tenant_id is 1, which the policy allows. The composite foreign key is what stops Acme from assigning work to a Globex user. Isolation needs both layers.
High availability and disaster recovery plan
High availability (HA) and disaster recovery (DR) answer different failures, and TaskFlow needs both.
High availability: the primary dies. Requirement 5 asks for under a minute of downtime and no lost commits:
- Run a primary and two standbys with streaming replication (Day 27), in different availability zones.
- Use synchronous replication with
synchronous_standby_names = 'ANY 1 (standby_a, standby_b)', so every commit is on at least one standby. With two candidates, losing one standby doesn't stall writes. - Automate failover with Patroni. An agent on each node keeps a leader lock in a consensus store such as etcd. If the primary stops renewing the lock, a standby takes it and promotes itself. Because only the lock holder may act as primary, the old primary is fenced off and split brain is avoided.
- Route clients through HAProxy, which asks each Patroni agent which node is the leader, with PgBouncer for pooling (Day 29).
Disaster recovery: the data itself is wrong, or the region is gone. Replication copies a bad DELETE to every standby within milliseconds, so it can't help here (Day 26):
- Continuous WAL archiving plus nightly base backups, sent to storage in another region, using pgBackRest or WAL-G.
- Point-in-time recovery to the minute before a destructive query. RPO is the time since the last archived WAL segment, so set
archive_timeout(for example, 60 seconds) for a quiet system. pg_dumpall --globals-onlywith the backups, so roles exist after a restore.- A written runbook, rehearsed every quarter by someone who didn't write it, with the restore timed. That time is your real RTO.
What to monitor in production PostgreSQL
Alert on these before they become outages:
| Signal | Where to look | Why it matters |
|---|---|---|
| Replication lag | pg_stat_replication (replay_lag) | Failover target is behind, and reads from it are stale |
| WAL archiving failures | pg_stat_archiver (failed_count) | A gap breaks point-in-time recovery |
| Inactive replication slots | pg_replication_slots | WAL piles up until the disk fills |
| Long or idle-in-transaction sessions | pg_stat_activity | Blocks vacuum and freezing (Day 24) |
| Transaction ID age | age(datfrozenxid) | Wraparound halts writes |
| Connections against the limit | pg_stat_activity count | New connections start failing |
| Top queries by total time | pg_stat_statements | Where the load comes from (Day 25) |
| Next month's partition exists | catalog check | Inserts fail without it |
Disk space, including pg_wal | OS metrics | A full disk stops the server |
Most teams collect these with Prometheus and postgres_exporter and graph them in Grafana. The tool matters less than having the alert fire before users notice.
What a strong answer includes
Compare your attempt against this checklist:
tenant_idon every tenant table, in the keys, with composite foreign keys so references can't cross tenants.- RLS with USING and WITH CHECK on every tenant table, tested as the real application role, never as a superuser.
- The tenant set per transaction, so it's safe behind a transaction-mode pooler.
taskspartitioned by time, with partitions created ahead of time and old ones dropped instead of deleted.- Indexes that start with
tenant_idand match named queries, not guesses. - An application role with no ownership, no superuser and no BYPASSRLS.
- HA (replicas and automated failover) and DR (WAL archiving and PITR) treated as separate needs, with RPO and RTO stated.
- Monitoring that covers replication, archiving, slots, long transactions and wraparound.
Check yourself
Logical mistakes. A bad UPDATE, DELETE or DROP reaches every replica within moments. Only backups with WAL archiving let you restore to the moment before it.
Name several candidates with a quorum, such as ANY 1 (standby_a, standby_b). A commit then waits for whichever standby answers first, and losing one doesn't stall writes.
The task row itself belonged to tenant 1, so the policy allowed it, and the foreign key only checked that the user existed somewhere. A composite foreign key on (tenant_id, assignee_id) makes the database reject references across tenants.
Replication lag and slot WAL retention, disk space, connection usage against the limit, long-running and idle-in-transaction sessions, transaction ID age for wraparound, failed backups or WAL archiving, and the slowest queries from pg_stat_statements. Each alert should map to a known action.
Policies are easy to get subtly wrong, and changes to schemas, roles or connection pooling can break them without any error. Tests that connect as the application role for tenant A and try to read or modify tenant B's rows catch those regressions before customers do.