0

I am trying to

  1. create a snapshot of a PostgreSQL database (using pg_dump),
  2. do some random tests, and
  3. 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?

thor
  • 21,418
  • 31
  • 87
  • 173
  • You can run a `drop schema ...` or even `drop owned by ...` before restoring the dump –  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
  • Sure, just run the drop script using `psql` –  Mar 09 '16 at 15:49
  • 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

2 Answers2

0

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.

Rob K
  • 8,757
  • 2
  • 32
  • 36
0

You can use PostgreSQL Schemas.

Evgeniy Chekan
  • 2,615
  • 1
  • 15
  • 23