Using foreign tables with Aurora Global Database

Aurora Global Database supports running analytical queries with per-Region setup. This section describes how to configure analytical queries across Regions, which resources replicate automatically, and how to plan for disaster recovery scenarios.

Using aurora_analytics with a Global Database

A foreign table is a PostgreSQL table definition that points to your Apache Iceberg or Parquet data in Amazon S3, Amazon S3 Tables, or the AWS Glue Data Catalog. No data is copied into Aurora. Only the following replicate from the primary DB cluster to each secondary DB cluster:

The following do not replicate and must be provisioned independently in every Region that contains a secondary DB cluster:

Setting up on a Global Database

Step 1: Create the IAM policy and role in every Region

The IAM role association does not replicate between the primary and secondary DB clusters. You must create and attach an IAM role with the AuroraAnalytics feature name in every Region that contains a primary or secondary DB cluster.

Step 2: Set up networking in every Region

VPC configuration also does not replicate. In the VPC for each DB cluster (primary and every secondary), provide a network path to the required services so that the DB cluster can reach the foreign table data to resolve queries. Ensure each secondary's cluster networking configuration permits reaching the foreign table's Region.

For the AWS Glue and AWS STS interface endpoints, enable Private DNS and allow inbound TCP 443 from your VPC CIDR.

Step 3: Install the extension on the primary

Connect to the writer DB instance in the primary DB cluster and install the extension in each database where you intend to create foreign tables:

CREATE EXTENSION aurora_analytics;

The extension replicates to each secondary DB cluster automatically.

Step 4: Create foreign tables on the primary

On the writer DB instance in the primary DB cluster, create foreign tables pointing to your data:

-- Parquet via S3 URI
CREATE FOREIGN TABLE ft_orders ()
SERVER aurora_analytics_server
OPTIONS (location 's3://my-bucket/orders/',
         format 'parquet', region 'us-east-1');

-- Iceberg via Glue ARN
CREATE FOREIGN TABLE ft_transactions ()
SERVER aurora_analytics_server
OPTIONS (location 'arn:aws:glue:us-east-1:123456789012:table/mydb/transactions',
         format 'iceberg');

Aurora replicates the DDL to each secondary DB cluster, where the foreign tables are available for querying.

Step 5: Verify on the secondary

Connect to a secondary DB cluster and confirm replication and read access:

SELECT extname, extversion FROM pg_extension WHERE extname = 'aurora_analytics';
SELECT count(*) FROM ft_orders;

Region pinning of foreign tables

When you create a foreign table, its data location and Region are stored in the table definition and replicate to every DB cluster in the global database. Every DB cluster reads from the same location.

This implies:

General guidance for DR readiness

Complete the per-Region setup in Steps 1–2 for the secondary DB cluster, then decide how the DB cluster will access foreign table data:

Handling switchover and failover

During an Aurora Global Database switchover or failover, foreign table queries have an additional dependency beyond standard database client connections: they must reach Amazon S3, AWS Glue, and AWS STS in the Region named by each foreign table.

Global Database Switchover

Both the primary and secondary DB clusters are healthy at the time of switchover. After the new primary DB cluster becomes available, queries continue to read from the Region named in each foreign table. If that Region is different from the new writer's Region, reads are cross-Region and incur data transfer costs and latency. To avoid cross-Region read latency, run the prepared ALTER FOREIGN TABLE ... OPTIONS (SET ...) statements to repoint foreign tables to a Region-local copy.

Resuming operations after a switchover

Global Database Failover

The original Region might be impaired or unreachable. If a foreign table names the impaired Region, queries against it experience high latency or fail with timeout errors until that Region recovers or you repoint the foreign table to data in a healthy Region. If you pre-staged a Region-local copy, run the prepared ALTER FOREIGN TABLE ... OPTIONS (SET region ...) statements to point foreign tables to the Region-local copy.

Returning to the original primary Region

When the original primary Region becomes available, Aurora automatically adds it back to the global database as a secondary DB cluster. Any ALTER FOREIGN TABLE changes made during the switchover or failover replicate to it through global replication. To restore the original primary Region, perform a switchover. After the switchover completes, determine whether to revert the foreign table locations to point to data in the restored primary Region.

Error messages on a Global Database

After a switchover or failover, queries in the new primary Region can fail with the same IAM role association, IAM permission, and networking errors that affect any Region running a secondary DB cluster. These errors and their resolutions are covered in Global Database switchover and failover under Monitoring and troubleshooting. The prerequisites must be satisfied in each secondary Region, because the IAM role association, IAM policy, and VPC networking do not replicate.