14

commands:

bin/pg_dump -b -o -Fc -Z 0 -p 5333 -U user template1 -f db.dump
bin/pg_restore -c  -h localhost -p 5333 -U user -d template1 db.dump

steps followed:

  1. add new tables to DB and took dump

  2. delete the newly added tables

  3. try restore with dump file

  4. restore exited with code 1 but still tables are restore successfully.

error in pg_restore:
pg_restore: [archiver (db)] could not execute query: err-1: table "test1" does not exist
Command was: DROP TABLE public.test1;
WARNING: errors ignored on restore: 2

Is this the expected behavior for dump/restore feature with tables add/delete steps?

Ras Ram
  • 173
  • 1
  • 7
  • Try removing `-c` (`--clean`) option from `pg_restore`. If the table didn't exist already, then the attempted `DROP TABLE` produced by `-c` will result in an error. – IanB Aug 09 '18 at 08:05

1 Answers1

0

Using pg_restore can be a pain in the neck if the tables of your target DB only partially overlap with the tables in the dump.

Using the --clean flag only partially solves the problem but you may still encounter errors for non-existing tables.

In my opinion your best shot is to delete your target DB (or delete-cascade the target schema) and go with the restore.

A similar question you may want to look at: will pg_restore overwrite the existing tables?

Lucat
  • 2,242
  • 1
  • 30
  • 41