Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

will pg_restore overwrite the existing tables?

Tags:

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?

like image 246
Karthik Avatar asked Apr 25 '17 06:04

Karthik


People also ask

Does pg_dump overwrite the file?

Restoring the data from pg_dump doesn't overwrite the data but it appends the data to the original database. Bookmark this question.

Does Pg_restore create database?

It will NOT create that database. It creates a database with the name from the archive you are restoring and restores the data into that database.

What is Pg_restore in Postgres?

Description. pg_restore is a utility for restoring a PostgreSQL database from an archive created by pg_dump in one of the non-plain-text formats. It will issue the commands necessary to reconstruct the database to the state it was in at the time it was saved.

How long does a Pg_restore take?

During pg_restore , the database size is increasing at a rate of 50 MB/minute estimated using the SELECT pg_size_pretty(pg_database_size()) query. At this rate, it will take approximately 130 hours to complete the restore which is a very long time.


1 Answers

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.

like image 149
Laurenz Albe Avatar answered Sep 23 '22 19:09

Laurenz Albe