Skip to content

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.

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, set rds.logical_replication = 1 in the parameter group; on Google Cloud SQL, the cloudsql.logical_decoding flag; on Azure Database for PostgreSQL, the wal_level server 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 REPLICATION attribute (on RDS and Aurora, GRANT rds_replication TO <role>) and SELECT on the tables they copy.
  • A line in pg_hba.conf that 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 the replication keyword 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.
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.
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.

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.

  • 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 with source_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.

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.

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.

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.

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_temp when it connects (the options startup 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 with SHOW search_path before it runs anything else: a pooler or proxy that drops the options startup parameter makes the test’s Sign in for replication step fail with search_path_unpinned, and a running pipeline stop and say so. Connect to the server directly, or have the pooler pass options through.

    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, ltree and other extension types) is copied in one statement, as the table below says (key_index_order).

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.

  1. 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.
  2. 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.
  3. Add the line to the SSH user’s ~/.ssh/authorized_keys on the bastion. It reads restrict,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.
  4. On the bastion, the SSH user needs AllowTcpForwarding yes in sshd_config. We recommend PermitTTY no, and ClientAliveInterval 30 with ClientAliveCountMax 3, so a session we lose is closed on your side within two minutes.
  5. 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 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.

  • 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.

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.

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.
  • MaxSessions does not limit us: it counts sessions inside one connection, and we open one channel per connection.
  • MaxStartups does: 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 PerSourceMaxStartups at its default (none) or no lower than the first number of MaxStartups. OpenSSH’s PerSourcePenalties (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, and PerSourcePenaltyExemptList with our addresses removes the question.
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.

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.

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 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_delay of 15 minutes or more, or -1 while 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.

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 => true when 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_slots lists the standby’s own physical replication slot (the one in the standby’s primary_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 warns failover_without_standby_slots when 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 (an ALTER TABLE, a VACUUM FULL, a TRUNCATE), then reads that part again; a session queued for a lock it does not block (an ANALYZE behind 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_name set, and a dbname in primary_conninfo (the database the pipeline reads). The standby then keeps a copy of the slot, which shows synced = true in its pg_replication_slots; a copy that has not yet reached synced = true when 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.

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.

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 SELECT on 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.

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.

  • 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.