how to Migrate data(with schema) from MSSQL to PostgreSQL?

2265
12
Jump to solution
02-28-2019 09:54 PM
Tshering_Lham
New Contributor II

i have a ARCSDE enterprise geodatabase in MSSSQL Server 2008. I want to replace MSSQL Server with PostgreSQL.

i have setup the enterprise geodatabase in PostgreSQL and now want ti import the data along with the schema to PostgreSQL.

i tried with export/import XML Workspace however my data is quite big(around 7GB) and its taking a very long time plus its giving some error while importing the XML Workspace.

can anyone help me how to  import data (with schema) from MSSQL to PostgreSQL ?

0 Kudos
1 Solution

Accepted Solutions
George_Thompson
Esri Frequent Contributor

I would recommend upgrading the "old" EGDB to a newer version that is compatible with your ArcGIS client v10.4: Client and geodatabase compatibility—ArcGIS Help | ArcGIS Desktop  These type of updates can sometimes include multiple steps to get the needed information out of an older version.

Then you could do the copy/paste over to the new Postgres EGDB.

Are you running Postgres version 10.4? Or is the new enterprise geodatabase at Esri's version 10.4?

--- George T.

View solution in original post

12 Replies
NeilAyres
MVP Alum
0 Kudos
Tshering_Lham
New Contributor II

Thanks Neil. I tried this method but somehow i am getting error in importing some feature dataset especially those features that has huge data!. i am able to successfully import some feature datasets  though!

so I am wondering if the problem is with my source feature dataset. But then it is working well in the current MSSQL setup! so i am confused.

0 Kudos
George_Thompson
Esri Frequent Contributor

I would recommend getting your SQL Server EGDB compressed (if versioned data is present), delete all the versions (except for DEFAULT), then Copy/Paste into new PostgreSQL EGDB as the data owner you want. It should cope over the schema and everything you have as it currently is.

Copying a geodatabase using the Copy tool—ArcGIS Help | ArcGIS Desktop 

Copying feature classes using Export—ArcGIS Help | ArcGIS Desktop 

Methods to move a geodatabase in PostgreSQL—Help | ArcGIS Desktop 

GeodatabaseEnterprise GIS

--- George T.
Tshering_Lham
New Contributor II

Thanks George. I will try the copy and paste method and see if it works for me.

 I tried with export/import and drag and drop method in arc catalog but i am not able to import few featuredatasets.(errors saying target table is not registered...) . i was able to import some  feature datasets successfully with this method.

Do you have any idea why i am getting this error on just few feature dataset?

All the feature datasets in the current setup(ArcGIS Enterprise Geodatabase(9.2) in MSSQL 2008) is working well.

thank you in advance.

0 Kudos
George_Thompson
Esri Frequent Contributor

It could be that the client you are using cannot connect to both SQL Server and Postgres correctly. Especially if the old enterprise GDB is at v9.2 and SQL 2008.

--- George T.
0 Kudos
Tshering_Lham
New Contributor II

Thanks George.

The new ArcSDE enterprise geodatabase i created in Postgresql is verison 10.4 and the client is also 10.4. Do you think i should first upgrade the old geodatabase which is at ver 9.2 to 10.4 and then export/import the data/schema ? or can i directly import the data/schema to the 10.4 version geodatabase?

As a test, i first imported just the schema and the imported the data in it through XML Recordset Document and the data was successfully imported. i am wondering if this will be correct way to do it?

thanks in advance.

0 Kudos
George_Thompson
Esri Frequent Contributor

I would recommend upgrading the "old" EGDB to a newer version that is compatible with your ArcGIS client v10.4: Client and geodatabase compatibility—ArcGIS Help | ArcGIS Desktop  These type of updates can sometimes include multiple steps to get the needed information out of an older version.

Then you could do the copy/paste over to the new Postgres EGDB.

Are you running Postgres version 10.4? Or is the new enterprise geodatabase at Esri's version 10.4?

--- George T.
Tshering_Lham
New Contributor II

Thanks George for the recommendation. i will try to upgrade my current EGDB from 9.3 to 10.4 and test the copy paste method to new postgrs EGDB.

My postgres is v 9.4.5 which is compatible with arcgis 10.4.I saw that the postgres version supported by arcgis 10.4 is postgres 9.4.5 so i downloaded and installed postgres 9.4.5.

0 Kudos
George_Thompson
Esri Frequent Contributor

Sounds good. Look forward to hearing the results.

--- George T.
0 Kudos