如何在 PostgreSQL 中复制模式

How to duplicate schemas in PostgreSQL

提问by Cristhian Boujon

I have a database with schema publicand schema_A. I need to create a new schema schema_bwith the same structure than schema_a. I found the function below, the problem is that it does not copy the foreign key constraints.

我有一个带有架构public和schema_A. 我需要创建一个schema_b与schema_a. 我找到了下面的函数，问题是它没有复制外键约束。

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

How can I clone/copy schema_Awith the foreign key constraints?

如何schema_A使用外键约束克隆/复制？

回答by user1517922

You can probably do it from the command line without using files:

您可以在不使用文件的情况下从命令行执行此操作：

pg_dump -U user --schema='fromschema' database | sed 's/fromschmea/toschema/g' | psql -U user -d database

pg_dump -U user --schema='fromschema' database | sed 's/fromschmea/toschema/g' | psql -U user -d database

Notethat this searches and replaces all occurrences of the string that is your schema name, so it may affect your data.

请注意，这会搜索并替换作为您的架构名称的所有出现的字符串，因此它可能会影响您的数据。

回答by Erwin Brandstetter

I would use pg_dumpto dump the schema without data:

我会pg_dump用来转储没有数据的模式：

-s
--schema-only

Dump only the object definitions (schema), not data.

This option is the inverse of --data-only. It is similar to, but for historical reasons not identical to, specifying --section=pre-data --section=post-data.

(Do not confuse this with the --schemaoption, which uses the word "schema" in a different meaning.)

To exclude table data for only a subset of tables in the database, see --exclude-table-data.

-s
--schema-only

仅转储对象定义（架构），而不转储数据。

此选项是 的反选项--data-only。它类似于但由于历史原因与指定--section=pre-data --section=post-data.

（不要将此与--schema选项混淆，后者以不同的含义使用“模式”一词。）

要仅排除数据库中一部分表的表数据，请参阅--exclude-table-data。

pg_dump $DB -p $PORT -n $SCHEMA -s -f filename.pgsql

Then rename the schema in the dump (search & replace) and restore it with psql.

然后重命名转储中的架构（搜索和替换）并使用psql.

psql $DB -f filename.pgsql

Foreign key constraintsreferencing tables in otherschemas are copied to point to the same schema.
References to tables within the sameschema point to the respective tables within the copied schema.

引用其他模式中的表的外键约束被复制以指向相同的模式。
对同一架构内的表的引用指向复制架构内的相应表。

回答by Adam

I will share a solution for my problem which was the same with a small addition. I needed to clone a schema, create a new database user and assign ownership of all objects in the new schema to that user.

我将为我的问题分享一个解决方案，该解决方案与一个小补充相同。我需要克隆一个模式，创建一个新的数据库用户并将新模式中所有对象的所有权分配给该用户。

For the following example let's assume that the reference schema is called ref_schemaand the target schema new_schema. The reference schema and all the objects within are owned by a user called ref_user.

对于以下示例，我们假设参考架构称为ref_schema，目标架构称为new_schema。引用模式和其中的所有对象都归名为ref_user的用户所有。

1. dump the reference schema with pg_dump:

1. 使用 pg_dump 转储参考模式：

pg_dump -n ref_schema -f dump.sql database_name

2. create a new database user with the name new_user:

2. 创建一个名为new_user的新数据库用户：

CREATE USER new_user

3. rename the schema ref_schemato new_schema:

3. 将架构ref_schema重命名为new_schema：

ALTER SCHEMA ref_schema RENAME TO new_schema

4. change ownership of all objects in the renamed schema to the new user

4. 将重命名模式中所有对象的所有权更改为新用户

REASSIGN OWNED BY ref_user TO new_user

5. restore the original reference schema from the dump

5.从转储中恢复原始参考模式

psql -f dump.sql database_name

I hope someone finds this helpful.

我希望有人觉得这有帮助。

回答by rabbitt

A bit late to the party but, some sql here could help you along your way:

聚会有点晚了，但是这里的一些 sql 可以帮助你一路走来：

get schema oid:

获取模式 oid：

namespace_id = SELECT oid 
                  FROM pg_namespace 
                 WHERE nspname = '<schema name>';

get table's oid:

获取表的 oid：

table_id = SELECT relfilenode 
                FROM pg_class 
               WHERE relnamespace = '<namespace_id>' AND relname = '<table_name>'

get foreign key constraints:

获取外键约束：

SELECT con.conname, pg_catalog.pg_get_constraintdef(con.oid) AS condef 
  FROM pg_catalog.pg_constraint AS con 
  JOIN pg_class AS cl ON cl.relnamespace = con.connamespace AND cl.relfilenode = con.conrelid 
 WHERE con.conrelid = '<table_relid>'::pg_catalog.oid AND con.contype = 'f';

A good resource for PostgreSQL system tables can be found here. Additionally, you can learn more about the internal queries pg_dumpmakes to gather dump information by viewing it's source code.

可以在此处找到有关 PostgreSQL 系统表的良好资源。此外，您可以pg_dump通过查看其源代码来了解更多关于内部查询用于收集转储信息的信息。

Probably the easiest way to see how pg_dumpgathers all your data would be to use straceon it, like so:

查看如何pg_dump收集所有数据的最简单方法可能是对其使用strace，如下所示：

$ strace -f -e sendto -s8192 -o pg_dump.trace pg_dump -s -n <schema>
$ grep -oP '(SET|SELECT)\s.+(?=\0)' pg_dump.trace

You'll still have to sort through the morass of statements but, it should help you piece together a cloning tool programmatically and avoid having to drop to a shell to invoke pg_dump.

您仍然需要对一堆语句进行分类，但是，它应该可以帮助您以编程方式拼凑一个克隆工具，并避免不得不进入 shell 来调用pg_dump.

回答by Ilya Dorfman

Just ran into same. Sometimes I am missing remap_schema :)
The problem - neither from above addresses the Fc - standard format which is crucial for large schemas.
So I came up with something which uses it :
Pseudo code below - should work.
Requires rename of source for duration of pg_dump which, of course, might not be an option :(
Source :

刚碰到一样。有时我会遗漏 remap_schema :)
问题 - 以上都没有解决 Fc - 标准格式，这对于大型模式至关重要。
所以我想出了一些使用它的东西：
下面的伪代码 - 应该可以工作。
需要在 pg_dump 的持续时间内重命名源代码，当然，这可能不是一个选项:(
源代码：

pg_dump --pre-data in sql format
psql rename sosurce to target
pg_dump -Fc --data-only
psql rename back
pg_dump --post-data in sql format

Target:

目标：

sed source_schema->target_schema pre-data sql |psql
pg_restore Fc dump
sed source_schema->target_schema post-data sql |psql

sed above usually will include any other manipulations ( say different user names between source and target ) But it will be way much faster as data will not be part of the file

上面的 sed 通常会包含任何其他操作（比如源和目标之间的不同用户名）但它会快得多，因为数据不是文件的一部分