PostgreSQL to Amazon S3 with AWS Glue: How to Move Your Data

Moving PostgreSQL into Amazon S3 with AWS Glue gives you an open table format your whole AWS estate can query. Athena, Glue jobs, EMR and Redshift Spectrum all read from the same files, and nothing is locked inside a warehouse you pay per query to use.

One thing to establish before you start: Airbyte's current route for this is the S3 Data Lake connector, which writes the Iceberg table format to S3 using a supported catalog, of which AWS Glue is one. So you are not writing loose files with a catalogue bolted on, you are writing Iceberg tables that Glue registers. This guide covers the managed path with Airbyte, and the two details that decide whether the result behaves.

PostgreSQL to Amazon S3 with AWS Glue at a glance:

Why move data from PostgreSQL to S3 with AWS Glue?

Two situations account for most of these pipelines.

The first is keeping your options open. Data in Iceberg on S3, registered in Glue, is readable by every AWS analytical service and by engines outside AWS that speak Iceberg. That is a meaningfully different position from data inside a proprietary warehouse, particularly if you expect your query engine choice to change.

The second is cost at volume. Object storage is cheap, and separating storage from compute means large historical tables cost almost nothing to keep and only cost when queried. If your data is modest and one warehouse serves everyone, this adds architecture without adding much.

What do you need before you start?

The destination has two prerequisites and the source has one that affects your production database:

An S3 bucket and a bucket policy. Created through the IAM console, granting the connector what it needs on that bucket. The S3 Data Lake destination documentation includes the policy to paste in, with your bucket name substituted.

A supported Iceberg catalog. AWS Glue is the one you want if the point is querying from Athena and the rest of the AWS estate, and REST, Nessie and Polaris are also supported. Choose deliberately, because the catalog is how every downstream engine finds these tables.

Logical replication on PostgreSQL, plus monitoring. A replication slot and a publication, and an alert on slot lag. An unconsumed slot means PostgreSQL retains write-ahead log it cannot discard, and left long enough that fills the disk of the database serving your application.

A look at your primary key types. Specifically whether any are numeric, because of how the connector handles them in Iceberg. This is covered below and it is the detail most likely to produce a surprising result.

If your organisation restricts inbound traffic by IP, add the Airbyte Cloud IP addresses to the allow list on the PostgreSQL side before you begin.

How do you build a PostgreSQL to S3 pipeline in Airbyte?

Step 1: Set up the bucket and the Glue catalog

Create the S3 bucket and apply the bucket policy, then confirm the Glue database you want tables registered in. Doing this first means the connection test tells you something useful rather than failing on permissions you have not granted yet, and it gives whoever owns your AWS account a chance to review before tables start appearing.

Step 2: Configure the PostgreSQL source

Click Sources in the left navigation, then New Source, and select Postgres, following adding a source. Supply the host, port, database, schema and credentials, and choose logical replication as the replication method with your slot and publication names, so deletions reach the lake rather than leaving orphaned rows.

Step 3: Configure the S3 Data Lake destination

Click Destinations, then New Destination, and select S3 Data Lake, following adding a destination. Supply your bucket, region and credentials, then select AWS Glue as the catalog and give it the database name. The connector writes Iceberg tables to the bucket and registers them in Glue, which is what makes them queryable from Athena without further work.

Step 4: Create the connection and choose sync modes

Click Connections, then New connection, select your tables and a sync mode per stream. If you use a deduplicating mode, the connector translates your stream's primary keys into Iceberg identifier columns and performs upserts as an equality delete on the key followed by an insert, which is Iceberg's merge-on-read approach.

After the first sync, query a table from Athena to confirm the Glue registration worked end to end. That check takes a minute and validates the whole chain rather than just the Airbyte half.

Why do numeric primary keys sort strangely?

This is the detail most likely to catch you out, and it comes from Iceberg rather than from Airbyte. When a primary key field has the Airbyte Number type, the connector stores it as an Iceberg string rather than a double, because Iceberg identifier fields do not support the double type and deduplication correctness depends on the key being a valid identifier column.

The trade-off is that ordering and comparison on those columns become lexicographic rather than numeric. So the value 9 sorts after 10, and a range filter on the key behaves in a way that looks broken to anyone who has not been told. Deduplication stays correct, which is what the choice protects, but any query that sorts or filters on that key needs to account for it.

Two things help. Check which of your PostgreSQL tables use a numeric primary key before you build, since integer identity columns are extremely common. And where the key is used analytically rather than only for deduplication, cast it explicitly in your downstream views so the behaviour is defined in one place rather than rediscovered by each analyst.

What happens to your table and column names?

They may be modified. Like most Airbyte destinations, this one adjusts identifiers for compatibility, and when using AWS Glue it changes any non-alphanumeric character in a table name or namespace to an underscore, for Athena's benefit.

PostgreSQL is permissive about identifiers, so schemas with hyphens or dots in names will look different once registered in Glue. That is not a problem in itself, and it becomes one if somebody writes Athena queries against the names they saw in Postgres rather than the names that actually exist.

So check the registered names after the first sync and use those in any documentation you hand to analysts. It is a five-minute check that prevents a class of confused support requests, and it matters more here than on most destinations because several different query engines will be reading these tables independently.

Frequently asked questions

Does this write plain files or actual tables?

Iceberg tables. The S3 Data Lake connector is Airbyte's official support for the Iceberg protocol on S3, registered through a catalog such as AWS Glue so engines like Athena can query them directly.

Which catalogs are supported?

AWS Glue, REST, Nessie and Polaris. Glue is the natural choice when the goal is querying from Athena and the wider AWS estate.

Why does my numeric ID column sort incorrectly?

Number-typed primary keys are stored as Iceberg strings, because identifier fields do not support the double type. Ordering is therefore lexicographic, so 9 sorts after 10. Cast explicitly in downstream views.

Why did my table names change?

When using AWS Glue, the connector replaces non-alphanumeric characters in table names and namespaces with underscores for Athena compatibility. Check the registered names after the first sync.

Can I do this without writing code?

The Airbyte setup is entirely UI-driven. The AWS side needs a bucket policy, which is JSON the documentation provides, and a Glue database.

Get your PostgreSQL data into S3

Set up the bucket and Glue database first, use logical replication so deletions reach the lake, check which tables have numeric primary keys, and verify the registered table names from Athena after the first sync. Then monitor the replication slot, because the worst failure here lands on your source database rather than your lake.

Airbyte's connector catalog includes 600+ pre-built connectors, so the same PostgreSQL source can feed a warehouse alongside the lake. For that pairing, see PostgreSQL to Databricks, and for file data going the other way, Parquet File to Teradata.

Start syncing now →