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

Question

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
);

Monosaccharide

Answer 1

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.

Mug

Yes dump was successfully created but while i restoring db pg_restore --exit-on-error -h XXXXXX -U user -d db_name < test.dump, Not printing any output & restore has not done. – Monosaccharide

If nothing was restored, then all the rows inserted had a conflict, that is, they already existed at the destination. This will not perform a "merge", that is, update rows at the destination. You cannot get that with pg_dump. – Mug

is there way to restore database as incremental data using pg utility ? Please suggest – Monosaccharide

No, you'll have to write code that uses INSERT ... ON CONFLICT ... DO UPDATE. pg_dump won't do that. Perhaps there are utilities that do that, but I can't think of any right now, and asking for tools is off-topic. – Mug