Logical replication

Set up PostgreSQL logical replication for SQL Flow, including WAL settings, the capture role, and the replication slot SQL Flow manages.

SQL Flow captures PostgreSQL changes through logical replication with the built-in pgoutput plugin. Nothing is installed on the database host; you configure the server and create a login.

How it works

When a pipeline starts for the first time, SQL Flow creates a logical replication slot and a publication for the captured tables, before the initial load, so changes made during the load aren't lost. The agent opens a replication connection, and PostgreSQL streams each committed transaction, decoded from the write-ahead log (WAL). PostgreSQL keeps the WAL until the agent has saved the changes and confirmed them to the slot.

The slot and the publication

Requirements

Setup

1. Set the WAL level and the slot retention limit. Replace <size> with the most WAL a slot may retain, for example '100GB'.

   ALTER SYSTEM SET wal_level = 'logical';
   ALTER SYSTEM SET max_slot_wal_keep_size = '<size>';

2. Restart PostgreSQL, then confirm the settings and the free slot capacity.

   SHOW wal_level;
   SHOW max_slot_wal_keep_size;
   SELECT (SELECT count(*) FROM pg_replication_slots) AS used_slots,
          current_setting('max_replication_slots') AS max_slots;

3. Create the capture role in the source database. Replace <password>, <database_name>, <schema_name> and <table_owner_role>, and repeat the schema grants for every schema you capture.

   CREATE ROLE sqlflow WITH LOGIN REPLICATION PASSWORD '<password>';
   GRANT CONNECT, CREATE ON DATABASE <database_name> TO sqlflow;
   GRANT USAGE ON SCHEMA <schema_name> TO sqlflow;
   GRANT SELECT ON ALL TABLES IN SCHEMA <schema_name> TO sqlflow;
   GRANT <table_owner_role> TO sqlflow;

   The last grant gives the role ownership rights on the tables, which PostgreSQL requires to add them to a publication. A superuser also works. On a managed service that doesn't allow the REPLICATION attribute, grant its replication role instead, for example GRANT rds_replication TO sqlflow; on Amazon RDS and Aurora.

4. Optional: send complete before-images. By default, PostgreSQL includes only the key columns in the before-image of an UPDATE or DELETE. Set REPLICA IDENTITY FULL on a table if a target's consumers need the whole old row, for example in the oldData field of Kafka messages. When the pipeline starts, its log lists every captured table whose replica identity isn't FULL.

   ALTER TABLE <schema_name>.<table_name> REPLICA IDENTITY FULL;

   FULL makes PostgreSQL write the whole old row to the WAL for every UPDATE and DELETE on the table.

5. Allow the agent's address in pg_hba.conf, then reload the configuration. Replace <agent_address> with the agent host's address range.

   hostssl  <database_name>  sqlflow  <agent_address>  scram-sha-256

6. In the pipeline wizard, choose PostgreSQL on the Source step, fill in Host, Port, Database, Username and the password, leave Encryption at Require (default) unless you have a reason to relax it, and click Test connection & list tables.

The source test doesn't check the server settings or the role's privileges. The agent checks them when the pipeline starts, and stops with a message that names what is missing. See Troubleshooting.

After the pipeline starts, confirm that its slot and publication exist and watch how much WAL the slot retains:

SELECT slot_name, active, wal_status, safe_wal_size,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS retained_wal
FROM pg_replication_slots
WHERE slot_name LIKE 'sqlflow\_%';

SELECT pubname FROM pg_publication WHERE pubname LIKE 'sqlflow\_%';

Encrypted data

The connection's Encryption field has two choices. The same setting applies to the agent's ordinary connections and to its replication connection.

Use Prefer only for a server you trust on a network you trust.

Under Require (default), the agent validates the server certificate against the trusted root certificates of the agent host. If the server's certificate comes from a private certificate authority, either install that authority's certificate in the agent host's trust store, or name a PEM file that holds it with the connection option sslRootCertificate, a path on the agent host.

The wizard has no field for sslRootCertificate. Set it on a saved connection with the sqlflow CLI, then use that connection in the pipeline. Replace <host>, <database_name>, <ca_file> and <NAME>, the environment variable that holds the password on the agent host.

sqlflow connection add orders-pg --engine postgres --host <host> --port 5432 --db <database_name> --user sqlflow --encryption require --opt sslRootCertificate=<ca_file> --secret-ref env:<NAME>

Pros and cons

Pros

• Nothing to install on the database host, and no access to WAL files.
• PostgreSQL pushes each committed transaction to the agent; there is no polling.
• The slot guarantees that no change is lost while the pipeline is paused or behind, within max_slot_wal_keep_size.
• TRUNCATE is captured as an operation of its own.
• Complete before-images are available per table with REPLICA IDENTITY FULL.
• Pipelines on the same tables share one slot and one decode.

Cons

• wal_level = logical needs a server restart and adds information to the WAL.
• The slot retains WAL on the source while the pipeline is paused or behind.
• Decoding runs inside PostgreSQL and uses its CPU and memory.
• The capture role needs replication rights and ownership rights on the captured tables.
• A transaction is sent only when it commits, so a very large transaction arrives at once.

Limitations

• DDL isn't part of the logical replication stream. SQL Flow detects schema changes from the table metadata PostgreSQL sends with the changes. See Schema evolution.
• When an UPDATE leaves a large, out-of-line (TOAST) value unchanged, PostgreSQL doesn't resend it. SQL Flow leaves that column unchanged on the target.
• PostgreSQL decodes a transaction only when it commits. Until then, a large transaction is held on the server, which spills it to disk once it exceeds logical_decoding_work_mem.
• A captured TRUNCATE is applied to database targets as TRUNCATE TABLE, without CASCADE. Kafka and Google Pub/Sub refuse a TRUNCATE on a keyed table, and Fabric Open Mirroring refuses every TRUNCATE.
• A logical slot exists only on the server where SQL Flow created it. After a failover to another server, or a restore of the database, the slot is missing, and the pipeline needs a reload.

Column types

No column type is refused at the start. A value SQL Flow can't decode stops the pipeline.

• Tested: smallint, integer, bigint, numeric, real, double precision, money, boolean, char, varchar, text, bytea, uuid, date, time, timestamp, timestamptz, interval, json and jsonb, including large (TOAST) values. An interval with months or years isn't tested into targets other than PostgreSQL.
• Composite types are captured as record text, such as (1,NY). A field whose type isn't a character type, boolean, an integer type, numeric or uuid is carried as hexadecimal bytes, not as its text form.
• Not tested: arrays, enums, ranges, domains, network types (an inet value loses its netmask), geometric and bit-string types, xml, hstore, tsvector, and extension types such as PostGIS. Test such a table before you rely on it.

See Data type support by engine for every type.

Troubleshooting

Unbounded WAL retention refused

PostgreSQL source-safety admission refused because max_slot_wal_keep_size is not a finite positive source-side WAL ceiling

Cause: max_slot_wal_keep_size is -1 (the PostgreSQL default), 0, or not available on this server. SQL Flow refuses to create or use a slot while WAL retention is unbounded. Nothing was created on the source.

Fix: set a positive size, reload the configuration, and start the pipeline again.

ALTER SYSTEM SET max_slot_wal_keep_size = '<size>';
SELECT pg_reload_conf();
SHOW max_slot_wal_keep_size;

Missing privileges or settings at start

Postgres CDC reader is missing privileges / settings

Cause: the agent's startup check found something missing. The message lists each item, for example REPLICATION role attribute (ALTER ROLE <user> WITH REPLICATION), wal_level=logical (currently 'replica'; SET wal_level = logical + restart), or SELECT on <schema.table>.

Fix: apply each listed fix from Setup, then start the pipeline again. A wal_level change needs a restart.

Role is not the table owner

must be owner of table

Cause: the role can't add a captured table to SQL Flow's publication. PostgreSQL allows that only for the table's owner.

Fix: grant the role membership in the owning role with GRANT <table_owner_role> TO sqlflow;, or use a superuser.

No free replication slots

Cannot create replication slot '<slot>': all <n> replication slots are in use

Cause: the server has no free replication slot.

Fix: drop slots that nothing uses any more, or raise max_replication_slots and restart the server. List the slots with SELECT slot_name, active FROM pg_replication_slots;.

Slot invalidated by PostgreSQL

Replication slot '<slot>' was invalidated by PostgreSQL

Cause: the slot retained more WAL than max_slot_wal_keep_size, so PostgreSQL removed WAL the pipeline hadn't read yet. Before this, the pipeline's log warned that the slot was near the limit.

Fix: reload the pipeline. A reload drops the slot, creates a new one, and runs the initial load again. Kafka, Google Pub/Sub and Fabric Open Mirroring targets can't be reloaded; create a new pipeline for them. To prevent a repeat, raise max_slot_wal_keep_size or keep the pipeline from staying paused or behind for long.

Established capture slot is missing

The established PostgreSQL capture slot is missing; refusing to create a replacement at the current WAL tail

Cause: the pipeline's slot was dropped, or the source is now a different server, for example after a failover. A new slot would skip every change made since, so SQL Flow doesn't create one.

Fix: reload the pipeline.

Replication slot already exists

PostgreSQL logical replication slot '<slot>' already exists

Cause: a first start or a reload found a slot with the same name, for example one left behind by a pipeline whose cleanup didn't finish.

Fix: check that no running pipeline uses the slot (active is false in pg_replication_slots), drop it with SELECT pg_drop_replication_slot('<slot>');, and start the pipeline again.

TLS handshake with the database fails

TLS handshake failed

Cause: with Encryption at Require (default), the server doesn't offer TLS, its certificate doesn't chain to an authority the agent trusts, or the certificate's name doesn't match the Host you entered.

Fix: give the server a certificate issued for the host name you connect to, and trust its certificate authority on the agent host or name it with sslRootCertificate. See Encrypted data. Choose Prefer only for a server you trust on a network you trust.

Retired sslMode option

option 'sslMode' is retired

Cause: the connection has the sslMode option, which the Encryption setting replaces. The Connections page shows such a connection's encryption as Retired option, edit and save.

Fix: open the connection on the Connections page, choose Encryption, and save it, or run sqlflow connection edit <connection> --encryption require. A pipeline keeps its own copy of its connections: pause it, replace the copy with sqlflow pipeline set-connection <pipeline> --which source --from <connection>, giving the password again, then start it.

UPDATE or DELETE fails without a replica identity

does not have a replica identity

Cause: a captured table has no primary key and no replica identity, and it is now in SQL Flow's publication.

Fix: add a primary key, or set a replica identity on the table, for example ALTER TABLE <schema_name>.<table_name> REPLICA IDENTITY FULL;.

See also

• PostgreSQL sources
• Lag, spool and log retention
• Source log retention
• Initial load
• PostgreSQL target