Migrating an existing geodatabase to an existing empty database in PostgreSQL using PostGIS extension. (Schema and Procedure error)

Question

I am attempting to migrate/export an existing geodatabase into an existing database that I created in pgAdmin. When I attempt to transfer tables inside my existing geodatabase into my PostgreSQL database connection, I receive a schema error. The error I keep receiving is a schema error reading:

Message [Error]: ERROR 000260: Database user name and current user schema do not match. [Database user name and current user schema do not match.] [arcgis_db.sde.DataSources]

I also have an error when copy a featureclass that reads:

Message [Error]: ERROR 000260: Function or procedure does not exist [Function or procedure does not exist] [arcgis_db.sde.GDB_Items][STATE_ID = 0] Message [Error]: ERROR 000260: Function or procedure does not exist [Function or procedure does not exist [arcgis_db.sde.GDB_Items][STATE_ID = 0] Message [Error]: Failed to execute (CopyMultiple).

The CRS I have set to the featureclasses and data inside my existing geodatabase is WGS 1984, so that can't be the issue. It seems like I have something incorrectly set up with my PostgreSQL database. The context to my problem is that I am attempting to migrate my existing geodatabase to PostgreSQL so that I can then deploy the geodatabase from PostgreSQL to ArcGIS Server. I can't deploy geodatabases from ArcGIS Pro it seems to Server, so I am trying to do this from PostgreSQL. I tried simply copying and pasting the data in the ArcGIS Pro contents pane, as documentation suggested was plausible. This is where I get errors. But I also get an error when using the export tool of a single featureclass.

Here are my software versions:

• ArcGIS Pro Version: 3.2.0
• pgAdmin Version: 8.10
• PostGIS Extension: 3.4
• PostgreSQL 14

I have followed the seemingly correct procedures to set up my database connection to PostgreSQL. That procedure is as follows:

1. Installed PostgreSQL
2. Installed PostGIS
3. Created a new PostgreSQL Database using CREATE DATABASE your_database_name;
4. Enabled PostGIS in my Database using CREATE EXTENSION postgis;
5. Created a database connection in ArcGIS Pro
6. Right-clicked databases and selected new database connection
7. Entered the following: Database Platform: PostgreSQL, Instance (host & port info.), Database Name, username/password. Then I verified and created it.
8. I also followed these steps to grant specific privileges to users just in case if multiple people will be working with the database in SQL with these following commands:

GRANT ALL PRIVILEGES ON DATABASE my_database_name_here TO my_user;

and this command:

GRANT USAGE ON SCHEMA public TO my_databasename_here; Neither of these made an effect to

Also, here is a screenshot of the properties of my PostgreSQL database:

Answer 1

The problem here is you have two paths to mirror an existing geodatabase into a new database:

1. Install an enterprise geodatabase in the new database, and
2. Choose to only have Query Layers available in PostGIS geometry database (without versioning, replication, relationship classes, and the rest of "geodatabase" functionality).

In either case, the ArcGIS tools will only function if schemas are created that match the login roles to be used to create data. Your procedure only mentions making connection files, and seems to indicate that you have user tables owned by the sde login. So you appear to have a database that could be configured with Create Enterprise Geodatabase or Enable Enterprise Geodatabase, but you haven't done that, and you haven't created schemas in the new database to match the schemas in the old one.

The correct procedure to follow for ArcGIS enterprise geodatabase configuration is:

1. Create PostgreSQL instance (using a supported or later minor release of PostgreSQL)
2. Create a database (lowercase names only)
3. Add the PostGIS extension
4. Create login roles (lowercase names only)
5. Create group roles (lowercase names only)
6. Create schemas in the database which match the login roles which will own data
7. Use Create Enterprise Geodatabase (requires a license)
   --OR--
   Use Enable Enterprise Geodatabase (also requires a license)
   --OR--
   Skip using a licensed Enterprise Geodatabase entirely
8. Use FeatureClassToFeatureClass or FeatureClassToGeodatabase or TableToTable or TableToGeodatabase or CREATE TABLE or any other ETL tool to load data.

You haven't mentioned steps 4, 5, 6, or 7, so it's not surprising that step 8 is not going well.

Note that best practice is to organize login/schemas and roles so that there are at least three logins (sde [if it is to be an EGDB], one or more data owners, logins for each connected user, and as many group roles as is necessary to implement your security model). The sde login should ONLY own the Enterprise geodatabase metadata tables, NEVER any user data. If you use an SSO solution, try to avoid having individual users own any shared data (so that user doesn't have to live on indefinitely like a ghost).

Older versions of ArcGIS (none that are currently actively supported) would not support 64-bit integers or certain other types, and would not permit group role ownership of tables.

While non-enabled databases are valid way to manage a publishing database, the lack of common editing tools (the Edit tab of ArcGIS Pro) will make simple editing tasks into something exotic, probably involving triggers and circuitous paths leveraging arcpy.ArcSDESQLExecute(). It can be done, but it's easier done by someone who is so familiar with the ArcGIS procedures and tools that generating an equivalent on the fly in SQL doesn't really require cognition.