Skip to main content
Solved

migration Oracle to PostGIS

  • November 21, 2024
  • 9 replies
  • 80 views
scootercat_nl
Contributor
Forum|alt.badge.img+5

I have a database NAME1.


If I open them as Oracle Non-Spatial I can access the following tables
NAME1.AA
NAME1.BB
NAME1.CC
NAME1.DD
NAME1.EE

If I open them as Oracle Spatial I can access the following tables
NAME1.CC
NAME1.DD

 

The database contains more than 50 tables.

 

They must be transferred to POSTGIS database with a different schema name: name2

I have already looked up some things in the community. But those are old posts and some transformers don't even exist.

 

What is the best way to approach this?

 

in short:
Oracle NonSpatial tables and Spatial tables from 1 database to POSTGIS database with a different schema name.

 

Who can help me and or has an example?

 

Thanks in advance

Best answer by ponceta

In the lower transformers you need to lowercase fme_feature_type (data) instead of fme_feature_type_name (schema). 

This approach is more advanced than the SchemaMapper because in this case you ignore the incoming columns names. ;)

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

    9 replies

    redgeographics
    Celebrity
    Forum|alt.badge.img+48

    Do you know which tables in Oracle are spatial and which are not?

    If you do, you can set up one Oracle Spatial reader and one Oracle Non-Spatial reader, then add a writer to PostGIS and simply duplicate the input feature types as output feature types. When you create the writer there’s a Table Definition parameter allowing you to copy them from the readers. 

    That’s where the fun starts though, if PostGIS enforces different rules on attribute and table names than Oracle that’ll be reflected in the output feature types. You also apparently want to rename tables anyway, so you are going to have to go through and sort that out. If it’s something basic like put all attribute names in lowercase there’s a BulkAttributeRenamer that you can use.

    scootercat_nl
    Contributor
    Forum|alt.badge.img+5
    • scootercat_nlContributor
    • Author
    • Contributor
    • November 21, 2024

    ​@redgeographics 

    thanks for your response.


    I can manage to migrate table 1 by 1.
    however, there are dozens (and then 2 databases with even more tables).

    I'm stuck on that.

    I saw a possible solution in post https://community.safe.com/data-7/lowercase-postgis-table-names-3910

    I think the last response in this post is a solution. I tried this, but it doesn't work very well and I don't know what to enter for some transformers.

    redgeographics
    Celebrity
    Forum|alt.badge.img+48

    Can you explain why it does not work very well? We might be able to help you better if we know.

    And I don’t want to discourage you, but migrating a large schema from one format to the other is going to take time and effort. 

    scootercat_nl
    Contributor
    Forum|alt.badge.img+5
    • scootercat_nlContributor
    • Author
    • Contributor
    • November 21, 2024

    -schema names in the new database are correct.

    -Nr of records pro table in the new database are correct.


    But the data and columns are not migrated. 

     

    redgeographics
    Celebrity
    Forum|alt.badge.img+48

    Is FeatureReader_2 (the table you’re showing) reading from your PostGIS database? Are all the tables like that?

     

    If so, did you get any errors or warnings in the translation log when you were writing to it?

    scootercat_nl
    Contributor
    Forum|alt.badge.img+5
    • scootercat_nlContributor
    • Author
    • Contributor
    • November 25, 2024

    there are no error messages
    As for FeatureReader 2. I have made a selection of tables. All tables have been created.

    I wonder if that is correct with the TOTAL FEATURES READ/WRITTEN lines

     

     

    redgeographics
    Celebrity
    Forum|alt.badge.img+48

    The total features read/written don't include the ones coming from FeatureReaders and FeatureWriters, so that is to be expected (and yes, it's kinda confusing)

    My suspicion is that because you're making some changes to the feature type names it loses the connection with the schema.

    I wonder if this should be better handled by the SchemaMapper transformer. There's some good tutorials on that.

    scootercat_nl
    1 person upvoted this
    • scootercat_nl
      scootercat_nl
    ponceta
    Contributor
    Forum|alt.badge.img+7
    • poncetaContributor
    • Contributor
    • Best Answer
    • December 23, 2024

    In the lower transformers you need to lowercase fme_feature_type (data) instead of fme_feature_type_name (schema). 

    This approach is more advanced than the SchemaMapper because in this case you ignore the incoming columns names. ;)

    scootercat_nl
    1 person upvoted this
    • scootercat_nl
      scootercat_nl
    scootercat_nl
    Contributor
    Forum|alt.badge.img+5
    • scootercat_nlContributor
    • Author
    • Contributor
    • December 31, 2024

    thanks everyone for the responses. This was very valuable and finally it works

    Reply


    Helpful Members This Week

    Community Stats

    30,921
    Posts
    117,189
    Replies
    38,725
    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.