Как дублировать схемы в PostgreSQL

У меня есть база данных со схемой 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 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 с ограничениями внешнего ключа?

Ответ 1

Возможно, вы можете сделать это из командной строки без использования файлов:

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

Обратите внимание, что это ищет и заменяет все вхождения строки, которая является вашим именем схемы, поэтому может повлиять на ваши данные.

Ответ 2

Я бы использовал pg_dump, чтобы выгрузить схему без данных:

-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

Затем переименуйте схему в дампе (поиск и замену) и восстановите ее с помощью psql.

psql $DB -f filename.pgsql

Ограничения внешнего ключа, ссылающиеся на таблицы в других схемах, копируются, чтобы указывать на одну и ту же схему.
Ссылки на таблицы в той же схеме указывают на соответствующие таблицы в скопированной схеме.

Ответ 3

Немного поздно для вечеринки, но некоторые sql здесь могут помочь вам по пути:

получить схему oid:

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

получить таблицу oid:

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

получить ограничения внешнего ключа:

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';

Хороший ресурс для системных таблиц PostgreSQL можно найти здесь. Кроме того, вы можете узнать больше о внутренних запросах pg_dump заставляет собирать данные дампа, просматривая его исходный код.

Вероятно, самый простой способ увидеть, как 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

Вам все равно придется разбираться в суматохе высказываний, но это поможет вам скомпоновать инструмент клонирования программно и не нужно бросать в оболочку для вызова pg_dump.