Skip to main content
Solved

Copy all tables schema database PostGIS to another database Postgis?

  • October 16, 2017
  • 3 replies
  • 298 views
D

Hello,

I have two Postgis databases that contain several schemas. Each schema of a database also contains many tables.

How I can do to create a workspace that allows you to copy all the tables of a schema from one database to another. The schema name containing the tables to be copied must be a published parameter. The user must just enter the schema name in order to copy its tables into the other database.

I work with the FME 2017 version but what bothers me is that every time I have to list the schema and choose the feature types to read to use.

To summarize I want to do a dump and restore tables of a schema database Postgis.

Thank you for your help.

Best answer by david_r

For this particular scenario I would actually reconsider using FME and rather use the pg_dump utility. Most importantly, pg_dump will recreate an exact copy of the entire schema structure, somthing that FME can't realistically do, including empty tables, relationships, constraints, sequences, etc. And yes, it will work on geometries too.

If you want to use FME to drive the process, you could use the SystemCaller to execute the pg_dump.exe utility, pick up the dumped SQL script using a FeatureReader (text line) and the apply it to the target schema using the SQLExecutor.

This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.

3 replies

david_r
Celebrity
  • david_rCelebrity
  • 8394 replies
  • Best Answer
  • October 16, 2017

For this particular scenario I would actually reconsider using FME and rather use the pg_dump utility. Most importantly, pg_dump will recreate an exact copy of the entire schema structure, somthing that FME can't realistically do, including empty tables, relationships, constraints, sequences, etc. And yes, it will work on geometries too.

If you want to use FME to drive the process, you could use the SystemCaller to execute the pg_dump.exe utility, pick up the dumped SQL script using a FeatureReader (text line) and the apply it to the target schema using the SQLExecutor.

david_r
Celebrity
  • david_rCelebrity
  • 8394 replies
  • October 16, 2017

If you're not recreating the schema defintion but only moving data, look into dynamic schemas on the reader/writer in FME: https://knowledge.safe.com/articles/1050/dynamic-workflow-tutorial-introduction.html

M
Forum|alt.badge.img+2
  • mark_f
  • 325 replies
  • October 16, 2017

For this particular scenario I would actually reconsider using FME and rather use the pg_dump utility. Most importantly, pg_dump will recreate an exact copy of the entire schema structure, somthing that FME can't realistically do, including empty tables, relationships, constraints, sequences, etc. And yes, it will work on geometries too.

If you want to use FME to drive the process, you could use the SystemCaller to execute the pg_dump.exe utility, pick up the dumped SQL script using a FeatureReader (text line) and the apply it to the target schema using the SQLExecutor.

exactly what I thought!

 

 

Community Stats

32,093
Posts
121,872
Replies
39,731
Members

Latest FME

Terms & ConditionsCookie settingsAccessibility statement
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.