在PostgreSQL数据库之间移动具有冲突密钥的数据

Question

情况

我有两个数据库，它们曾经是彼此的直接副本，但现在它们包含了新的不同数据。

我想做什么

我想从数据库"中移动数据；SOURCE"；到数据库"；TARGET"；但问题是，这些表使用自动递增的键，而且由于两个数据库同时使用，所以TARGET中已经占用了很多ID，所以我不能只标识插入来自SOURCE的数据。

但在理论上，我们根本不能使用身份插入，而是让数据库负责分配新的ID。

更困难的是，我们有大约50个表，每个表都由外键连接。显然，外键也必须更改，否则它们将不再引用正确的东西。

让我们看一个非常简单的例子：

table Human {
id integer NOT NULL PK AutoIncremented
name varchar NOT NULL
parentId integer NULL FK -> Human.id 
}
table Pet {
id integer NOT NULL PK AutoIncremented
name varchar NOT NULL
ownerId integer NOT NULL FK -> Human.id 
}
SOURCE Human
Id      name      parentId
==========================
1       Aron      null
2       Bert      1
3       Anna      2
SOURCE Pet
Id      name      ownerId
==========================
1       Frankie   1
2       Doggo     2    
TARGET Human
Id      name      parentId
==========================
1       Armin      null
2       Cecil     1
TARGET Pet
Id      name      ownerId
==========================
1       Gatto     2 

假设我想将Aron、Bert、Anna、Frankie和Doggo转移到TARGET数据库。

但是，如果我们直接尝试插入它们，而不关心原始ID，外键将被篡改：

TARGET Human
Id      name      parentId
==========================
1       Armin     null
2       Cecil     1
3       Aron      null
4       Bert      1
5       Anna      2
TARGET Pet
Id      name      ownerId
==========================
1       Gatto     2 
2       Frankie   1
3       Doggo     2

安娜的父亲是塞西尔，狗狗的主人是塞西尔而不是伯特。伯特的父母是阿明而不是阿伦。

我希望它看起来是：

TARGET Human
Id      name      parentId
==========================
1       Armin     null
2       Cecil     1
3       Aron      null
4       Bert      3
5       Anna      4
TARGET Pet
Id      name      ownerId
==========================
1       Gatto     2 
2       Frankie   3
3       Doggo     4

想象一下，有50个类似的表，有1000行，所以我们必须自动化解决方案。

问题

有没有我可以使用的特定工具？

有没有一些简单的SQL逻辑可以精确地做到这一点？

我需要推出自己的软件来实现这一点吗(例如，一个连接到两个数据库的服务，读取EF中的所有内容，包括所有关系，并将其保存到另一个数据库(？我担心问题太多，而且很耗时。

Answer 1

是否有特定的工具？据我所知不是
是否有一些简单的SQL？不是很简单，但也不是那么复杂
你需要自己滚吗？也许，这取决于你是否认为你使用了SQL(balow(

我想没有直接的路径，正如您所注意到的，问题是重新分配FK值。以下内容为所有表添加了一列，可用于跨表。为此，我会使用uuid。然后，您可以从一个表集复制到除FK之外的另一个表集中。复制后，您可以在uuid上加入以完成FK。

-- establish a reference field unique across databases. 
alter table target_human add sync_id uuid default gen_random_uuid ();
alter table target_pet   add sync_id uuid default gen_random_uuid ();
alter table source_human add sync_id uuid default gen_random_uuid ();
alter table source_pet   add sync_id uuid default gen_random_uuid ();  

--- copy table 2 to table 1 except parent_id 
insert into target_human(name,sync_id)
select name, sync_id 
from source_human;

-- update parent id in table to prior parent in table 2 reasigning parent  
with conv (sync_parent, sync_child, new_parent) as 
( select h2p.sync_id sync_parent, h2c.sync_id sync_child, h1.id new_parent
from source_human h2c
join source_human h2p on h2c.parentid = h2p.id
join target_human h1  on h1.sync_id = h2p.sync_id 
) 
update target_human  h1
set parentid = c.new_parent
from conv c 
where h1.sync_id = c.sync_child;
----------------------------------------------------------------------------------------------- 
alter table target_pet alter column ownerId drop not null; 
insert into target_pet(name, sync_id) 
select name, sync_id 
from source_pet ;

with conv ( sync_pet,new_owner) as 
( select p2.sync_id, h1.id 
from source_pet p2 
join source_human h2  on p2.ownerid = h2.id
join target_human h1  on h2.sync_id = h1.sync_id
)  
update target_pet  p1
set ownerid = c.new_owner 
from conv c 
where p1.sync_id = c.sync_pet; 

alter table target_pet alter column ownerId set not null; 

请参阅演示。现在，您可以反转源表和目标表定义，以完成同步的另一端。如果需要，可以删除uuid列。但你可能想保留它们。如果你让它们不同步，你会再次这样做。你甚至可以更进一步，将UUID作为你的PK/FK，然后只复制数据，密钥将保持正确，但这可能需要将应用程序更新到修改后的DB结构。这并不涉及跨数据库的通信，但我认为您已经处理好了。您需要为每个集合重复，也许您可以编写一个脚本来生成它们。此外，我想与自己滚动相比，gotchas更少，耗时更少。这基本上是每个表集5个查询，但要清理当前的混乱，500个查询并不算多；