Catalog My learning Blog
PostgreSQL Course: Beginner to Production in 30 Days · Phase 4 · Lesson 30 · 8 min read

PostgreSQL High Availability and Disaster Recovery

On this page
  1. The TaskFlow design exercise
  2. Multi-tenant schema with composite foreign keys
  3. Testing tenant isolation
  4. High availability and disaster recovery plan
  5. What to monitor in production PostgreSQL
  6. What a strong answer includes
  7. Check yourself

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:

  1. Schema. Tenants, users, projects and tasks, with keys and constraints that stop bad data.
  2. Isolation. No tenant may ever read or write another tenant's rows, even through an application bug or an ad hoc query.
  3. Scale. tasks will grow by millions of rows a month. Old tasks are archived after two years.
  4. Access. The application connects with a role that has only what it needs.
  5. Availability. If the primary server dies, service should be back within a minute, losing no committed data.
  6. Recovery. If someone runs a destructive query, you must be able to restore to the minute before it.
  7. 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.

SQL
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): tasks is split by month, so archiving two-year-old tasks means detaching and dropping a partition. The primary key includes created_at because 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_id comes 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:

SQL
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:

SQL
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-only with 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:

SignalWhere to lookWhy it matters
Replication lagpg_stat_replication (replay_lag)Failover target is behind, and reads from it are stale
WAL archiving failurespg_stat_archiver (failed_count)A gap breaks point-in-time recovery
Inactive replication slotspg_replication_slotsWAL piles up until the disk fills
Long or idle-in-transaction sessionspg_stat_activityBlocks vacuum and freezing (Day 24)
Transaction ID ageage(datfrozenxid)Wraparound halts writes
Connections against the limitpg_stat_activity countNew connections start failing
Top queries by total timepg_stat_statementsWhere the load comes from (Day 25)
Next month's partition existscatalog checkInserts fail without it
Disk space, including pg_walOS metricsA 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_id on 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.
  • tasks partitioned by time, with partitions created ahead of time and old ones dropped instead of deleted.
  • Indexes that start with tenant_id and 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.