Copier les résultats d'une vue PostgreSQL dans une base de données vers une table dans une autre

Question

Débutant complet en PostgreSQL.

J'ai 7 à 8 vues de données dans db1 que je dois copier dans des tables avec un schéma correspondant dans une base de données différente, db2. La base de données de destination peut être la même instance de PostgreSQL ou une sur une boîte complètement différente.

Je connais 2 à 3 façons différentes d'accomplir cela avec les bases de données que je connais, mais je suis désemparé sur celle-ci. Quelqu'un peut-il me suggérer quelques stratégies de base ?

Dans un monde parfait, je préférerais ne pas avoir à faire quoi que ce soit qui ressemble trop à de l'ETL - j'aimerais plutôt faire quelque chose du genre

SELECT FROM instance1.db1.viewname INTO instance2.db5.tablename

puis extraire les données de la vue sous forme de fichier texte et les recharger dans la table de destination.

Comme je ne connais pas PostgreSQL, je ne sais pas vraiment ce qui est possible, cependant.

Answer 1

Toute requête peut être la source de COPY TO depuis Postgres 8.2.

COPY (SELECT * FROM view1) TO '/var/lib/postgres/myfile1.csv';

Pour copier également rapidement le schéma de la table, créez la ou les tables nécessaires localement avec :

CREATE table tbl1 AS
SELECT * FROM view1
LIMIT 0;   -- pas de données, seulement le schéma.

Ensuite, copiez les instructions DDL (reversées) et créez toutes les tables dans la base de données cible. pgAdmin est une interface graphique pratique pour cela. Supprimez ensuite la ou les tables vides dans la base de données source. Chargez les données avec :

COPY tbl1 FROM '/var/lib/postgres/myfile1.csv';

Si vous manquez de privilèges nécessaires ou n'avez pas accès au système de fichiers du serveur, considérez plutôt \copy dans psql. Voir :

• Exporter des lignes spécifiques d'une table PostgreSQL sous forme de script SQL INSERT
• Comment mettre à jour des lignes sélectionnées avec des valeurs d'un fichier CSV dans Postgres?

La sauvegarde / restauration (comme le décrit @wildplasser) est une autre méthode.

Pour un transfert ponctuel, l'une de ces méthodes est recommandée. Pour une application répétée, dblink ou SQL/MED (Gestion des données externes) peuvent être plus adaptés.
Ou envisagez la réplication.

Answer 2

CREATE TEMPORARY TABLE mytmp
AS SELECT * from myview
WHERE 1=1
;

COPY mytmp TO '/tmp/test.csv'
;

Une méthode encore meilleure est de :

• copier les vues dans des tables (créer table1 as select * from view1; ...)
• utiliser pg_dump -t table1 -t table2 ... mydbname >myfile.out
• utiliser myfile.out pour recréer et remplir les tables.