How can I migrate data between 2 PostgreSQL clusters without auto-increment ID conflicts?

All we need is an easy explanation of the problem, so here it is.

I have 2 database clusters that operate independently. In the future, I may need to move the records for a customer from cluster 1 to 2. I have a bash script where I do server-side copying of the table records into CSV files from cluster 1 and then restore them into cluster 2.

The associations between data use sequential IDs. These IDs are not unique across the two instances. It seems that there are two possibilities:

  1. I am not using the best tools for my use-case. There exists other tools that permits me to do this with much less hassle. I have glossed over foreign data wrappers. I do not want to do a dump because I only need some records.

  2. I need to just suck it up and use UUIDs that are unique across my dataset.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

It’s option 2.
To get unique identifiers across independent databases, use UUIDs.

Else you need to COPY TO staging tables and use elaborate SQL scripts to, in case of conflict, propagate newly assigned serial numbers to all depending rows in other tables while inserting to target tables. And you may have to keep track of each translation from old to new serial IDs for future imports.

Method 2

to merge them you need to change the numbers so that they don’t clash.

eg: on one database you could double all the ids, and on the other double them all and add one.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply