ArcSDE Enterprise Geodatabase and PostGIS Integration under same PostgreSQL Database

 After few days crawling the GIS Stackexchange and many hours of reading ESRI ArcGIS Help Documents, finally I can manage to integrate ArcSDE Enterprise Geodatabase and PostGIS to be working together on a single PostgreSQL database. This configuration will enables you to use most of the cool features of Enterprise Geodatabase as well as PostGIS. For example, I can do Multiuser Editing Sessions using ArcGIS and QGIS at same time, I can enable Editor Tracking feature from Enterprise Geodatabase, perform versioning or archiving, but I also can connect my geospatial data to BI software like Tableau in Live Settings (not extracted). The latter is very important for me because up until this article writing, Tableau has not supported Enterprise Geodatabase Connection thru database connection. They only provide it thru API Services Connection (OGC WFS or ArcGIS Map Services). 

Here are the steps that I compiled after many hours of trial and errors

1. copy st_geometry from.dll C:\Program Files (x86)\ArcGIS\Desktop10.4\DatabaseSupport\PostgreSQL\9.4\Windows64 to C:\Program Files\PostgreSQL\9.4\lib (or whatever version of arc and postgresql you have)

2. run esri tool "create enterprise geodatabase" (this tool will create a new postgresql DB with the needed sde login name and supply the password), don't forget to prepare your keycodes file generated from arcgis server installation.

3. create a superuser called postgis, then created a schema in the database called postgis and ran create extension postgis *this could be bad practice to have another superuser in the DB but I did not want the postgis functions to be in the same schema as the SDE. Schema creation and PostGIS extension installation can be done from PgAdmin4. Make sure geometry_columns and geography_columns can be found in Public Schema views after installation, or else you must re-run the postgis extension installation. 

4. run esri tool "export geodatabase configuration keywords". database connection = the DB you just created

edit the file in notepad, go to line 27 change ST_GEOMETRY to "PG_GEOMETRY", SAVE!

5. run esri tool "import geodatabase configuration keywords" for that database connection and any layer you bring in now should have postgis default backend with sde functionality!

6. Data import can be done from shptopgsql-gui.exe, available from C:\Program Files\PostgreSQL\11\bin\postgisgui . When you are first using it after PostgreSQL installation, run shp2pgsql.exe from C:\Program Files\PostgreSQL\11\bin (this step is not needed if you are using pgAdmin3 because it is already part of the pgAdmin3)

7. When you are importing shp using shptopgsql-gui.exe always set the schema to sde. Set the SRID to 0. 

8. After successful import, the data would be available from arccatalog/arcpro, to fully use the SDE capabilities, Do register to geodatabase (from left contex menu > administer geodatabase) for every imported feature classes, then you can do versioning, enable editor tracking, archiving and replication.

9. As the last step, set the coordinate system after you are done registering the data to database. You can use Define Tool or Layer Properties in the ArcCatalog. 

I have a plan to make a series of video tutorial about how to do those steps above so it can be more clear and easier to follow for you all the readers. Just Stay Tuned.