how to restore a postgresql database to the exact same state?

Question

I am trying to

create a snapshot of a PostgreSQL database (using pg_dump),
do some random tests, and
restore to the exact same state as the snapshot, and do some other random tests.

These can happen over many/different days. Also I am in a multi-user environment where I am not DB admin. In particular, I cannot create new DB.

However, when I restore db using

 gunzip -c dump_file.gz | psql my_db

changes in step 2 above remain.

For example, if I make a copy of a table:

 create table foo1 as (select * from foo);

and then restore, the copied table foo1 remains there.

Could some explain how can I restore to the exact same state as if step 2 never happened?

-- Update --

Following the comments @a_horse_with_no_name, I tried to to use

DROP OWNED BY my_db_user

to drop all my objects before restore, but I got an error associated with an extension that I cannot control, and my tables remain intact.

ERROR:  cannot drop sequence bg_gid_seq because extension postgis_tiger_geocoder requires it
HINT:  You can drop extension postgis_tiger_geocoder instead.

Any suggestions?

You can run a `drop schema ...` or even `drop owned by ...` before restoring the dump — a_horse_with_no_name, Mar 09 '16 at 15:38
@a_horse_with_no_name Thanks. Is there a way to do this in the command line? I need it in a scripting environment. I can drop everything I own, but some structures in my db is created by the admin. — thor, Mar 09 '16 at 15:46
It's a pity you can't create a new DB. Using template DBs is as fast and simple as it gets: `CREATE DATABASE mydb TEMPLATE mytemplate;` http://stackoverflow.com/a/12082038/939860 — Erwin Brandstetter, Mar 09 '16 at 16:50

score 0 · Answer 1 · answered Mar 09 '16 at 15:35

You have to remove everything that's there by dropping and recreating the database or something like that. pg_dump basically just makes an SQL script that, when applied, will ensure all the tables, stored procs, etc. exist and have their data. It doesn't remove anything.

score 0 · Answer 2 · answered Mar 09 '16 at 15:36

0

You can use PostgreSQL Schemas.

answered Mar 09 '16 at 15:36

Evgeniy Chekan

1,572
1
11
21

how to restore a postgresql database to the exact same state?

2 Answers2