pg_dump and pg_restore (DROP IF EXISTS)

Question

I want a completely shell-based solution for testing my database by allowing my to restore it to a consistent state by running a terminal command.

I dump my database like so:

pg_dump -F c -b -f -i -h domain.edu -p 5432 -n myschema -U me mydatabase -W -f mydump.sql

Then I want to restore it like so:

pg_restore -h domain.edu -p 5432 -U me -d mydatabase -W mydump.sql

but it doesn't work because I get tons of errors like these:

pg_restore: [archiver (db)] could not execute query: ERROR:  constraint "settings_person_id_fkey" for relation "settings" already exists
    Command was: ALTER TABLE ONLY settings
    ADD CONSTRAINT settings_learner_id_fkey FOREIGN KEY (person_id) REFERENCES pe...

basically there are just a bunch of things that need to be dropped first before being re-added (DROP TABLE <whatever> IF EXISTS, same with types, etc).

How can I do this with Postgres? I don't want to use the psql console, only the linux terminal.

Answer 1

If you want to restore the database to a consistent state, I recommend you to drop and recreate it before restoring the dump:

dropdb -h domain.edu -U me mydatabase
createdb -h domain.edu -U me -T template0 mydatabase # adjust encoding/locale if necessary
pg_restore -h domain.edu -p 5432 -U me -d mydatabase -W mydump.sql

Answer 2

You could use --clean in pg_dump to first drop objects:

pg_dump --clean

Backup

pg_dump --clean -U user database > database.sql

Restore

psql -U user -d database -f database.sql