Critical OID Mismatch and Data Type Casting Errors during pg_restore on Azure Database for PostgreSQL with Apache AGE Extension

Question

We are experiencing significant blockers when performing a disaster recovery (DR) and migration of Graph Databases (using the Apache AGE extension) within Azure Database for PostgreSQL.

Note on Scope: We are explicitly looking for a database-level backup/restore solution (e.g., pg_dump/pg_restore). We cannot rely on Azure’s Server-Level Restore (Point-in-Time Restore) as it is not viable for our operational requirements, which involve moving individual databases between environments and maintaining independent backup lifecycles.

Key Issues Detected:

OID Inconsistency in Managed Environment: During a pg_restore to a new database, the Azure system catalog assigns new OIDs to the ag_graph and ag_label objects. Because Apache AGE encodes the original OID within the 64-bit ID of every vertex and edge, the restored data becomes logically disconnected. This results in the following error when querying:

ERROR: graph with oid 44482 does not exist

Casting & Operational Restrictions (Error 42846): Post-restore synchronization scripts fail. The Azure environment restricts casting between graphid and bit(64) or bigint, preventing us from fixing the ID mapping manually:

ERROR: cannot cast type graphid to bit

Scalability Issues (Error 22003): When processing multi-gigabyte datasets, arithmetic re-mapping attempts trigger bigint out of range errors, suggesting limitations in how the managed engine handles intermediate calculations on these custom types.

Logical Restore Viability: While a logical restore using --column-inserts bypasses the OID issue, it is unacceptable for large-scale production databases (GBs of data) due to extreme execution times, high IOPS consumption, and massive WAL overhead.

Expected Resolution:

We require a supported method to perform a database-level binary restoration that either:

• Preserves the internal OIDs of the Apache AGE extension.

Provides a high-performance mechanism to re-align Graph OIDs post-restoration without hitting the current casting and range restrictions of the managed service.We are experiencing significant blockers when performing a disaster recovery (DR) and migration of Graph Databases (using the Apache AGE extension) within Azure Database for PostgreSQL.

Note on Scope: We are explicitly looking for a database-level backup/restore solution (e.g., pg_dump/pg_restore). We cannot rely on Azure’s Server-Level Restore (Point-in-Time Restore) as it is not viable for our operational requirements, which involve moving individual databases between environments and maintaining independent backup lifecycles.

Key Issues Detected:

OID Inconsistency in Managed Environment: During a pg_restore to a new database, the Azure system catalog assigns new OIDs to the ag_graph and ag_label objects. Because Apache AGE encodes the original OID within the 64-bit ID of every vertex and edge, the restored data becomes logically disconnected. This results in the following error when querying:

ERROR: graph with oid 44482 does not exist

  __Casting & Operational Restrictions (Error 42846):__ Post-restore synchronization scripts fail. The Azure environment restricts casting between `graphid` and `bit(64)` or `bigint`, preventing us from fixing the ID mapping manually:
  
  > `ERROR: cannot cast type graphid to bit`
  
     __Scalability Issues (Error 22003):__ When processing multi-gigabyte datasets, arithmetic re-mapping attempts trigger `bigint out of range` errors, suggesting limitations in how the managed engine handles intermediate calculations on these custom types.
     
        __Logical Restore Viability:__ While a logical restore using `--column-inserts` bypasses the OID issue, it is __unacceptable for large-scale production databases (GBs of data)__ due to extreme execution times, high IOPS consumption, and massive WAL overhead.
        
        ### __Expected Resolution:__

        We require a supported method to perform a __database-level binary restoration__ that either:
        
           Preserves the internal OIDs of the Apache AGE extension.
           
              Provides a high-performance mechanism to re-align Graph OIDs post-restoration without hitting the current casting and range restrictions of the managed service.
              
                
              Thanks a lot
              

Answer 1

Hello soriano morales, miguel angel (ext),

Welcome to the Microsoft Q&A and thank you for posting your questions here.

I understand that you are having critical OID Mismatch and Data Type Casting Errors during pg_restore on Azure Database for PostgreSQL with Apache AGE Extension.

Running Apache AGE on Azure’s managed PostgreSQL cannot ensure stable OIDs, so the only reliable path is to shift the workload to a self‑hosted PostgreSQL instance or Azure VM. This setup gives full control over system catalogs and prevents OID regeneration during restores. It also aligns properly with AGE’s internal graph ID structure. Official PostgreSQL design confirms that catalog OIDs are not preserved on managed services: https://www.postgresql.org/docs/current/datatype-oid.html.

To maintain graph integrity without casting failures or ID corruption, physical binary backups must replace pg_dump/pg_restore. Using pg_basebackup ensures that every catalog entry, graph label, and graph OID is restored exactly as originally written. This method avoids Azure’s casting restrictions that block graphid conversions. - https://www.postgresql.org/docs/current/app-pgbasebackup.html.

Deploying PostgreSQL + Apache AGE on an Azure VM preserves the benefits of cloud hosting while restoring full low‑level database control. You can install AGE manually, customize extension behavior, and manage storage snapshots as needed for disaster recovery. Full cluster snapshots eliminate bigint overflows caused by graph ID remapping attempts. Azure VM deployment guidance: https://learn.microsoft.com/azure/virtual-machines/linux/quick-create-portal.

Finally, graph ID repair through casting or recomputation must never be attempted, as Azure’s managed engine blocks these operations for internal type safety. Preserving original graph IDs is only possible through physical cluster‑level backups. Avoid all attempts to convert or reinterpret graphid, since AGE encodes the OID and local identifier internally. Check this Apache AGE documentation.

I hope this is helpful! Do not hesitate to let me know if you have any other questions or clarifications.

---

Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful.

Answer 2

Hi **soriano morales, miguel angel,
What we cannot do:

• You cannot use pg_dump/pg_restore to move an AGE graph database and keep it working if your data contains stored graphid values (vertices/edges), because AGE 1.6 encodes a catalog OID (label/graph OID) into the 64-bit graphid.
• A logical restore recreates objects with new OIDs, so existing graphids point to objects that no longer exist → the “graph with oid … does not exist” failure mode.
• On Azure Database for PostgreSQL Flexible Server, you also generally can’t use superuser-only/binary-upgrade/catalog manipulation techniques that might otherwise let you force OIDs or create low-level casts to rewrite graphid values.

What is supported here:

 Option 1 (only way to preserve graphids): use physical/server-level restore which is the best/recommended:

 If we must preserve existing vertex/edge IDs exactly, then you need a restore method that preserves the physical catalog/OIDs:

• Azure PITR / geo-restore / replica promotion (server-level) works because it’s effectively physical.

Option 2 (database-level move): logical migration by rebuilding graphids

If you must move individual databases with pg_dump/pg_restore, then you need to accept that graphids will change and migrate in a way that reconstructs vertices/edges:

High-level approach that scales (avoids --column-inserts):

1. Ensure every vertex has a stable external key (UUID/business key in properties or an extra column).
2. COPY source vertices/edges into staging relational tables (fast bulk load).
3. Bulk INSERT … SELECT into AGE label tables so AGE generates new graphids.
4. Build a mapping table (external_key -> new_graphid) and then bulk insert edges by joining that mapping.

This is the only database-scoped approach that stays within managed-service restrictions and can be made performant.
For example:

1. Export from source (client-side files via psql \copy) (e.g. to csv file)
2. Prepare target graph + labels (fresh OIDs, fresh graphids)
3. Load into staging tables (client-side files via psql \copy)
4. Bulk insert vertices (AGE generates new graphid)
5. Build an ext_id -> new graphid mapping table,  and bulk insert edges by joining through the mapping
6. Reindex/analyze

In that way:

• No dependency on preserved OIDs: you’re not restoring the old graphids; you’re letting the target generate valid ones.
• No casting graphid to bit/bigint: you never do low-level remapping.
• Scales much better than --column-inserts: the heavy operations are COPY into staging + set-based inserts/joins.

 But option 1 is the best,
can we know why PITR/replica is not an option here per their requirements?

Hope this helps. If you have any follow-up questions, please let me know. I would be happy to help.

Please do not forget to "up-vote" wherever the information provided helps you, as this can be beneficial to other community members.