A PostgreSQL source
A source is a database your pipelines read from. This page is about setting up a PostgreSQL source: what each field means, how the connection is secured, what the connection test checks, and what to allow in your network. It is the long form of the setup guide beside the form (Sources, then Add a source).
The SSH tunnel (An SSH tunnel) is switched on per account while it is proven: the form says when it is not yet available to yours.
Prepare the server
Section titled “Prepare the server”A pipeline reads a PostgreSQL source in one of two ways, chosen when the pipeline is created: from its
change log (logical replication, below) or by queries (Reading by queries),
which needs none of this list except a role that may SELECT the tables and the pg_hba.conf line.
wal_level = logical. On Amazon RDS and Aurora, setrds.logical_replication = 1in the parameter group; on Google Cloud SQL, thecloudsql.logical_decodingflag; on Azure Database for PostgreSQL, thewal_levelserver parameter; on Neon, enable logical replication in the project’s settings, which restarts the project’s computes and cannot be turned off again. A server that cannot have it (Heroku Postgres, for example) is read by queries instead.- A role for the pipelines, with the
REPLICATIONattribute (on RDS and Aurora,GRANT rds_replication TO <role>) andSELECTon the tables they copy. - A line in
pg_hba.confthat lets that role reach the database from our addresses (Network), over TLS:hostssl <database> <role> <address> scram-sha-256. A logical replication connection names a database, so thereplicationkeyword in the database column does not match it. - The publication and the replication slot are per pipeline: the pipeline form shows the statements to run, with that pipeline’s names. We never run a statement on your server for you.
The fields
Section titled “The fields”| Field | Default | Notes |
|---|---|---|
| Host | — | A DNS name or an IP address. No port, no brackets, no @, /, ?, #, % or spaces. |
| Port | 5432 | 1 to 65535. |
| Database | — | Cannot be changed after the source is saved: it decides the pipelines’ slot and publication. Create another source for another database. |
| User | — | The role above. |
| Password | — | Write-only: once saved it is shown as **** and its last four characters. Leave it empty for trust or certificate sign-in. |
| TLS | verify-full |
See TLS. |
| Trusted to verify the server | the system’s trust store | Only under verify-full and verify-ca. |
| Channel binding | when the server offers it | See Channel binding. |
| Client certificate | none | See Client certificates. |
On Add a source, Encryption (TLS), Trusted to verify the server, Channel binding and Client certificate are under Show all settings.
Paste a connection URL instead. The form also takes postgres://user:password@host:5432/database.
The URL is read once, by the service, and the test shows what it was read as before anything is saved.
sslmode and channel_binding are read; sslrootcert=system means the system’s trust store. Any
other setting is refused by its name (it would do nothing here), and a file path
(sslrootcert=<path>, sslcert, sslkey) is refused with a pointer to the upload fields: a path
names a file on our machine, not yours.
Every mode below verify-full is your choice, and stays visible as a standing warning on the source’s
page for as long as it is chosen.
| Mode | What the connection gets |
|---|---|
verify-full (default) |
Encrypted; the server proven by its certificate and its name. |
verify-ca |
Encrypted; the certificate proven against a CA you chose (an uploaded one, an approved certificate, or Amazon RDS), the name not checked. Never with the system’s trust store, where any public certificate for any host would pass (libpq refuses it too). |
require |
Encrypted; the server not proven: anyone in the network path can stand in for it. |
prefer |
Encrypted if the server offers it, otherwise in the clear; never proven. |
allow |
In the clear unless the server insists on encryption. |
disable |
In the clear. |
What verifies the server
Section titled “What verifies the server”| Trust | Use it for |
|---|---|
| The system’s trust store | A certificate a public authority signed: Azure, Neon, most hosted databases. |
| Amazon RDS and Aurora | RDS and Aurora. Amazon’s RDS root certificates are built into the service, so there is nothing to upload, and they survive RDS’s own server-certificate rotations. |
| A certificate authority you upload | A private CA; Google Cloud SQL’s server CA (with verify-ca: its default certificate names no host); Supabase’s root certificate. 1 to 20 PEM certificates, at most 32 KiB. Amazon’s whole bundle is larger: choose Amazon RDS instead. |
| One certificate you approve | A self-signed server, or one whose CA nobody uploaded. See below. |
Approving a certificate
Section titled “Approving a certificate”When nothing the connection trusts signs the server’s certificate, the test’s Secure the connection step stops with approval needed and shows the chain the server presented: each certificate’s subject, issuer, SHA-256 and dates, and what approving it becomes.
- A root (a self-signed certificate authority) becomes the anchor: the chain and, under
verify-full, the name are still checked. It survives the server’s own renewals, so it is recommended when the server sends one. - The server’s own certificate is pinned: exactly that certificate is accepted, its name not checked. A renewed certificate stops the pipelines until you approve the new one.
- An intermediate cannot be approved: some providers replace theirs without notice.
- An Amazon RDS chain is answered with “choose Amazon RDS” instead of any certificate.
Approving one sets the trust to that certificate and the test runs again with it before anything is saved. There is no “trust whatever is presented”: an approval is always one exact certificate you were shown. Approving a new certificate for a saved source asks for its password again.
When only the name fails (the certificate is valid but issued for another name), the step says
which names it lists. Beside a CA you chose it offers verify-ca as one action; under the system’s
trust store the name is what proves the server, so connect using a name the certificate lists.
When a certificate changes or ends
Section titled “When a certificate changes or ends”- The source’s page lists every certificate it watches with its end date and the days left: the client certificate, an uploaded CA, the approved certificate. A server certificate under a public authority or Amazon RDS is renewed on its own and is not listed.
- 30 and 7 days before one ends, the pipelines raise the A certificate of the source ends within 30 days alert (on by default for email).
- A pipeline whose approved certificate is no longer what the server presents stops with
source_certificate_changed; one whose chain no longer verifies stops withsource_certificate_untrusted. Nothing is lost: the checkpoint is kept and the slot keeps the log. Test the source, approve the new certificate, then start the pipeline.
Revoking an approval
Section titled “Revoking an approval”Settings › Network and security lists every certificate approved for a source of the account; a source’s page
lists that source’s own, with who approved each and when, and the pipelines reading it. Revoke, on the source’s
page, asks first, then removes the approval
without a test: the source then trusts the system’s store (under verify-ca it becomes verify-full,
since verify-ca never stands on the system’s store), and the pipelines reading it restart and stop at
their TLS step until a certificate is approved again.
If someone else changes the source while your edit is being tested (an edit’s test can take up to 50
seconds), your edit is not saved over theirs: it answers 409 source_changed and you make it again
over the source as it is now.
Channel binding
Section titled “Channel binding”By default the sign-in is bound to the TLS session (SCRAM-SHA-256-PLUS) whenever the server offers it,
as libpq’s channel_binding=prefer does, so a middleman who ends the TLS between us and the server
cannot relay the sign-in. Required refuses a server that cannot be bound before any password is
sent (Neon’s URLs carry channel_binding=require, which is kept); it cannot be combined with allow
or disable. Never turns it off, deliberately.
Client certificates
Section titled “Client certificates”For a server that signs in by certificate (cert in pg_hba.conf) or requires one beside the
password (clientcert=verify-full), with any TLS mode. Paste the certificate (and any intermediates,
leaf first) and its private key: PKCS#8, PKCS#1 or SEC1, unencrypted (remove a passphrase with
openssl pkey -in key.pem -out key-plain.pem). The key is write-only: the page names it by its
certificate’s SHA-256.
Network
Section titled “Network”The source’s page and the form show where we connect from: the addresses to allow in your
firewall, security group or pg_hba.conf, with a copy button. A planned change of address is shown
there at least 30 days before it takes effect.
-
A deployment that publishes no address (self-hosted) says so; allow the addresses of the network it runs in.
-
We connect over IPv4. A host with only IPv6 addresses fails the test’s Find the server step with
ipv6_only. On Supabase, the direct connection has IPv6 addresses only: enable the IPv4 add-on. (Replication needs the direct connection, not the pooler.) -
A server that does not answer within 10 seconds fails Reach the database server with
timed_out, and the sentence names the addresses to allow: a firewall dropping the connection is the usual cause. -
Every session the pipeline opens to your source sets
search_path = pg_catalog, pg_tempwhen it connects (theoptionsstartup parameter), whatever your role or database sets, so every name its queries use is PostgreSQL’s own and no function created in another schema can stand in for one (2026-10-25 (IST)). Every session, the replication stream included, reads the setting back withSHOW search_pathbefore it runs anything else: a pooler or proxy that drops theoptionsstartup parameter makes the test’s Sign in for replication step fail withsearch_path_unpinned, and a running pipeline stop and say so. Connect to the server directly, or have the pooler passoptionsthrough.Corrected 2026-10-26 (IST): “It reads the setting back first” was true of the pipeline’s ordinary sessions only. The replication stream read it back after its first statement, whose operators were by then already looked up through the role’s own path; it now reads it back first, as every session does. A key column whose type’s operators live outside
pg_catalog(citext,ltreeand other extension types) is copied in one statement, as the table below says (key_index_order).
An SSH tunnel
Section titled “An SSH tunnel”When the database is in a private network, reach it through an SSH jump server (bastion) you run: we connect to the bastion, and the bastion connects to the database inside your network. This is Fivetran’s SSH tunnel, with the bastion’s host key pinned rather than accepted.
- On Add a source, open Show all settings, choose Through an SSH tunnel under How to connect,
and give the Jump server (bastion) host, the Jump server (bastion) SSH port (22 unless you changed it)
and the SSH user. The database’s host and port above are the
database as the bastion reaches it:
10.0.4.12, or a name the bastion resolves. - Choose a key, or Create a key. Each account has its own Ed25519 keys (at most five at a time).
We keep a key’s private half sealed; only its public half, its
SHA256:fingerprint and its line ever leave this service. - Add the line to the SSH user’s
~/.ssh/authorized_keyson the bastion. It readsrestrict,port-forwarding,permitopen="10.0.4.12:5432" ssh-ed25519 AAAA... <comment>: our key may forward to that database and to the others this key’s sources reach through the same bastion, and nothing else. No shell, no other port. When another source starts using the key through the same bastion, the line gains its database; the source’s page always shows the current line. - On the bastion, the SSH user needs
AllowTcpForwarding yesinsshd_config. We recommendPermitTTY no, andClientAliveInterval 30withClientAliveCountMax 3, so a session we lose is closed on your side within two minutes. - Test the connection. The Open the SSH tunnel step reaches the bastion, checks its host key, signs in with our key and opens a channel to the database. Every later step runs through that channel, TLS included: the certificate is checked end to end with the database, by the host name you gave.
The bastion’s host key
Section titled “The bastion’s host key”The first test of a tunnel with no host key fails Open the SSH tunnel with
host_key_approval_needed and shows the key the bastion presented: its algorithm and its SHA256:
fingerprint. On the bastion, run ssh-keygen -lf /etc/ssh/ssh_host_ed25519_key.pub (or the .pub of
the key the bastion uses) and compare. If they match, Approve this host key: the test runs again
with it, and it is saved with the source. You can also give the key up front: paste the fingerprint
ssh-keygen -l prints, or the line ssh-keyscan -t ed25519 <bastion> prints.
We verify Ed25519 and ECDSA (P-256, P-384, P-521) host keys. A bastion with only an RSA host key fails
with host_key_algorithm_unsupported: add an Ed25519 host key
(ssh-keygen -t ed25519 -f /etc/ssh/ssh_host_ed25519_key, then reload sshd).
A host key that changes stops the pipeline. If the bastion presents a key other than the approved
one, the test fails host_key_changed, and a running pipeline stops with source_host_key_changed on
its feed, naming both fingerprints. It is never restarted into the new key on its own: if you replaced
the bastion’s key, compare and approve the new one on the source’s page (the password is needed
again); if you did not, something may be standing in for your bastion.
What the tunnel guards
Section titled “What the tunnel guards”- Only the bastion’s address is checked against this service’s network rules. The database’s address is resolved by your bastion inside your network, and a private address there is the point of a tunnel. The bastion itself must have an address this service may reach.
- Every connection a pipeline makes goes through the tunnel: the stream, the initial copy, the catalogue reads and the setup test. None reaches the database’s address directly.
- Each tunnel opening is on the pipeline’s feed (
source_tunnel_opened): the bastion, the host key it was trusted on, our key’s fingerprint and the database.
When the tunnel drops
Section titled “When the tunnel drops”Each connection a pipeline makes to the database is carried by an SSH session of its own, so a busy stream
never holds back the others. If a session ends (the bastion
restarts, the network drops, three keepalives in a row go unanswered), the pipeline’s connections end
with it; the worker restarts, opens a new session and resumes from its last saved position, exactly
as it recovers from any other lost connection. No row is skipped: the position is saved only after the
destination has the rows, so a batch the drop interrupted is read again and written as a restart
writes it. A bastion that is down is retried; one that
refuses our key (ssh_auth_refused) or refuses to forward (ssh_forwarding_refused) stops the
pipeline with the line to fix, because retrying cannot fix either.
How many SSH connections a pipeline opens
Section titled “How many SSH connections a pipeline opens”Each connection a pipeline makes to the database is an SSH connection of its own to your bastion, with one forwarding channel in it, so size the bastion’s limits by connections. Measured against OpenSSH 10.3p1 with one 50-row table, on PostgreSQL 18.6 and 17.11:
- While it streams, a pipeline holds 2: its change stream’s, and the one it reads the source’s catalogue on (the replication slot, the publication, the table’s key). Nothing else stays open, and no connection sits idle.
- When it starts, it signs in about 10 times within a few seconds, at most 3 connections open at once: one to check the bastion’s host key, one to prove it can reach the database, then one for each thing it reads (the catalogue, the table’s initial copy, the stream). Each table’s initial copy has a connection of its own while it runs.
MaxSessionsdoes not limit us: it counts sessions inside one connection, and we open one channel per connection.MaxStartupsdoes: it counts connections that have not finished signing in, from every client at once. Its default,10:30:100, refuses 30% of new connections once 10 are waiting, more as the count grows: in review, 12 connections opened at the same moment let 11 through, 16 let 13 or 14, and 32 let 23 to 26. A refused connection is retried (ssh_unreachable), so a bastion at its limit delays pipelines rather than failing them, but many pipelines starting together, after the bastion restarts for instance, all meet it at once. Allow 3 waiting connections for each pipeline that goes through the bastion: for up to 10 pipelines,MaxStartups 30:30:100; for up to 30,MaxStartups 90:30:200.- Every pipeline connects from the same few addresses (the ones Network lists), so a per-address limit
sees them all as one client. Leave
PerSourceMaxStartupsat its default (none) or no lower than the first number ofMaxStartups. OpenSSH’sPerSourcePenalties(on by default in 10.3) penalises an address for failed sign-ins; a pipeline whose key is refused stops rather than retrying, so it does not keep adding to them, andPerSourcePenaltyExemptListwith our addresses removes the question.
The tunnel’s codes
Section titled “The tunnel’s codes”| Code | Step | What to do |
|---|---|---|
ssh_unreachable |
Open the SSH tunnel | The bastion did not answer, or the session ended: check that it is up, that it listens on the port, and that its firewall allows our addresses. Retried. |
ssh_handshake_failed |
Open the SSH tunnel | The port may not be SSH, the bastion offers no algorithm we use, or it sent something we could not read during the handshake, such as a host key or signature cut short (retried; if it persists, check the bastion’s host keys and that nothing on the path alters the connection), or a signature that did not verify (stopped: something on the path may be altering the connection). |
host_key_approval_needed |
Open the SSH tunnel | Compare the key shown with ssh-keygen -lf on the bastion, and approve it. |
host_key_changed |
Open the SSH tunnel | See The bastion’s host key. |
host_key_algorithm_unsupported |
Open the SSH tunnel | Add an Ed25519 host key to the bastion. |
ssh_auth_refused |
Open the SSH tunnel | Add the line to the SSH user’s authorized_keys. |
ssh_forwarding_refused |
Open the SSH tunnel, or Reach the database server | AllowTcpForwarding yes, and the line’s permitopen must list the database: use the current line. |
ssh_target_unreachable |
Open the SSH tunnel, or Reach the database server | The bastion could not connect to the database: check the database’s host and port as the bastion sees them. Retried. |
ssh_failed |
Open the SSH tunnel | Ours: contact support with the request id. |
Through the API
Section titled “Through the API”POST /v1/ssh-keys creates a key (GET /v1/ssh-keys lists them, POST /v1/ssh-keys/{id}/retire
retires one no source uses: Retire on Settings › Network and security, for every role, asks first; a key a
source still uses, through a tunnel or a proxy agent, is not retired and the refusal names those sources), and
GET /v1/ssh-keys/{id}/authorized-keys-line?bastion_host=... gives the line. A source’s connection takes
"connect": {"method": "ssh_tunnel", "ssh": {"host", "port", "user", "key_id", "host_key"}}. A change to any part
of the tunnel needs the password again.
The connection test
Section titled “The connection test”Nothing is saved until the test has passed every step up to the server’s settings (a deployment that cannot reach the host at all offers to save it untested instead). The test reports each step, in the order a connection is made: Find the server (the bastion, through a tunnel), Open the SSH tunnel (skipped for a direct connection), Reach the database server, Secure the connection, Sign in for replication (a replication connection, as a pipeline makes), Read the server, Check the server’s settings, then the pipeline’s own publication, replication slot, tables and history. Each step is passed, passed with a warning, failed, skipped or not run, with the server’s own sentence beneath (any statement to run included).
A source tested on its own (the new-source form, the source page’s Test the connection) has no pipeline yet, so its publication, slot and tables are checked when a pipeline is set up and each time it starts, with that pipeline’s names; the test says so rather than naming a publication nobody will read.
A refused sign-in says what refused it: a missing pg_hba.conf line, a reject line, a required
client certificate, a role that does not exist or may not log in, a missing CONNECT grant or
REPLICATION attribute, a channel binding broken in the path. A refusal the server’s answer does not
let us place says so; it is never guessed.
A standby as the source
Section titled “A standby as the source”A pipeline can read a streaming standby on PostgreSQL 16 or later instead of the primary, so the first copy and the stream put no load on the server your application writes to. On the standby:
hot_standby_feedback = on. Without it the primary can remove rows the standby’s slot still needs, the slot is invalidated, and the only way back is to copy every table again. The connection test’s Check the server’s settings step fails without it (hot_standby_feedback_off).max_standby_streaming_delayof 15 minutes or more, or-1while the first copy runs. A standby cancels a read that holds up its replay of the primary’s changes once this delay passes (30 seconds by default), and a first copy is a long read. The test warns below 15 minutes (standby_delay_short). Both settings take effect on a reload.- The replication slot is made on the standby, the server the source connects to: a slot on the
primary is not the one a pipeline reading the standby uses. The test’s replication slot step
reads the standby and, when the slot is missing, gives the statement to run there. Making a slot on a
standby waits until the primary records which transactions are running, which a busy primary does
within seconds; if it waits, run
SELECT pg_catalog.pg_log_standby_snapshot();on the primary. We never run it ourselves: it writes to the primary’s log. - The publication is made on the primary, with the statements the pipeline’s form shows (a standby cannot create anything); the standby has it as soon as it replays it.
- Amazon RDS Multi-AZ DB clusters copy a slot made on the writer to their readers (AWS’s logical replication guide for Multi-AZ DB clusters), so there the slot is made on the writer.
An Aurora reader cannot be the source: Aurora readers cannot decode changes, so the test’s Read
the server step refuses one (aurora_reader) and names the cluster’s writer endpoint instead. A
server is taken for an Aurora reader when AWS’s own record (aurora_replica_status()) does not name it
the writer, or when it is in recovery.
When the standby cancels the first copy. If the standby cancels a read of the first copy anyway
(SQLSTATE 40001, or 40P01 for a deadlock with its replay), the pipeline stops rather than retrying.
The stop names the table, the max_standby_streaming_delay the standby ran under and what to change:
raise it (or set -1 while the copy runs), or point the source at the primary. With
hot_standby_feedback on, the usual cause is VACUUM on the primary removing empty pages from the end
of the table, which takes a lock the copy’s read holds up; ALTER TABLE <table> SET (vacuum_truncate = off); on the primary stops it while the copy runs. Start the pipeline again once the setting is
changed: the copy carries on from the last part of the table it saved.
Keeping a pipeline through a failover
Section titled “Keeping a pipeline through a failover”A pipeline reads its changes from a replication slot on the server it connects to. When a standby takes over from a failed primary, the pipeline continues on the new primary only if a copy of its slot is already there, kept in step with the old one. Without one, the pipeline stops (the slot is missing) and every table must be copied again, as Fivetran and Airbyte require there too.
On PostgreSQL 17 and later this is built in:
-
The slot is a failover slot. The statement the pipeline’s setup gives creates the slot with
failover => truewhen it runs on a PostgreSQL 17 or later primary (on a standby, or before 17, it creates an ordinary slot: PostgreSQL makes a failover slot only on a primary). The connection test’s replication slot step reports whether the slot is one (failover), and whether it is a standby’s copy (synced). -
On the primary,
synchronized_standby_slotslists the standby’s own physical replication slot (the one in the standby’sprimary_slot_name), never the pipeline’s slot. The primary then sends the pipeline nothing the standby has not received, and the pipeline copies nothing the standby has not received either: each part of a table’s copy waits until the listed standbys have the log its read saw. So the standby that takes over has everything the pipeline read and wrote. The test warnsfailover_without_standby_slotswhen a failover slot has this setting empty. While a standby listed there is behind, the pipeline waits for it, for as long as it keeps receiving, the stream and a copy alike: each part of a copy waits for the standby to reach the log that part read, so a standby 15 seconds behind adds about 15 seconds to each part, as it delays the stream. A copy waiting holds its share lock on the table, and lets go of it at once when another session queues for a lock that share lock blocks (anALTER TABLE, aVACUUM FULL, aTRUNCATE), then reads that part again; a session queued for a lock it does not block (anANALYZEbehind autovacuum, say) does not make it let go. (Corrected 2026-10-26 (IST): this said “when another session queues”, and the copy let go for any session queued on the table, which behind a standby that trails could stop it finishing.) Only a standby that receives nothing for 30 seconds stops the copy: it says so, naming the standby’s slot and where it is, and tries again. So list every standby you might fail over to and no other, and take one out of the list before taking it down for long.Corrected 2026-10-25 (IST): this said a copy “that has waited 30 seconds says so”. That was the first version’s rule, and it stopped a copy for good behind a standby only a little more than 10 seconds behind (each attempt read the log’s newest end again); the copy now waits while the standby receives.
-
On the standby:
sync_replication_slots = on,hot_standby_feedback = on,primary_slot_nameset, and adbnameinprimary_conninfo(the database the pipeline reads). The standby then keeps a copy of the slot, which showssynced = truein itspg_replication_slots; a copy that has not yet reachedsynced = truewhen the primary fails is not kept. A synced copy cannot be read while the standby is a standby: point a pipeline at the primary.
When the standby takes over, point the source at it (or let its address move to it). The pipeline
checks that the new primary’s history still holds everything it had read, and continues where it was.
With synchronized_standby_slots listing the standby that took over, nothing is copied again, nothing
is skipped, and the destination holds nothing the new primary does not, a copy that was under way
included: it carries on from its last saved part, read on the new primary.
Without it (the setting empty, or listing another standby), the standby can take over behind what the pipeline had read and written. When the pipeline’s saved position is past where the standby took over, it notices and stops instead of skipping changes, and says what happened. When it is not (the pipeline had written changes, or part of a copy, that it had not saved yet, or a copy had read past the standby), it cannot tell: the destination can keep rows and values the new primary never had. That is why the test warns, and why the setting matters.
On PostgreSQL 11 to 16, the pg_failover_slots
extension does the same: load it in shared_preload_libraries on the primary and on the standby; on the
standby set hot_standby_feedback = on and primary_slot_name; its
pg_failover_slots.synchronize_slot_names copies every logical slot by default; and on the primary
pg_failover_slots.standby_slot_names lists the standby’s physical slot, as synchronized_standby_slots
does. The test warns failover_without_standby_slots when the extension is loaded and that list is
empty. (We have not run the extension ourselves: the warning follows its documentation.)
Providers. Amazon RDS for PostgreSQL 17 synchronises failover slots to a read replica with
sync_replication_slots, rds.logical_slot_sync_dbname on the replica, and synchronized_standby_slots
on the primary (AWS).
An RDS Multi-AZ DB cluster copies a slot made on its writer to its readers. Azure Database for
PostgreSQL loses slots on a high-availability failover on 16 and earlier unless pg_failover_slots is
loaded, and synchronises them on 17 and later
(Microsoft). AWS
does not document whether a slot survives an RDS Multi-AZ instance failover or an Aurora writer failover,
nor Google whether one survives a Cloud SQL high-availability failover; a pipeline whose slot did not
survive stops, naming the slot to create.
Editing a source
Section titled “Editing a source”Edit the connection on the source’s page changes any field but the database. The change is tested before it is saved, and every pipeline reading the source restarts onto it. A change of the host, port, TLS mode, trust, CA, approved certificate or channel binding needs the password again (and the client key, when one is stored): a stored secret is never sent to a server the change points at. A change that reaches a different database server while a pipeline reads the source is refused; create a new source to move to another server.
A source saved before this release, as a connection URL, shows no fields on its page. Edit it to give
them: a host, port, user or password you type replaces the URL’s, one you leave empty keeps it, and
the TLS settings you choose replace the URL’s sslmode.
A change to a row’s primary key
Section titled “A change to a row’s primary key”Under REPLICA IDENTITY DEFAULT your database leaves an unchanged large value (one stored apart,
“TOAST”) out of an UPDATE. That is harmless for an ordinary update, whose row keeps the value it
had. An UPDATE that changes the row’s primary key is different: the row arrives at a key your
destination has never held, and nothing in the change carries that value.
So the pipeline reads the value from your database, at the row’s new key, once the change is visible there:
- one short read-only transaction on a separate ordinary connection, as the pipeline’s role, which
needs
SELECTon the table (as its copy does); it writes, creates and alters nothing, and holds only a read’s lock while it runs, waiting at most 5 seconds behind a lock of yours; - only the large values the change left out are taken from it; every value the change carried is written as it came;
- the pipeline’s history says each such read happened, with the table and the columns read, never a value or a key.
The read waits, for at most 30 seconds, for the change to become visible to it: a commit can be sent
to the pipeline while it still waits for a synchronous standby to confirm it. If the pipeline’s own
replication slot is the synchronous standby your database waits for (synchronous_standby_names = '*' matches it), the wait could never end, so the pipeline stops and says so at once: name your real
standbys in synchronous_standby_names instead of *. A wait that runs out is retried, as is a read
that fails for a reason that can pass (a lost connection, a timeout).
When there is no row at the new key any more, because a later change deleted the row or moved it
again before it could be read, nothing can supply the value, and the pipeline never writes the row with
the value empty. Where the same batch of changes also removes the new key (deletes it, or moves it on)
and your destination table is kept live, the pipeline writes the old key’s delete: that is exactly
what the whole row would have come to. Anywhere else (a table kept in soft delete, whose deleted rows
are kept with their values, or a removal the pipeline does not receive, such as a delete your
publication does not send) that delete alone would lose a row, so only that table stops, saying
why, and the pipeline’s other tables carry on. Resume it to continue: it is copied again. On a
PostgreSQL destination kept live, ask for the copy that also deletes the rows your source no longer
has (delete_rows_not_in_source) for the old key’s row to go too.
Only that table stops, too, in the read’s own words, when the column or the table was dropped
after the change, or the table’s name now belongs to another table (dropped and created again): resume
it, and it is copied again. When the pipeline’s role may not read the table, or a row security policy
applies to that role, the table stops until you grant the role what it lacks (or BYPASSRLS) and
resume it: the value is then read again.
To have every key change carry every value, so no read is needed, give the table REPLICA IDENTITY FULL, which logs every old value and makes your WAL correspondingly larger: but not while a
publication that sends its updates and deletes has a column list for it, nor, on PostgreSQL 18 or
later, while one does not send one of its generated columns, since your database then refuses its
UPDATE and DELETE.
An update of a row your destination does not hold
Section titled “An update of a row your destination does not hold”An ordinary UPDATE that leaves a large value unchanged is written to the row your destination
holds, which keeps the value. A row your destination does not hold (deleted there, never copied
because you declined the initial copy, or in a table created again) has nothing to keep it from. So,
before writing a batch that holds such an update, the pipeline asks your destination which of those
rows it lacks, and reads only those from your database, exactly as for a key change above (the same
short, read-only, fenced read), and writes them whole. A row your destination holds is never read.
If the row is no longer in your database when it is read (deleted since), nothing is written for it and nothing stops: the row is in neither your database nor your destination, and any later change to it arrives as usual. The pipeline’s history counts each kind of row in the read’s own entry.
Under REPLICA IDENTITY FULL your database still leaves the unchanged large value out of the update’s
new row, but carries it in the old row, so such a row is completed from the change itself and nothing
is read.
Reading by queries
Section titled “Reading by queries”A pipeline created with By queries (query-based) ("update_method": "query_based") reads each table with
ordinary queries on its schedule, as a role that may only SELECT it: no replication slot, no publication, no
REPLICATION attribute, nothing created on your server. Its connection test signs in as that role, skips the
publication, slot and settings steps (each says it is not used), and, for the tables you pick, checks that each
can be read, whether row security hides rows from the role, and how much each sync reads. A row deleted in your
database is not found: it stays in your destination until you re-sync the table.
A source behind an SSH tunnel is read through it exactly as a log pipeline’s is: the test runs
Open the SSH tunnel first, and each sync opens and checks the tunnel before it reads anything, so a changed host
key stops the pipeline with source_host_key_changed before any table is read.
A query pipeline also syncs a view or a materialized view you pick with its key columns (key_columns, the
columns whose values identify one of its rows, since a view has no primary key to tell us). Each sync reads it
whole, at every sync, in one snapshot: every row it has then is written by that key, and the rows gone from it
since the last sync are found by that read (marked deleted at Snowflake and in a PostgreSQL table kept
soft_delete, counted and kept in a PostgreSQL table kept live). So it costs a whole copy at every sync, however
few rows changed, and a view holds a share lock on the tables it reads while it is read (a materialized view takes
none). A view whose rows can differ between two reads of the same data (such as TABLESAMPLE, LIMIT,
DISTINCT ON or a window function), or that reads a materialized view or a foreign table, or calls a volatile
function or one defined in your database, is parked view_not_syncable; a NULL or a repeated value in its key parks
it before anything of it is written. The key is fixed once named. A view is picked through the API for now: the
app’s table list shows tables. A pipeline that reads the change log syncs no view, since a view is never in a
publication. In full: Syncing a view you pick.
What it reads, what it can miss, what it costs and how to choose between the two ways: Query-based capture.
What is not supported yet
Section titled “What is not supported yet”- The pipeline form picking a source you added here. It takes the source as a connection string
for now; a pipeline is created from a source you added through the API (
source_id). - PrivateLink and a proxy agent.
- A cursor column under query-based capture: a query pipeline finds a table’s changed rows by their row version, never by a column you choose, and reads a view whole at every sync.
- Picking a view in the app’s table list, which shows tables only: a view is picked through the API with its
key_columns(Reading by queries). - Syncing a view, a materialized view or a foreign table from the change log, and a foreign table under query-based capture. One met where a table is expected (picked with no key, picked for a pipeline that reads the change log, or left under a synced table’s name after a rename) is told as what it is, with the tables it reads: see Views and other relations that are not tables.