Moving records from one database instance to another

Question

I have a PostgreSQL database instance located in EU region. I plan on introducing another PostgreSQL database instance located in a new geographical region.

As part this work, I am to migrate data for selected customers from a database instance in EU to a database instance in this new geo region and am seeking for advice.

On a surface, this boils down to doing the following work:

• given a specific accounts.id,
• find and copy the record from accounts table from EU database instance to accounts table in another region's database instance,
• identify and copy records across all tables that are related to given account record, recursively (e.g. as well as potentially from tables related to those tables...).

Effectively, having a specific DB record as starting point, I need to:

1. build a hierarchy, or rather a graph of DB records across all available tables, all directly (or indirectly) related to the "starting point" record (all possible relations, perhaps, could be established based on a foreign key constraints),
2. for each record found across all tables, generate a string containing an INSERT statement,
3. replay all INSERT statements, in a transaction, on another database instance.

It appears as if I might need to build a tool to do this kind of work. But before I do, I wonder:

• is there a common approach for implementing this?,
• if not, what might be a good starting point to approach this problem?

Answer 1

Indeed You need a whole proccess yo do this, i think that You should create a new schema to do the data Select i think functions could do the magic, then replicate that data.

They replication tool it's not that hard to configurate.

Here it's the link: https://www.postgresql.org/docs/current/runtime-config-replication.html!