CODEWITHSUNDEEP
postgresqlamazon-web-servicesamazon-rds
Student
Student

Reputation: 684

How do I merge a local Postgres dump to AWS RDS without the -c flag?

I have version A of my PostgreSQL database in AWS RDS and version B on my local machine. DB B was created from a dump of DB A and has been updated locally. I am trying to merge the data I have locally to RDS.

A has data that B doesn't have and B has data that A doesn't have, hence I cannot use the -c flag in pg_dump (as in this question).

I export my database with pg_dump:

pg_dump -f dump.sql mydb

I try to import my database to RDS using psql:

psql -h ABC.XYZ.eu-west-2.rds.amazonaws.com -U myself mydb < dump.sql

This updates schema, i.e. adds columns I had locally to RDS, but fails to insert any values into these columns. I get the following error for every table that exists in DB A:

ERROR:  duplicate key value violates unique constraint "table_pkey"

As I understand from this question, my sequence may be out of sync, but this seems odd given I get it for every table in DB A, and DB B was created from a dump of DB A.

If I use the -c flag with pg_dump the merge works but all of the data in DB A that DB B did not have gets deleted.

How do I merge my local database into the remote one on AWS without losing data?

Upvotes: 0

Views: 207

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247665

If you don't need to modify any of the existing rows, you can use pg_dump's option --on-conflict-do-nothing, new in v12.

This will not add any new columns. pg_dump is not a schema migration tool.

Upvotes: 2

Related Questions