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

Question

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.

Answer 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.

Answer 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.