So duplizieren Sie schemas in PostgreSQL

Ich habe eine Datenbank mit schema public und schema_A. Ich brauche ein neues schema zu erstellen schema_b mit der gleichen Struktur als schema_a.
Ich fand die Funktion unten, das problem ist, dass Sie nicht kopieren Sie die foreign key-Einschränkungen.

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

Wie kann ich Klonen/kopieren schema_A mit der foreign key-Einschränkungen?

InformationsquelleAutor der Frage Cristhian Boujon | 2013-08-13

19

Können Sie wahrscheinlich tun Sie es von der Kommandozeile aus ohne Verwendung von Dateien:

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

Hinweisdass dies sucht und ersetzt alle vorkommen der Zeichenfolge, ist Ihrem schema name, so es kann auf Ihre Daten.

InformationsquelleAutor der Antwort user1517922
10

Ich würde pg_dump - dump-schema ohne Daten:
```
-s
--schema-only
```
Dump nur die Objekt-Definitionen (schema), keine Daten.

Diese option ist das Gegenteil von --data-only. Es ist ähnlich, aber aus historischen Gründen nicht identisch mit der Angabe --section=pre-data --section=post-data.

(Nicht zu verwechseln mit der --schema - option, die verwendet das Wort "schema" in einer anderen Bedeutung.)

Auszuschließen Tabelle nur Daten für eine Teilmenge der Tabellen in der Datenbank, siehe --exclude-table-data.
```
pg_dump $DB -p $PORT -n $SCHEMA -s -f filename.pgsql
```
Dann benennen Sie das schema in die dump (suchen & ersetzen) und es wieder mit psql.
```
psql $DB -f filename.pgsql
```
Foreign key-Einschränkungen verweisen auf Tabellen in anderen schemas kopiert wurden, zeigen das gleiche schema.

Verweise auf Tabellen innerhalb des gleichen schema zeigen Sie auf die entsprechenden Tabellen innerhalb der schema kopiert.

InformationsquelleAutor der Antwort Erwin Brandstetter

Ein bisschen spät, um der Partei, aber einige sql hier könnte helfen, Sie auf Ihrem Weg:

get oid schema:

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

erhalten Tabelle oid:

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

bekommen foreign key-Einschränkungen:

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

Eine gute Ressource für PostgreSQL-system-Tabellen finden Sie hier. Darüber hinaus können Sie lernen, mehr über die internen Abfragen pg_dump macht zu sammeln, die dump-Informationen anzeigen, indem Sie es Quellcode.

Wahrscheinlich der einfachste Weg, um zu sehen, wie pg_dump sammelt alle Ihre Daten zu verwenden strace auf, etwa so:

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

Werden Sie noch haben, um durch den Morast der Aussagen, aber es sollte Ihnen helfen Stück zusammen ein Klonen-Werkzeug programmgesteuert und vermeiden, fallen zu lassen, um eine shell aufrufen pg_dump.

InformationsquelleAutor der Antwort rabbitt

1

Werde ich teilen eine Lösung für mein problem, das war das gleiche mit einem kleinen Zusatz. Ich musste clone-a-schema, erstellen Sie eine neue Datenbank-Benutzer, und weisen Sie den Besitz aller Objekte in dem neuen schema zu diesem Benutzer.

Für das folgende Beispiel nehmen wir an, dass das Referenz-schema genannt wird ref_schema und die Ziel-schema new_schema. Das Referenz-schema und alle Objekte im Besitz von einem Benutzer namens ref_user.

1. dump das Referenz-schema mit pg_dump:
```
pg_dump -n ref_schema -f dump.sql database_name
```
2. erstellen Sie einen neuen Datenbankbenutzer mit dem Namen new_user:
```
CREATE USER new_user
```
3. benennen Sie das schema ref_schema zu new_schema:
```
ALTER SCHEMA ref_schema RENAME TO new_schema
```
4. änderung der Inhaberschaft für alle Objekte im schema umbenannt, um den neuen Benutzer
```
REASSIGN OWNED BY ref_user TO new_user
```
5. wiederherstellen der ursprünglichen Referenz-schema-von der Müllkippe
```
psql -f dump.sql database_name
```
Ich hoffe jemand findet dies sehr hilfreich.

InformationsquelleAutor der Antwort Adam

Schreibe einen Kommentar

Du musst angemeldet sein, um einen Kommentar abzugeben.