如何复制PostgreSQL数据库中的数据量？

为了清楚地说明原理,这种解释假设如下：

>每个表都有一个名为“id”的bigserial主键列
>没有对表的唯一约束(主键除外)
>外键约束仅引用其他表的主键

将以下内容应用于数据库架构：

>确保架构中的表之间没有循环依赖关系.如果有,请选择会破坏此类依赖关系的外键约束并删除它们(稍后在手动处理受影响的字段后重新创建它们).
>按照拓扑顺序对表进行排序,并按顺序为每个表执行脚本排序(3)
>对于每个表< table_schema>.< table_name>从(2)执行：

/*
Creating a lookup table which contains ordered pairs (id_old,id_new). 
For every existing row in table <table_schema>.<table_name>,new row with id = new_id will be created and with all the other fields copied. Nextval of sequence <table_schema>.<table_name>_id_seq is fetched to reserve id for a new row.
*/
CREATE TABLE _l_<table_schema>_<table_name> AS 
SELECT id as id_old,nextval('<table_schema>.<table_name>_id_seq') as id_new
FROM <table_schema>.<table_name>;

/*
This part is for actual copying of table data with preserving of referential integrity.

Table <table_schema>.<table_name> has the following fields:
id - primary key
column1,...,columnN - fields in a table excluding the foreign keys; N>=0;
fk1,fkM - foreign keys; M>=0;

_l_<table_schema_fki>_<table_name_fki> (1 <= i <= M) - lookup tables of parent tables. We use LEFT JOIN because foreign key field could be nullable in general case.

*/
INSERT INTO <table_schema>.<table_name> (id,column1,columnN,fk1,fkM)
SELECT tlookup.id_new,t.column1,t.columnN,tablefk1.id_new,tablefkM.id_new
FROM <table_schema>_<table_name> t
INNER JOIN _l_<table_schema>_<table_name> tlookup ON t.id = tlookup.id_old
LEFT JOIN _l_<table_schema_fk1>_<table_name_fk1> tablefk1 ON t.fk1 = tablefk1.id_old
...
LEFT JOIN _l_<table_schema_fkM>_<table_name_fkM> tablefkM ON t.fkM = tablefkM.id_old;

>删除所有查找表.

(二)描述我的实施

>为了检查循环依赖关系,我查询了传递闭包(https://beagle.whoi.edu/redmine/projects/ibt/wiki/Transitive_closure_in_PostgreSQL)
>我实现了拓扑排序功能(从某些博客的t-sql移植).它对自动化很方便.
>我制作了一个代码生成器(在plpgsql中实现).这是一个带< table_schema>的函数.和< table_name>作为输入参数并返回该表中(I.2)所示的文本(SQL).通过以拓扑顺序连接每个表的函数结果,我生成了复制脚本.
>我对脚本进行了手动更改,以满足独特的约束和其他细微差别,而模板脚本没有涵盖这些细微差别.
>完成.脚本准备好在一个事务中执行.

当我有机会的时候,我会将我的代码“anonimize”一点点,然后把它放在github上并在这里放一个链接.