CODEWITHSUNDEEP
postgresqlrestoredatabase-restorepg-restore
UlfR
UlfR

Reputation: 4395

How to restore/rewind my PostgreSQL database

We do nightly full backups of our db and I then use that dump to create my own dev-db. The creation of the dev-db takes roughly 10 minutes so its scheduled every morning by cron before I get to work. So I can now work with an almost live db.

But when I'm testing things it would sometimes be convenient to rollback the full db or just some specific tables to the initial backup. Of course I could do the full recreation of the dev-db but that would make me wait for another 10 minutes before I could run the tests again.

So is there an easy way to restore/rewind the database/table to a specific point in time or from a dump?

I have tried to use pg_restore like this to restore specific tables:

pg_restore -d my-dev-db -n stuff -t tableA -t tableB latest-live-db.dump

I have tried with options like -cand --data-only also. But there seems to be several issues here that I did not foresee:

Edit: more things I tested/looked into:

Both of the alternatives above fail because I have several local databases running in the same cluster and that sums up to a lot of data on disk. There is no way to separate the databases this way! So the file copy action here will not give me any speed gain.

Upvotes: 2

Views: 2470

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125204

I'm assuming you are asking about a database not a cluster. The first thing that comes to my mind is to restore the backup to 2 different dbs, one with the dev_db name and the other with another name like dev_db_back. Then when you need a fresh db drop dev_db and rename dev_db_backup to dev_db with

drop database if exists dev_db;
alter database dev_db_backup rename to dev_db;

After that, to have another source to rename from, restore the backup to dev_db_backup again. This could be done by a script so the dropping, renaming and restoring would be automated. As dropping and renaming are instantaneous just start the script and the renaming is done without a need to wait for the new restore.

If it is common to need repeated restores in less 10 minutes intervals I think you can try to do what you are doing inside a transaction:

begin;
-- alter the db
-- test the alterations
commit; -- or ...
-- rollback;

Upvotes: 1

Related Questions