Postgres

Availability

Core Standard Plus Pro Enterprise Flex PyAirbyte

Connector type

Data replication Agent(learn more)

Support Level

Airbyte

Connector Version

3.8.10 (last updated 13 days ago)

Sync Success Rate

High

Usage Rate

High

Data workers used

0.5 per sync (Learn more)

Definition ID

decd338e-5647-4c0b-adf4-da0e75f5a750

Airbyte's certified Postgres connector offers the following features:

• Replicate data from tables, views and materialized views. Other data objects won't be replicated to the destination like indexes, permissions.
• Multiple methods of keeping your data fresh, including Change Data Capture (CDC) and replication using the xmin system column.
• All available sync modes, providing flexibility in how data is delivered to your destination.
• Reliable replication at any table size with checkpointing and chunking of database reads.

The contents below include a 'Quick Start' guide, advanced setup steps, and reference information (data type mapping, and changelogs). See here to troubleshoot issues with the Postgres connector.

Please note the required minimum platform version is v0.58.0 for this connector.

Quick Start

Here is an outline of the minimum required steps to configure a Postgres connector:

1. Create a dedicated read-only Postgres user with permissions for replicating data
2. Create a new Postgres source in the Airbyte UI using xmin system column
3. (Airbyte Cloud Only) Allow inbound traffic from Airbyte IPs

Once this is complete, you will be able to select Postgres as a source for replicating data.

Step 1: Create a dedicated read-only Postgres user

These steps create a dedicated read-only user for replicating data. Alternatively, you can use an existing Postgres user in your database.

The following commands will create a new user:

CREATE USER <user_name> PASSWORD 'your_password_here';

Now, provide this user with read-only access to relevant schemas and tables. Re-run this command for each schema you expect to replicate data from:

GRANT USAGE ON SCHEMA <schema_name> TO <user_name>;
GRANT SELECT ON ALL TABLES IN SCHEMA <schema_name> TO <user_name>;
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema_name> GRANT SELECT ON TABLES TO <user_name>;

Step 2: Create a new Postgres source in Airbyte UI

From your Airbyte Cloud or Airbyte Open Source account, select Sources from the left navigation bar, search for Postgres, then create a new Postgres source.

To fill out the required information:

1. Enter the hostname, port number, and name for your Postgres database. On Airbyte Cloud, the host must be reachable from the internet: a database on localhost, 127.0.0.1 or a private network address is reachable from Airbyte Cloud only through an SSH tunnel, which also provides the encryption Airbyte Cloud requires. Enter only the hostname or IP address in Host, not a URL and not host:port.

2. Optionally list the names of the schemas you want to sync tables from. These are case-sensitive. When not provided, all available schemas are discovered.

3. Enter the username you created in Step 1. If your Postgres user authenticates with a password, enter it in the Password field. The password is optional — some PostgreSQL authentication methods (such as trust, peer, or certificate-based auth) do not require one.
4. Select an SSL mode. You will most frequently choose require or verify-ca. Both of these always require encryption. verify-ca also requires certificates from your Postgres database. Airbyte Cloud never connects unencrypted: the unencrypted modes disable, allow and prefer are accepted on Airbyte Cloud only together with an SSH tunnel (see Connecting with SSL or SSH Tunneling).
5. Select Standard (xmin) from available replication methods. This uses the xmin system column to reliably replicate data from your database.
   1. If your database is particularly large (> 500 GB), you will benefit from configuring your Postgres source using logical replication (CDC).

Step 3: (Airbyte Cloud Only) Allow inbound traffic from Airbyte IPs

If you are on Airbyte Cloud, you will always need to modify your database configuration to allow inbound traffic from Airbyte IPs. You can find a list of all IPs that need to be allowlisted in our Airbyte Security docs.

Airbyte Cloud never connects to a database unencrypted. The connection must either use TLS/SSL (SSL mode require, verify-ca or verify-full) or go through an SSH tunnel, which encrypts the hop between Airbyte Cloud and your network. The unencrypted SSL modes disable, allow and prefer are accepted on Airbyte Cloud only together with an SSH tunnel. A database on localhost or on a private network is not reachable from Airbyte Cloud directly: connect to it through an SSH tunnel. Airbyte Cloud checks the Host field before it connects: localhost, 127.0.0.1 or 0.0.0.0 with No Tunnel fails the connection test with Airbyte Cloud cannot reach a database on your own machine. Use a hostname reachable from the internet, or configure an SSH tunnel., and a URL, credentials or host:port pasted into the field fail with Enter only the hostname in Host; put the port in the Port field.

Now, click Set up source in the Airbyte UI. Airbyte will now test connecting to your database. Once this succeeds, you've configured an Airbyte Postgres source!

If the test fails with Airbyte could not reach the database at the configured host and port, Airbyte could not open a TCP connection to your database. Check the host and port, that Postgres accepts remote connections (listen_addresses in postgresql.conf and a matching entry in pg_hba.conf), and that your firewall or security group allows the Airbyte IP addresses listed above.

Advanced Configuration using CDC

Airbyte uses logical replication of the Postgres write-ahead log (WAL) to incrementally capture deletes using a replication plugin:

• See here to learn more on how Airbyte implements CDC.
• See here to learn more about Postgres CDC requirements and limitations.

We recommend configuring your Postgres source with CDC when:

• You need a record of deletions.
• You have a very large database (500 GB or more).
• Your table has a primary key but doesn't have a reasonable cursor field for incremental syncing (updated_at).

These are the additional steps required (after following the quick start) to configure your Postgres source using CDC:

1. Provide additional REPLICATION permissions to read-only user
2. Enable logical replication on your Postgres database
3. Create a replication slot on your Postgres database
4. Create publication and replication identities for each Postgres table
5. Enable CDC replication in the Airbyte UI

Step 1: Prepopulate your Postgres source configuration

We recommend following the steps in the quick start section to confirm that Airbyte can connect to your Postgres database prior to configuring CDC settings.

CDC requires PostgreSQL 14 or later. Debezium 3.4, the CDC engine in connector version 3.8.10 and later, no longer supports PostgreSQL 13 and earlier.

For CDC, you may connect to primary/master databases or replicas. To use a replica as a source, Postgres must be at version 16.1 or later and this connector must be at version 3.6.21 or later. You must also enable additional configurations on the database instance (for help, see the Postgres official documentation).

Step 2: Provide additional permissions to read-only user

To configure CDC for the Postgres source connector, grant REPLICATION permissions to the user created in step 1 of the quick start:

ALTER USER <user_name> REPLICATION;

Step 3: Enable logical replication on your Postgres database

To enable logical replication, follow these steps based on your deployment environment.

Bare Metal, VMs, and Docker

To enable logical replication on bare metal, VMs (EC2/GCE/etc), or Docker, configure the following parameters in the postgresql.conf file for your Postgres database.

Parameter	Description	Set value to
wal_level	Type of coding used within the Postgres write-ahead log	logical
max_wal_senders	The maximum number of processes used for handling WAL changes	min: 1
max_replication_slots	The maximum number of replication slots that are allowed to stream WAL changes	1 (if Airbyte is the only service reading subscribing to WAL changes. More than 1 if other services are also reading from the WAL)

AWS Postgres RDS or Aurora

1. Go to the Configuration tab for your DB cluster.
2. Find your cluster parameter group. Either edit the parameters for this group or create a copy of this parameter group to edit. If you create a copy, change your cluster's parameter group before restarting.
3. Within the parameter group page, search for rds.logical_replication. Select this row and click Edit parameters. Set this value to 1.
4. Wait for a maintenance window to automatically restart the instance or restart it manually.

note

AWS Aurora implements a CDC caching layer that is incompatible with Airbyte's CDC implementation. To use Airbyte with AWS Aurora, disable the CDC caching layer. Disable CDC caching by setting the rds.logical_wal_cache parameter to 0 in the AWS Aurora parameter group.

Azure Database for Postgres

Change the replication mode of your Postgres DB on Azure to logical using the replication menu of your PostgreSQL instance in the Azure Portal. Alternatively, use the Azure CLI to run the following command:

az postgres server configuration set --resource-group group --server-name server --name azure.replication_support --value logical
az postgres server restart --resource-group group --name server

Step 4: Create a replication slot on your Postgres database

Airbyte requires a replication slot configured only for its use. Only one source should be configured that uses this replication slot.

For this step, Airbyte requires use of the pgoutput plugin. To create a replication slot called airbyte_slot using pgoutput, run as the user with the newly granted REPLICATION role:

SELECT pg_create_logical_replication_slot('airbyte_slot', 'pgoutput');

The output of this command will include the name of the replication slot to fill into the Airbyte source setup page.

Step 5: Create publication and replication identities for each Postgres table

For each table you want to replicate with CDC, follow the steps below:

1. Add the replication identity (the method of distinguishing between rows) for each table you want to replicate:

ALTER TABLE tbl1 REPLICA IDENTITY DEFAULT;

In rare cases, if your tables use data types that support TOAST or have very large field values, consider instead using replica identity type full: ALTER TABLE tbl1 REPLICA IDENTITY FULL;. Ensure that TOAST-able tables use non-TOAST-able primary keys (integers, varchars, etc), and there will only be a modest increase in resource utilization, in addition to increased WAL storage size.

2. Create the Postgres publication. You should include all tables you want to replicate as part of the publication:

CREATE PUBLICATION airbyte_publication FOR TABLE <tbl1, tbl2, tbl3>;

The publication name is customizable. Refer to the Postgres docs if you need to add or remove tables from your publication in the future.

note

The Airbyte UI currently allows selecting any tables for CDC. If a table is selected that is not part of the publication, it will not be replicated even though it is selected. If a table is part of the publication but does not have a replication identity, that replication identity will be created automatically on the first run if the Airbyte user has the necessary permissions.

Step 6: Enable CDC replication in Airbyte UI

In your Postgres source, change the update method to Read Changes using Change Data Capture (CDC), and enter the replication slot and publication you just created.

note

If max_slot_wal_keep_size is exceeded, PostgreSQL can invalidate the replication slot. The slot then has wal_status = lost and a null restart_lsn, so the connector fails the sync and cannot recover automatically. Drop and recreate the replication slot, then reset the connection for a full re-sync.

Postgres Replication Methods

The Postgres source currently offers 3 methods of replicating updates to your destination: CDC, xmin and standard (with a user defined cursor). Both CDC and xmin are the most reliable methods of updating your data.

CDC

Airbyte uses logical replication of the Postgres write-ahead log (WAL) to incrementally capture deletes using a replication plugin. To learn more how Airbyte implements CDC, refer to Change Data Capture (CDC). We recommend configuring your Postgres source with CDC when:

• You need a record of deletions.
• You have a very large database (500 GB or more).
• Your table has a primary key but doesn't have a reasonable cursor field for incremental syncing (updated_at).

If your goal is to maintain a snapshot of your table in the destination but the limitations prevent you from using CDC, consider using the xmin replication method.

Xmin

Xmin replication is the new cursor-less replication method for Postgres. Cursorless syncs enable syncing new or updated rows without explicitly choosing a cursor field. The xmin system column which (available in all Postgres databases) is used to track inserts and updates to your source data.

This is a good solution if:

• There is not a well-defined cursor candidate to use for Standard incremental mode.
• You want to replace a previously configured full-refresh sync.
• Your database doesn't incur heavy writes that would lead to transaction ID wraparound.
• You are not replicating non-materialized views. Non-materialized views are not supported by xmin replication.

Connecting with SSL or SSH Tunneling

SSL Modes

Airbyte uses SSL (require) by default. Airbyte Cloud never connects to a database unencrypted: the connection must use TLS/SSL (require, verify-ca or verify-full), or go through an SSH tunnel, which encrypts the hop between Airbyte Cloud and your network. The unencrypted modes disable, allow and prefer are accepted on Airbyte Cloud only together with an SSH tunnel; with No Tunnel the connection test fails with Connection from Airbyte Cloud requires SSL encryption or an SSH tunnel. You will most frequently choose require or verify-ca. Both of these always require encryption. verify-ca also requires certificates from your Postgres database.

Here is a breakdown of available SSL connection modes:

• disable to disable encrypted communication between Airbyte and the source
• allow to enable encrypted communication only when required by the source
• prefer to allow unencrypted communication only when the source doesn't support encryption
• require to always require encryption. Note: The connection will fail if the source doesn't support encryption.
• verify-ca to always require encryption and verify that the source has a valid SSL certificate
• verify-full to always require encryption and verify the identity of the source

SSH Tunneling

Airbyte Cloud requires encrypted communication, and an SSH tunnel provides it: the hop between Airbyte Cloud and your bastion host is encrypted by SSH. The unencrypted SSL modes disable, allow and prefer are therefore accepted on Airbyte Cloud only when you also select SSH Key Authentication or Password Authentication; with No Tunnel the connection fails. An SSH tunnel is also the only way for Airbyte Cloud to reach a database that is not exposed to the internet, such as one on localhost or on a private network: Airbyte connects to a bastion host that you expose to the Airbyte IPs, and the bastion forwards the connection to the database.

For SSH Tunnel Method, select:

• No Tunnel for a direct connection to the database
• SSH Key Authentication to use an RSA private key as your secret for establishing the SSH tunnel
• Password Authentication to use a password as your secret for establishing the SSH tunnel

Connect via SSH Tunnel

You can connect to a Postgres instance via an SSH tunnel.

When using an SSH tunnel, you are configuring Airbyte to connect to an intermediate server (also called a bastion or a jump server) that has direct access to the database. Airbyte connects to the bastion and then asks the bastion to connect directly to the server.

To connect to a Postgres instance via an SSH tunnel:

1. While setting up the Postgres source connector, from the SSH tunnel dropdown, select:
   • SSH Key Authentication to use a private key as your secret for establishing the SSH tunnel
   • Password Authentication to use a password as your secret for establishing the SSH Tunnel
2. For SSH Tunnel Jump Server Host, enter the hostname or IP address for the intermediate (bastion) server that Airbyte will connect to.
3. For SSH Connection Port, enter the port on the bastion server. The default port for SSH connections is 22.
4. For SSH Login Username, enter the username to use when connecting to the bastion server. Note: This is the operating system username and not the Postgres username.
5. For authentication:
   • If you selected SSH Key Authentication, set the SSH Private Key to the private Key that you are using to create the SSH connection.
   • If you selected Password Authentication, enter the password for the operating system user to connect to the bastion server. Note: This is the operating system password and not the Postgres password.

Generating a private key for SSH Tunneling

The connector supports any SSH compatible key format such as RSA or Ed25519. To generate an RSA key, for example, run:

ssh-keygen -t rsa -m PEM -f myuser_rsa

The command produces the private key in PEM format and the public key remains in the standard format used by the authorized_keys file on your bastion server. Add the public key to your bastion host to the user you want to use with Airbyte. The private key is provided via copy-and-paste to the Airbyte connector configuration screen to allow it to log into the bastion server.

Configuring Entra authentication

The Airbyte source can be configured to authenticate as a Microsoft Entra service principal. This allows Airbyte to use short-lived identity tokens to authenticate to an Azure Postgres server. Consult the Microsoft documentation on this topic for more detail on configuring the server and other Entra resources.

To configure the Airbyte Postgres source with Entra authentication:

1. Set the Username to the Entra ID, as discussed in Microsoft's documentation.
2. Set the password to a client secret for your Entra service principal.
3. Enable the Entra service principal authentication toggle.
4. Provide the Entra tenant ID and Entra client (or app) ID of the service principal.

Limitations & Troubleshooting

To see connector limitations, or troubleshoot your Postgres connector, see more in our Postgres troubleshooting guide.

Data type mapping

According to Postgres documentation, Postgres data types are mapped to the following data types when synchronizing data. You can check the test values examples here. If you can't find the data type you are looking for or have any problems feel free to add a new test!

Postgres Type	Resulting Type	Notes
bigint	number
bigserial, serial8	number
bit	string	Fixed-length bit string (e.g. "0100").
bit varying, varbit	string	Variable-length bit string (e.g. "0100").
boolean, bool	boolean
box	string
bytea	string	Variable length binary string with hex output format prefixed with "\x" (e.g. "\x6b707a").
character, char	string
character varying, varchar	string
cidr	string
circle	string
date	string	Parsed as ISO8601 date time at midnight. CDC mode doesn't support era indicators. Issue: #14590
double precision, float, float8	number	Infinity, -Infinity, and NaN are not supported and converted to null. Issue: #8902.
hstore	string
inet	string
integer, int, int4	number
interval	string
json	string
jsonb	string
line	string
lseg	string
macaddr	string
macaddr8	string
money	number
numeric, decimal	number	Infinity, -Infinity, and NaN are not supported and converted to null. Issue: #8902.
path	string
pg_lsn	string
point	string
polygon	string
real, float4	number
smallint, int2	number
smallserial, serial2	number
serial, serial4	number
text	string
time	string	Parsed as a time string without a time-zone in the ISO-8601 calendar system.
timetz	string	Parsed as a time string with time-zone in the ISO-8601 calendar system.
timestamp	string	Parsed as a date-time string without a time-zone in the ISO-8601 calendar system.
timestamptz	string	Parsed as a date-time string with time-zone in the ISO-8601 calendar system.
tsquery	string
tsvector	string
uuid	string
xml	string
enum	string
tsrange	string
array	array	E.g. "["10001","10002","10003","10004"]".
composite type	string

IP allow list

If you use Airbyte Cloud and your organization restricts access to specific IPs, add the Airbyte Cloud IP addresses to your allow list.

Reference

Config fields reference

Field

Type

Property name

string

database

string

host

integer

port

object

replication_method

object

tunnel_method

string

username

boolean

check_privileges

integer

checkpoint_target_interval_seconds

string

entra_client_id

boolean

entra_service_principal_auth

string

entra_tenant_id

string

jdbc_url_params

integer

max_db_connections

string

password

array<string>

schemas

object

ssl_mode

Changelog

To see this connector's version history, read the Postgres changelog. For older versions, see versions 0.x–2.x.