PostgreSQL database restore using pg_restore as incremental data without overwritten or drop existing tables

user_15644642 0
Viewed 770

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.

For backup and restore used these commands:

pg_dump -Fc -h XXXXX -U user -d dbname > test.dump

pg_restore  -h XXXXX -U user -d dbname < test.dump

But when I restore using pg_restore, I get this error:

pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 200; 1259 16435 TABLE table_name database_name
pg_restore: error: could not execute query: ERROR:  relation "address" already exists
Command was: CREATE TABLE public.table_name (
    id integer NOT NULL,
    status character varying(1) NOT NULL,
   table_name1 character varying(80) NOT NULL,
   table_name2 character varying(80),
   table_name3 character varying(80),
    location_id integer NOT NULL
);
pg-restore pg-dump restore postgresql
1 Answers

You could dump like this:

pg_dump -Fc -h XXXXX -U user --inserts --on-conflict-do-nothing --data-only -n n1 dbname > test.dump

Restoring such a dump will skip rows that have a primary key or unique constraint conflict with already existing rows. The option --on-conflict-do-nothing is available from v12 on.

user_64643080
Related