postgresql merge dump from production to test server; add only new records

user_8753279 0
Viewed 112

i want to merge dump from production to test server database, to test few things, til now i always dropped current database, and restored everything from dump, but problem is that database is rather large to do this everytime (~90gb in database, and ~25gb as sql file)

database have ~50 tables, and i want to update all tables, in which data have been updated or there is new data

im using postgres 9.5, command i use for dump is

PGPASSWORD="mypassword" pg_dump -U $user -h $host -d $dbname | gzip > $dest_dir/postgres_backup_$DATE.gz

and to restore i use either

cat postgres_file | sudo -u postgres psql $dbname 
#or 
gunzip -c postgres.gz | pg_restore -U $user -h $host -d $dbname

but when i use them without dropping current database, i get errors, because schemes and relations are already there and it gives errors like 

ERROR:  duplicate key value violates unique constraint
DETAIL:  Key (mykey)=(some_integer_value) already exists.
ERROR:  multiple primary keys for table "table_name" are not allowed
ERROR:  relation "some_relation" already exists

and it doesn't update database

i started to read about rules, but i lack knowledge in sql to make rule which would do what i want or is there other ways how to do it?

postgresql sql database
1 Answers

There is no way to selectively dump only new rows, because PostgreSQL has no way to know which ones are new.

But you can avoid deleting all the data upon restore. For that, upgrade to PostgreSQL v12 or better and use pg_dump with the options --on-conflict-do-nothing and --rows-per-insert=100. Then all the rows that already exist at the destination will be skipped.

You should upgrade anyway, 9.5 will go out of support this year.

user_64643080
Related