will pg_restore overwrite the existing tables?

postgresql psql postgresql-9.5 pg-dump pg-restore
Karthik picture Karthik · Apr 25, 2017 · Viewed 20.9k times · Source

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?

Answer

Laurenz Albe picture Laurenz Albe · Apr 25, 2017

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.

Related questions

PostgreSQL "DESCRIBE TABLE"

How do you perform the equivalent of Oracle's DESCRIBE TABLE in PostgreSQL (using the psql command)?

Read More
Run a PostgreSQL .sql file using command line arguments

I have some .sql files with thousands of INSERT statements in them and need to run these inserts on my PostgreSQL database in order to add them to a table. The files are that large that it is impossible to …

Read More
How to switch databases in psql?

In MySQL, I used use database_name; What's the psql equivalent?

Read More