0

Situation

I have a Postgres database where most of the data belongs to a specific tenant. Most tables have an auto-incremented primary key, and the tables have a deeply nested web of foreign keys through each other's primary keys (the schema is generated by Entity Framework Core ORM).

This means each table has a similar structure:

ID: int PK
TenantID: int FK
Other fields

Where tenantId is the Primary Key of the Tenants table.

There are no foreign keys where the 2 records are for different tenants.

Deleting a tenant is as simple as delete from Tenants where ... as a lot of cascade deletes are set up.

Currently there is a second instance of the system being built, meaning that certain tenants are moved to the new system. I plan to perform the following for this:

  1. Dump-restore the database from the old to the new system
  2. Delete tenants from the new DB that are not intended to be moved yet
  3. Profit

What do I want to do

The tenants left in the old system will have to be moved later. They are not ready to be moved, as the 2 system will work as Test/Production and depending on the tenants they are either just testing the system or using it in production.

For moving to production it would be imperative to move all their data to the new system whenever they are ready for it.

Problem

Initially I thought that getting a dump for a specific tenant (with deleting all other data from the dump) and running it on the new system would be easy however from now on both systems will live side-by-side so both systems will generate auto-incremented primary keys for the same tables but they will mean different records.

I would need a way to map all those primary keys to ones that are not taken in the new database, keep all foreign key in sync.

Questions

Is there a specific tool I can utilize?

Is there some simple SQL logic to precisely do that?

Do I need to roll my own software to do this (e.g. a service that connects to both databases, read everything in EF with including all relations, and save it to the other DB)? I fear that there are too many gotchas and it is time consuming.

2
  • I am not sure I get this: you have two databases that are used, and you don't want them to use conflicting auto-generated primary keys? Apr 14, 2021 at 13:41
  • I have 2 databases used and I want to move a large portion of one to the other but there are conflicting auto-generated primary keys already. So the data being moved needs new primary keys. And since there are multiple tables the foreign keys in the moved data need also updates. There are 50 or so tables. Apr 15, 2021 at 9:26

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.