PostgreSQL Geodatabase Management

PostgreSQL Geodatabase Management

PostgreSQL, when combined with the PostGIS extension, offers a powerful backend for managing spatial data. It’s the foundation for enterprise-grade ArcGIS Geodatabases. Administering a PostgreSQL-based geodatabase might seem straightforward at first, but once you begin to deal with schemas, roles, permissions, and data restoration, things can quickly get complex. In this article, we’ll walk through a real-world PostgreSQL geodatabase setup tailored for ArcGIS use cases, explain how to restore data properly, and explore how to fix some common errors during the import process.

🏗️ Creating the Database and Preparing the Environment

First, we create a new database that will host our spatial data:

CREATE DATABASE dashboarddb;

Make sure the sde role (used by ArcGIS clients) exists. If not, create it with superuser privileges (you can revoke them later for better security):

CREATE ROLE sde LOGIN ENCRYPTED PASSWORD 'test123' SUPERUSER INHERIT;

Now connect to the database and enable PostGIS:

\c dashboarddb
CREATE EXTENSION postgis;

Define the schemas to organize your spatial and business data:

CREATE SCHEMA sde AUTHORIZATION sde;
CREATE SCHEMA dashboard;
CREATE SCHEMA ibbpersonel;

Grant usage privileges to allow access between schemas:

GRANT USAGE ON SCHEMA sde TO dashboard;
GRANT USAGE ON SCHEMA sde TO ibbpersonel;

✍️ Note: If you receive a permission error like:

ERROR: permission denied for schema sde: you may need to also grant USAGE to the public schema or adjust schema privileges accordingly.

📀 Backing Up the Source Database

To migrate the contents from a source database, use pg_dump in directory format for better parallelism:

pg_dump --create --clean --if-exists --format=d --jobs=2 --verbose \
  --file=/var/pg_backup/31102024platformdb.dump \
  postgres://postgres:dummy@10.5.70.62:9998/platformdb

🔄 Restoring the Database in Stages

Restoring a PostgreSQL geodatabase can be tricky. It’s best to restore schema-by-schema to avoid permission conflicts and dependency errors.

Restore the public schema:

pg_restore --verbose -n public --clean --if-exists --format=d \
  --dbname=postgres://postgres:test123@10.**.**.**:5432/dashboarddb \
  31102024dashboard.dump

Restore the sde schema:

pg_restore --verbose -n sde --clean --if-exists --format=d \
  --dbname=postgres://postgres:test123@10.**.**.**:5432/dashboarddb \
  31102024dashboard.dump

Restore everything else excluding public and sde:

pg_restore --verbose -N public -N sde --clean --if-exists --format=d \
  --dbname=postgres://postgres:test123@10.**.**.**:5432/dashboarddb \
  31102024dashboard.dump

⛔️ Warning: Before restoring, terminate all active connections to avoid locked objects and restore failures.

🛠️ Post-Restore Fixes: Version Metadata

Once the restore is complete, you might need to manually update the version metadata table to match your target environment:

UPDATE sde.sde_version
SET bugfix = 0,
    description = '10.8.0.2.5 Geodatabase';

This helps avoid version compatibility issues with ArcGIS clients.

🧩 Common Restore Error: Missing OID Column

If you get an error like:

ERROR: column “oid” does not exist

This usually happens when a legacy ArcGIS script expects an OID column that doesn’t exist in the restored table. The fix may involve:

• Checking the source schema compatibility
• Performing a Geodatabase upgrade using ArcGIS Pro (Right-click > Properties > Upgrade)

📖 More Info:
 ESRI Knowledge Base — Attribute column not found error
 ESRI Proceedings: Working with Geodatabases in PostgreSQL

✅ Conclusion

Administering PostgreSQL geodatabases, especially in ArcGIS environments, requires more than resotore unspatial data. Make sure you always test your restore process in a non-production environment and check for schema-level mismatches early.