Skip to main content
Solved

Copy all tables schema database PostGIS to another database Postgis?

  • October 16, 2017
  • 3 replies
  • 234 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.

View original
Did this help you find an answer to your question?

3 replies

david_r
Celebrity
  • david_rCelebrity
  • 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
  • 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
  • October 16, 2017
david_r wrote:

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!

 

 

Reply


Helpful Members This Week

Community Stats

31,227
Posts
118,415
Replies
38,827
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings
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.