FRSign in
Learning path

PostgreSQL: backup, restore and PITR

  • 3 labs
  • 6 h 10 min
  • Intermediate → Advanced

A backup that nobody has ever restored is not a backup. Three missions on a fintech's payments database: automating dumps to S3, replaying them under pressure the day the database goes down, then bringing data loss down from twenty-four hours to the minute with continuous archiving.

The steps

  1. Step 1
    Tutorial

    PostgreSQL: automated, encrypted backups to Amazon S3

    The data team of a fintech in Douala keeps its PostgreSQL backups on the application server's disk. A disk failure in July cost two days of transactions: management now demands daily backups, encrypted, stored off the server and verifiable. In this scenario, you take over the infrastructure as it stands: a PostgreSQL payments database that receives a new transaction every two seconds, and an empty S3 bucket. It is up to you to build the complete backup chain: extraction with pg_dump, encryption, upload to S3, rotation of old copies and an alert on failure. The sandbox is ready: PostgreSQL, the AWS CLI and an IAM role restricted to your bucket are already in place, and you open the terminal directly in your browser, with no SSH key. You spend the session on backup logic, not on installing tools. At the end, you deliberately drop a table and prove that your backup gets you back on your feet.

    • 4.0 out of 5, (2 reviews)
    • Intermediate
    • 1 h 40 min
    • 500 credits
    • First lab free
  2. Step 2
    Advanced

    PostgreSQL: restore a database after an incident and measure the RTO

    It is 6 a.m., the payments database will not come back up and the CTO asks you one thing only: how long until the service is back. You have the encrypted backups uploaded to S3 the day before and a blank standby server. This scenario follows on from the previous lab's backup chain, but on the side that really matters: the restore. You fetch the archive, decrypt it, bring the database back on the standby server, switch the application traffic back and put a figure on the whole thing, in a time you measure. The sandbox starts with the incident already under way: the main database is corrupted, the backups are in place, the clock is running.

    • No reviews yet
    • Advanced
    • 2 h
    • 1,000 credits
    • First lab free
  3. Step 3
    Advanced

    PostgreSQL: point-in-time recovery (PITR) with WAL

    Monday, 2:07 p.m., a migration script goes into production. It was supposed to regularise the weekend's payments; its last query lost its WHERE clause during review. Nobody notices. At 3:30 p.m., the bank reconciliation no longer balances: every transaction has an amount of zero. You restore the 2 a.m. dump, the service comes back, and thirteen hours of payments vanish along with the blunder. The path's first two labs dealt with the time to restore service. This one tackles the other figure, the one nobody had asked for: the amount of data lost. With one backup a day, no restore, however fast, gets below 24 hours of losses: what was not backed up no longer exists. You need to change the mechanism. You set up continuous archiving of the transaction logs to S3, then you prove it on an incident you trigger yourself: you rerun the faulty script on a live database, and bring it back to the second before. A recovery plan that has never been rehearsed is not a plan, it is a hope. The sandbox starts with production running, one transaction every two seconds, the 02:00 dump already on S3, and the WAL archiving still to be built.

    • No reviews yet
    • Advanced
    • 2 h 30 min
    • 1,000 credits
    • First lab free