Background

As part of the Biotics AWS migration, NatureServe now provides data backups as native Postgres dump files (pg_dump) rather than Oracle-compatible exports. This format cannot be loaded directly into Oracle or SDE. This article outlines the steps required to get the data from a Postgres dump file into your SDE environment.

This same standard export is provided to all Biotics programs, so the format and delivery method (via Kiteworks) cannot be customized per program. The steps below describe how to adapt your local import process to work with this format.

Overview of the Process

  1. Download the backup file
  2. Set up Postgres and restore the dump file into it
  3. Verify the restored data
  4. Move the data from Postgres into your SDE tables
  5. Schedule and repeat the process on your required cadence

Step 1: Download the Backup File

  1. Log in to Kiteworks using your existing credentials.
  2. Navigate to the folder where NatureServe posts your program's backup file.
  3. Download the pg_dump file to a secure local or server location.

Step 2: Set Up Postgres and Restore the Backup

Before you can move any data into SDE, you need a running Postgres database with the backup restored into it.

  1. Choose a hosting option for Postgres:
    • Self-hosted: Install PostgreSQL on a local server or VM your team controls (no license cost).
    • AWS RDS for PostgreSQL: A managed instance in AWS; incurs hosting cost but no license fee, and AWS handles patching/backups for you.
  2. Follow the detailed setup and restore instructions here: Import B6 Backups
    • This document covers installing required Postgres extensions (PostGIS, Orafce), importing database roles/users, setting passwords, and running pg_restore to load the backup file.
    • Note: That document's extension-installation steps are written for a self-managed Windows Postgres install. If you're using a hosted option such as AWS RDS or Azure, both PostGIS and Orafce are already provided by the service — skip that section and go directly to importing roles/users and restoring the backup.
  3. Once restored, confirm you can connect to the database and see the expected tables, using psql or a client such as pgAdmin.

Note: PostgreSQL is free and open-source — there is no licensing cost regardless of hosting choice. Setup of a basic instance typically takes under an hour. If your team has only worked with Oracle previously, expect a small learning curve with Postgres-specific tools and syntax; budget roughly a day or two to get comfortable.

Step 3: Verify the Restored Data

  1. Run row counts on key tables and compare against expected values (e.g., previous backup or known record counts).
  2. Spot-check a sample of records against known values from your current SDE tables.
  3. Confirm any date, encoding, or numeric fields imported correctly (Postgres and Oracle have some data type differences worth checking, e.g., boolean handling, date/time formats).

Step 4: Move Data from Postgres into SDE

Once the data is verified in the staging Postgres database, choose the method below that best fits your team's existing tools and skills.

Option A — FME (Safe Software)

Best fit if your team already has FME licensed.

  1. Create an FME workspace with a PostgreSQL reader pointed at your staging database.
  2. Add a writer targeting your SDE feature classes/tables.
  3. Map source tables/fields to destination tables/fields.
  4. Run the workspace manually to confirm correct output; verify versioned tables behave as expected.
  5. Once verified, schedule the workspace to run automatically via FME Server/Flow or a scheduled task.

Option B — arcpy Script

Best fit if your team has ArcGIS licensing and Python scripting experience.

  1. Write a Python script using arcpy and a Postgres connector (psycopg2 or an ODBC connection) to read from the staging database.
  2. Use arcpy.management.Append or arcpy.conversion.TableToTable to load records into the corresponding SDE tables.
  3. Test against a non-production copy of your SDE tables first.
  4. Schedule the script to run via Windows Task Scheduler or a similar scheduler.

Option C — Oracle Heterogeneous Connectivity / Database Gateway

Best fit if your team prefers running SQL directly against the Postgres instance from Oracle.

  1. Confirm your Oracle license includes the Heterogeneous Services/Gateway option.
  2. Install and configure an ODBC driver for PostgreSQL on the Oracle server.
  3. Create a database link in Oracle pointing to the staging Postgres database.
  4. Write SQL statements (e.g., INSERT INTO local_table SELECT * FROM postgres_table@dblink) to pull data across.
  5. Use caution with versioned SDE tables — raw SQL inserts do not automatically handle Esri versioning; involve your GIS administrator if any target tables are versioned.

Option D — Flat-File (CSV) Export

Best fit if your team wants to reuse an existing bulk-load process with minimal changes.

  1. Export the needed tables from the staging Postgres database to CSV:
    \copy table_name TO 'path/to/table_name.csv' WITH CSV HEADER
  2. Feed the resulting CSV files into your existing bulk-load process (the same one currently used for Oracle-based imports), adjusting only the input file format.

Step 5: Schedule for Ongoing Updates

  1. Determine your required update cadence (e.g., weekly or biweekly) based on how frequently NatureServe posts new backups.
  2. Automate Steps 2 and 4 (restore and load) to run on that schedule, either through your chosen tool's built-in scheduler (FME Server, Task Scheduler, cron) or your organization's job scheduling system.
  3. Set up a notification or log review process so failures are caught promptly, given at least one downstream application requires 24/7 availability.

Notes and Considerations

  • Versioned SDE tables: If any target tables are versioned, use Option A or B (FME or arcpy), as these handle Esri versioning correctly. Option C (raw SQL) requires additional care.
  • Custom/local tables: The NatureServe backup contains only the core Biotics data model tables. Any custom tables your program maintains locally are not included and must continue to be managed separately.
  • Testing before go-live: Run the full process (Steps 2–4) against a test/non-production SDE environment at least once before your conversion date to confirm timing and catch issues early.

Who to Contact

  • NatureServe Biotics Migration Team — questions about backup format, delivery schedule, or table contents
  • Your internal GIS/database administrator — implementation of Steps 2 and 4
  • Your Esri/FME support contact (if applicable) — licensing or tool-specific configuration questions