How to migrate SQLserver data to PostgreSQL?

Question

Have several sde geodatabases stored in SQL Server Express instance.

Just created new Linux Ubuntu instance of PostgreSQL, added st_geometry.so file to /lib.

I used the create enterprise geodatabase tool in ArcMap; sucessfully created sde gdb on PostgreSQL server.

To migrate the data:

1) export XML workspace file from SQL server sde gdb

2) attempt to import the XML workspace file to the PostgreSQL sde gdb

Error:

"database user name and current user schema do not match".

Can someone explain how to migrate the data to new PostgreSQL instance?

thanks.

danbecker · Answer

I used the Create Database User tool, then added user DBO to the new postgreSQL db. I then connected to the postgreSQL gdb as user DBO and was able to import the .XML workspace file. Now, when I try to register the postgreSQL gdb.sde connection file with ArcGIS Server, I get this error: "Machine: [: The connection property set was missing a required prooperty or the property value was unrecognized. Bad login user]" Doesn't matter if I edit the .sde connection file to connect as postgres, sde or DBO user, they all give the same error above when attempting to register with ArcGIS Server. edit: got this figured out! Looked at the PostgreSQL log and saw entries: " not granted access in pg_hba.conf" Then remembered, I restricted PostgreSQL client access to a single workstation (mine) for testing... Added in the entire LAN and now everything is working!! thanks again.

Asrujit_SenGupta · Answer

Check this link: Add login roles to PostgreSQL—Help | ArcGIS Desktop

PostgreSQL uses roles to log in to the database cluster and databases. Individual users are called login roles. For all login roles that will own objects in the geodatabase, you must also create a schema in that database. To use PostgreSQL with ArcGIS, the schema must have the same name as and be owned by the login role.

You can use the Create Database User tool to add a login role that can create tables and feature classes. The tool creates a login role in the PostgreSQL database cluster, creates a matching schema for the user in the database you specify, and grants USAGE privileges on the new schema to the public.