Copie complète d'une table postgres avec SQL

AVERTISSEMENT: Cette question est similaire à la dépassement de pile question ici , mais aucune de ces réponses pour mon problème, comme je l'expliquerai plus tard.

j'essaie de copier une grande table (~40M lignes, 100+ colonnes) dans postgres où beaucoup de colonnes sont indexées. Actuellement j'utilise ce morceau de SQL: 

CREATE TABLE <tablename>_copy (LIKE <tablename> INCLUDING ALL);
INSERT INTO <tablename>_copy SELECT * FROM <tablename>;

cette méthode a deux issues:

  1. It Ajoute les indices avant l'ingestion de données, il faudra donc beaucoup plus de temps que la création de la table sans indices et ensuite l'indexation après la copie de toutes les données.
  2. cela ne copie pas correctement les colonnes de style "SERIAL". Au lieu de créer un nouveau "compteur" sur le nouveau tableau, il définit la valeur par défaut de la colonne dans la nouvelle table au comptoir du passé table, sens de ne pas incrémenter comme des lignes sont ajoutées.

la taille de la table fait indexer un problème en temps réel. Il est également impossible de vider un fichier pour le ré-ingérer. Je n'ai pas aussi l'avantage d'une ligne de commande. J'ai besoin de le faire en SQL.

ce que je voudrais faire est soit faire une copie exacte avec une commande miracle, ou si ce n'est pas possible, pour copier la table avec toutes les contraintes mais sans indices, et s'assurer qu'ils sont les contraintes "en esprit" (alias un nouveau compteur pour une colonne série). Puis copiez toutes les données avec un SELECT * puis copier sur tous les indices.

Sources

  1. question de débordement de pile sur la copie de base de données : ce n'est pas ce que je demande pour trois raisons

    • il utilise l'option de ligne de commande pg_dump -t x2 | sed 's/x2/x3/g' | psql et dans ce paramètre Je n'ai pas accès à la ligne de commande
    • il crée les indices pré données ingérer, qui est lent
    • il ne met pas à jour les colonnes série correctement comme preuve par default nextval('x1_id_seq'::regclass)

  2. méthode pour réinitialiser la valeur de séquence pour une table de postgres : c'est grand, mais malheureusement il est très manuel.

48
demandé sur Community 2011-07-06 23:41:11

6 réponses

Eh bien, vous allez devoir faire certaines de ces choses à la main, malheureusement. Mais il peut être fait à partir de quelque chose comme psql. La première commande est assez simple: 

select * into newtable from oldtable

cela créera newtable avec les données de oldtable mais pas les index. Ensuite, vous devez créer les index et les séquences etc sur votre propre. Vous pouvez obtenir une liste de tous les index sur une table avec la commande: 

select indexdef from pg_indexes where tablename='oldtable';

ensuite, Lancez psql-E pour accéder à votre base de données et utiliser \d l'ancienne table. Vous pouvez ensuite mangle ces deux requêtes pour obtenir les informations sur les séquences: 

SELECT c.oid,
  n.nspname,
  c.relname
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname ~ '^(oldtable)$'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;

SELECT a.attname,
  pg_catalog.format_type(a.atttypid, a.atttypmod),
  (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
   FROM pg_catalog.pg_attrdef d
   WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
  a.attnotnull, a.attnum
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = '74359' AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum;

remplacez ce 74359 ci-dessus par l'IOD que vous obtenez de la requête précédente.

47
répondu Scott Marlowe 2011-07-07 16:19:46

la fonction create table as de PostgreSQL pourrait bien être la réponse recherchée par L'OP.

https://www.postgresql.org/docs/9.5/static/sql-createtableas.html 

create table my_table_copy as
  select * from my_table

cela créera une table identique avec les données.

ajouter with no data copie le schéma sans les données. 

create table my_table_copy as
  select * from my_table
with no data

cela créera la table avec toutes les données, mais sans index, déclencheurs, etc.

create table my_table_copy (like my_table including all)

la syntaxe create table like inclura tous les triggers, index, contraintes, etc. Mais ne pas inclure des données.

38
répondu Phill 2017-01-20 17:09:40

la "commande miracle" la plus proche est quelque chose comme 

pg_dump -t tablename | sed -r 's/\btablename\b/tablename_copy/' | psql -f -

en particulier, cela prend soin de créer les index après avoir chargé les données de table.

mais cela ne réinitialise pas les séquences; vous devrez le script vous-même.

14
répondu Peter Eisentraut 2011-07-07 12:41:30

avertissement:

toutes les réponses qui utilisent pg_dump et n'importe quelle expression régulière pour remplacer le nom de la table source sont vraiment dangereuses. Et si vos données contiennent le substrat que vous essayez de remplacer? Vous finirez par la modification de vos données!

je propose une solution en deux passes:

  1. éliminer les lignes de données de la sauvegarde à l'aide de certaines données spécifiques regexp
  2. effectuer une recherche et remplacer sur les lignes restantes

voici un exemple écrit en rubis: 

ruby -pe 'gsub(/(members?)/, "\1_copy_20130320") unless $_ =~ /^\d+\t.*(?:t|f)$/' < members-production-20130320.sql > copy_members_table-20130320.sql

dans ce qui précède, j'essaie de copier la table" membres "dans"members_copy_20130320". Mon regexp spécifique aux données est / ^\d+ \ T.*(?: t|f)$/

le type de solution ci-dessus fonctionne pour moi. Caveat emptor...

edit:

OK, en voilà un autre chemin dans la syntaxe de pseudo-shell pour les gens de regexp-averse:

  1. pg_dump-s-t mytable mydb > mytable_schema.sql
  2. recherche-et-remplacer le nom de la table dans mytable_schema.sql > mytable_copy_schema.sql

  3. psql-F mytabl_copy_schema.sql mydb

  4. pg_dump-a-t mytable mydb > mytable_data.sql

  5. remplacer "mytable" dans la peu de déclaration SQL précédant la section des données
  6. psql-F mytable_data.sql mydb
2
répondu Tomek 2013-03-20 02:40:34

apparemment vous voulez "reconstruire" une table. Si vous voulez seulement reconstruire une table, pas la copier, alors vous devriez utiliser CLUSTER à la place. 

SELECT count(*) FROM table; -- make a seq scan to make sure the table is at least
                            -- decently cached
CLUSTER someindex ON table;

vous pouvez choisir l'index, essayer d'en choisir un qui convient à vos requêtes. Vous pouvez toujours utiliser la clé primaire si aucun autre indice est adapté.

si votre table est trop grande pour être mise en cache, CLUSTER peut être lent cependant.

0
répondu peufeu 2011-07-06 20:30:25

créer table newTableName (comme oldTableName y compris les index); insérez dans newTableName sélectionnez * à partir de oldTableName

cela a fonctionné pour moi 9.3

-1
répondu user2940756 2017-06-06 11:12:39