Complètement la copie d'un postgres table 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.

Je suis en train de copier un tableau de grande taille (~40M lignes, 100+ colonnes) dans postgres, où beaucoup de colonnes sont indexées. Actuellement, je utiliser ce bits de SQL:

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

Cette méthode a deux questions:

Il ajoute que les indices avant que les données de l'ingestion, de sorte qu'il sera beaucoup plus long que la création de la table sans indices et ensuite l'indexation après la copie de toutes les données.
Ce n'est pas copier `SERIAL' des colonnes de style correctement. 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 de l'indexation d'un problème en temps réel. Il rend également impossible de la vider sur un fichier puis de le ré-acquisition. 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 de droite créer une copie exacte avec certains miracle de commande, ou si ce n'est pas possible, pour copier le tableau avec toutes les contraintes, mais sans indices, et assurez-vous qu'ils sont les contraintes 'dans l'esprit' (aka un nouveau compteur pour une SÉRIE de colonnes). Puis, copie toutes les données avec un SELECT * puis copiez tous les indices.

Sources

Un Débordement de pile question à propos de la copie de la base de données: Ce n'est pas ce que je suis demander ce, pour trois raisons
- Il utilise l'option de ligne de commande pg_dump -t x2 | sed 's/x2/x3/g' | psql et dans ce cadre je n'ai pas accès à la ligne de commande
- Il crée les indices de pré données de l'acquisition, qui est trop lent
- Il n'a pas de mise à jour de la série des colonnes correctement, comme l'attestent les default nextval('x1_id_seq'::regclass)
Méthode pour réinitialiser la valeur de la séquence pour une postgres table: C'est très bien, mais malheureusement, il est très manuel.

Votre question est probablement une copie de stackoverflow.com/questions/198141/...
J'ai vu cette question, il n'y avait pas de réponses satisfaisantes qui peut réellement faire ce que je demande, mais cela me demande de faire un autre edit de mon post.
Il n'y a pas une meilleure réponse.
Il y a trois problèmes majeurs avec le plus voté solution sur cette page. L'un, ils utilisent la ligne de commande fonctions dans pg_dump -t x2 | sed 's/x2/x3/g' | psql que je n'ai pas accès trop. De deux, Il crée les indices avant d'ajouter les données qui seront très lent! Trois, le numéro de SÉRIE par défaut du paramètre références à la première table default nextval('x1_id_seq'::regclass). Ces trois défauts je l'ai déjà indiqué dans ma question. Vous me dites il n'y a pas de solution à l'un de ces? @peter
Ce n'est qu'une Question de Programmation.

InformationsquelleAutor Erik | 2011-07-06

53

Bien, tu vas avoir à 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 va créer nouvelletable avec oldtable de données, mais pas d'index. Puis vous avez à 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';
```
Puis exécutez 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;
```
Remplacer que 74359 ci-dessus avec l'oid vous obtenez à partir de la requête précédente.
- Notez que si vous souhaitez ensuite les séquences à être dépendant de la nouvelle table parent, vous devrez utiliser "modifier la séquence de seqname détenue par nouvelletable.colonne;"
- Merci. Avec quelques modifications de ce code, je suis capable de faire ce dont j'avais besoin.
InformationsquelleAutor Scott Marlowe
46

La create table as fonctionnalité dans PostgreSQL peut-être maintenant la réponse que l'OP a été à la recherche pour.

https://www.postgresql.org/docs/9.5/static/sql-createtableas.html
```
create table my_table_copy as
  select * from my_table
```
Cela va créer une identique tableau avec les données.

Ajoutant 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 va créer le tableau avec toutes les données, mais sans les index et les déclencheurs etc.

create table my_table_copy (like my_table including all)

Create table syntaxe qui comprendra tous les déclencheurs, index, contraintes, etc. Mais ne pas inclure des données.
- J'ai posté cette question il y a si longtemps que je n'ai pas de moyen facile de vérifier facilement. Cependant, il ne semble pas que la "create table comme des" copies d'autres objets associés à la table, comme des indices et des séquences.
- Ah vous avez raison @Erik. Quelle honte. Je vais laisser ma réponse au cas où quelqu'un d'autre le trouve utile. Mais il a ajouté une note pour dire il l'habitude de copier les autres informations. Merci.
- En effet, il a été très utile en indiquant les limites. Maintenant, nous savons ce qu'il faut rechercher lors de l'utilisation de cette méthode. Merci!
- (like my_table including all) peut ne pas satisfaire les OP qui ne veulent pas d'index, mais c'est parfait pour moi d'essayer d'obtenir un tableau avec toutes les contraintes.
InformationsquelleAutor Phill
14

Le plus proche "miracle de commande" est quelque chose comme
```
pg_dump -t tablename | sed -r 's/\btablename\b/tablename_copy/' | psql -f -
```
En particulier, il s'occupe de la création de l'index après le chargement de la table de données.

Mais qui ne réinitialise pas les séquences; vous devez le script que vous-même.

InformationsquelleAutor Peter Eisentraut
4

Pour copier une table complètement, y compris à la fois la structure des tables et des données, vous utilisez l'instruction suivante:
```
CREATE TABLE new_table AS 
TABLE existing_table;
```
Pour copier un tableau de la structure de l'absence de données, vous ajoutez le SANS clause de DONNÉES à l'instruction CREATE TABLE comme suit:
```
CREATE TABLE new_table AS 
TABLE existing_table 
WITH NO DATA;
```
Pour copier un tableau avec des données partielles à partir d'une table existante, vous utilisez l'instruction suivante:
```
CREATE TABLE new_table AS 
SELECT
*
FROM
    existing_table
WHERE
    condition;
```
- Vous pouvez lier une référence? Je ne pouvais pas trouver toutes les informations à propos de cette syntaxe postgresql.org. Quelques exemples de questions sont un) sont les indices conservés? b) quelles sont les versions de postgresql est-ce valable dans?
InformationsquelleAutor K M Rakibul Islam
2

AVERTISSEMENT:

Toutes les réponses qui utilisent pg_dump et toute forme d'expression régulière pour remplacer le nom de la table source sont vraiment dangereux. Que faire si vos données contient la sous-chaîne que vous êtes en train de remplacer? Vous finirez par la modification de vos données!

Je propose un deux passe-solution:
1. éliminer les lignes de données de la sauvegarde à l'aide de certaines données spécifiques regexp
2. d'effectuer une recherche et remplacer sur les lignes restantes
Voici un exemple écrit en Ruby:
```
ruby -pe 'gsub(/(members?)/, "\_copy_20130320") unless $_ =~ /^\d+\t.*(?:t|f)$/' < members-production-20130320.sql > copy_members_table-20130320.sql
```
Dans le ci-dessus, je suis en train de copier les "membres" de la table en "members_copy_20130320". Mes données spécifiques regexp est /^\d+\. t.*(?:t|f)$/

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

edit:

OK, voici une autre façon de pseudo-syntaxe shell pour la regexp aversion des gens:
1. pg_dump -s -t mytable mydb > mytable_schema.sql
2. de recherche et remplacer le nom de la table dans mytable_schema.sql > mytable_copy_schema.sql
3. psql -f mytable_copy_schema.sql mydb
4. pg_dump -a-t mytable mydb > mytable_data.sql
5. remplacer "matable" dans les quelques instruction SQL qui précède la section de données
6. psql -f mytable_data.sql mydb
InformationsquelleAutor Tomek
0

Apparemment, vous voulez à "reconstruire" une table. Si vous ne souhaitez reconstruire une table, de ne pas copier, alors vous devriez utiliser les armes à 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 aurez à choisir l'index, essayez de choisir celui qui convient à vos requêtes. Vous pouvez toujours utiliser la clé primaire si aucun autre indice est adapté.

Si votre tableau est trop grand pour être mis en cache, le CLUSTER peut être lent si.
- Je n'ai réellement envie de les copier, j'ai enlevé le code supplémentaire qui n'était pas vraiment ème à la question. À partir de ce que je peux dire, CLUSTER réorganise les lignes en fonction de l'index, ce qui n'est pas vraiment ce que je cherche. Désolé pour la désinformation.
InformationsquelleAutor peufeu
-1

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

Cela a fonctionné pour moi 9.3

InformationsquelleAutor user2940756

Vous devez vous connecter pour publier un commentaire.