如何在 PostgreSQL 中复制模式 [英] How to duplicate schemas in PostgreSQL

问题描述

我有一个带有 public 和 schema_A 架构的数据库.我需要创建一个与 schema_a 具有相同结构的新模式 schema_b.找到下面这个函数，问题是它没有复制外键约束.

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?

How can I clone/copy schema_A with the foreign key constraints?

推荐答案

你或许可以在不使用文件的情况下从命令行完成:

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屋！