Restoring a PostgreSQL backup without overwriting the live database

Thursday, 4.30 p.m. A deploy at 2.10 p.m. contained a migration that set paid_at in invoices to NULL for 2,300 rows. Since then, paying customers have been getting reminders. The 3 a.m. backup is there, and the reflex is obvious: pg_restore --clean -d shop backup.dump against production. The 2,300 rows would be fixed – and everything since 3 a.m. gone: 412 new orders, 90 sign-ups, every payment of the day.

Restoring a PostgreSQL backup means writing a database’s saved state back – and the most important decision is where to. Search for “restore postgresql backup” and you mostly find the command, rarely this question. The safe answer is almost always: into a new database.

What kinds of PostgreSQL backup are there?

The PostgreSQL documentation names three approaches; they decide what you can restore:

• SQL dump (logical). pg_dump exports exactly one database, consistently even while it is in use. The custom format (-Fc) is compressed and can be restored selectively with pg_restore; a plain SQL dump goes into psql. Roles are missing – only pg_dumpall exports them.
• File-system backup or snapshot (physical). A copy of the data files or the volume – always the whole cluster with every database.
• Continuous archiving (point-in-time recovery). A base backup plus archived WAL: recovery to any moment since the base backup – again only for the whole cluster.

So only a logical backup can be restored into a single database.

Why does a restore belong in a new database?

• Production keeps running. Orders, payments and sign-ups since the backup are kept.
• You can compare. Old and new sit side by side, showing which rows are really affected.
• You only bring back what is missing. Usually one table or a few thousand rows.
• A failure costs nothing. If the restore breaks off, an empty database is broken, not production.
• You switch over deliberately. If the old state is really better, switch when you choose.

How do I restore a backup into a new database with pg_restore?

1. Check what you have. pg_restore -l backup.dump lists an archive’s contents. If that fails, it is probably a plain SQL dump for psql.
2. Create an empty database. CREATE DATABASE shop_restore TEMPLATE template0; – template0, per the documentation, carries no local additions.
3. Restore. pg_restore --no-owner --no-privileges -d shop_restore -j 4 backup.dump. --no-owner makes the connecting user the owner of every object, --no-privileges leaves out the GRANTs, -j 4 loads in parallel (custom and directory format only), -t invoices restores a single table.
4. Verify. Row counts and SELECT max(created_at) FROM orders; show which state you have.

Two options do not belong here: --clean, meant for overwriting an existing database, and --create, which only uses -d to issue CREATE DATABASE and restores into the name stored in the archive – on the same server, production, dropped first if --clean is added.

Owners and privileges: why does the read user suddenly see nothing?

Without --no-owner, pg_restore restores the old owners, which per the documentation fails unless you connect as a superuser or as that very owner. With --no-owner, everything belongs to whoever restores. The consequences:

• If the app connects as another user, it gets “permission denied” until it is granted rights or connects as the new owner.
• --no-privileges brings no GRANTs along, not even your reporting user’s – that user sees nothing.
• ALTER DEFAULT PRIVILEGES applies only to objects a given role creates in the future. Set them again for a new owner, or the read user goes blind after the next migration.

More on roles and default privileges per service in the article One database per service.

Copy back or switch over?

Usually only part is broken, so you copy back – with a catch: a PostgreSQL connection sees only one database, so production and the restored copy cannot be joined. The route is a file:

1. Restored database: \copy (SELECT id, paid_at FROM invoices) TO 'fix.csv' CSV
2. Production: create a helper table fix and fill it with \copy fix FROM 'fix.csv' CSV.
3. In one transaction run UPDATE invoices i SET paid_at = f.paid_at FROM fix f WHERE i.id = f.id AND i.paid_at IS NULL;, check the row count, then COMMIT.

If the whole database is unusable, switch over: point the app’s DATABASE_URL at the new database and restart; whatever reached the old one since the backup you carry across deliberately. Then clean up: keep the old database a few days as evidence, drop it, and delete downloaded dumps with customer data from laptops.

What does Scaleway’s Managed Database for PostgreSQL offer?

• Automatic backups on Block Storage are snapshots of the whole instance, daily by default and kept for seven days; a restore creates a new, separately billed instance.
• Logical backups of one database are made manually on Block Storage (up to 585 GB of storage, per the documentation); on local storage the automatic backups are logical. When restoring, the console asks: original or new database? Choose the new one.
• Export: “Prepare export”, then “Download”. PostgreSQL backups come in PostgreSQL’s compressed format; pg_restore -l tells you whether it is an archive.

How Clusterward does it

For databases Clusterward manages (see Managed databases), the instance page shows a “Backups” card: automatic snapshots with an editable schedule, and database backups you take with “Back up now” and keep for 7, 30 or 90 days. Downloads use a short-lived link Clusterward never stores; snapshots are restored in the linked Scaleway console.

“Restore into a new database” never overwrites the running database: once you type the database name, Clusterward creates db_<name>_r<date> next to it, with its own user usr_<name>_r<date> and a generated password, and has Scaleway restore the backup into it. When Scaleway reports done, Clusterward applies the new user’s privileges again – the restore creates tables after the first grant. You compare read-only in the SQL console; to switch, point the app at the new database or assign it to the service and restart. The old one stays until you drop it. More under Backups & recovery.

Conclusion

Restoring a backup over the live database fixes the mistake and destroys everything since. Restored into a new database, it becomes a source: compare, copy back selectively, switch over deliberately. Plan for owners and privileges before the next reminder goes out.

Want to rehearse your restore? Tell us how your databases are backed up today. We’ll show you a restore that never risks production. Ask a question →

Sources and further reading

• PostgreSQL docs: Backup and Restore
• PostgreSQL docs: pg_restore
• PostgreSQL docs: ALTER DEFAULT PRIVILEGES
• Scaleway: How to manage backups for Database Instances
• Scaleway: How to manage snapshots