PostgreSQL backup and restore: a practical guide for production teams
Date Published
Every PostgreSQL deployment has a backup. Far fewer have a restore they have timed.
That gap is where an incident turns into an outage. A backup job that reports success proves that files were written somewhere. It does not prove that you can bring the database back, at the right point in time, on hardware you still have, inside the window your business has agreed to.
This guide covers the methods PostgreSQL gives you, the tools that sit on top of them, and the checks that turn a backup plan into a recovery plan.
Decide the two numbers before you choose a tool
Recovery point objective (RPO) is how much data you can afford to lose, measured in time. Five minutes, one hour, one day.
Recovery time objective (RTO) is how long the service can stay down while you recover it.
Then write down the scenario you are recovering from. A dropped table, a bad deployment, a corrupted index, a failed storage array and a lost data centre are different problems with different answers. Most production systems end up with more than one backup method because those cases have different shapes and different costs.
Teams that skip this step tend to buy a tool first and discover their real requirement during an incident.
Logical backups: pg_dump and pg_dumpall
pg_dump exports one database to SQL text or a compressed archive. It reads from a consistent snapshot and does not block writers, and it produces the most portable output PostgreSQL has: the result can be restored into a different version, a different architecture or a different cluster.
What it handles well:
Recovering a single table or schema after a mistake
Moving data between environments, or seeding a smaller dataset for test
Databases small enough that a full export finishes inside the maintenance window
Where it stops being enough:
There is no point-in-time recovery. A dump captures the database at the moment it ran and nothing after that.
Restoring rebuilds indexes and constraints, so it is slower than most teams expect on large databases.
The backup window grows with the data. On a multi-terabyte database, a nightly dump becomes an operational problem rather than a safety net.
pg_dumpall extends this to cluster-wide objects such as roles and tablespaces, but it writes plain SQL text, which makes it a poor fit for large clusters.
Logical dumps still earn their place next to a physical backup. They are the fastest route back from a single-object mistake, and a poor foundation on their own.
Physical backups and point-in-time recovery
pg_basebackup copies the data directory of a running cluster. On its own that gives you a snapshot of the whole cluster, useful but blunt: from the moment it finishes, it starts going stale.
The interesting part is what happens when you combine a physical backup with continuous WAL archiving. Every committed change lands in the write-ahead log, and when those segments are archived to a separate location, you can restore the base backup and replay the log up to a chosen timestamp. That is point-in-time recovery, usually shortened to PITR.
PITR answers the question that matters in a real incident: can we come back to 14:32, just before that script ran?
Two constraints are worth stating plainly. Physical backups are tied to the major version and platform of the cluster that produced them, so they are not a portability tool. Restoring is also cluster-wide, which means bringing back one table means restoring a cluster, or restoring a clone and extracting what you need from it.
The failure mode to watch for is silence. WAL archiving that stops working does not stop the database. It quietly shrinks the range of time you can recover into, and teams often find out at the worst possible moment. Treat archive failures as production incidents.
The tool layer: pgBackRest, WAL-G and pg_probackup
Running base backups, WAL archiving, retention, compression and storage targets by hand does not scale, so most production teams use a dedicated tool. The three backup tools supported by Worlber's deployment automation are pgBackRest, WAL-G and pg_probackup.
They solve the same core problem: take physical backups and archive WAL on a schedule, apply retention rules, and write the result to storage that is not the database server. What differs is where they write, how they parallelise and compress, how retention and expiry work, what verification they offer, and which restore options they expose. Those details change between releases, so check the project documentation for the version you plan to run rather than a comparison table someone published three years ago.
On choosing between them: the tool matters less than three decisions around it. Where the copies live, how long they are kept, and whether anyone has proven a restore.
Where the copies live
A backup on the same volume, array or cloud account as the database is not a backup. It is a copy that disappears with the thing it was meant to protect.
Object storage has made off-site copies cheap enough that there is little reason to skip them. Two settings are worth getting right while you are there. Use separate credentials, so the account your database servers hold cannot delete or expire backup objects. Where your storage supports immutability or object lock, switch it on, so a compromised server or a ransomware run cannot rewrite history.
The long-standing guidance of three copies, on two kinds of media, with one copy off-site, still holds up. What has changed is how little the off-site copy costs.
Encryption, keys and non-production data
Encrypting the database and encrypting the backups are related tasks, but they are not the same task, and the difference shows up during a recovery.
CYBERTEC PGEE provides transparent data encryption at every level: data at rest, client and server communication, and replication. Key management runs against external key management systems, and CYBERTEC's recommendation is to keep the key store off the database server. That separation is the right default, and it adds a question to your recovery plan: who can reach those keys, and how, if the primary site is gone? A complete backup set is worthless to a recovery team that cannot reach the keys.
The second question is who gets restored data. Copies of production that land in development or test are a common source of exposure. PGEE includes data masking and obfuscation, and masked dumps can be produced for non-production restores, so developers get a usable dataset without live customer records inside it.
The step most teams skip
A backup has one job: to restore. Until that has been demonstrated, the backup is a hypothesis.
A restore drill should answer five questions, and it should be timed:
Restore the most recent full backup plus the WAL needed, and measure the total elapsed time.
Run a point-in-time recovery to a timestamp your team picks in advance, not one the tool picks.
Start the application against the restored cluster and run a smoke test, so you know the data is usable and not merely present.
Restore a single table from a logical dump, since that is the failure people hit most often.
Follow the written runbook exactly, with the person who would actually be on call, and note every step that needed improvisation.
Cadence matters less than evidence. Quarterly is reasonable for stable systems, monthly for high-change environments, and any major infrastructure change should trigger one.
Failure patterns worth checking this week
Retention shorter than the time it takes to notice a problem. A weekly retention policy cannot help you if a bad delete is discovered three weeks later.
Backup storage that the database server can reach with credentials that can delete it.
WAL archiving that failed weeks ago, with no alert.
A restore procedure that lives in one engineer's memory.
Encryption keys that are not documented anywhere the recovery team can reach.
Logical dumps as the only protection for a database too large to restore from one.
A restore that has never been attempted on different hardware, or on a newer major version.
What Worlber does with this
Worlber is a Riyadh-based database consultancy working across PostgreSQL and SQL Server estates. Backup usually arrives as part of a wider conversation about availability, security and cost.
In practice, that work covers:
Designing and reviewing the backup and restore strategy, including RPO and RTO targets the business has actually agreed to.
Cluster architecture for high availability on Patroni with automatic failover, so availability and backup are designed together rather than bolted on separately.
Implementing pgBackRest, WAL-G or pg_probackup for physical backups and WAL archiving, with retention and off-site storage configured.
PGEE security features where regulated workloads need them, including transparent data encryption and masked dumps for non-production restores.
Aftercare once a system is live: performance tuning and issue resolution.
Support that includes emergency restore support, on tiers that range from business-hours cover up to 24x7, with response time SLAs that differ by tier.
Where a migration is involved, Worlber and CYBERTEC run migrations through a five-step method: scoping, pre-assessment, detailed assessment, execution with quality assurance, and aftercare. Dry runs validate the plan before cutover, and PostgreSQL to PGEE migrations are non-destructive with the source database left operational, so a working system stays available on both sides while the cutover is planned.
The short version
Put together, a workable setup looks like this: logical dumps for object-level recovery and portability, physical backups with archived WAL for point-in-time recovery, a tool that runs both reliably, copies stored off the database server under credentials that cannot delete them, keys the recovery team can reach, and a restore that has been timed by the people who would run it at 3am.
If any of those are missing, the gap is worth closing before the incident that finds it.
Contact Worlber to review your current PostgreSQL backup and restore plan, or to run a restore drill with your team.