CODEWITHSUNDEEP
postgresql
lollercoaster
lollercoaster

Reputation: 16513

pg_dump and pg_restore (DROP IF EXISTS)

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.

Upvotes: 31

Views: 48837

Answers (3)

not2savvy
not2savvy

Reputation: 4243

You can use --clean --if-exists when restoring the data without the need for having used any specific option when the backup was created.

You don't need to drop and recreate the whole database, nor does it require that --clean has been used in the backup.

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

--clean makes pg_restore drop all objects first, and --if-exists prevents that non-existent objects cause a failure.

Note that --if-exists is not listed as a separate option in the postgres docs for pg_restore, but it is mentioned in the description of the --clean option:

-c
--clean

Clean (drop) database objects before recreating them. (Unless --if-exists is used, this might generate some harmless error messages, if any objects were not present in the destination database.)

Upvotes: 16

Cassio Seffrin
Cassio Seffrin

Reputation: 8570

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

Upvotes: 39

MatheusOl
MatheusOl

Reputation: 11835

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

Upvotes: 21

Related Questions