PostgreSQL to Amazon Redshift: How to Move Your Data

Replicating PostgreSQL to Redshift moves operational data out of a transactional database and into a warehouse built for analytical queries across large volumes. Airbyte does it with no code. The thing that distinguishes this pipeline from other warehouse destinations is that the loading path is not negotiable: the Redshift destination requires S3 staging, uploading files to a bucket and issuing a COPY command, which is the approach Redshift's own best practices recommend.

That single fact drives most of the setup work, and one of its consequences catches out anyone running a private cluster.

The private cluster problem

The Redshift destination supports connecting through a bastion host using SSH tunnelling, which is the usual arrangement for a cluster inside a private VPC. But the documentation is explicit about a limitation that is easy to miss: S3 staging does not use the SSH tunnel. The tunnel covers the SQL connection only, and S3 is secured through public HTTPS access.

So a network design that assumes all traffic flows through the bastion is wrong. Your data reaches S3 over the public internet, encrypted in transit but not routed through your tunnel. If your security review assumed otherwise, raise it before the pipeline is built rather than during an audit, because the answer is a VPC endpoint or a bucket policy rather than a connector setting.

If your Redshift cluster is in a private VPC, you will also need to allow connections from Airbyte, particularly where Airbyte and the cluster live in separate VPCs. All Redshift connections are encrypted using SSL regardless.

Setting up S3 staging

Create a bucket for staging and place it in the same AWS region as your Redshift cluster. This is not merely tidy: cross-region transfer costs money and slows the load, and since every sync moves your data through this bucket, the effect compounds. Then create an IAM user with read and write permissions to the staging bucket and generate an access key for it. A dedicated Airbyte user is worth the small extra effort for permissioning and auditing.

During a sync the connector writes CSV files into a path built from the bucket path, namespace, and stream name, each containing three columns for the record identifier, the data, and the emitted timestamp, along with a manifest file. The COPY command then loads them into Redshift. These staging files are normally deleted once COPY completes, and there is a setting to retain them if you want the files for another purpose. Leave purging enabled unless you have a specific reason, or the bucket grows indefinitely.

Redshift permissions worth getting right first

Create Airbyte-specific Redshift entities, a database, schema, and user, rather than reusing existing ones. Airbyte publishes a script you can run in the Redshift Query Editor to create them.

Two permission details cause most first-attempt failures. Airbyte writes into two schemas: the one you nominate for your data, and a raw data schema used to improve ELT reliability, which defaults to airbyte_internal and can be overridden in the destination's advanced settings. Your user needs create permissions on the database and usage plus create on the schema.

The one people forget is that the connector queries Redshift's SVV_TABLE_INFO view for metadata about the tables it manages, so the Airbyte user needs SELECT on it. Without that grant the connection may pass a basic test and then fail during a sync, which is a confusing place to discover a missing permission.

Choosing how to read from Postgres

Decide between CDC and cursor-based incremental before configuring anything, because the prerequisites differ and the guarantees differ.

Cursor mode needs a column that only increases and a read-only user with SELECT on your tables. It is simpler, and it misses deletes along with any update that does not touch the cursor. CDC mode reads the write-ahead log, requiring wal_level set to logical, a replication slot using the pgoutput plugin, a publication covering your tables, and a user with the REPLICATION attribute.

If you choose CDC, alert on replication slot lag from day one. A replication slot retains write-ahead log segments until they are read, so a paused connection or a repeatedly failing sync accumulates disk on your Postgres primary, and a full disk stops the database accepting writes. This is the failure that takes production down, not the sync itself.

On the destination side, remember that Redshift is a columnar warehouse where sort keys and distribution keys determine query performance. Airbyte creates the tables and loads the rows, but tuning those keys for your query patterns is your job afterwards, and it is usually the difference between a warehouse that feels fast and one that does not.

Frequently asked questions

Can I avoid using S3?

No. S3 staging with COPY is required for this destination, and it is the loading approach Redshift's own best practices recommend.

Does the SSH tunnel protect my staging data?

No. The tunnel covers the SQL connection only. S3 staging traffic is secured through public HTTPS rather than routed through the bastion.

Why is there a second schema in my database?

Airbyte writes to your target schema and to a raw data schema used for ELT reliability, defaulting to airbyte_internal. You can override the name in the destination's advanced settings.

My connection tests fine but syncs fail.

Check that the Airbyte user has SELECT on SVV_TABLE_INFO, which the connector queries for table metadata, and that the IAM credentials have write access to the staging bucket.

Get your PostgreSQL data into Amazon Redshift

Put the staging bucket in the cluster's region, grant SELECT on the metadata view, understand where the SSH tunnel does and does not apply, and tune sort and distribution keys once the tables exist. For the same source into other warehouses, see our guides to PostgreSQL to Snowflake and PostgreSQL to BigQuery.

Start syncing now →