I've had mixed results with this. I'm also attempting to create egdbs in Azure Postgres Flexible Server. Until very recently, I could create a egdb by following this process:
- Connect to the database as administrator
- psql -U <admin user> -h <hostname> -d postgres
- Create the database
- CREATE DATABASE "database_name";
- Create the sde role (user)
- CREATE ROLE sde WITH LOGIN PASSWORD '<password>';
- Grant all privileges on the database to the sde role
- GRANT ALL PRIVILEGES ON DATABASE "database_name" TO sde;
- Connect to the database as administrator
- \c "database_name"
- Create the sde schema
- CREATE SCHEMA sde;
- Grant all rights on the sde schema to the sde role
- GRANT ALL ON SCHEMA sde TO sde;
- Enable the POSTGIS extension in Azure Database for PostgreSQL flexible server
- Settings > Server Parameters
- Search for "azure.extensions"
- Enable POSTGIS (for enterprise geodatabase) extension and Save
- Create the extension
- CREATE EXTENSION IF NOT EXISTS postgis;
- Grant usage on the sde schema to the public role
- GRANT USAGE ON SCHEMA sde TO public;
- Use the "Enable Enterprise Geodatabase" tool in ArcGIS Pro with your authorization file
This worked until just sometime within the last few days. Then, I started getting this error in my sde_setup.log:
[11:37:22.894] In SE_arcsde_schema_setupEx() ...
[11:37:22.894] Calling sdepgsrvr.dll::DB_arcsde_setup() ...
[11:37:22.932] ev.SQLstate = 42P01
SDE DBMS Error: -37 ERROR: relation "pg_dist_node" does not exist
LINE 1: SELECT nodename FROM pg_dist_node WHERE position('innovation...
^
[11:37:22.951] Current bytea_output setting in postgresql.conf is "hex".
Require 2 bytes of storage for each byte of input data.
[11:37:22.996] db_execute_sql Execute Error (-25).
[11:37:22.996] ERROR: CREATE EXTENSION POSTGIS SCHEMA PUBLIC failed (-25)
ERROR: Only members of "azure_pg_admin" are allowed to use CREATE EXTENSION
SQLSTATE=42501
[11:37:22.997] ERROR installing/upgrading ArcSDE, Error = -25
File: C:\Users\CJTHOM~1\AppData\Local\Temp\sde_setup.log
Mode:
GMT Time: 2024-10-23T16:47:28+00.00
Local Time: 2024-10-23T11:47:28-05:00
So I added my sde user to the "azure_pg_admin" role, and I'm able to create and then enable egdbs again. I'm desperately trying to figure out what's changed in the last week but haven't been able to figure it out. I think some backend update to Azure Postgres but I'm not sure.
