Postgres in depth · Chapter 55 of 65

The day the disk died

By the end of this chapter you can take a logical backup with pg_dump and restore it, explain physical backups and point-in-time recovery, find the exact WAL position to recover to, and check replication from the catalog. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.

  • Part: Inside Postgres
  • 4 tasks
  • PostgreSQL
  • In the all labs plan

The first 2 chapters of Postgres in depth are free. This one comes with the all labs plan.

Why you need this

At 3 am the disk under LCO's database failed. The WAL (chapter 45) protects commits from a crash, but it lives on the same disk: when the disk is gone, so is everything on it. The same week, Sana dropped a table by mistake, a perfectly durable mistake that the WAL faithfully kept. Kabir asks two questions: how much data can we lose, and how long until we are back? Priya answers with three tools: dumps, physical backups with the WAL archive, and replicas.

What you build: 4 tasks

Each task has tests. You write the code in the lab's editor and press Check.

  1. Task 1

    wal-settings.sql: is this server ready for replicas?

    Before Priya sets up a standby, she checks the server. Return one row: the WAL level, the number of WAL senders allowed (as a number) and the archive mode, then two booleans worked out from them: physical_ready (the WAL level is replica or logical, and at least one WAL sender is allowed) and logical_ready (the WAL level is logical).

    Return five columns: wal_level, max_wal_senders, archive_mode, physical_ready and logical_ready.

  2. Task 2

    publication.sql: publish the sales tables

    Arjun's analytics database will copy LCO's sales as they happen, by logical replication. Create a publication called lco_sales for the tables orders, order_items and payments that publishes inserts and updates only, never deletes or truncates.

    Afterwards pg_publication must have lco_sales for exactly those three tables, with only insert and update switched on.

    Careful: Postgres warns that the WAL level is too low for a subscriber yet; a warning is not an error.

  3. Task 3

    pitr.sql: where to stop the recovery

    Sana dropped refund_requests by mistake. The Check saved the WAL position just before and just after the drop, in wal_marks with the labels before_drop and after_drop. To restore a backup and replay the WAL up to the moment before the drop, Priya needs the DROP's commit record: its start LSN and its transaction id.

    Return one row with two columns: recovery_target_lsn and xid.

    Careful: other databases commit in the same window; the DROP's commit is this database's COMMIT whose description lists the files it removed.

  4. Task 4 · interview question

    Interview: replication-lag.sql, how far behind are the replicas?

    "How do you know a replica is not hours behind?" For each replica connected to this server, return its application name, its state, the bytes of WAL it has still to replay (from the server's current WAL position to the replica's replay position), and the replay lag as a time.

    Return four columns: application_name, state, replay_lag_bytes and replay_lag.

    Careful: the lab is a single server, so the answer is no rows; the Check also reads how you measure the bytes.

What you will learn

  • pg_dump and pg_restore: logical backups, formats, selective restore.
  • Physical backups and PITR: a base backup plus archived WAL, replayed to any moment.
  • Finding the recovery target in the WAL with pg_walinspect.
  • Replicas: streaming replication, pg_stat_replication, and lag.
  • Logical replication: publications and subscriptions.

More in Inside Postgres

Build The day the disk died yourself

LCO, an edtech company, moves its platform onto a single Postgres server you run. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.

The first 2 chapters of Postgres in depth are free. This one comes with the all labs plan.