Le portail francophone de la géomatique

Xavier Julian · Wed 11 January 2017 16:17

Bonjour,

J'aimerais copier une table d'une BDD PostGIS en local à une autre sur un serveur à distance.
En cherchant sur le forum, j'ai commencé à tester plusieurs propositions, notamment celle provenant de ce post : http://georezo.net/forum/viewtopic.php?id=80074

Celle proposée par Yves en #2 (sous pgAdmin faire un sauvegarde/restaurer) fonctionne en partie seulement.
Ma table étant assez lourde (plus d'1.6 millions de lignes), la copie n'est pas intégrale.

Quant à la proposition de Nicolas Ribot en #3 ...

Code:

pgdump -t table1 database1 | psql -h serveur2 -d database2

... je n'arrive pas à écrire cette commande correctement.
Est-ce que quelqu'un peut m'aiguiller ?

J'ai également vu certains post parlant de l'extension dblink.
Comment cela fonctionne ?

Je précise que je n'ai pas besoin que les modifications apportées sur la table originelle se répercutent sur la nouvelle.
Donc je ne pense pas avoir besoin de mettre en place un trigger

Merci par avance

VianneyD · Wed 11 January 2017 19:12

Bonjour,

Celle proposée par Yves en #2 (sous pgAdmin faire un sauvegarde/restaurer) fonctionne en partie seulement.
Ma table étant assez lourde (plus d'1.6 millions de lignes), la copie n'est pas intégrale.

Je ne pense pas que le volume de ta table en soit la cause. As-tu interrompu le traitement ? Quel est la sortie renvoyée par pgAdmin ?

Quant à la proposition de Nicolas Ribot en #3 ...
Code:
pgdump -t table1 database1 | psql -h serveur2 -d database2
... je n'arrive pas à écrire cette commande correctement.
Est-ce que quelqu'un peut m'aiguiller ?

Que renvoie ta commande ?
Pour ton pg_dump (et non pas pgdump, au passage ^^), il manque peut-être des options, notamment si ta table se trouve dans un schéma particulier (option "-n").
Voir la doc de pg_dump adaptée à ta version de PostgreSQL.

Idem pour psql, la commande nécessite peut-être d'autres options, notamment si ton accès à la BDD cible nécessite une authentification (options "-U" et "-W"). Voir la doc de psql.

Si ça ne fonctionne pas mieux, voici d'autres causes susceptibles d'empêcher la migration :
- As-tu les droits d'accès sur le serveur cible depuis la machine source ? (voir également le paramétrage du fichier pg_hba.conf sur le serveur cible)
- Ton utilisateur PostgreSQL a-t-il les droits d'écriture sur le schéma cible ?
- Le serveur cible a-t-il bien la même version de PostGIS ? ("SELECT postgis_version()" pour en être sûr)

Xavier Julian · Thu 12 January 2017 11:16

Bonjour Vianney et merci beaucoup pour ta réponse.

VianneyD a écrit:

Je ne pense pas que le volume de ta table en soit la cause. As-tu interrompu le traitement ? Quel est la sortie renvoyée par pgAdmin ?

Je n'ai pas interrompu le traitement, et le code de sortie retournée est bien 0. Pourtant quand je compte le nombre de ligne entre ma table sauvegardé et ma table restauré, il y a des différences.

si ça ne fonctionne pas mieux, voici d'autres causes susceptibles d'empêcher la migration :
- As-tu les droits d'accès sur le serveur cible depuis la machine source ? (voir également le paramétrage du fichier pg_hba.conf sur le serveur cible)
- Ton utilisateur PostgreSQL a-t-il les droits d'écriture sur le schéma cible ?
- Le serveur cible a-t-il bien la même version de PostGIS ? ("SELECT postgis_version()" pour en être sûr)

Alors :
- oui, j'ai les droits d'accès au serveur, je peux y accéder par le bureau à distance et j'y ai directement installé postgresql.
- oui, j'ai les droits d'écriture, je suis le superutilisateur.
- oui, j'ai bien la même version de postgres (9.5) ET de PostGIS (2.3) entre le serveur et mon ordinateur.

Concernant la commande, je lance le SQL Shell, puis voilà ce que tape :

Code:

Server [localhost]: pg_dump -t ma_table -n mon_schéma
ma_base | psql -h nom_hôte -d ma_base2 -U nom_superutilisateur -W motdepasse

Cela me retourne la demande Database [postgres] : -> je retape ma_base
Cela me retourne Port [5433] : -> je tape Entrée (C'est le port en local)
Cela me retourne Username [postgres] : -> je tape postgres

Et là, rien, la fenêtre se ferme toute seule, j'ai juste eu le temps de lire ".... psql n'a pas pu ....."
Merci par avance pour votre aide -_-

Loic_GR · Thu 12 January 2017 11:39

Si tu as un pgadmin cela s'effectue simplement clic droit sauvegarder sous

Xavier Julian · Thu 12 January 2017 11:53

Bonjour heretik25,

C'est la 1èere solution que j'ai testé.
Mais je ne sais pas pourquoi le processus de sauvegarde/restauration ne fonctionne pas correctement.
D’où le test des autres solutions ....

Dernière modification par Xavier Julian (Thu 12 January 2017 11:53)

VianneyD · Thu 12 January 2017 12:04

Bonjour,

Je n'ai pas interrompu le traitement, et le code de sortie retournée est bien 0. Pourtant quand je compte le nombre de ligne entre ma table sauvegardé et ma table restauré, il y a des différences.

As-tu fait un "SELECT count(*) FROM [table]" de part et d'autre ? Car le nombre de lignes estimées donné par pgAdmin n'est pas forcément exact...

Loic_GR · Thu 12 January 2017 12:29

VianneyD a écrit:

Bonjour,

Je n'ai pas interrompu le traitement, et le code de sortie retournée est bien 0. Pourtant quand je compte le nombre de ligne entre ma table sauvegardé et ma table restauré, il y a des différences.

As-tu fait un "SELECT count(*) FROM [table]" de part et d'autre ? Car le nombre de lignes estimées donné par pgAdmin n'est pas forcément exact...

je confirme que l'estimation faite par pgadmin est souvent assez fantaisiste

Yves · Thu 12 January 2017 15:43

Bonjour,

Concernant la commande, je lance le SQL Shell, puis voilà ce que tape :
Code:

Server [localhost]: pg_dump -t ma_table -n mon_schéma
ma_base | psql -h nom_hôte -d ma_base2 -U nom_superutilisateur -W motdepasse

Sauf que la commande pg_dump est une commande dos et pas SQL, il faut lancer pg_dump dans la console DOS. Donc j'en déduis que tu es sous Windows, donc que ca sera quelque chose comme :

Code:

C:\Program Files\PostgreSQL\9.5\bin\pg_dump.exe -t ma_table -n mon_schéma ma_base | psql -h nom_hôte -d ma_base2 -U nom_superutilisateur -W motdepasse

à partir de la console dos que tu peux ouvrir par : Menu Démarrer > Executer > cmd

Y.

Xavier Julian · Thu 12 January 2017 16:37

Bonjour Yves.

Alors effectivement, je suis sous Windows.
Je vous joins la commande que je lance, mais qui me renvoi le chemin d'accès spécifié est introuvable

Je ne me suis jamais servi de la commande dos, désolé :-/

Fichier(s) joint(s) :
Pour accéder aux fichiers vous devez vous inscrire.

Dof · Thu 12 January 2017 17:20

Bonjour,
Est-ce le bon chemin de "pg_dump.exe"?
Sinon, essayez de mettre les chemins entre guillemets

jmarsac · Thu 12 January 2017 17:27

Bonjour,

Comme l'a indiqué Yves, ce n'est pas

C:\Programmes\Post....

mais

si version 64 bits ou windows 32 bits

"C:\Program Files\Postgresql\9.5\bin\"pg_dump

ou si version 32 bits (sous windows 64 bits)

"C:\Program Files (x86)\Postgresqll\9.5\bin\"pg_dump

NB: les guillemets sont nécessaires

le mieux serait d'ajouter le chemin à la variable PATH

Code:

set PATH="C:\Program Files\Postgresql\9.5\bin\";%PATH%

ce qui permettrait de localiser également psql

Le dossier Programmes affiché dans les fenêtres est renommé à l'affichage par Windows

Dernière modification par jmarsac (Thu 12 January 2017 17:30)

ChristopheV · Fri 13 January 2017 08:39

Bonjour,

Il existe aussi l’extension dblink().

non ?

Dernière modification par ChristopheV (Fri 13 January 2017 08:40)

Xavier Julian · Fri 13 January 2017 10:51

Bonjour ChristopheV,

ChristopheV a écrit:

Bonjour,

Il existe aussi l’extension dblink().

non ?

Oui j'avais lu plusieurs sujets du forum parlant de dblink.
Apparemment il est désormais conseillé d'utiliser plutôt postgres_fdw, ce que je teste en ce moment même.
Avez-vous utilisé l'une ou l'autre de ces extensions avec succès?

J'essaye également de modifier ma commande DOS grâce aux remarques de Dof et jmarsac.

Xavier Julian · Fri 13 January 2017 11:25

Alors je progresse au sujet de la commande DOS...
Désormais j'ai une erreur "could not find a "psql" to execute".
Pourtant psql.exe est bien dans le même répertoire que pg_dump.exe.
Voilà mon code :

Code:

set PATH="C:\Program Files\Postgresql\9.5\bin\";%PATH% pg_dump -t matable -n monshema mabase | psql -h monhote -d matable2

Dernière modification par Xavier Julian (Fri 13 January 2017 11:25)

jmarsac · Fri 13 January 2017 11:35

Il faut taper deux commandes successives :

Code:

set PATH="C:\Program Files\Postgresql\9.5\bin\";%PATH%

 pg_dump -t matable -n monshema mabase | psql -h monhote -d matable2

et peut-être ajouter le ".exe" :

Code:

 pg_dump.exe -t matable -n monshema mabase | psql.exe -h monhote -d matable2

jmarsac · Fri 13 January 2017 11:47

Au temps pour moi, il ne faut pas mettre les guillemets lorsqu'on redéfinit la variable PATH (Ils sont requis seulement si l'on donne le chemin complet au lancement de la commande.
Il s'agit bien d'un Windows 32 bits ou d'un PostgreSQL 64 bits sur Windows 64 bits ?

Code:

set PATH=C:\Program Files\Postgresql\9.5\bin\;%PATH%

Xavier Julian · Fri 13 January 2017 12:08

Il s'agit d'un PostgreSQL 64 bits sur Windows 64 bits.

J'ai donc enlevé les guillemets de la variable PATH, et cette fois-ci la commande se lance, mais me demande un mot de passe.
J'ai d'abord essayé le mdp de mon serveur à distance, puis de mon serveur en local, mais ça ne marche pas.

J'ai l'erreur : pg_dump : [programme d'archivage (db)] la connexion à la base de données "x.julian" a échoué.
Mais je comprends pas pourquoi x.julian, c'est le nom de ma session Windows ?

Décidément cette commande DOS ça me perd... ><
Je ne dois pas être le seul cartographe à penser qu'il devrait y avoir un moyen plus ergonomique et "user friendly" pour administrer les bases de données?
Merci vraiment de votre aide.

jmarsac · Fri 13 January 2017 12:32

Il faut créer et renseigner le fichier %APPDATA%\x.julian\pgpass.conf (corriger le numéro de port si nécessaire)

avec

Code:

localhost:5432:mabase:postgres:password
monhote:5432:mabasedistante:postgres:passworddistant

"passworddistant" est le mot de passe de l'utilisateur "postgres" sur monhote (serveur distant)

Puis il faut indiquer le nom d'utilisateur PostgreSQL pour la connexion et indiquer de ne pas saisir le mot de passe :

Code:

pg_dump.exe -U postgres -w -t matable -n monshema mabase | psql.exe -U postgres -w -h monhote -d mabasedistante

NB: l'option -d (de psql) introduit le nom de la base et non celui de la table

Plus d'infos dans la doc :

https://www.postgresql.org/docs/9.5/sta … -psql.html
https://www.postgresql.org/docs/9.5/sta … gdump.html
https://www.postgresql.org/docs/9.5/sta … gpass.html

Dernière modification par jmarsac (Fri 13 January 2017 13:25)

Xavier Julian · Fri 13 January 2017 14:09

On avance, on avance et merci !

J'avais déjà configuré le fichier pgpass.conf lorsque j'avais installé postresql sur le serveur distant.

Donc j'ai bien rajouté les -U et -w dans la commande pour préciser le nom utilisateur et mdp.
Cette fois-ci on touche au but, car l'erreur renvoyé c'est "pg_dump : aucune table correspondante n'a été trouvé".
Pourtant (je ne suis pas fou^^) la table existe bien (écrite en minuscule), dans le bon schéma.

Nicolas Ribot · Fri 13 January 2017 15:35

Bonjour,

Pour dumper une table, c'est l'option -t (ex: -t monschema.matable)
l'option -n, c'est pour dumper un schéma entier.

Donc là, vous avez sûrement copié le schéma entier sur le serveur cible.

Nicolas

Xavier Julian · Fri 13 January 2017 16:15

Merci Nicolas,

Effectivement, ça aide. J'ai ENFIN réussi à faire la copie d'une table test vide.
Voilà le code pour info (et pour les suivants) :

Code:

set PATH=C:\Program Files\PostgreSQL\9.5\bin\;%PATH%

pg_dump -U postgres -w -t monschema.matable mabase | psql -h monhote -U postgres -w -d mabase2

Après ça se corse avec mes vraies tables, il y a des erreurs refus pour créer pg_catalog.matable, mes relations qui n'existent pas, etc.
Je vais continuer de fouiller dans les options décrites dans la doc officielle.

Dernière modification par Xavier Julian (Fri 13 January 2017 16:37)

jmarsac · Fri 13 January 2017 16:35

au cas où... ce n'est pas -d monschema2 mais -d mabase2

Code:

pg_dump -U postgres -w -t monschema.matable mabase | psql -h monhote -U postgres -w -d mabase2

Xavier Julian · Fri 13 January 2017 16:37

Ha oui zut, moi qui voulait réécrire le code proprement (je l'ai modifié du coup dans le post précédant) !
merci !

Dernière modification par Xavier Julian (Fri 13 January 2017 16:37)

Nicolas Ribot · Fri 13 January 2017 16:41

"refus pour créer pg_catalog.matable"

ca, c'est parce que la table existe déja dans le serveur et que vous ne précisez pas l'option -c (clean: drop des tables existantes avant copie)

deux options: soit supprimer la table dans le serveur cible et relancer votre commande
soit relancer la commande avec l'option -c

Nico

Xavier Julian · Fri 13 January 2017 17:07

Ha ... pourtant ma "base2" est vide...
Bon j'ai aussi des erreurs de syntaxe, de commande \N invalide, etc.

Nicolas Ribot · Fri 13 January 2017 17:21

c'est la premiere erreur qui est importante. Les \n viennent apres la premiere erreur en general.

ChristopheV · Mon 16 January 2017 15:45

Bonjour,

Avez-vous utilisé l'une ou l'autre de ces extensions avec succès?

DBLink oui sans soucis, le seul truc un peu tricky c'est de compléter tous les paramètres de la commande (host,pwd,etc ...).

Xavier Julian · Tue 17 January 2017 10:31

J'ai trouvé la source de l'erreur "refus pour créer pg_catalog.matable".
En fait il faut que dans ma bdd de réception mabase2 (sur le serveur à distance), il faut qu'il y ait le même nom de schéma que dans ma bdd en local mabase.
J'ai trouvé cette astuce sur http://forums.postgresql.fr/viewtopic.php?id=2963

Je pensais que, par défaut, la table que je cherchais à sauvegarder allait se créer dans le schéma "public" de mabase2 ....

Un grand merci à tous pour votre précieuse aide.

Communauté	Ressources	L'emploi	Le marché	L'association	Fils RSS
Forum Blogs Wiki Planet Sites amis	Agenda Biblio Fiches AMO-CNIG Paris PCGIS Téléchargements	Offres d'emploi Banque de CV Formations Démarche Métiers	Geo-Entreprises Geo-Communiqué Appels d'offres	L'association Qui sommes-nous ? Nous aider Bienfaiteurs Partenaires Historique Statuts	Tout le site Job Géomatique Services GeoBlogs ...	Faire un don Mentions légales

Le portail francophone de la géomatique

Annonce

#1 Wed 11 January 2017 16:17

[PostGIS] Copie d'une table sur une autre base

Code:

#2 Wed 11 January 2017 19:12

Re: [PostGIS] Copie d'une table sur une autre base

Code:

#3 Thu 12 January 2017 11:16

Re: [PostGIS] Copie d'une table sur une autre base

Code:

#4 Thu 12 January 2017 11:39

Re: [PostGIS] Copie d'une table sur une autre base

#5 Thu 12 January 2017 11:53

Re: [PostGIS] Copie d'une table sur une autre base

#6 Thu 12 January 2017 12:04

Re: [PostGIS] Copie d'une table sur une autre base

#7 Thu 12 January 2017 12:29

Re: [PostGIS] Copie d'une table sur une autre base

#8 Thu 12 January 2017 15:43

Re: [PostGIS] Copie d'une table sur une autre base

Code:

#9 Thu 12 January 2017 16:37

Re: [PostGIS] Copie d'une table sur une autre base

#10 Thu 12 January 2017 17:20

Re: [PostGIS] Copie d'une table sur une autre base

#11 Thu 12 January 2017 17:27

Re: [PostGIS] Copie d'une table sur une autre base

Code:

#12 Fri 13 January 2017 08:39

Re: [PostGIS] Copie d'une table sur une autre base

#13 Fri 13 January 2017 10:51

Re: [PostGIS] Copie d'une table sur une autre base

#14 Fri 13 January 2017 11:25

Re: [PostGIS] Copie d'une table sur une autre base

Code:

#15 Fri 13 January 2017 11:35

Re: [PostGIS] Copie d'une table sur une autre base

Code:

Code:

#16 Fri 13 January 2017 11:47

Re: [PostGIS] Copie d'une table sur une autre base

Code:

#17 Fri 13 January 2017 12:08

Re: [PostGIS] Copie d'une table sur une autre base

#18 Fri 13 January 2017 12:32

Re: [PostGIS] Copie d'une table sur une autre base

Code:

Code:

#19 Fri 13 January 2017 14:09

Re: [PostGIS] Copie d'une table sur une autre base

#20 Fri 13 January 2017 15:35

Re: [PostGIS] Copie d'une table sur une autre base

#21 Fri 13 January 2017 16:15

Re: [PostGIS] Copie d'une table sur une autre base

Code:

#22 Fri 13 January 2017 16:35

Re: [PostGIS] Copie d'une table sur une autre base

Code:

#23 Fri 13 January 2017 16:37

Re: [PostGIS] Copie d'une table sur une autre base

#24 Fri 13 January 2017 16:41

Re: [PostGIS] Copie d'une table sur une autre base

#25 Fri 13 January 2017 17:07

Re: [PostGIS] Copie d'une table sur une autre base

#26 Fri 13 January 2017 17:21

Re: [PostGIS] Copie d'une table sur une autre base

#27 Mon 16 January 2017 15:45

Re: [PostGIS] Copie d'une table sur une autre base

#28 Tue 17 January 2017 10:31

Re: [PostGIS] Copie d'une table sur une autre base

Pied de page des forums