Forums PostgreSQL.fr

Magalie

Membre

Update de tables Oracle vers PostgreSQL

Bonjour à tous,

Je cherche des informations sur les solutions permettant de mettre à jour les données issues d'une BD Oracle en production dans une BD PostgreSQL de consultation. J'ai trouvé plusieurs pistes (ORA2PG, trigger, connexion ODBC, ...) en effectuant des recherches sur ce forum ou sur d'autres sources d'info sur le net. Cependant cela ne me permet pas d'avoir une vision claire des solutions techniques envisageables et surtout si elles sont réalisables dans ce contexte (communication entre 2 SGBD sur OS différents).

Le contexte est le suivant :
- nous disposons d'une BD Oracle spatiale sur serveur Windows utilisée en production,
- nous souhaitons que les données de certaines tables (une dizaine pour l'instant, mais susceptible d'augmenter considérablement) soient copiées de cette BD de production vers une autre base de consultation PostgreSQL/PostGIS située sur un serveur Linux (distribution Debian),
- les données dans ces deux BD ne sont pas structurées à l'identique (champs enlevés, concaténés, ...),
- la gestion des droits (utilisateurs/rôles) n'est également pas la même,
- des mises à jour hebdomadaires, voire journalières, permettront d'ajouter/modifier/supprimer de nouveaux enregistrements dans les tables, il faut donc pouvoir automatiser cette mise à jour.

Merci d'avance pour votre retour d'expérience !
Magalie.

PS : Pour info, je ne suis pas DBA.

Marc Cousin

Membre

Re : Update de tables Oracle vers PostgreSQL

Ora2PG n'ira pas pour cela à mon avis (c'est fait pour de la migration one-shot, pas pour des mises à jour incrémentales)

Vous pouvez effectivement développer vous même totalement la solution (avec des triggers par exemple, mais ce n'est pas obligatoire). Je connais au moins un organisme français qui fait des transferts réguliers de données géographiques d'Oracle vers Postgres, au moyen d'un développement maison en java.

Vous pouvez aussi essayer la piste de l'ETL. Talend et Kettle ont apparemment des extensions géométriques. Il y a même une variante GeoKettle apparemment. C'est certainement le plus efficace pour développer rapidement un traitement de transformation d'une base vers l'autre, surtout si vous avez des modifications de schéma à faire au passage. Mais ça demande pas mal d'apprentissage… ces outils sont évidemment assez riches fonctionnellement.

Je présume qu'il doit aussi y avoir des ETL propriétaires capables de faire ce genre de traitements, mais là on s'éloigne de mon domaine de compétence.

---

Marc.

Magalie

Membre

Re : Update de tables Oracle vers PostgreSQL

Bonjour Marc et merci pour votre réponse !

J'avais aussi pensé à l'utilisation d'un ETL pour réaliser ces mises à jour de manière "automatisée". L'ETL propriétaire de référence est FME (de Safe Software) et permet de gérer (quasiment) tous les formats géographiques.

Concernant Ora2PG, il ne serait pas possible de l'utiliser derrière une tâche planifiée (côté Windows) qui permettrai de lancer des mises à jour régulières de la BD Postgres sur Linux ?

Lorsque vous parlez de "développer nous-même totalement la solution", vous dites que ce type de développements n'utilise pas obligatoirement des triggers, à quoi pensez-vous d'autre exactement ?
Concernant les triggers, est-il possible de les mettre en place entre Oracle et PostgreSQL ? Et si oui, de quel côté faut-il les déployer ?

Je m'interroge beaucoup sur la question de la communication entre ces 2 plateformes complètement différentes et plus précisément sur la compatibilité des solutions évoquées.

Je suis preneuse de toute ressource (en anglais également) sur ce sujet.

Marc Cousin

Membre

Re : Update de tables Oracle vers PostgreSQL

Ora2PG fait habituellement un export complet de table… ce qui me semble contraire à ce que vous demandiez (export incrémental). Par ailleurs, je ne sais pas si ora2pg fonctionne bien sous Windows (je n'ai jamais essayé). Il est en perl, et a besoin du driver DBD::Oracle. Je ne sais pas si ce dernier est disponible dans un package perl pour windows.

Pour n'utilise pas forcément les triggers, cela dépend de la proportion de données mises à jour: si vous ne modifiez que très peu d'enregistrements, effectivement, le trigger est probablement la meilleure méthode pour récupérer ce qui a bougé entre deux exports. Par contre, si c'est par exemple 20% d'une grosse table, un mécanisme par fusion-tri sera certainement plus efficace (des ETL comme Kettle font ça très bien).

Je ne pense pas, toujours pour les triggers, que ça soit une bonne idée que vos triggers eux-même s'occupent de la recopie des données. Laissez-les simplement stocker les modifications (par exemple, par clé primaire, les ajouts/suppressions/modifications), puis faites la recopie de façon asynchrone. Je ne pense pas que vous voulez qu'une erreur de recopie des données vers PostgreSQL ait un impact sur la base Oracle. Je ne sais pas du tout s'il est facile de recopier des données d'Oracle vers PG dans du code PL, que ça soit côté Oracle ou PG d'ailleurs. Peut-être en passant par ogr2ogr, à l'extérieur de la base ? http://www.gdal.org/ogr2ogr.html

Si vous voulez un bon point de départ sur PostGIS, un excellent livre vient tout juste de sortir sur le sujet: PostGIS in action: http://www.manning.com/obe/ . Il traite même des nouveautés de la version 2.0, comme le support les rasters.

---

Marc.

Magalie

Membre

Re : Update de tables Oracle vers PostgreSQL

Merci pour ces informations.
J'avance toujours dans ma réflexion ainsi que mes recherches, ce qui provoque de nouveaux questionnements !

Je ne pense pas, toujours pour les triggers, que ça soit une bonne idée que vos triggers eux-même s'occupent de la recopie des données. Laissez-les simplement stocker les modifications (par exemple, par clé primaire, les ajouts/suppressions/modifications), puis faites la recopie de façon asynchrone. Je ne pense pas que vous voulez qu'une erreur de recopie des données vers PostgreSQL ait un impact sur la base Oracle.

Pourriez-vous détailler un peu plus cette étape Marc ? notamment la manière dont sont stockées les modifications, et comment vous voyez la réalisation de la recopie de façon asynchrone.

Je ne sais pas du tout s'il est facile de recopier des données d'Oracle vers PG dans du code PL, que ça soit côté Oracle ou PG d'ailleurs.

Est-ce que quelqu'un d'autre à un avis ou pourrait nous faire un retour d'expérience sur cette solution ?
J'avais également pensé à un export des tables Oracle sous la forme d'un dump ou d'un fichier sql. Puis d'un import de ce fichier dans Postgres. Or je ne sais pas s'il est possible de mettre en œuvre ce type de processus et par quel moyen.

Merci d'avance.

Magalie

Marc Cousin

Membre

Re : Update de tables Oracle vers PostgreSQL

Je peux difficilement détailler. C'est le genre de choses que j'ai déjà fait, mais pas avec des données géographiques

L'idée, c'est d'avoir des triggers sur les tables qui bougent, et d'utiliser ces triggers pour tracer dans une table de log les enregistrements créés, modifiés ou supprimés. Ça vous donne une table que vous pouvez utiliser pour récupérer ce qui a bougé. La difficulté étant ensuite d'extraire ces données de façon à pouvoir les réinjecter dans PostgreSQL.

Le Dump SQL ne marchera probablement pas. Les syntaxes risquent d'être trop différentes. Apparemment, ogr2ogr fait pas mal de choses permettant de convertir les données d'Oracle … http://www.gdal.org/ogr/drv_oci.html . Mais je n'en sais pas plus

Quelqu'un d'autre ?

---

Marc.

Magalie

Membre

Re : Update de tables Oracle vers PostgreSQL

Le fait que ce soit des données géographiques ne m'inquiète pas plus que cela, je ne pense pas que cela change grand chose par rapport à des données tabulaires si ce n'est un champ supplémentaire

Concernant le fonctionnement des triggers et le stockage dans une table de log, c'est plus clair pour moi.

J'avais un doute sur la compatibilité d'un fichier exporté d'Oracle et ré-injecté dans PostgreSQL, vous confirmez mes craintes.

Je vais regarder de plus près ogr2ogr ainsi que l'utilisation d'un ETL. Vous parliez d'ailleurs d'ETL propriétaire dans un précédent message, pour info l'ETL propriétaire de référence est FME distribué par Safe Software (qui a l'avantage de manipuler des quantités de formats géographiques ainsi que de réaliser des requêtes spatiales).

flo

Membre

Re : Update de tables Oracle vers PostgreSQL

Sinon il y a toujours la solution rustique d'avoir 2 programmes "maison" :
1 programme qui lit la base Oracle et qui export dans un fichier les enregistrements (soit tout, soit juste ce qui a bougé, en fonction des volumétries respectives). Par exemple un fichier de type csv.
1 programme qui lit et charge le fichier et qui met à jour la base Postgresql (si le fichier est juste un diff, c'est simple, il suffit d'ajouter/mettre à jour les nouveaux enregistrements, sinon, charger les données dans une table temporaire, et faire les comparaisons dans la base Postgres). Par exemple un chargement avec COPY.

Mais en gros le problème principal est toujours le même : repérer efficacement ce qui a bougé.

Ce type de solution marche bien, et est simple à mettre en oeuvre si la structure des 2 bases est assez proche (en gros si chaque table de départ correspond à une table à l'arrivée, et réciproquement). Plus les modèles sont différents, plus c'est compliqué.
L'autre intérêt c'est de pouvoir faire des mécanismes de reprise en cas d'erreur du programme de chargement dans PG (le fichier est toujours dispo pour être chargé plus tard, une fois le programme de chargement corrigé).

On peut le faire en Java, par exemple. Ainsi il est facile de faire, soit 1 programme qui fait tout et se connecte aux 2 bases, soit 2 programmes, ou 3...

vincentp

Membre

Re : Update de tables Oracle vers PostgreSQL

Bonsoir Magalie, salut les gars,

Je suis d'accord avec flo pour dire que le problème principal est de repérer efficacement ce qui a bougé. Une fois ce souci résolu, qui est plutôt fonctionnel que technique, on peut chercher une solution pour la synchro des données.
La solution des trigger pour enregistrer les modifications dans une table spécifique me parait bonne.

Il faut ensuite un programme pour faire le transfert.
* FME
avantages : gère très bien les deux types de base, gère très bien le spatial, permet de faire le transfert en flux sans fichier au milieu, permet de faire les traitements de mise à jour dans la base destination, normalement.
inconvénients : pas très pratique pour automatiser la synchro, licence

* programme maison (python, java, whatever)
avantages : simple et efficace, pas de cout de licence
inconvénient : connaissances minimales en dev requises

Il faut aussi choisir dans le deuxième cas un mécanisme d'import/export
* CSV
C'est encore la solution la plus simple. Attention à dumper dans ce cas les géométries au format WKT, les formats internes binaires PG et Oracles diffèrent. Il faut alors reconstituer les geométries après le chargement dans PG (une ligne de SQL avec st_geomfromtext() )

* OGR2OGR
Permet de faire le transfert directement d'une base à l'autre car il gère Oracle et PG. on a aussi un transfert en flux dans ce cas. Il faut toutefois toujours utiliser un programme pour lancer le SQL qui va mettre à jour les tables de destination à partir des données chargées (qui sont sous forme de diff). Attention en général GDAL/OGR n'est pas compilé avec le support oracle par défaut.

* SHP
les outils oracle et pg peuvent lire et sortir du shapefile. On peut utiliser ce format comme format d'échange, mais on peut alors etre contraint par les limitations du shapefile (nb de caractères dans les noms de champs)

Perso je ferais CSV + Python, mais c'est par gout personnel. En efficacité pure peut etre que FME est à privilégier. Ça dépend du profil de celui qui met tout ça en place.

A retenir : attention aux dumps binaires pas compatibles entre les deux bases, en particulier pour les géométries.

Au passage si je me trompe pas, il me semble que je connais cette base...

Magalie

Membre

Re : Update de tables Oracle vers PostgreSQL

Bonjour tout le monde !

Merci Marc, Flo et Vincent pour vos réponses.
Je vous ferai un retour suite au choix que nous aurons fait.

> à Vincent : Oui, oui, tu ne te trompes pas . Mais ce n'est pas le même contexte de déploiement (autres OS/serveur/données).

detrygeoffroy

Membre

Re : Update de tables Oracle vers PostgreSQL

Bonjour à tous,

Je suis actuellement face à un problème similaire.
-    D’une part, j’ai une base de données PostgreSQL/Postgis servant à stocker des tables spatiales.
-     De l’autre, nous avons une base de données Oracle où se trouvent des tables alphanumériques.
-    Les deux bases de données sont pour l’instant indépendantes. Les tables spatiales et alphanumériques pourraient être liées via un même identifiant.
-    Les utilisateurs ajoutent/modifient/suppriment des données alphanumériques stockées dans Oracle quotidiennement

Je souhaiterais établir un lien entre les deux bases de données de manière à récupérer certaines tables d’Oracle sous postgresql et pouvoir lier les informations avec une table spatiale. Une procédure automatique de mise à jour devrait pouvoir récupérer les modifications quotidiennes apportées depuis Oracle.

J’ai fait des recherches et ai certaines pistes qui se dégagent telles que l’utilisation de DBI-Link (mais nécessite Perl et Driver Oracle), Geokettle ou comme dit plus haut, la définition de trigger mais là je ne sais pas trop comment débuter.

Je suis donc très intéressé par les retours d’expérience et par les choix que vous auriez effectué.

Un grand merci

Geoffroy

SQLpro

Membre

Re : Update de tables Oracle vers PostgreSQL

...
Je suis d'accord avec flo pour dire que le problème principal est de repérer efficacement ce qui a bougé. Une fois ce souci résolu, qui est plutôt fonctionnel que technique, on peut chercher une solution pour la synchro des données.

Il suffit d'utiliser l'outil Oracle approprié qui est Change Data Capture : http://docs.oracle.com/cd/B13789_01/ser … 36/cdc.htm

A +

---

Frédéric Brouard, alias SQLpro,  ARCHITECTE DE DONNÉES,  Expert langage SQL
Le site sur les SGBD relationnel et langage SQL   : http://sqlpro.developpez.com/
Modélisation de données, conseil, expertise, audit, optimisation, tuning, formation
* * * * *  Enseignant CNAM PACA, ISEN Toulon,  CESI Aix en Provence  * * * * *

daamien

damien clochard

Re : Update de tables Oracle vers PostgreSQL

@Magalie :

A partir de PostgreSQL 9.1, tu as un connecteur direct entre PostgreSQL et Oracle.

http://oracle-fdw.projects.postgresql.org/
http://pgxn.org/dist/oracle_fdw/

Tu peux simplement déclarer tes tables Oracles comme des tables "externes" à l'intérieur de PostgreSQL. Le connecteur oracle_fdw est encore en mode béta mais ça vaut la peine de l'essayer !

---

damien clochard
http://dalibo.org | http://dalibo.com

detrygeoffroy

Membre

Re : Update de tables Oracle vers PostgreSQL

Bonjour à tous,

Un grand merci pour vos réponses. Je m'excuse pour le délai (mission à l'étranger)
Je vais tester les solutions proposées. Je n'ai pas vu la possibilité de connecter directement les deux BD mais si c'est le cas, ce serait parfait :-)

Je reviens vers vous pour vous donner les résultats

Geoffroy

Alan2007

Membre

Re : Update de tables Oracle vers PostgreSQL

Merci pour le partage

detrygeoffroy

Membre

Re : Update de tables Oracle vers PostgreSQL

Bonjour,

Ce post date mais j'y apporte (mieux vaut tard que jamais), la méthode que j'avais suivie pour passer des informations d'Oracle à Postgres.

Le contexte voulait que je n'avais qu'un nombre limité de tables (6) à utiliser de la base de données Oracle du client, tables qui sont soit figées (contiennent des données qui ne seront plus updatées) ou qui est updatées périodiquement, de manière définie . Après avoir beaucoup cherché et chipoter (dblink ,ETL, etc.) j'ai finalement opté pour une solution assez basique mais qui, dans mon cas, me donne satisfaction.

Je réalise le transfert via de simples scripts PHP, qui se décomposent comme suit :

1. Ouverture d'une connexion en ODBC sur la base Oracle et requête SQL. Le résultat est retourné en json

$rdb_connect = odbc_connect('namedb', 'nameuser', 'log');

2. Ouverture vers BD postgres, le json est décodé et j'insère les datas dans ma table pg créée auparavant.

Je lance ce script une fois ou via le task scheduler de windows, paramétré en fonction des updates sur la BD du client.

Voilà donc la manière utilisée. Il y a sans doute plus efficace mais cela m'a permis de m'en sortir

Geoffroy