如何在PostgreSQL中复制模式 [英] How to duplicate schemas in PostgreSQL
本文介绍了如何在PostgreSQL中复制模式的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个数据库,该数据库的架构为 public
和 schema_A
。我需要创建一个新架构 schema_b
,其结构与 schema_a
相同。
我发现下面的函数,问题是它不复制外键约束。
I have a database with schema public
and schema_A
. I need to create a new schema schema_b
with the same structure than schema_a
.
I found the function below, the problem is that it does not copy the foreign key constraints.
CREATE OR REPLACE FUNCTION clone_schema(source_schema text, dest_schema text)
RETURNS void AS
$BODY$
DECLARE
object text;
buffer text;
default_ text;
column_ text;
BEGIN
EXECUTE 'CREATE SCHEMA ' || dest_schema ;
-- TODO: Find a way to make this sequence's owner is the correct table.
FOR object IN
SELECT sequence_name::text FROM information_schema.SEQUENCES WHERE sequence_schema = source_schema
LOOP
EXECUTE 'CREATE SEQUENCE ' || dest_schema || '.' || object;
END LOOP;
FOR object IN
SELECT table_name::text FROM information_schema.TABLES WHERE table_schema = source_schema
LOOP
buffer := dest_schema || '.' || object;
EXECUTE 'CREATE TABLE ' || buffer || ' (LIKE ' || source_schema || '.' || object || ' INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING DEFAULTS)';
FOR column_, default_ IN
SELECT column_name::text, REPLACE(column_default::text, source_schema, dest_schema) FROM information_schema.COLUMNS WHERE table_schema = dest_schema AND table_name = object AND column_default LIKE 'nextval(%' || source_schema || '%::regclass)'
LOOP
EXECUTE 'ALTER TABLE ' || buffer || ' ALTER COLUMN ' || column_ || ' SET DEFAULT ' || default_;
END LOOP;
END LOOP;
END;
$BODY$ LANGUAGE plpgsql
如何克隆/复制具有外键约束的schema_A
?
推荐答案
您可能可以在命令行中使用它而无需使用文件:
You can probably do it from the command line without using files:
pg_dump -U用户--schema ='fromschema'数据库| sed’s / fromschmea / toschema / g | psql -U用户-d数据库
注意,这将搜索并替换所有出现的字符串架构名称,因此它可能会影响您的数据。
Note that this searches and replaces all occurrences of the string that is your schema name, so it may affect your data.
这篇关于如何在PostgreSQL中复制模式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文