Comment faire pour dupliquer des schémas dans PostgreSQL

J'ai une base de données avec le schéma public et schema_A. J'ai besoin de créer un nouveau schéma schema_b avec la même structure que schema_a.
J'ai trouvé la fonction ci-dessous, le problème est qu'il ne copie pas les contraintes de clé étrangère.

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

Comment puis-je clone/copie schema_A avec les contraintes de clé étrangère?

Si vous voulez cloner le schéma à l'aide de requêtes SQL, découvrez this réponse.

OriginalL'auteur Cristhian Boujon | 2013-08-13

20

Vous pouvez probablement le faire à partir de la ligne de commande sans l'aide de fichiers:

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

Note que cette recherche et remplace toutes les occurrences de la chaîne qui est votre nom de schéma, de sorte il peut affecter vos données.

Comment puis-je éviter d'affecter les données?

OriginalL'auteur user1517922
13

Je voudrais utiliser pg_dump vider le schéma sans données:
```
-s
--schema-only
```
Dump seulement l'objet de définitions (schéma), pas de données.

Cette option est l'inverse de --data-only. Il est similaire, mais pour des raisons historiques, n'est pas identique à, en précisant --section=pre-data --section=post-data.

(À ne pas confondre avec le --schema option, qui utilise le mot "schéma" dans un sens différent.)

À exclure les données de la table pour qu'un sous-ensemble de tables dans la base de données, voir --exclude-table-data.
```
pg_dump $DB -p $PORT -n $SCHEMA -s -f filename.pgsql
```
Puis renommez le schéma de l'image (recherche & remplacer) et de le restaurer avec psql.
```
psql $DB -f filename.pgsql
```
Les contraintes de clé étrangère font référence à des tables dans autres schémas sont copiés sur le point de le même schéma.

Des références à des tableaux à l'intérieur de la même schéma de point pour les tableaux correspondants dans la copie de ce schéma.

Oui, je vous remercie! mais je voulais éviter de travailler avec des fichiers. Je suis à la recherche d'un moyen rapide de le faire.

OriginalL'auteur Erwin Brandstetter

Un peu en retard à la fête, mais, certains sql ici pourrait vous aider le long de votre chemin:

obtenir schéma oid:

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

obtenir de la table oid:

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

obtenir les contraintes de clé étrangère:

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

Une bonne ressource pour PostgreSQL tables système peut être trouvé ici. En outre, vous pouvez en savoir plus sur les requêtes internes pg_dump permet de recueillir des informations de vidage par la visualisation, c'est le code source.

Probablement le moyen le plus facile de voir comment pg_dump regroupe toutes vos données serait d'utiliser strace sur elle, comme suit:

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

Vous aurez encore à trier à travers le fatras de déclarations, mais, il devrait vous aider à élaborer un outil de clonage par programme et éviter d'avoir à déposer d'une coquille d'invoquer pg_dump.

Voici un python classe je viens d'écrire à réaliser un schéma de clone (y compris la copie de données) à l'aide de tous les composants de SQL et pas les bombardements de pg_dump: https://gist.github.com/rabbitt/97f2c048d9e38c16ce62

OriginalL'auteur rabbitt

3

Je vais partager une solution à mon problème qui est le même avec un petit plus. J'avais besoin de cloner un schéma, de créer une nouvelle base de données de l'utilisateur et d'en céder la propriété de tous les objets dans le nouveau schéma de l'utilisateur.

Dans l'exemple suivant, on suppose que le schéma de référence est appelé ref_schema et le schéma cible new_schema. La référence de schéma et de tous les objets à l'intérieur sont détenus par un utilisateur appelé ref_user.

1. vidage de la référence de schéma avec pg_dump:
```
pg_dump -n ref_schema -f dump.sql database_name
```
2. créer un nouvel utilisateur de base de données avec le nom de new_user:
```
CREATE USER new_user
```
3. renommer le schéma ref_schema à new_schema:
```
ALTER SCHEMA ref_schema RENAME TO new_schema
```
4. modifier la propriété de tous les objets dans la renommé de schéma pour le nouvel utilisateur
```
REASSIGN OWNED BY ref_user TO new_user
```
5. restaurer la référence d'origine de schéma à partir de la décharge
```
psql -f dump.sql database_name
```
J'espère que quelqu'un trouve cela utile.

j'ai besoin de copier sans données. Quelle est la solution. Seulement besoin de structures

OriginalL'auteur Adam

Vous devez vous connecter pour publier un commentaire.