topic Re: how to Migrate data(with schema) from MSSQL to PostgreSQL? in Data Management Questions

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

Tshering_Lham — Fri, 01 Mar 2019 05:54:27 GMT

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 ?

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

NeilAyres — Fri, 01 Mar 2019 12:40:20 GMT

Would this not work for you??

Exporting a geodatabase schema to an XML workspace document—ArcGIS Help | ArcGIS Desktop

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

George_Thompson — Fri, 01 Mar 2019 13:20:25 GMT

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

Geodatabase‌ Enterprise GIS‌

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

Tshering_Lham — Mon, 04 Mar 2019 03:46:02 GMT

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.

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

Tshering_Lham — Mon, 04 Mar 2019 04:01:14 GMT

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.

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

George_Thompson — Mon, 04 Mar 2019 21:03:33 GMT

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.

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

Tshering_Lham — Tue, 05 Mar 2019 04:09:10 GMT

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.

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

George_Thompson — Tue, 05 Mar 2019 11:57:54 GMT

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?

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

Tshering_Lham — Wed, 06 Mar 2019 04:38:45 GMT

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.

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

George_Thompson — Wed, 06 Mar 2019 13:40:45 GMT

Sounds good. Look forward to hearing the results.

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

Tshering_Lham — Wed, 13 Mar 2019 04:29:21 GMT

Hi George,

i am trying/testing the EGDB upgrade from 9.3 to 10.2.2 and i was able to upgrade successfully. i am yet to do the copy/paste method to move from mssql to postgres.

Just one doubt i want to ask is that i did not install ArcGIS for Server in my local system (i have arcgis desktop advanced installed and msssql server). i did a backup/restore method to move a copy of the database from production server to my local system and used the upgrade geodatabase tool to do the upgarde. so i am bit confused since i did not have the Arcgis for server/Arcsde installed in my local system, how the EGDB upgrade work or is what i did the correct way?

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

Tshering_Lham — Wed, 20 Mar 2019 03:52:20 GMT

Hi George,

As recommended by you, i have upgraded my EGDB 9.3 in MSSQL Server 2008 to 10.4.1 in MSSQL Server 2012.

i did drag/drop method to transfer my data+schema from MSSQL(2012) to postgres(9.4.5) and seems to be working fine.

Thank you very much for your help.

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

Asrujit_SenGupta — Wed, 20 Mar 2019 03:57:19 GMT

It is not required to have ArcGIS for Server or ArcSDE installed on the local system, to perform the Geodatabase upgrade. Just the ArcGIS Desktop is enough for that.