Re-sync, Sync now, and a table's sync mode
This page covers three things you can ask of a pipeline whose source is PostgreSQL: copy a table again (a re-sync of some tables, or of every table), write what is waiting now (Sync now), and how deletes reach a destination table (its sync mode). Each is a request: it is recorded at once and applied by the pipeline, within about a minute when it is running, or when you next start it.
Re-sync some tables
Section titled “Re-sync some tables”Re-sync in a table’s drawer on the Schema tab, or several ticked tables at once (or
POST /v1/pipelines/{id}/resync with "tables"), copies those tables
again from your source, while the pipeline keeps its place in your source’s change log. Each table is
copied at the pipeline’s next start, one after another, and catches up from the log after its copy.
While a table copies, the pipeline’s other tables wait and then catch up.
What happens to the rows your source no longer has depends on the table’s sync mode, its destination, and whether it has a primary key (a table with no primary key kept live is copied replacing what your destination holds, at either destination). The rows a new row filter leaves out are told under Setting or changing a filter:
- Soft delete: the copied rows are written over the ones there, and once the copy has finished, rows
your source no longer has are marked deleted (
_REYANSH_DELETED_ATon Snowflake,_reyansh_deleted_aton PostgreSQL). None of them is deleted. - Live on Snowflake: the same, then rows your source no longer has are deleted.
- Live on a PostgreSQL destination: a table with a primary key keeps them, counted, unless you ask
it to delete the rows your source no longer has (
"delete_rows_not_in_source": true); a table with no primary key is copied replacing what your destination holds, so those rows are removed. A table you write to yourself is yours: say so before you ask.
A copy is metered as copy work and shows on your usage. A copy that is interrupted continues from its last saved batch when it can; the pipeline’s page says whether the running copy can.
Re-sync every table
Section titled “Re-sync every table”Re-sync every table (on the pipeline’s page under Settings › Re-sync, or
POST /v1/pipelines/{id}/resync without "tables") copies every table that is
not removed, and forgets where the pipeline had got to in your source’s change log: it starts again
from where its replication slot stands now. It is the way back after a lost or recreated replication
slot or a source restored from a backup, where “delete this pipeline and create a new one” used to be
the only answer.
- One at a time. A second full re-sync is refused while the copies the first asked for are still
waiting or running (
409 resync_in_progress), and no table request is taken while a full re-sync waits. A table waiting for you (itswaiting.reasonsays why: the publication, the source’s version, the key) is not one of those copies, so it does not hold a second one off. - A table read to its end is delivered first. If a table had already been read to the end of what your source sent, the re-sync waits until that is written, so nothing read is lost.
- New source names (optional). You can move the pipeline to a new replication slot and publication
with the same request (
"slot_name","publication_name"). Draw the pair first: tick Use a new change bookmark (replication slot) and publication under Settings › Re-sync, then Draw a new pair, orGET /v1/pipelines/{id}/new-source-names, which answers the names and what creates them, and stores nothing. Create them on your source before the re-sync is applied, because a running pipeline restarts onto it within a minute: the publication (statements) before you ask, the slot (start_statements) right before, or for a stopped pipeline right before you start it, because a slot keeps your source’s change log from the moment it exists. The pipeline never creates or drops them. The re-sync’s answer repeats the creation (setup.statementsfor the publication,setup.start_statementsfor the slot) and adds the old ones’ drop statements, for once it has settled. The draw is refused where the re-sync would be (a request already waiting, copies still running, an old slot still there), so you are never told to create a slot for nothing. - The old slot keeps your source’s log until you drop it. We never drop a slot on your database.
While the old slot is there, the pipeline raises the warning
old_slot_holds_log, naming the slot and the statement that drops it, and emails it by default; it ends when the pipeline finds the slot gone. A further rename is refused while it is there (409 previous_slot_still_present). - On PostgreSQL 17 before 17.6, new source names are what let the copies run. Those releases can
lose the changes of a table added to a publication while a replication slot reads it, so a table
asked to be copied there waits (
waiting.reasonversion). A re-sync with new source names copies every table at its first start, because its new slot began after its publication. A restart part way through those copies (a deploy, a crash) leaves the tables not copied yet waiting for the version: upgrade the source to 17.6 or later, or drop the old replication slot that re-sync left (theold_slot_holds_logwarning names it), then re-sync every table with new source names again. A full re-sync that keeps its names is judged like any other copy, and its tables wait there too. - While it copies, no table receives new changes until the last table is copied; then every table catches up. Your source keeps its change log from the re-sync until the last copy, which is more for a large pipeline.
Sync now
Section titled “Sync now”Sync now (POST /v1/pipelines/{id}/sync) writes everything the pipeline has received from your
source up to the press, without waiting for its next batch. It never restarts the pipeline. Its answer
says what it does:
effect |
What happens |
|---|---|
writing_held_changes |
What it holds is written now; it reaches your destination as fast as the destination takes it. |
nothing_held |
This pipeline writes every change as it arrives, so nothing is waiting. |
after_the_copy |
It is copying a table; what it holds is written as soon as the copy finishes. |
started |
A pipeline at rest after its last sync (one that syncs on a schedule or by hand) starts, and syncs now. |
next_run |
This pipeline reads your source in runs, on its schedule, and holds nothing between them: its next run reads every change made up to it. |
A stopped pipeline refuses it (409 pipeline_stopped): start it, and it writes what changed while it was
stopped. A pipeline that stopped and needs you refuses it too (409 pipeline_gave_up).
Switching how a pipeline reads its source
Section titled “Switching how a pipeline reads its source”A full re-sync can switch a pipeline’s update method (update_method on POST /v1/pipelines/{id}/resync):
every table is copied again into the same tables and schema, so your models keep working, billed like the first
copy.
- To query-based (
query_based): every table must be keptliveorsoft_delete(history and append need the source’s log). The answer’ssetupholds the statements that drop the replication slot and publication the pipeline leaves; run them once the re-sync has settled. Until you do, the pipeline keeps naming that slot (it holds your source’s log), and deleting the pipeline names it too. - To logical replication (
logical_replication): draw a new slot and publication withGET /v1/pipelines/{id}/new-source-names, run the statements its answer’ssetupholds on your source, and send the two names with the re-sync.
A table’s sync mode
Section titled “A table’s sync mode”| Mode | A row deleted at your source (PostgreSQL, MySQL, read from the change log) | Offered on |
|---|---|---|
Soft delete (soft_delete) |
stays, marked deleted with when. A truncate at your source marks every row of a table with no primary key, whatever the pipeline’s truncate rule says, and does what the rule says to any other table; when the table is copied again, every row your source no longer has is marked. A Stripe source sends no deletes: a row deleted there stays here as it was, unmarked, until the table is truncated at your source or copied again | Snowflake (the default), PostgreSQL |
Live (live) |
is deleted here. A truncate at your source removes every row of a table with no primary key, whatever the pipeline’s truncate rule says, and does what the rule says to any other table; when the table is copied again, a row your source no longer has, marked deleted or not, is removed, unless the table has a primary key in a PostgreSQL destination, which keeps it until you purge or delete it. A Stripe source sends no deletes: a row deleted there stays here as it was until the table is truncated at your source or copied again | Snowflake, PostgreSQL (the default) |
A query-based pipeline (update_method: query_based) reads your tables’ rows, never a change log: its source
sends no delete and no truncate, and nothing finds a row deleted there (Query-based capture),
so it stays here as it was, unmarked, in either mode, and so does every row of a table emptied or truncated at your
source. A re-sync of the table brings it in line: its copy marks the rows your source no longer has (soft delete) or
removes them (live; into PostgreSQL only when you ask the re-sync to delete rows your source no longer has, since a
table with a primary key kept live there keeps them otherwise, as above). Its copies, purges and mode changes are the
table above’s.
GET /v1/connectors names each destination’s default and its modes. A table takes its mode when it is
added: the pipeline’s default (sync_mode_default when you create it, or PUT /v1/pipelines/{id}/sync-mode),
else the destination’s; another mode for one table is asked for once it is added. It changes only when you ask (PUT .../tables/{table_id}/sync-mode); a change
between soft delete and live is applied in place, copies nothing, and waits until the table is syncing. From soft delete to live, the
rows already marked deleted of a table with a primary key stay until you purge them or a copy removes
them; a table with no primary key removes them the next time the pipeline writes to it, because a table
with none kept live removes every row marked deleted after each write. A waiting change can be
cancelled (DELETE .../tables/{table_id}/sync-mode-request).
History (history) and append-only (append) are offered at a Snowflake and a PostgreSQL destination
for a PostgreSQL source read from its change log, to every account. Nothing about a table changes until you pick
its mode: a table keeps the mode it has. A deployment’s operator can switch the two off; then a request for either
answers 422 not_yet_available, a table already kept so goes on being written so, and GET /v1/connectors says which
modes your account is offered.
- A change of a table’s shape needs
confirm: true(422 confirm_requiredotherwise, saying what happens): out of history or append, into append, and into history at a PostgreSQL destination. The old table is kept, with every row it has, under a name fixed when you ask (<table>__<old mode>_<UTC yyyymmddhhmmss>, also in the request’s history entry asaside_table, and on the table’s row aspending_mode.asideuntil the change is applied). It is yours: the pipeline never writes it or drops it. A change out of history or append on a table stopped because it has no primary key (keyless_mode_not_offered), with nothing written in that mode, is made in place at the pipeline’s next start instead, while the table is stopped: nothing is copied, and no table is kept aside. A table merely waiting for its first copy is not: its destination table may already be in the old mode’s shape, so its change waits for it to sync. The copy into the new shape is metered as copy work. A change to history at Snowflake, from soft delete or live, is made in place and needs no confirm. - At Snowflake the new table is built beside the old one, copied, and then takes its name with its
grants. A view that selects
*from the table, or names a column the new table does not have, stops working at that swap until you recreate it; a Snowflake stream on the table stays on the old one, which is now the kept table. A change whose grants cannot be carried is refused before anything is built: a grant on the table to a share or an application, or a schema with managed access that the pipeline’s role does not own. The table syncs on as it was, and the pipeline’s history says what blocks it (A change of a table’s sync mode was refused, and the table goes on as it was,table_mode_change_refused); ask again once that has changed. - At PostgreSQL the pipeline stops the table with the statements that build its new shape, to run as its owner; resume it once they have run, and it is copied into the new table.
- A history table’s first copy is read from your source’s primary server, never a standby: on a standby
it waits (
history_standby) until the pipeline reads the primary, which its next start then copies. - A history table’s copy waits for another session’s lock on it (
history_copy_lock), as a table with no primary key’s does: the pipeline tries it again on its own about once a minute, and the third time in a row parks it (history_copy_lock_held), naming the sessions holding the lock. End them, or let them finish, then resume it. - A table with no primary key cannot be kept in history or append-only yet (Fivetran keeps one in history,
by a hash of its row; this release does not yet). Asking for either on a table the pipeline has read with no
primary key answers
422 mode_not_offered. A table that reaches one anyway (the pipeline’s default for new tables is history or append-only, or its key was dropped after you asked) is stopped on its own when the pipeline next starts (keyless_mode_not_offered), and the pipeline syncs its other tables; nothing more of it is written or added to its table. If nothing of it was written yet, give it a primary key at your source, or ask for it to be kept in soft delete or live instead (that is applied while it is stopped), then resume it. If rows of it are kept in history or append-only already (its key was dropped after it synced), a request for soft delete or live waits until the table syncs again, which it does not while it has no primary key: give it a primary key at your source, then resume it, and it is copied again into the table it is kept in, as a re-sync of its mode copies (below).
History mode (history)
Section titled “History mode (history)”A history table keeps every version of every row: one version per key for each transaction at your source
that changed it, ordered by where that transaction is in your source’s log. Besides your columns it holds
_reyansh_start and _reyansh_end (when the version was current), _reyansh_active (it is the current one),
_reyansh_committed_at (your source’s own commit time, as it sent it), _reyansh_position (the change that made
it: with the key, the version’s identity), _reyansh_deleted_at and _reyansh_synced_at; on Snowflake the same
names in capitals, and _REYANSH_META.
- The current version has
_reyansh_activetrue and ends at9999-12-31 23:59:59.999, as in Fivetran’s history tables, so a query written for those finds it the same way. - The times are microseconds, your source’s own. A key’s versions never overlap and leave no gap:
WHERE <instant> BETWEEN _reyansh_start AND _reyansh_endfinds exactly one version of each key for any instant in microseconds or coarser, and none after its delete. Two commits in one microsecond, or a clock that stepped back, are told apart by moving the later start forward a microsecond;_reyansh_committed_atkeeps the time your source sent. - Changes inside one transaction are one version: nobody outside it could see the steps between. A row inserted and deleted in one transaction leaves no version at all (Fivetran keeps a version that lasts no time).
- A row deleted at your source closes its current version, which keeps the delete’s instant in
_reyansh_deleted_at; every earlier version stays, and nothing is written for the delete itself. If the key comes back, it opens a new version. So a deleted row and one a later version replaced are told apart by_reyansh_deleted_at, without reading the next version. - A change of a row’s primary key closes the old key’s version as deleted and opens one for the new key; a value your source did not send (an unchanged large value) is taken from the old key’s version.
- A value your source did not send in an update is taken from the version before, which holds it.
- A truncate at your source, under Mark its rows deleted or Delete its rows, closes every current version as deleted at that time and removes none: a history table never has a version deleted. Keep its rows leaves it as it was, and Ask me waits for your answer.
- A version being written has no
_reyansh_startyet and is not active, so a query for the current row or for an instant never matches it. - A re-sync writes no version for a row that has not changed (it only moves
_reyansh_synced_at), writes a version for each one that has, and closes, as deleted, the current version of every row your source no longer has. A change your source made while the copy ran is written after the copy’s version: if the copy’s snapshot already saw it, that version holds the same values and lasts one microsecond; if it was older than what the snapshot saw, the history’s first microseconds after the copy hold that older value. From the moment the pipeline has caught up past the snapshot, the current versions are your source’s rows. - A column added later is NULL in the older versions, which is true of them; a fill of a new column writes the current version only, because the older versions’ values in it are not known.
- Your warehouse runs more statements for a history table than for one kept by key (each run closes and opens versions), and the table grows with every change: that work and that storage are your warehouse’s. Measured on Snowflake (an X-Small warehouse, a table of about a million versions, two batches of 1,000 changes each), history’s statements took about the same execution time as soft delete’s (1.0 to 1.1 times), 1.3 to 1.7 times its elapsed time, and read 5 to 6 times the bytes, because each batch reads the history of every key it touches. Snowflake bills the time your warehouse runs, so expect a history table’s writes to cost about what soft delete’s do, a little more, and more as each key’s history grows long.
Append-only (append)
Section titled “Append-only (append)”An append table keeps a row for every change, in order, and never updates or removes one. Besides your
columns (every one nullable: a delete row carries only the key, a truncate row none) it holds _reyansh_op
(insert, update, delete, read or truncate), _reyansh_change_id (the row’s identity),
_reyansh_sequence (its order), _reyansh_committed_at (your source’s commit time; NULL on a read row),
_reyansh_meta and _reyansh_synced_at.
- Every change is written exactly once, by its identity, however often the pipeline replays it.
- A change of a row’s primary key is two rows: a
deleteof the old key and aninsertof the new one, so a “latest row per key” query sees the old key end. - A value your source did not send (an unchanged large value) is NULL in that row and named in
_reyansh_meta’sabsentas “unchanged at the source, not sent”, so it is never read as a NULL value. The pipeline never reads your source or your table to fill it. - A delete is a row with the key and
_reyansh_op=delete; a truncate is one row with every column of yours NULL, unless the pipeline’s truncate rule is Keep its rows; under Ask me the table waits for your answer. - A copy, or a re-sync, appends one
readrow for each row your source has; nothing already in the table is changed. A copy that resumes after the pipeline was stopped can append a second, complete set ofreadrows after a partial one: a “latest row per key” view that orders by_reyansh_sequencetakes the newer. - A column added later is NULL in older rows, and is never filled in them: they record what was sent.
- Your warehouse’s work stays flat as the table grows: each write reads only the table’s newest part (on 10^8 rows, the write read 2 of 12 micro-partitions); the storage is your warehouse’s.
Soft delete into PostgreSQL is offered to every account. A deployment that does not offer it answers a request for
it with 422 not_yet_available (on a table’s mode, the pipeline’s default and a create’s default), and
GET /v1/connectors does not list it.
A MySQL or Stripe pipeline into PostgreSQL writes every table live and runs no purge, so its tables are
offered live alone: a change of a table’s mode, the pipeline’s default (PUT .../sync-mode) and a create’s
sync_mode_default are refused there with 422 mode_fixed_by_source, and a purge with
422 purge_not_offered. GET /v1/connectors names each destination’s modes by source kind
(sync_modes: {"<source kind>": {default, offered, purge}}), for the account asking.
On a PostgreSQL destination, soft delete adds two columns to your table (_reyansh_deleted_at,
_reyansh_synced_at). If the pipeline’s role cannot add them, the table is parked, and nothing of it is
written until they are there: its park names the statement to run as its owner, and you then resume it. A truncate at your source, under the Mark its rows deleted rule, marks
every row of a soft-delete table not yet marked (_reyansh_deleted_at and _reyansh_synced_at set to that
time; a row marked already keeps its first mark) and removes none; under Delete its rows its rows are
deleted, as on Snowflake (schema changes). A table with no primary key is
emptied whatever the rule says: every row is removed when it is kept live, and marked when it is kept in soft delete.
Purge the rows marked deleted
Section titled “Purge the rows marked deleted”Delete the rows marked deleted in the table’s drawer on the Schema tab, confirmed with Yes, delete
them (POST .../tables/{table_id}/purge-deleted with "confirm": true; when an API key asks for it, every owner of the account is told) deletes the
rows marked deleted from a destination table, at the pipeline’s next start. It cannot be undone, and a
change replayed from your source’s log can mark a row deleted again.
- A waiting purge can be cancelled (
DELETE .../tables/{table_id}/purge-request; Cancel the delete in the table’s drawer on the Schema tab): the rows stay. When an API key cancels one, every owner is told, as for the purge. A purge the pipeline has already begun runs to its end, and the table’srows_purgedthen says what it removed;404 no_pending_purgewhen none waits. - A parked table is not purged (
422 purge_needs_resume): a purge runs only for a table the pipeline writes, so resume it first. A purge asked before the table parked waits for it (pending_purge.waits_for_resume), and so does the table’s resume: cancel the purge to resume it first, as for a waiting change of its mode. - A purge your PostgreSQL destination refuses stops its table (a trigger, rule, constraint or
permission of yours refused it, or a
BEFORE DELETEtrigger kept one of its rows): nothing is removed, the table stops, saying what refused or kept the purge, and the purge stays pending, so the table cannot be resumed while it waits. Cancel the purge, then resume the table; once the cause is fixed and the table is syncing, ask for the purge again. - Nor is a table waiting for you (
422 purge_table_waiting): one whosewaiting.reasonis set and is notdestination_busy(its publication, the source’s PostgreSQL version, a partition’s identity, the source’s room, or its key). The pipeline skips such a table until what it waits for is done, so the refusal names that, and you ask for the purge once the table is copied. A table waiting for its first copy, or on a busy destination table, is written at a start, so its purge is accepted. A purge asked before the table was found to wait stays pending with it until the table is copied; cancel it if you would rather not wait. - A cancel’s
takes_effectsays a purge the pipeline has already begun runs to its end, for every table that is not parked, even on a stopped pipeline: its worker may still be finishing one.
An edit restarts the pipeline
Section titled “An edit restarts the pipeline”Changing the schedule, the discarded-rows answer, the change policy, a table, its mode, or the
destination restarts a running pipeline within a minute. If a copy is running then, the pipeline’s page
says so before you save: a copy that keeps its place pauses and continues from its last saved batch; one
that cannot starts again, and the answer names it in copy_restarts.
A repair we ask for
Section titled “A repair we ask for”If a defect of ours needs a table copied again, we ask for the re-sync ourselves. Its copy is recorded as repair work and never charged; the pipeline’s history says it was ours.