Postgres

The Postgres source connector:

• Copies tables, views and materialized views. Other database objects, such as indexes and permissions, are not copied.
• Keeps your destination up to date in several ways, including Change Data Capture (CDC) and the xmin system column.
• Supports every sync mode, so you choose how data lands in your destination.
• Handles tables of any size: reads are split into chunks and progress is checkpointed, so an interrupted sync resumes instead of starting over.

This page has a quick start, the extra steps for CDC, and reference material such as the data type mapping.

Quick Start

The minimum setup for a Postgres source:

1. Create a read-only Postgres user that can read the data you want to copy.
2. Create the Postgres source in Pipeloom, using the xmin system column.

After that, Postgres is ready to use as a source in your syncs.

Step 1: Create a read-only Postgres user

We recommend a dedicated read-only user for Pipeloom. You can use an existing Postgres user instead.

Create the user:

CREATE USER <user_name> PASSWORD 'your_password_here';

Then give it read-only access to the schemas and tables you want to copy. Run these commands once for each schema:

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 the Postgres source in Pipeloom

Create a new source and choose Postgres. You can do this from the Sources page, or from a Source step on a pipeline canvas.

Fill in the form:

1. Enter your Postgres database's host name, port and database name.

2. Optionally, list the schemas to copy tables from. Schema names are case-sensitive. If you leave this empty, every schema the user can see is discovered.

3. Enter the user you created in Step 1. If it signs in with a password, enter it under Password. The password is optional, because some Postgres authentication methods (such as trust, peer or certificate authentication) don't need one.

4. Choose an SSL mode. Most setups use require or verify-ca. Both always encrypt the connection; verify-ca also checks your database's certificate.

5. Under replication method, choose Standard (xmin). This uses the xmin system column to pick up new and changed rows reliably.

   1. For a very large database (over 500 GB), set up the source with logical replication (CDC) instead.

Save the source. Pipeloom tests the connection to your database, and the source is ready once the test passes.

Advanced setup: CDC

With CDC, Pipeloom reads changes from the Postgres write-ahead log (WAL) using logical replication. Unlike the other methods, this also picks up deleted rows.

Use CDC when:

• You need to know which rows were deleted.
• Your database is very large (500 GB or more).
• A table has a primary key but no good cursor column (such as updated_at) for incremental syncs.

After finishing the quick start, CDC needs these extra steps:

1. Give the read-only user the REPLICATION permission.
2. Turn on logical replication in your Postgres database.
3. Create a replication slot.
4. Create a publication, and set a replica identity on each table.
5. Switch the source to CDC in Pipeloom.

Step 1: Check the basic connection first

Follow the quick start first and make sure Pipeloom can connect to your database before you change any CDC settings.

CDC works against a primary database or a replica. To read from a replica, you need Postgres 16.1 or later, and the replica needs extra configuration (see the Postgres documentation on cascading replication).

Step 2: Give the user the REPLICATION permission

Grant REPLICATION to the user from step 1 of the quick start:

ALTER USER <user_name> REPLICATION;

Step 3: Turn on logical replication

How you do this depends on where Postgres runs.

Bare metal, VMs and Docker

On bare metal, a VM (EC2, GCE and so on) or Docker, set these parameters in your database's postgresql.conf file:

AWS RDS or Aurora for Postgres

1. Open the Configuration tab of your DB cluster.
2. Find the cluster's parameter group. Edit its parameters, or make a copy and edit that. If you use a copy, switch the cluster to the new parameter group before restarting.
3. In the parameter group, search for rds.logical_replication, select it, click Edit parameters and set it to 1.
4. Restart the instance, or wait for a maintenance window to restart it for you.

:::note AWS Aurora has a CDC caching layer that doesn't work with this connector's CDC. To use CDC on Aurora, turn the cache off by setting rds.logical_wal_cache to 0 in the Aurora parameter group. :::

Azure Database for Postgres

In the Azure Portal, open your PostgreSQL instance's replication menu and set the replication mode to logical. Or use the Azure CLI:

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

Pipeloom needs a replication slot of its own. Only one source should use a given slot.

The slot must use the pgoutput plugin. As the user that now has REPLICATION, run this to create a slot named pipeloom_slot:

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

The command returns the slot's name. Enter that name in the source's Replication slot field.

Step 5: Create a publication and set replica identities

For each table you want to copy with CDC:

1. Set its replica identity, which is how Postgres tells rows apart in the log:

ALTER TABLE tbl1 REPLICA IDENTITY DEFAULT;

Occasionally, when a table uses data types that support TOAST or holds very large values, use a full replica identity instead: ALTER TABLE tbl1 REPLICA IDENTITY FULL;. Make sure such tables have primary keys of types that can't be TOASTed (integers, varchars and so on). The cost is a modest rise in resource use and a larger WAL.

2. Create the publication, listing every table you want to copy:

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

You can name the publication anything you like. To add or remove tables later, see the Postgres ALTER PUBLICATION docs.

:::note Pipeloom lets you select any table for a CDC sync. A selected table that isn't in the publication is not copied, even though it's selected. If a table is in the publication but has no replica identity, the first sync sets one for it, provided the source's user has permission to. :::

Step 6: Switch the source to CDC

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

:::note If the WAL kept for the slot grows beyond max_slot_wal_keep_size, Postgres can invalidate the slot. The slot then shows wal_status = lost and an empty restart_lsn, and the sync fails without being able to recover by itself. Drop the slot, create it again, then clear the sync's data so the next run copies everything again. :::

Replication methods

The Postgres source can keep your destination up to date in three ways: CDC, xmin, and standard (with a cursor column you choose). CDC and xmin are the most reliable.

CDC

CDC reads changes from the Postgres write-ahead log (WAL) using logical replication, which also captures deletes. Use CDC when:

• You need to know which rows were deleted.
• Your database is very large (500 GB or more).
• A table has a primary key but no good cursor column (such as updated_at) for incremental syncs.

If you want the destination to mirror your table but CDC's limitations rule it out, use xmin instead.

Xmin

Xmin picks up new and changed rows without you choosing a cursor column. It uses the xmin system column, which every Postgres database has, to track inserts and updates.

Xmin is a good fit when:

• No column works well as a cursor for standard incremental syncs.
• You want to replace a sync that currently copies everything each time (full refresh).
• Your database doesn't have such heavy write traffic that transaction IDs are at risk of wrapping around.
• You aren't copying regular (non-materialized) views. Xmin doesn't support them.

Connecting with SSL or an SSH tunnel

SSL modes

Most setups use require or verify-ca. Both always encrypt the connection; verify-ca also checks your database's certificate.

The available modes:

• disable: never encrypt the connection
• allow: encrypt only if the database requires it
• prefer: encrypt unless the database doesn't support it
• require: always encrypt. The connection fails if the database doesn't support encryption.
• verify-ca: always encrypt, and check that the database's SSL certificate is valid
• verify-full: always encrypt, and check the database's identity

SSH tunnel

If you chose disable, allow or prefer as the SSL mode, we recommend connecting through an SSH tunnel (SSH Key Authentication or Password Authentication) so the connection is still encrypted.

For SSH Tunnel Method, choose:

• No Tunnel to connect to the database directly
• SSH Key Authentication to open the tunnel with a private key
• Password Authentication to open the tunnel with a password

Connect through an SSH tunnel

With an SSH tunnel, Pipeloom connects to an intermediate server (a bastion or jump server) that can reach your database, and the bastion then connects to the database for it.

To set it up:

1. While creating the Postgres source, choose one of these under SSH tunnel:
   • SSH Key Authentication, to use a private key
   • Password Authentication, to use a password
2. For SSH Tunnel Jump Server Host, enter the host name or IP address of the bastion server.
3. For SSH Connection Port, enter the bastion's SSH port. The default is 22.
4. For SSH Login Username, enter the user to sign in to the bastion as. Note: this is an operating system user, not a Postgres user.
5. Then:
   • For SSH Key Authentication, paste the private key for that user into SSH Private Key.
   • For Password Authentication, enter that operating system user's password. Note: this is the operating system password, not the Postgres password.

Generate a private key for the SSH tunnel

Any SSH key format works, such as RSA or Ed25519. For example, to create an RSA key:

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

This writes the private key in PEM format, and the public key in the usual authorized_keys format. Add the public key to the bastion, for the user Pipeloom signs in as. Paste the private key into the source's settings so Pipeloom can sign in to the bastion.

Microsoft Entra authentication

The Postgres source can sign in as a Microsoft Entra service principal, using short-lived identity tokens to authenticate to an Azure Postgres server. Setting up the server and the Entra resources is covered in Microsoft's documentation.

To use Entra authentication:

1. Set Username to the Entra ID, as described in Microsoft's sign-in guide.
2. Set the password to a client secret of your Entra service principal.
3. Turn on Entra service principal authentication.
4. Enter the service principal's Entra tenant ID and Entra client (or app) ID.

Data type mapping

Postgres data types are copied as the following types: