Skip to content

Set up a PostgreSQL source, step by step

This guide prepares your PostgreSQL database so a pipeline can read it from its change log: every insert, update and delete arrives as it happens. Choose where your database runs in the tabs below; the steps are the same everywhere, and only what you click differs.

If your database cannot turn on logical replication (Heroku Postgres, for example), a pipeline can read it by queries instead, which needs none of the first two steps: see Reading PostgreSQL by queries.

Only tables in the public schema can be synced for now. A table in another schema is shown so you can see it, and cannot be picked yet.

You need:

  • An administrator login for the database: the login that created it, or one that may create roles (on Amazon RDS and Aurora, the master user; on Google Cloud SQL, the postgres user; on Azure, the server’s administrator; on Supabase and Neon, your project’s own console).
  • A place to run SQL. Each step below says where, for your host.
  • Permission in your provider’s console to change the database’s settings and its network rules.
  • A moment when a restart is acceptable, on the hosts where turning on logical replication restarts the database. Each tab says whether it does.

Set wal_level = logical in postgresql.conf (or with ALTER SYSTEM SET wal_level = logical; as a superuser), then restart PostgreSQL: this setting is read only when the server starts. SHOW config_file;, run as a superuser, prints where postgresql.conf is.

A restart briefly stops the database. Choose a quiet moment.

2. Create the role the pipelines sign in as

Section titled “2. Create the role the pipelines sign in as”

The role needs to sign in, to read the tables, and the right to read the change log (REPLICATION). Run the statements as the administrator. Replace <role>, <a strong password> and <database>.

Where: psql, or any SQL client (pgAdmin, DBeaver), connected to the database as a superuser.

CREATE ROLE <role> WITH LOGIN REPLICATION PASSWORD '<a strong password>';
GRANT CONNECT ON DATABASE <database> TO <role>;
GRANT USAGE ON SCHEMA public TO <role>;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO <role>;

SELECT ON ALL TABLES covers the tables that exist now. A table created later needs its own GRANT SELECT ON <table> TO <role>; before a pipeline can copy it.

Your database must accept connections from the addresses we connect from. The source form (Sources › Add a source) shows them, with a copy button, and so does GET /v1/network; a planned change of address is shown at least 30 days ahead. We connect over IPv4.

Add a line per address to pg_hba.conf, then reload PostgreSQL (a reload, not a restart):

hostssl <database> <role> <address> scram-sha-256

A logical replication connection names a database, so the replication keyword in the database column does not match it. Allow the addresses in any firewall in front of the server too.

4. Create the source, test it, and create the pipeline

Section titled “4. Create the source, test it, and create the pipeline”

In the app, open Sources and press Add a source. Under Where is your database hosted? (optional), choose your host. The choice changes nothing that is saved: it picks the setup steps the page shows for that host, beside the form on a wide screen and under Show the setup guide on a narrower one.

Give the source a Name, then the Host, Port and Database, the User you created in step 2 and its Password. Or open Paste a connection URL instead and paste one URL, the password in it.

The source form: where the database is hosted, its name, the connection field by field, and the buttons to test it and save it. The source form: where the database is hosted, its name, the connection field by field, and the buttons to test it and save it.

How the server’s certificate is checked is under Show all settings: Encryption (TLS) starts at verify-full, and Trusted to verify the server at the system’s trust store, which is right for a server whose certificate a public authority signed. On Amazon RDS and Aurora choose Amazon RDS and Aurora as the trust instead: the system’s trust store does not verify an RDS instance. The setup steps for your host (beside the form, or under Show the setup guide) say what to choose for the others, and TLS explains every choice. A pasted URL is saved with the mode its sslmode names: a provider’s URL often says sslmode=require, which encrypts the connection without proving the server, so change it to sslmode=verify-full unless your host’s steps say otherwise.

Press Test the connection. The test shows each stage of the connection in order (Network, Security, Sign-in, Server), with how each one ended:

A connection test that passed: each step of it, in order, with how it ended. A connection test that passed: each step of it, in order, with how it ended.

A stage that fails opens to the step that failed, which says why and what to change:

A connection test that failed at the server's settings: the step that failed, in its place, says why and what to change. A connection test that failed at the server's settings: the step that failed, in its place, says why and what to change.

Troubleshooting PostgreSQL has every message with what to do, and the connection test lists every step and message. A server whose certificate nothing you trust has signed shows the certificate it presents, for you to approve: compare its SHA-256 with your server’s own before you do. A pasted URL cannot carry an approved certificate: to approve one, give the connection field by field.

When the test has passed, press Save the source. It cannot be saved before: the form keeps it closed until a test of exactly what it would save has passed every step up to the server’s settings. (If this deployment cannot reach your host from where the test runs, the form says so and offers Save it untested; the pipeline’s start checks it then.)

Then, under Pipelines, press New pipeline, choose Use a saved source and the source you saved, and carry on through the steps: Your first pipeline says what each one asks. You can also give the connection there instead, with Paste a connection URL or Enter the details; the details are saved as a source once their test passes.

When you create a pipeline, it shows the statements to run on your database, with that pipeline’s own names, and we never run them for you. They look like this example (the names and the table are yours):

Run the first ones now. Each one is for a single table, and refuses, changing nothing, if that table is not ready for it (the message says what to change):

CREATE PUBLICATION reyansh_pub_orders_ac7c90565434c477 WITH (publish_via_partition_root = true);
DO $reyansh$
DECLARE
t regclass := pg_catalog.format('%I.%I', E'public', E'<table>')::regclass;
pub text := E'reyansh_pub_orders_ac7c90565434c477';
m regclass;
ident "char";
usable boolean;
immediate boolean;
pk boolean;
full_ok boolean;
why text;
ways text[];
bad record;
BEGIN
IF EXISTS (SELECT FROM pg_catalog.pg_publication_rel r JOIN pg_catalog.pg_publication p ON p.oid = r.prpubid
WHERE p.pubname = pub AND r.prrelid = t) THEN
RETURN;
END IF;
FOR m, ident IN SELECT c.oid, c.relreplident FROM pg_catalog.pg_class c
WHERE c.oid IN (SELECT t::oid UNION SELECT relid::oid FROM pg_catalog.pg_partition_tree(t))
ORDER BY c.oid <> t, c.oid::regclass::text LOOP
SELECT i.indisvalid AND i.indimmediate AND i.indisunique AND i.indpred IS NULL, i.indimmediate
INTO usable, immediate
FROM pg_catalog.pg_index i
WHERE i.indrelid = m
AND CASE ident WHEN 'd' THEN i.indisprimary WHEN 'i' THEN i.indisreplident ELSE false END;
CONTINUE WHEN ident = 'f' OR usable;
why := CASE
WHEN ident = 'n' THEN 'has REPLICA IDENTITY NOTHING'
WHEN ident = 'd' AND immediate IS NULL THEN 'has no primary key'
WHEN ident = 'd' AND NOT immediate THEN
'has a DEFERRABLE primary key, which PostgreSQL cannot use as a replica identity'
WHEN ident = 'd' THEN 'has a primary key whose index is not valid, which PostgreSQL cannot use as a replica identity'
WHEN immediate IS NULL THEN
'has a replica identity index that no longer exists, which PostgreSQL takes as REPLICA IDENTITY NOTHING'
ELSE 'has a replica identity index that PostgreSQL cannot use (it is not valid)'
END;
pk := EXISTS (SELECT FROM pg_catalog.pg_index i WHERE i.indrelid = m AND i.indisprimary AND i.indisvalid
AND i.indimmediate AND i.indisunique AND i.indpred IS NULL);
full_ok := true;
IF pg_catalog.current_setting('server_version_num')::int >= 150000 THEN
full_ok := NOT EXISTS (SELECT FROM pg_catalog.pg_publication_rel r JOIN pg_catalog.pg_publication p ON p.oid = r.prpubid
WHERE (p.pubupdate OR p.pubdelete) AND r.prattrs IS NOT NULL
AND r.prrelid IN (SELECT m::oid UNION SELECT relid::oid FROM pg_catalog.pg_partition_ancestors(m)));
END IF;
IF full_ok AND pg_catalog.current_setting('server_version_num')::int >= 180000 THEN
full_ok := NOT EXISTS (SELECT FROM pg_catalog.pg_attribute a WHERE a.attrelid = m AND a.attnum > 0
AND NOT a.attisdropped AND a.attgenerated <> '');
END IF;
ways := CASE
WHEN pk THEN ARRAY['REPLICA IDENTITY DEFAULT, which uses its primary key']
WHEN ident = 'd' AND immediate IS NOT NULL AND NOT immediate THEN
ARRAY['its primary key dropped and added again NOT DEFERRABLE']
WHEN ident = 'd' AND immediate IS NOT NULL THEN ARRAY['its primary key dropped and added again']
WHEN ident = 'd' THEN ARRAY['a primary key']
ELSE ARRAY['a primary key and REPLICA IDENTITY DEFAULT']
END;
IF full_ok THEN
ways := ways || 'REPLICA IDENTITY FULL'::text;
END IF;
RAISE EXCEPTION '%', pg_catalog.format('%s is not added to the publication %I: %s %s, and published without a replica identity PostgreSQL refuses every UPDATE and DELETE on it. Nothing was changed.',
t, pub, m, why)
USING HINT = pg_catalog.format('Give %s %s, then run this again.', m, pg_catalog.array_to_string(ways, ', or '));
END LOOP;
IF pg_catalog.current_setting('server_version_num')::int >= 180000 THEN
SELECT c.oid::regclass AS member, a.attname, a.attgenerated = 'v' AS virtual,
CASE c.relreplident WHEN 'f' THEN 'REPLICA IDENTITY FULL, every column'
WHEN 'd' THEN 'its primary key' ELSE 'its replica identity index' END AS identity
INTO bad
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_attribute a ON a.attrelid = c.oid AND a.attnum > 0 AND NOT a.attisdropped AND a.attgenerated <> ''
JOIN pg_catalog.pg_publication p ON p.pubname = pub AND (p.pubupdate OR p.pubdelete)
AND (a.attgenerated = 'v' OR p.pubgencols <> 's')
WHERE c.oid IN (SELECT t::oid UNION SELECT relid::oid FROM pg_catalog.pg_partition_tree(t)) AND c.relkind <> 'p'
AND (c.relreplident = 'f'
OR EXISTS (SELECT FROM pg_catalog.pg_index i
WHERE i.indrelid = c.oid
AND CASE c.relreplident WHEN 'd' THEN i.indisprimary WHEN 'i' THEN i.indisreplident
ELSE false END
AND a.attnum = ANY (i.indkey::int2[])))
ORDER BY c.oid <> t, c.oid::regclass::text, a.attgenerated <> 'v', a.attnum
LIMIT 1;
IF FOUND THEN
RAISE EXCEPTION '%', pg_catalog.format('%s is not added to the publication %I: on PostgreSQL 18 or later a publication that sends a table''s updates and deletes refuses every UPDATE and DELETE of it while its replica identity holds a generated column the publication does not send, and the replica identity of %s (%s) holds the generated column %I, %s. Nothing was changed.',
t, pub, bad.member, bad.identity, bad.attname,
CASE WHEN bad.virtual THEN 'which is VIRTUAL, and no publication sends a VIRTUAL column'
ELSE pg_catalog.format('which is STORED, and %I does not send stored generated columns', pub) END)
USING HINT = CASE WHEN bad.virtual
THEN pg_catalog.format('Give %s a replica identity without a generated column (a primary key, or REPLICA IDENTITY USING INDEX, on columns that are not generated), then run this again.', bad.member)
ELSE pg_catalog.format('Have the publication %I send stored generated columns (its publish_generated_columns option set to stored, which applies to every table it lists), or give %s a replica identity without a generated column, then run this again.', pub, bad.member) END;
END IF;
END IF;
EXECUTE pg_catalog.format('ALTER PUBLICATION %I ADD TABLE ONLY %s', pub, t);
END
$reyansh$;

The last one creates the pipeline’s change bookmark (its replication slot). Run it right before you start the pipeline, not earlier: a slot holds your database’s change log from the moment it exists, even while nothing reads it.

DO $reyansh$ BEGIN IF pg_catalog.current_setting('server_version_num')::int >= 170000 AND NOT pg_catalog.pg_is_in_recovery() THEN PERFORM pg_catalog.pg_create_logical_replication_slot('reyansh_orders_ac7c90565434c477', 'pgoutput', failover => true); ELSE PERFORM pg_catalog.pg_create_logical_replication_slot('reyansh_orders_ac7c90565434c477', 'pgoutput'); END IF; END $reyansh$;

Run it signed in as the role the pipeline uses; any role with the right to read the change log may run it too, and on a server of your own a superuser may.

Where: psql, or any SQL client, connected to this database as an administrator. That is a superuser, or the role that owns these tables and may create in this database.

Doing this with the API or Terraform instead: see The REST API and Terraform.