38

Say I have two host servers s1 and s2. In both the servers i have a schema named n1, now i have made some changes to some of the tables present in schema n1 of s1. I want the same change to be done to schema n1 of server s2. what i am planning to do is to take a backup of the schema n1 of server s1 using pg_dump and restore in the server s2 using pg_restore. Now my question is ,since there is already the same schema n1 in the server s2 with the same set of tables. what the restore process will do? will it overwrite the existing tables or should i drop the existing schema of server s2 and restore it using the dump from server s1?

Karthik
  • 629
  • 2
  • 8
  • 12
  • 1
    Try the --clean option of pg_resotre if you want to drop objects before restore. https://www.postgresql.org/docs/current/static/app-pgrestore.html – Jayadevan Apr 25 '17 at 06:31

1 Answers1

82

If you use the --clean option of pg_restore, the old tables will be dropped before the new ones are created.

If you do not use the --clean option, you will get an error message that the table already exists, but pg_restore will continue processing unless you use the --exit-on-error option.

If your goal is to "merge" the existing data in the database and the data in the dump, you may be able to do that if you

  • create the dump with the options --inserts --on-conflict-do-nothing options

  • ignore the errors you get from the CREATE TABLE statements

  • have a primary key or unique constraint on all tables (that is necessary for INSERT ... ON CONFLICT TO NOTHING to work)

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • 1
    --clean option will drop only those tables which are already there? or all the tables present? I mean if suppose i have some additional tables in the existing schema will it be retained as such? – Karthik Apr 25 '17 at 08:49
  • 14
    It would only drop those tables that it tries to restore. Other tables would be left untouched. – Laurenz Albe Apr 25 '17 at 09:13
  • Thanks for the answer. I have a related question. What will happen to the tables that don't have a primary key if I use the --inserts --on-conflict-do-nothing options? Will they remain just untouched? – Taw HK Jul 28 '23 at 14:02
  • 1
    @TawHK If you don't have a primary or unique constraint, the rows will be added, and you can end up with duplicates. If you have tables without primary key, you are making a serious mistake. – Laurenz Albe Jul 28 '23 at 16:17
  • Got it. Thanks a lot. – Taw HK Jul 28 '23 at 17:55