What pg2osync is

pg2osync keeps a search index in sync with a database, in real time, from one binary. It reads changes straight from the database's replication stream — PostgreSQL's WAL or MySQL's binlog — and writes them to OpenSearch, Elasticsearch or Meilisearch within milliseconds. No Logstash, no Kafka, no Redis, no JVM.

export PG2OSYNC_SOURCE_URL="postgres://user:pass@db-host/mydb"
pg2osync init --table users   # writes pg2osync.toml, checks the table exists
pg2osync validate             # checks both ends and the server's settings
pg2osync run                  # initial load, then streaming

Installing it, the comparison against Debezium and Logstash, and the current feature matrix are in the README. This site is the rest:

• Configuration — every option, with what each one costs.
• Sources — what PostgreSQL and MySQL/MariaDB each need switched on, and how much of the server's behaviour leaks into the pipeline.
• Sinks — OpenSearch, Elasticsearch and Meilisearch, including where they differ in what they can guarantee.
• Deployment — Docker, Kubernetes, systemd, probes.
• Operations — metrics, every failure mode, and the recovery for each.
• What it costs your database — connections, privileges, and the measured load on a busy source.
• Architecture and design decisions — how it works, and why it was built this way rather than another.

Every number in these pages was measured by a script in dev/, and the page says which one. Where a limit has not been measured, it says that instead.

Configuration reference

One TOML file describes the whole pipeline. Unknown keys are rejected at load time, so a typo fails immediately instead of silently doing nothing.

You do not have to write it from this page. pg2osync init --table users writes the smallest config that runs, qualifying the table name from the source's own catalogue and declaring a table with no primary key append_only; this reference is for the options you add afterwards. Every command defaults to pg2osync.toml, which is what init writes.

Full example: examples/pg2osync.example.toml.

Everything structural is checked by pg2osync validate, which also connects to both ends and verifies server prerequisites.

Secrets

Credentials belong in environment variables. Every secret has an *_env form:

[source]
url_env = "PG2OSYNC_SOURCE_URL"      # preferred

[target]
password_env = "PG2OSYNC_TARGET_PASSWORD"

Plain url and password keys still work but log a deprecation warning on startup. Secrets never appear in logs or error messages.

[source]

URL formats:

postgres://user:pass@host:5432/dbname
mysql://user:pass@host:3306/dbname

Percent-encoded credentials are decoded, so a password containing @ or : works if you encode it.

admin_url_env exists so the replication connection and ordinary queries can use different users — the replication role needs REPLICATION, the admin role needs SELECT on the synced tables. It is also the only one of the two that may sit behind a connection pooler; the stream cannot, and both must reach the primary — see Proxies and connection poolers.

TLS

sslmode follows libpq exactly, and applies to every connection pg2osync opens — the replication stream and the MySQL binlog dump included, so a source can never end up half encrypted.

An explicit sslmode in the config wins over one in the connection URL, so a URL pasted from a provider cannot weaken a deployment that pinned its mode.

prefer is the default because libpq uses it and it improves an unconfigured deployment without breaking a server that has no certificate. It is not a guarantee: a server that does not offer TLS is silently accepted. Anything crossing a network you do not control wants verify-full.

With verify-ca and verify-full, sslrootcert points at the CA bundle; when it is omitted the bundled Mozilla roots are used, which is what public managed providers chain to.

sslcert and sslkey are the other direction: the certificate this process presents so the server knows who is connecting, for a PostgreSQL pg_hba.conf with clientcert=verify-full or a MySQL account declared REQUIRE X509. Set both or neither — half a client identity is refused before anything connects. The key must be unencrypted, in PKCS#8, RSA (PKCS#1) or EC (SEC1) form; one file holding both the chain and the key works for both options. They apply to every connection the process opens: the replication stream, the catalog and admin queries, and the initial load.

This is orthogonal to sslmode. require plus a client certificate is a real combination — encrypt, prove who I am, do not check who you are — and is what a self-signed managed instance that still demands a client certificate needs. The URL may carry sslcert= and sslkey= as well; the config wins per option.

Poll mode

For managed PostgreSQL instances where logical replication cannot be enabled. It re-reads rows whose timestamp column advanced since the last cycle.

• Deletes are invisible. There is no log to read them from.
• Requires a monotonically increasing timestamp column per table.
• Each start re-runs the initial load: there is no position to resume from, and re-indexing is harmless under idempotent writes. Existing WAL checkpoints are ignored in this mode so a gap can never be skipped.
• A row whose id, index template or routing column changed leaves its old document behind: a cycle sees the row's new state and has no before-image to find the old one by.

[target]

Meilisearch has no place to store an arbitrary document, so its checkpoint is a local file. Give that directory persistent storage, or a restart re-runs the initial load.

[sync.<key>]

One section per table. <key> is the index name when index is omitted.

Projection and transforms apply to every path — initial load, live streaming and poll mode — so an excluded column never reaches the target. The primary key is read before projection, so excluding a key column is rejected at load time (it would collide document ids). Ids, likewise, render from the row's raw values: before projection and before transforms. fields renames run after projection and transforms, and constants are added after that; every other option names the column as the source knows it.

Document ids

By default a document's _id is its row's primary key, exactly as it always has been — configuring nothing changes nothing, and an existing index needs no rebuild. A table with no key can still be synced insert-only, under a hash of the row, see Append-only tables. id overrides the shape:

[sync.orders]
table = "public.orders"
id = "tenant-{tenant_id}-{id}"

Literals plus {column} placeholders. The name has to be a column of the table, and the value renders like a key does: strings unquoted, numbers and booleans as text.

• A NULL in a column the id references halts the pipeline — the id cannot be invented, and the document the row already owns would be stranded. validate warns up front for nullable columns.
• An id naming only key columns works everywhere. An id that references columns outside the key needs the row's before-image to delete and move its documents, so on PostgreSQL the table must be REPLICA IDENTITY FULL; run refuses to start otherwise. MySQL already guarantees it (binlog_row_image = FULL).

Append-only tables

A table with no primary key can be synced as long as it only ever gains rows — an event log, an audit trail, a metrics table. Declare it:

[sync.events_log]
table = "public.events_log"
append_only = true

Without a key nothing can say which document a row is, so the document id is a content hash: sha256 of the row's raw values as canonical JSON, hex, 32 characters. The same row hashes the same on the initial load, the stream and in poll mode, so a replay lands on the document it already wrote — and two identical rows are one document, which is the right answer for an append-only table. If the table carries a unique column such as an event_id, set id and the document is named from it instead.

• An UPDATE or DELETE on the table halts the pipeline: public.events_log: an UPDATE arrived on an append-only table; nothing can say which document it is. There is no document to move or remove, so the pipeline stops at that change rather than guess; an append-only table is one on which it never arrives.
• where, columns, exclude_columns, transform, fields, constants, index templates and pipeline all work. A row that a where filter excludes is deleted under its own hash, which is a no-op on the first pass.
• primary_key contradicts the declaration and is refused; so are fan_out, join, [[children]] and soft_delete, each of which needs a key to address a document by.
• reconcile refuses an append-only table — it pages the index by a key column the table does not have. resnapshot works and writes the same hashes, and so does reindex, which checks its count as documents <= rows: rows the source cannot tell apart are one document.
• init writes append_only = true for a table it finds without a primary key, so the generated config runs unedited.

Sharing an index

An index built before pg2osync is usually a union of several tables, and several sections may name the same index:

[sync.users]
table = "public.users"
index = "search"
id = "user-{id}"

[sync.orders]
table = "public.orders"
index = "search"
id = "order-{id}"

• Every section sharing the index declares an id. The default id is the row's key, and two tables that both have a row 1 would be one document by accident; an explicit template on each section is the declaration that they are not. A shared index with a section that omits id is refused at config load. The templates themselves are not compared: user-{id} on both sections collides, and that is the operator's own declaration — nothing checks the values, because nothing can see them.
• At most one of the sections sets mapping_file. An index is created once; a second section describing it is refused.
• reconcile refuses a shared index. It pages the index by one table's key column and cannot tell one table's documents from another's, so every other table's documents would be reported as orphans — and removed by --delete.
• TRUNCATE on any of the tables is not applied. Clearing the index would wipe the tables the source never truncated, and halting would replay the same TRUNCATE from the slot at every restart with nothing to change to get past it — so the truncated table's documents are left in place, the pipeline logs TRUNCATE not applied to index search, which other tables also feed; its documents are left in place and counts a truncate_skipped event in pg2osync_events_total. Clear them by hand, or give the table an index of its own. A join pair is different: its halves are told apart by the join field, so a truncate there clears one relation exactly.
• resnapshot works on any one of the tables for the same reason: it writes by id and touches nothing else in the index.
• reindex refuses a shared index, including a join pair's. It reads one table, so the fresh index it built would hold that table's documents and nothing else — and the alias would then hide the others. Rebuild a shared index with a second instance of the whole config.

A join pair is the other way two sections share an index: there the join field scopes every document to its relation, which is why reconcile can check either half of the pair against its own table.

Per-row indices

index may carry {column} placeholders, so each row chooses the index it lands in. Two shapes cover most of what this is for. Time-based retention, where an old month is dropped as one index instead of deleted row by row:

[sync.events]
table = "public.events"
index = "events-{created_month}"    # a column holding e.g. 2026-08

And per-tenant isolation, where every tenant is searched, sized and secured on its own:

[sync.events]
table = "public.events"
index = "{tenant}-events"

The rules are the id rules, because a name derived from a column is the same problem as an id derived from one: the column can change, and the document is then in the old index.

• Same grammar, same row. Literals plus {column} placeholders, rendered from the row's raw values — before projections and transforms — exactly as id is. Every placeholder must name a column of the table, and validate checks that against the catalogue. A fanned row's element documents all go where the row goes, so the template may not name the fan_out column.
• A rendered name that is not a legal index halts the pipeline. An uppercase letter, an empty value, a NULL in a named column: none of these can become an index the target accepts, so the pipeline stops and names the template, the column and the value it rendered. validate warns up front for nullable columns.
• Non-key columns need the before-image. A template naming only key columns works everywhere. One naming a column outside the key needs the old row to find the index a changed row was in, so on PostgreSQL the table must be REPLICA IDENTITY FULL; run refuses to start otherwise. MySQL already guarantees it (binlog_row_image = FULL).
• The index is created on demand, at the first document that needs it, with the section's mapping_file if one is set. Nothing is created at startup, because the set of indices is not known until the rows are.
• A template must have a literal part, and may not overlap another section's index or be shared. Each placeholder stands for * in what a TRUNCATE clears, so index = "{tenant}" — a claim on the whole cluster — is refused at config load; so is a template whose pattern also matches an index another section writes to, and a template two sections name.
• TRUNCATE clears the pattern. Every index the template claims is searched, and each hit is deleted under its own index as a versioned write, so a row committed after the truncate is not swept away with it.
• A row that changes its index-choosing column moves. It is written in the new index and deleted from the old — the same move id makes for a row whose id changed.
• reconcile, switch-alias and reindex refuse a templated table. Reconcile pages one index by its key column, and the table's documents are spread over every index the template renders; an alias points at one index, and so does a rebuild. resnapshot works.
• Meilisearch refuses a template at startup: it has no mappings to create an index with.
• Bulk-load settings are not relaxed for a templated index. An index created during the initial load takes the target's defaults.

Rebuilding an index

A mapping cannot be changed on an index that already exists, so changing one means building a new index and moving the traffic to it. reindex is that in one command:

pg2osync reindex -c pg2osync.toml --table public.users --alias users

It creates users-<unix seconds> with the section's mapping_file, loads the table into it, compares what it wrote against the source's row count, and points the alias at the new index in one atomic request. --drop-old deletes the index the alias came off; by default it is kept, because it is the rollback — one alias flip away.

• Stop the pipeline first; the command refuses to run beside it. A re-snapshot is safe beside the stream because a copied row and a streamed change meet in the same index and the higher position wins. A fresh index the stream is not writing to has no second document to compare against, so a row that changed during the rebuild would be wrong there for good — and the count would still add up. The refusal is evidence, not a flag: an active replication slot on PostgreSQL, and on either source a checkpoint seen moving. There is no --force.
• The checkpoint does not move, by construction: the rows carry position 0, exactly as a re-snapshot's do. So everything committed while the pipeline was stopped is still in the log, and the restart replays it into the new index. That is also what proves the contents: the count only proves how many.
• Two follow-ups are yours, and the command prints both: set index to the new name in the section, and start the pipeline again.
• A count the source does not explain leaves the alias where it is. The source is counted before and after the load; anything outside that range is reported with all three numbers and a non-zero exit, and the rebuilt index is left for you to look at.
• Refused for a templated index, a shared index or a join pair, a fanned table, and an --alias equal to the index the section already writes to.
• On Meilisearch the alias is the index, so --alias there must be the name the section already writes to and every other value is refused. There is no alias namespace on that target; the rebuilt index is swapped into the live name with POST /swap-indexes instead, which is atomic in the same way. Two things differ afterwards: no config edit is needed, only the restart, and it is <index>-<unix seconds> that ends up holding the documents from before the rebuild — see the Meilisearch sink.
• A live cutover with no freshness gap at all is still two instances, as operations.md describes. A rebuild trades the gap for one command.

Retention

An index per month is only half of time-based retention: something still has to delete August once nothing searches it any more. pg2osync does not, and neither target needs it to — both have an index-lifecycle feature that acts on an index pg2osync created on demand without pg2osync knowing anything about it. The policy is one PUT the operator makes once; the target owns the lifecycle of its own indices, and a sync tool that also deleted them would be a second, weaker copy of a scheduler that is already there.

Elasticsearch: name an ILM policy in the mapping's settings. The mapping_file is the index-creation body, settings included, and it is sent verbatim for every index the template renders — so the policy is attached to events-2026-08 at the moment the first August row creates it, and to events-2026-09 a month later, with no template to maintain:

{
  "settings": {
    "index.lifecycle.name": "events-30d"
  },
  "mappings": {
    "properties": {
      "created_at": { "type": "date" }
    }
  }
}

The policy itself is created once, by hand:

PUT _ilm/policy/events-30d
{
  "policy": {
    "phases": {
      "delete": {
        "min_age": "30d",
        "actions": { "delete": {} }
      }
    }
  }
}

index.lifecycle.name is all that is needed here: min_age counts from the index's creation date for an index that never rolls over, which is exactly what a month bucket is. index.lifecycle.rollover_alias belongs to the rollover action — a write alias moving from one index to the next — and a row-chosen index has no such alias: the row's own column says where it goes. Setting it without a rollover action makes the policy fail on an index it cannot roll over. (See ILM index settings and the delete action.)

OpenSearch: match the indices from an ISM policy. ISM attaches itself. A policy carrying an ism_template is applied to every index created after it whose name matches one of the template's patterns, so nothing goes in mapping_file at all:

PUT _plugins/_ism/policies/events-30d
{
  "policy": {
    "description": "Delete an events index 30 days after it was created.",
    "default_state": "hot",
    "states": [
      {
        "name": "hot",
        "actions": [],
        "transitions": [
          { "state_name": "delete", "conditions": { "min_index_age": "30d" } }
        ]
      },
      {
        "name": "delete",
        "actions": [{ "delete": {} }],
        "transitions": []
      }
    ],
    "ism_template": [
      { "index_patterns": ["events-*"], "priority": 100 }
    ]
  }
}

The legacy way of attaching a policy — an index template setting named plugins.index_state_management.policy_id (opendistro. before that) — is deprecated in favour of ism_template. It still works on the 2.x line, but it needs an index template to carry the setting, which is the maintenance the ism_template field removes. (See Index State Management and the ISM API.)

Both mechanisms have the same two edges:

• The policy has to exist before the index does. An ISM template is consulted at index creation only, and an ES index created before the policy existed carries no index.lifecycle.name. Indices already in the cluster are attached by hand — POST _plugins/_ism/add/events-2026-07 on OpenSearch, PUT /events-2026-07/_settings with index.lifecycle.name on Elasticsearch — and every index created from then on is covered.
• Nothing deletes on the stroke of the boundary. ILM checks its indices every indices.lifecycle.poll_interval (10 minutes by default), ISM on its own job schedule; an index outlives its min_age by that much.

Keep the pattern narrow enough to miss pg2osync's own state. events-* is fine; a policy matching * would also claim the hidden .pg2osync_meta checkpoint index and eventually delete the position the pipeline resumes from.

Data streams are not supported. A data stream accepts only create actions in a bulk request, and pg2osync writes index actions: every document is keyed by its id and rewritten, because at-least-once delivery means a replayed change has to overwrite the document it already wrote rather than be rejected or duplicated. That holds even for an append_only table, whose content hash exists precisely so that a re-delivered row lands on the same document again. A time-bucketed index with a lifecycle policy is what a data stream would have given here anyway: whole indices dropped by age, never documents deleted one at a time.

Fan-out

One row whose array column holds N elements can become N documents:

[sync.tickets]
table = "public.tickets"
id = "ticket-{id}"

[sync.tickets.fan_out]
field = "tags"            # a PostgreSQL array column, or jsonb holding an array
id = "ticket-{id}-{tags}"

Each element document is the parent document minus the array, merged with the element: an object element's fields are merged in and win on collision, a scalar element lands under the array's own field name. The element id renders from that merged document, so its placeholders can name parent columns and element fields alike.

• A row with an empty or missing array emits nothing; a row whose array is NULL keeps one parent document under the plain id.
• Updates diff before against after: elements that left the array have their documents deleted, the rest are rewritten. Deletes remove every element document the row owned. All of it as ordinary versioned writes, in the same order as everything else — write_concurrency keeps working.
• PostgreSQL: the table needs REPLICA IDENTITY FULL (checked at startup), because deletes and diffs come from the row's old values. Poll mode and [[children]] on the same table are refused; so is naming the fan-out column in columns/exclude_columns, which would cut the array before identity and fan-out ever see it. reconcile and resnapshot do not support fanned tables yet: both page by key, and one row now has many documents. reindex refuses one too — it checks what it wrote against the source's row count, and here one row is many documents.

Two tables may map to the same index once each declares its id; see Sharing an index.

Transforms

A column can be reshaped on its way into the document. transform maps a source column to one of six named operations: a string for an op that takes no parameter, an inline table for one that does.

[sync.users]
table = "public.users"

[sync.users.transform]
email   = "hash"
phone   = "redact"
payload = "json"                             # or { op = "json" }
price   = "number"
tags    = { op = "split", by = "," }
born    = { op = "date", from = "%d/%m/%Y" }

hash replaces the value with a truncated SHA-256 digest, stable across runs so it can still be grouped on. redact replaces it with ***. The other four turn a string into something more structured:

NULL is left alone by every op, and so is a value already in the target shape: a parsed json/jsonb/JSON column under json, an array under split, a number under number. That is what keeps the ops idempotent when at-least-once delivery replays a row, and it is why the three exist for text columns that hold something more structured. number is also the explicit opt-out of the rule that numeric/DECIMAL arrive as strings to keep their precision — for an index that sorts or range-queries on the value and accepts the double.

A value an op cannot convert — "abc" under number, a date that does not match from — is indexed exactly as it arrived, counted in pg2osync_transform_unconverted_total, and logged once per table and column. The pipeline never halts on it: the target's mapping is the arbiter of what a field holds, and a document the mapping refuses takes the ordinary rejection path (see on_permanent_rejection). A fanned row counts once per element document.

• If one field will hold both converted and unconverted values, mapping_file should type it as text. Otherwise dynamic mapping types the field from the first document and refuses the second — and that refusal is the halt or quarantine path, not this policy.
• Transforms name the source column and run after projection, before fields renames and constants.
• split cannot feed fan_out: fan-out reads the raw row, before any transform, so it needs a real array column.

Refused at load: an unknown op, a parameter the op does not take, split without a non-empty by, date without a non-empty from, and a transform on the fan_out.field.

Field names

An index that already exists is rarely named after the database. fields stores a column under another name:

[sync.users]
table = "public.users"

[sync.users.fields]
usr_nm = "username"

The rename is the last shaping step — identity, fan-out, projection and transforms all run first — so every other option (columns, exclude_columns, transform, id, fan_out.field, primary_key, soft_delete, poll_column) keeps naming the column as the source knows it. The new name applies on the initial load, the stream, poll mode and a re-snapshot alike, and inside embedded child arrays through the child's own fields (see Nested children).

Refused at load: an empty name, renaming a column to itself, two columns to the same name, renaming an excluded column or one missing from columns, a target that equals a non-renamed column in columns, and a parent rename that names or targets a child field (or its _truncated/_total, which a single child does not write and so does not claim). validate warns when a renamed column does not exist — a stale config, as with exclude_columns — and refuses a target that equals a live column that is not itself renamed away.

• TOAST completion reads the stored document, so it finds the column under its new name.
• mapping_file must declare the renamed names: the mapping is compared against the index, never against the table.

Constant fields

A tag several indices can be queried by, or a marker of where a document came from, needs no column. constants adds a literal value to every document of the section:

[sync.users]
table = "public.users"

[sync.users.constants]
entity = "user"
tenant = "eu"
origin = "{schema}.{table}"
rank = 3
active = true

Scalars only — string, integer, float, boolean; arrays, tables and datetimes are refused at load. {schema} and {table} are the only placeholders, allowed only inside a string and rendered once at startup, so a string naming any other placeholder (or with a malformed {) is refused at load. A string without { is taken verbatim; there is no way to write a literal {.

Constants are added last — after identity, fan-out, projection, transforms and renames — because columns would otherwise strip a field that is not a column. Every fanned element document carries them; child arrays do not.

Refused at load: a name that is a rename target, a surviving entry of columns (one not itself renamed away), a child field (or its _truncated/_total, which a single child does not claim), or the fan_out.field. validate additionally refuses a name that equals a live column the projection keeps. A name equal to a rename key is fine: that column leaves the document first. At write time the constant wins.

• mapping_file is compared for containment, so a constant it does not name gets whatever dynamic mapping infers from the first document; declare it there if the type matters.

Row filters

Not every row of a table belongs in the index. where is a predicate in a restricted SQL subset that decides which rows do:

[sync.users]
table = "public.users"
where = "status = 'active' AND tenant IN ('eu', 'us') AND deleted_at IS NULL"

Keywords are case-insensitive. There are no functions, no LIKE and no column-to-column comparison; anything outside the subset is refused at config load with a message listing what is supported.

The initial load pushes the predicate into its query — the COPY on PostgreSQL, the chunk reads on MySQL — so a row that does not match is never read, shipped or indexed; resnapshot --where ANDs with it, and reconcile treats a row that no longer matches as gone. The engine then evaluates the same predicate on every streamed and polled row: one whose new state matches is written, one whose new state does not is deleted from the index — every element document of a fanned row, the id a moved row used to own. That is what makes a row that leaves the filter disappear and one that enters it appear. The predicate sees the raw row, before projection, so a column that columns excludes can still be filtered on.

• NULL follows SQL: a comparison against NULL is unknown, NOT of unknown is unknown, and a row matches only when the predicate is TRUE. IS NULL also matches a column the source did not send.
• Strings compare byte-wise. Equality is exact everywhere; ordering is exact for ASCII and ISO 8601, which is what makes created_at >= '2024-01-01' work against the textual timestamps the sources hand over.
• A number compared against a string holding a number compares numerically: numeric/DECIMAL reach the engine as strings to keep their precision, and SQL would compare them as numbers, so price > 10 matches 10.01.
• Nothing new is asked of the source: a key-only id renders its delete from the key, and non-key ids and fan_out already required the before-image.
• validate refuses a predicate naming a column the table does not have, and runs SELECT 1 FROM t WHERE (predicate) LIMIT 0 against the live table to catch what the grammar cannot, such as a type error.
• Poll mode does not push the predicate into its query, on purpose: a row that has left the filter must keep arriving so the engine can turn it into the delete it now is. See Soft deletes for how the two compose.
• The cost, stated plainly: a WAL insert of a row that never matched still produces one idempotent delete, which the target answers not-found, and a non-matching parent of a child collection produces one such delete per child change.
• A filter selects rows; it computes no values. There is still no transformation language.

Soft deletes

Poll mode has no replication log, so a row that is simply gone leaves nothing to poll and cannot be seen. A row marked deleted can be:

[sync.users]
table = "public.users"
soft_delete = "deleted_at IS NOT NULL"

A row matching the predicate is removed from the index instead of upserted, and the initial load skips it rather than indexing it only to delete it on the first cycle. The predicate is evaluated by the database — poll mode has a query to put it in — so any boolean expression over the row's own columns works, status = 'archived' as much as a timestamp check.

It is poll-mode only, and configuring it elsewhere is rejected rather than ignored. The general form is a row filter: where = "deleted_at IS NULL" works in WAL, binlog and poll mode alike, and turns the UPDATE that marks a row deleted into the delete it means. What soft_delete keeps for poll mode is the database's evaluation, and with it any expression the grammar of where does not accept. The two compose — soft_delete deletes, where gates — and naming the same column in both is redundant rather than wrong.

Index mappings

Without mapping_file the index is created empty and the target infers field types from the first document that carries each field. That is enough to get started and not enough for real search: analyzers, keyword subfields for aggregation, explicit date formats and vector fields all have to exist before the first document lands.

[sync.users]
table = "public.users"
index = "users"
mapping_file = "users-mapping.json"

The path is resolved relative to the config file, and the file is read at startup so a missing or malformed one fails before anything connects. It holds either a full index-creation body or just the mapping:

{
  "mappings": {
    "properties": {
      "id":         { "type": "long" },
      "name":       { "type": "text", "fields": { "raw": { "type": "keyword" } } },
      "created_at": { "type": "date" }
    }
  },
  "settings": { "number_of_shards": 1 }
}

Three rules, and the reasoning behind each:

• It applies only when the index does not exist. A target refuses to change an existing field's type — that is a reindex — so applying a mapping to a live index would either fail or quietly do half the job.
• An existing index is compared against it at startup. A field the index maps to a different type is an error: every document carrying it would be rejected, and a permanent rejection halts the pipeline. A field the index does not declare is a warning: it will be mapped from whatever value arrives first, which may be what you wanted.
• Only the fields you name are checked. The target normalises what it is given and dynamic mapping legitimately adds fields you never declared, so an equality check would report differences on a mapping that is exactly right.

If you would rather manage an index template, leave mapping_file unset: the index is then created without a body and your template applies. Configuring both means the creation body wins and the template is ignored for these indices.

Meilisearch has no field types to declare; mapping_file is refused for that target rather than ignored.

Ingest pipelines

A vector field is the one thing a mapping can declare that no row can fill: the embedding has to be computed by something that holds the model. pg2osync does not; the target does. pipeline names an ingest pipeline on the target, and every document the section writes carries it on its bulk action, so the target runs the pipeline's processors on the way in:

[sync.products]
table = "public.products"
index = "products"
mapping_file = "products-mapping.json"
pipeline = "embed-products"

The pipeline is yours to create, before the first document lands. A text_embedding processor (OpenSearch's neural-search plugin; Elasticsearch has the inference processor) reads a text field and writes the vector into a field the mapping declares as knn_vector:

{
  "mappings": {
    "properties": {
      "name":        { "type": "text" },
      "description": { "type": "text" },
      "embedding":   { "type": "knn_vector", "dimension": 384 }
    }
  },
  "settings": { "index.knn": true }
}

pipeline = "embed-products" is the whole of pg2osync's part; the model, the processor and the dimension above belong to the pipeline and the mapping, and a set processor or any other works the same way.

validate asks the target for the pipeline (GET _ingest/pipeline/<name>) and refuses a name it does not have, because every document would otherwise be rejected at the first write, with the pipeline named but the config already running. A pipeline that exists is reported by name, one line per section.

Three things follow from the pipeline riding on the operation rather than on the index:

• It is per section, not per index. Two tables feeding one index (see Sharing an index) may name different pipelines, so each embeds its own columns; a section without pipeline writes to the same index with none.
• A delete carries no pipeline. Ingest pipelines run on index actions only, which is also what the target does.
• A quarantined document is replayed through the pipeline again. The record kept by on_permanent_rejection = "quarantine" stores the pipeline with the operation, so pg2osync rejects --replay submits it the way it was first submitted, and the document does not land without its vector.

Meilisearch has no ingest pipelines; pipeline is refused for that target at config load rather than ignored.

Nested children

Embed a one-to-many relation as a JSON array on the parent document:

[sync.customers]
table = "public.customers"
index = "customers"
primary_key = "id"

[[sync.customers.children]]
table = "public.orders"      # child table
field = "orders"             # array field on the parent document
foreign_key = "customer_id"  # column on the CHILD referencing the parent key
# max_rows = 1000            # optional: embed at most this many, see below
# single = true              # optional: a 1:1 relation, see below

A child's columns are renamed the same way, on the child element rather than on the parent:

[[sync.customers.children]]
table = "public.orders"
field = "orders"
foreign_key = "customer_id"

[sync.customers.children.fields]
total = "amount"             # every element of `orders` carries `amount`

<field>_truncated and <field>_total follow the child field, not a rename.

A child collection is projected the same way as a section, with columns or exclude_columns on the child rather than on the parent:

[[sync.customers.children]]
table = "public.orders"
field = "orders"
foreign_key = "customer_id"
exclude_columns = ["internal_notes"]   # every element leaves this column out
# columns = ["id", "total"]            # or list what to keep — not both

The two are mutually exclusive, as on a section, and an empty columns list is refused. The projection happens in the read: the initial load and the per-transaction re-fetch are built from the same expression, so they cannot embed different shapes, and PostgreSQL never reads a column the element does not name. fields runs after the projection and names the source column, so a column that is excluded — or left out of columns — cannot also be renamed; that is refused at startup rather than silently dropping the rename. The foreign_key is kept only if you list it: it is read as its own column beside the element, so leaving it out of the array changes nothing but the array.

• PostgreSQL and MySQL/MariaDB alike.
• One level deep only.
• Children are fetched during the initial load and re-fetched whenever the parent or any of its children changes, so the array is never stale.
• Child tables are added to the publication automatically.
• The initial load reads each collection once and joins it, so it costs one query per table no matter how many parents there are.
• Streamed changes cost one query per collection per transaction, not per row. Rows are held until the transaction commits, the distinct parents they affect are collected, and each collection is read once for the whole group — so a transaction touching 2,000 children of 20 parents issues 3 queries and writes 20 documents, where per-row resolution issued 4,001 and wrote 2,000. Index the foreign key on the child table: those lookups compare the key in its own type, and without an index each one scans the whole child table.
• The array is ordered by the child table's primary key, so the initial load and a later re-fetch embed it identically and a re-snapshot does not rewrite documents for no reason. A child table with no primary key has no such order, and says so at startup.

How many children to embed

max_rows is unset by default, so the whole collection is embedded however large it is. That is deliberate: a cap loses data, and the bound that matters is already the target's. Past index.mapping.nested_objects.limit — 10,000 by default — OpenSearch refuses a document whose field is mapped nested, because every element becomes a hidden Lucene sub-document; that refusal names the parent and is quarantined rather than lost (see on_permanent_rejection). Below the limit the cost is gradual. An array past 10,000 is logged, naming the parent, so the decision is visible rather than a surprise later.

Setting max_rows trades a complete array for a bounded document. A document whose array was cut says so, in two extra fields:

{ "id": 42, "orders": [ /* max_rows of them */ ],
  "orders_truncated": true, "orders_total": 100000 }

They appear only when something was left out, so orders_truncated: true finds every affected parent in one query. Which rows are kept is decided by the child table's primary key, so the same ones are kept every time and the initial load and a streamed re-fetch agree — a cap without that order would keep a different subset on each run. max_rows on a child table with no primary key is refused at startup for the same reason.

• The field name must not collide with a column of the parent table; the initial load refuses to start rather than shadow a real column.
• Give child tables REPLICA IDENTITY FULL (ALTER TABLE public.orders REPLICA IDENTITY FULL). Without it a DELETE carries no foreign key, so the parent cannot be located; pg2osync warns at startup and fails on such a delete rather than silently going stale.
• MySQL/MariaDB: the child table is streamed from the binlog automatically; binlog_row_image = FULL (already required) is what lets a child DELETE carry its foreign key, so there is no REPLICA IDENTITY caveat.

A one-to-one relation

A users → profiles relation is one child, and an array of one makes every query and every mapping carry an index that is always zero. single = true embeds the element itself:

[[sync.customers.children]]
table = "public.profiles"
field = "profile"
foreign_key = "customer_id"
single = true

{ "id": 42, "profile": { "customer_id": 42, "bio": "..." } }

The field is always present: a parent with no child gets "profile": null, so a query need not know whether this parent happens to have one. fields, columns and exclude_columns work exactly as they do on an array child.

Map the field as object, not nested. nested exists to keep the elements of an array from being flattened into one another; there is no array here, so it buys nothing and index.mapping.nested_objects.limit — the whole reason the array form has a cap — does not apply.

• max_rows is refused with single: a relation declared one-to-one has nothing to cap. <field>_truncated and <field>_total are never written, and the two names are free for a column, a constant or a rename.
• The child table needs a primary key, refused at startup otherwise: with no order there is no first row, so two runs could embed different ones.
• A second matching row does not fail the run — a duplicate that exists for the length of a migration must not halt an index. The lowest-keyed row is embedded, which is the same one a re-snapshot picks, and each batch logs one warning naming the collection, how many parents matched twice and the worst of them. Fix the data, or drop single, and the next change to those parents rewrites them.

Join fields

The embedded array above is one document and one write, and it is the right choice nearly always. When the children are many, change far more often than the parent, or have to be searched in their own right, a join field keeps each child a document of its own — on its parent's shard, so has_child and has_parent queries work — instead of re-fetching the whole collection on every child change:

[sync.customers]
table = "public.customers"
index = "shop"
id = "customer-{id}"
mapping_file = "shop-mapping.json"

[sync.customers.join]
field = "relation"           # the join field the mapping declares
name = "customer"            # this table's relation name inside it

[sync.orders]
table = "public.orders"
index = "shop"
id = "order-{id}"

[sync.orders.join]
field = "relation"
name = "order"
parent = "customer_id"       # column on THIS table holding the parent's key

parent is what makes a section the child: it names the column whose value, rendered through the parent section's id, is the parent document's id — and the child's routing. The parent omits it. Each document carries the join field in the shape the target expects, "customer" on a parent and {"name": "order", "parent": "customer-1"} on a child. It is written after projection and renames, like a constant, so nothing can strip it or move a document to another shard. OpenSearch and Elasticsearch only.

• The mapping lives on the parent, and only there. A join pair is two sections and one index; the parent's mapping_file creates it and has to declare the field:

  { "mappings": { "properties": {
      "relation": { "type": "join", "relations": { "customer": ["order"] } } } } }
  

  A child that sets mapping_file is refused. Dynamic mapping cannot invent a join field, so without this mapping — or an index template that declares the field — the first document is rejected.

• Ids must be unique across the shared index. Parent customers.id = 1 and child orders.id = 1 would both render _id = "1", and the child would overwrite the parent. Configuration cannot see it; give each section an id with its own prefix, as customer-{id} and order-{id} above.

• The parent's id may name only its key. The child holds one column and has to compute the parent's id from it alone, so an id naming anything else is refused at load, and a parent with a composite key at startup.

• PostgreSQL: the child needs REPLICA IDENTITY FULL unless its parent column is part of its own key. A delete has to reach the shard that holds the document, and the routing comes from the old row — the same rule a non-key id follows; run refuses to start otherwise. A child whose parent column changes moves: written under the new parent, deleted under the old. MySQL already guarantees the before-image.

• A parent delete cascades. The engine does not know which children the target holds, so the sink refreshes the index and searches for them, after the parent's own delete and at the parent's position. The refresh is what makes it correct — a child written seconds earlier would otherwise survive — and it is the cost: one per deleted parent, which the batch waits for. Each batch that carries one counts a join_cascade event in pg2osync_events_total.

• TRUNCATE on either table clears its relation only. The join field tells the halves apart, so a truncate of the orders removes every order document — routed to its parent's shard — and leaves the customers, unlike a plain shared index, where nothing can tell one table's documents from another's. PostgreSQL makes you truncate tables that reference each other together anyway, so usually both.

• A where filter is the operator's to keep consistent across the pair. A child whose parent the parent section's where filters out is indexed with a parent that no document has.

• A NULL in the parent column halts the pipeline, as a NULL in an id column does; validate warns when the column is nullable, and refuses a column the table does not have.

• reconcile scopes its scan to one relation, so it checks either half of the pair against its own table and routes the deletes it makes.

Refused at config load: two sections on one index without join on every one of them and without an id on every one of them (see Sharing an index); sections of one index naming different join fields; an index with no parent, or with two; two sections sharing a relation name; a child that only names a parent no other section provides; a child with mapping_file; fan_out together with join; a parent id naming a column outside its key; join against Meilisearch, which has no parent-child model; a fields rename, a constant, a columns entry or a [[children]] field that collides with the join field. [[children]] on a join child is allowed: embedding an array on this document and filing this document under a parent are unrelated. A table that is both an embedded child of another section and a section of its own is warned about at startup, not refused: the replication runner reads its rows only as a re-fetch of the owner, so its own index receives the initial load and no streamed change.

Routing

A document's shard is chosen from its _id unless something says otherwise. routing says otherwise: it names a column whose value decides the shard, so every document sharing that value lands on one shard and a query for it reads one shard instead of all of them.

[sync.documents]
table = "public.documents"
index = "documents"
routing = "tenant_id"

A tenant column is the usual case: hundreds of small tenants in one index, each query scoped to one of them. An index per tenant would be the alternative, and it is the wrong one when tenants are many and small — every index costs shards, and shards cost memory whether they hold ten documents or ten million.

• The value is the column's raw value, read before projection and transforms, like an id is: a projection must not be able to move a document to another shard. The column stays an ordinary field of the document; routing adds nothing to it.
• NULL, missing, or empty halts the pipeline. The target rejects an empty routing outright, and quietly writing the document to its default shard would hide it from every routed query. validate refuses a column the table does not have and warns when it is nullable.
• PostgreSQL: a routing column outside the key needs REPLICA IDENTITY FULL. A delete has to reach the shard that holds the document, and the old value comes from the before-image — the same rule a non-key id follows; run refuses to start otherwise. MySQL already guarantees the before-image with binlog_row_image = FULL.
• A changed value moves the document: written under the new routing first, deleted under the old second, exactly as a changed id or a changed index template moves it.
• Fanned-out elements inherit the row's routing, and a row that changes its routing takes them all with it.
• A routing column that is both projected away and TOASTable halts on an update that does not resend it, like a non-key id column: the read-back fills the document, not the row the routing renders from.
• reconcile and TRUNCATE are unaffected. Both work index-wide and take each document's routing from the hit itself, so neither has to derive one. A document duplicated under a stale routing is not something reconcile collects: the row it belongs to is still there.
• Poll mode leaves the old copy. A poll cycle sees the row's new state and nothing else, so a changed routing value writes a second copy under the new routing and never deletes the first — the same limitation a changed id has in poll mode.

Refused at config load: routing together with join, which already routes a child to its parent's shard, and routing against Meilisearch, which ignores routing entirely.

[engine]

Defaults are production-sane; tune only against measurements.

checkpoint_interval_ms is the ceiling on replayed work after a crash: a lower value means less replay and more writes to the target.

A transaction larger than txn_buffer_cap_mb is split across requests, which means the target briefly holds part of it. Everything is idempotent, so the end state is correct, but a reader can observe the transaction half-applied.

Transient failures (HTTP 429, 5xx, connection resets) are retried with exponential backoff. A permanent rejection — a mapping conflict, for example — stops the pipeline instead of skipping the document, because skipping is silent data loss. on_permanent_rejection = "quarantine" trades that for availability: the document is recorded with its position before the position is acknowledged, so nothing is lost, but the transaction it belonged to is applied without it. pg2osync rejects --replay puts it back once the mapping is fixed. Only the OpenSearch and Elasticsearch targets can quarantine; configuring it against Meilisearch fails at startup.

[api]

The read-your-writes endpoint. Off by default: it is a surface applications call, not an operational one.

GET /synced

Blocks until everything committed before the request is written to the target, then answers. A query made after it returns is guaranteed to see those writes.

GET /synced?refresh=true&timeout=2000
200 {"synced":true,"requested":"0/1B4F2A8","confirmed":"0/1B4F2B0","waited_ms":5}
408 {"synced":false,…}   still behind when the timeout elapsed
400 the position could not be parsed

Leave position out unless you have a reason not to. Reading it requires REPLICATION CLIENT on MySQL — a privilege an application account should not hold — and pg2osync already has a connection that does.

refresh=true is what separates stored from searchable: OpenSearch and Elasticsearch only expose a write to search after a refresh, on their own interval. Without it the document is retrievable by id but a search may not find it yet.

The wait costs nothing on the write path. A background job that does not care never calls this and pays nothing.

[metrics]

Only GET /metrics and GET /healthz are served; anything else is a 404. /healthz is never authenticated, because a kubelet probe has nowhere to keep a token and a liveness check that fails on a missing one would restart a healthy pipeline.

With token_env set, Prometheus sends the same token:

scrape_configs:
  - job_name: pg2osync
    authorization:
      type: Bearer
      credentials_file: /etc/prometheus/pg2osync-token
    static_configs:
      - targets: ["pg2osync:9100"]

pg2osync_events_total{type="row|truncate|join_cascade"}   # join_cascade: a batch that removed a deleted parent's children
pg2osync_batches_flushed
pg2osync_toast_readbacks_total                  # reads to complete TOASTed columns
pg2osync_sink_errors_total
pg2osync_rejected_total                         # documents the target refused, quarantined instead of written
pg2osync_transform_unconverted_total            # values a transform could not convert, indexed as they were
pg2osync_schema_drift_total{table="schema.table"}  # a table changed shape; the index keeps the old one until rebuilt
pg2osync_reconnects_total
pg2osync_source_connected                       # 1 while the source is streaming, 0 while reconnecting
pg2osync_latency_ms{quantile="0.5|0.9|0.99"}   # source commit to indexed
pg2osync_latency_ms_count
pg2osync_position_current                       # highest position received
pg2osync_position_confirmed                     # highest position checkpointed
pg2osync_position_lag                           # difference between the two

Environment variables

Complete example

[source]
flavor = "postgres"
url_env = "PG2OSYNC_SOURCE_URL"
slot_name = "pg2osync"
publication = "pg2osync_pub"

[target]
flavor = "opensearch"
url = "https://opensearch.internal:9200"
username = "pg2osync"
password_env = "PG2OSYNC_TARGET_PASSWORD"
tls_verify = true

[engine]
batch_size = 500
batch_max_bytes = 10485760
write_concurrency = 1
checkpoint_interval_ms = 500

[metrics]
enabled = true
bind = "127.0.0.1:9100"

[sync.users]
table = "public.users"
index = "users"
exclude_columns = ["password_hash"]
where = "deleted_at IS NULL"

[sync.users.transform]
email = "redact"
interests = { op = "split", by = "," }

[sync.customers]
table = "public.customers"
index = "customers"
primary_key = "id"

[[sync.customers.children]]
table = "public.orders"
field = "orders"
foreign_key = "customer_id"

PostgreSQL source

pg2osync's primary source. Uses logical replication (pgoutput protocol) for real-time change capture with a consistent-snapshot backfill.

Requirements

• PostgreSQL 15 or newer — 17 runs on every pull request and 15, the floor, runs nightly (see compatibility)
• wal_level = logical in postgresql.conf (restart required)
• Sync user needs:
  • REPLICATION privilege (or superuser)
  • SELECT on all synced tables (used by backfill and child queries)
  • schema usage rights

pg2osync setup-sql -c pg2osync.toml prints the whole script for your config — role, grants, publication, the wal_level change and the restart it needs — so it can be handed to whoever holds the privileges. By hand it is:

CREATE USER sync_user WITH REPLICATION PASSWORD '...';
GRANT CONNECT ON DATABASE appdb TO sync_user;
GRANT USAGE ON SCHEMA public TO sync_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO sync_user;

Creating the publication additionally requires CREATE on the database and ownership of every published table — a PostgreSQL restriction that a grant cannot work around. If the tables belong to someone else, have a privileged role create the publication and slot once; pg2osync validate prints the exact statements. See database impact for the full privilege matrix and what the tool costs the server.

Verify readiness:

pg2osync validate -c pg2osync.toml
# ✓ connected to PostgreSQL
# ✓ wal_level = logical
# ✓ table public.users exists

TLS

Every connection — catalog, snapshot, nested-child queries and the replication stream — honours [source] sslmode. It defaults to prefer, matching libpq.

[source]
url_env = "PG2OSYNC_SOURCE_URL"
sslmode = "verify-full"
sslrootcert = "/etc/ssl/certs/rds-ca.pem"   # omit to use the Mozilla roots

Managed PostgreSQL (RDS with rds.force_ssl, Cloud SQL, Supabase, Neon) refuses unencrypted connections, so disable fails there by design. See the mode table in configuration for what each one actually verifies.

Client certificates

A server that authenticates its clients by certificate needs both halves of an identity, and refuses the connection outright without them:

[source]
url_env = "PG2OSYNC_SOURCE_URL"
sslmode = "verify-full"
sslrootcert = "/etc/ssl/certs/server-ca.pem"
sslcert = "/etc/ssl/certs/client.crt"
sslkey = "/etc/ssl/private/client.key"

On the server side that is a pg_hba.conf line such as

hostssl all all 0.0.0.0/0 cert clientcert=verify-full

with the issuing CA in ssl_ca_file. Under cert and under clientcert=verify-full the certificate's CN must equal the database role the URL connects as; a certificate that verifies but names someone else is rejected. pg2osync validate prints the DN the server saw, which is the quickest way to tell the two failures apart.

WAL mode (default)

[source]
url_env = "PG2OSYNC_SOURCE_URL"
slot_name = "pg2osync"          # optional, this is the default
publication = "pg2osync_pub"    # optional, this is the default

The URL must reach PostgreSQL directly: the stream is a replication connection, and a pooler in transaction mode cannot carry one — see Proxies and connection poolers.

What pg2osync creates automatically on first run:

• CREATE PUBLICATION pg2osync_pub FOR TABLE <your tables>
• CREATE REPLICATION SLOT pg2osync LOGICAL pgoutput

You can also create them beforehand with pg2osync bootstrap — useful when the sync user can't run DDL and a DBA provisions the objects instead.

Row identity

• Documents are keyed by the table's primary key (_id = pk) unless the table configures id, which derives the id from the row's raw values. Composite PKs are supported. A table with no primary key syncs only when declared append_only: its documents are keyed by a hash of the raw row, and an UPDATE or DELETE on it halts the pipeline. See Append-only tables.
• UPDATE/DELETE events only carry the old row if the table has REPLICA IDENTITY FULL. Default (DEFAULT) is enough as long as you don't change primary keys; if PKs can change, set:

ALTER TABLE users REPLICA IDENTITY FULL;

An id that references columns outside the key, and any fan_out table, need the same thing for the opposite reason: removing or moving a document means knowing the id the row had, and only the old row says that. Those are refused at startup without REPLICA IDENTITY FULL, naming the ALTER.

FULL changes what the WAL carries, not what identifies a document: pgoutput then flags every column as part of the identity, and pg2osync still files a row under its primary key, read from the catalogue at startup — the same key the initial load used.

pg2osync reads the actual setting from pg_class.relreplident and warns at startup when a table cannot support what your configuration asks of it.

Column selection

[sync.users]
table = "public.users"
index = "users_index"
exclude_columns = ["password_hash", "internal_notes"]
# ...or whitelist instead:
# columns = ["id", "name", "email"]

Projection applies to the initial load and to live streaming alike, so an excluded column never reaches the target. The table's where predicate is pushed into the COPY statement and evaluated again on every WAL row, so a row that does not match is never read and one that stops matching is deleted; see Row filters. To store a column under another name see Field names; projection and transforms still refer to the source name. numeric arrives as a string to keep its precision; transform = "number" converts it if you accept float precision (see Transforms).

TOASTed columns (very large values) that an UPDATE did not modify arrive as markers rather than values. pg2osync completes them from the old tuple when the table has REPLICA IDENTITY FULL, and otherwise reads the previously indexed document back from the target — so the document is never written with a hole in it.

Poll mode (fallback)

For managed databases where you can't enable logical replication (some RDS/Cloud SQL tiers, shared hosting):

[source]
mode = "poll"
url_env = "PG2OSYNC_SOURCE_URL"
poll_column = "updated_at"        # timestamp column maintained by triggers/app
poll_interval_secs = 30

Limitations:

• Upsert-only: deletes are invisible to polling. A soft-delete column plus a filter in your queries is the usual workaround.
• Primary key changes are invisible too. Polling only ever sees the row as it is now, never the key it had before, so the document left behind at the old key stays in the index. WAL mode handles this correctly; in poll mode, avoid mutable primary keys.
• Rows need a reliably bumped, monotonically increasing timestamp column.
• The latency floor is the poll interval.
• There is no position to resume from, so every start re-runs the initial load. WAL checkpoints left by a previous mode = "wal" run are ignored on purpose: using one would skip rows that changed while the process was down.
• poll_page_size (default 5000) bounds how many rows one cycle reads per table; a large backlog drains over several cycles.

Nested children

Child collections are embedded during the initial load with a single aggregating join per table, and re-fetched afterwards whenever the parent or one of its children changes.

Index the child's foreign key. Both paths compare the key in its own type so an index can be used, but if none exists PostgreSQL still has to scan.

CREATE INDEX ON public.orders (customer_id);

Truncates and deletes

TRUNCATE on a synced table clears the target index. It is ordered against writes still queued for the target, so a row written just before the truncate cannot survive it.

DELETE needs the row's key, which the default replica identity provides when the table has one. A table with REPLICA IDENTITY NOTHING cannot replicate updates or deletes at all; pg2osync fails with the exact ALTER TABLE to run. A table with no primary key keeps the default identity (d) but has no key for it to name, and once it is published PostgreSQL itself rejects an UPDATE or DELETE on it. Such a table syncs only as append_only; should a change reach the pipeline anyway (under REPLICA IDENTITY FULL), it halts rather than guess.

Slot hygiene

A replication slot that isn't consumed retains WAL forever and will fill the database disk. Operational rules:

• Monitor with pg_replication_slots (restart_lsn, confirmed_flush_lsn) or just pg2osync status.
• Decommissioning an environment: always run pg2osync drop-slot.
• If a slot was dropped while pg2osync was down, the next start detects the missing slot and re-backfills safely (idempotent writes).

MySQL / MariaDB source

Change capture through the binary log. dev/e2e-mysql-test.sh runs against MySQL 8.0 on every pull request, and against MySQL 8.4, MariaDB 10.6 and MariaDB 11.8 nightly (see compatibility): consistent initial load, live INSERT/UPDATE/DELETE streaming with real column names, resumable positions and crash recovery (dev/e2e-mysql-test.sh).

Requirements

-- row-based logging with full row images: MINIMAL and NOBLOB omit unchanged
-- columns, which silently loses data on update
SET GLOBAL binlog_format = ROW;
SET GLOBAL binlog_row_image = FULL;

-- the sync user needs to read the tables, the catalog and the binlog
CREATE USER 'pg2osync'@'%' IDENTIFIED WITH mysql_native_password BY '...';
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'pg2osync'@'%';

log_bin must be enabled. MySQL 8.0+ enables it by default; MariaDB does not — it needs log_bin in the configuration file (or --log-bin on the command line) and a restart. binlog_format/binlog_row_image are usually already ROW/FULL. Put the settings in my.cnf as well — SET GLOBAL does not survive a restart.

pg2osync setup-sql -c pg2osync.toml prints the whole script for your config — the my.cnf block, the user and the grants — so it can be handed to whoever holds the privileges. pg2osync validate then checks the settings, the connection and every configured table before you run anything.

Every synced table needs a primary key or is declared append_only. The key becomes the document _id, unless the table configures id to derive one from its columns or fan_out to turn an array column into one document per element; binlog_row_image = FULL already guarantees the before-images both need. An append_only table is filed under a hash of each row, and an UPDATE or DELETE on it halts the pipeline — see Append-only tables.

TLS

Every MySQL connection honours [source] sslmode, using the same five levels as the PostgreSQL source. MySQL's own vocabulary maps onto them directly:

[source]
flavor = "mysql"
url_env = "PG2OSYNC_SOURCE_URL"
sslmode = "verify-ca"
sslrootcert = "/etc/mysql/ca.pem"

MySQL's auto-generated certificates are issued to MySQL_Server_<version>_Auto_Generated_Server_Certificate, not to your hostname, so verify-full rejects them by design. Use verify-ca with the server's own ca.pem, or install certificates issued for the real hostname.

Client certificates

The option names are libpq's here too, so one spelling covers both sources:

[source]
sslcert = "/etc/mysql/client-cert.pem"
sslkey = "/etc/mysql/client-key.pem"

Both are required together, and the key must be unencrypted.

Authentication

caching_sha2_password (the MySQL 8 default) and mysql_native_password are both supported. Accounts created REQUIRE X509 or REQUIRE SUBJECT work once sslcert and sslkey are set; without them the server answers a plain access denied, which pg2osync annotates with that possibility.

The first authentication of an account needs full authentication, because the server has nothing cached yet. pg2osync handles both routes:

• On a TLS connection the password is sent as cleartext inside the encrypted session, which is what the server expects.
• On a plaintext connection it asks for the server's public key, XORs the password with the nonce and encrypts it, so the password is never recoverable from the wire even without TLS.

Later connections take the fast path while the account remains in the server's cache. FLUSH PRIVILEGES empties that cache and forces full authentication again — which is exactly how the full-auth paths above were tested.

Configuration

[source]
flavor = "mysql"
url_env = "PG2OSYNC_SOURCE_URL"     # mysql://user:pass@host:3306/appdb
server_id = 424242                  # unique across the server's replicas

[target]
url = "http://localhost:9200"

[sync.users]
table = "appdb.users"               # database.table
index = "users"

server_id must not collide with any other replica or CDC tool attached to the same server, and it is what the checkpoint is keyed on — changing it forces a full initial load.

The URL is also what the binlog dump connection uses, so it must reach the server directly: a query-routing proxy does not carry COM_BINLOG_DUMP — see Proxies and connection poolers.

How it works

1. Prerequisite check on a plain connection (log_bin, binlog_format, binlog_row_image, binlog_row_value_options), plus column and primary-key resolution from information_schema.
2. Initial load in primary-key chunks, each one statement, with the binlog coordinate read before the first chunk and the stream running from it — so anything a chunk missed or read stale is replayed onto an idempotent write. The load runs beside the stream, not before it. The table's where predicate is pushed into each chunk statement and evaluated again on every binlog row, so a non-matching row is never read and one that stops matching is deleted.
3. COM_BINLOG_DUMP from that coordinate on a second connection, after SET @master_binlog_checksum = @@global.binlog_checksum so CRC32-checksummed events are usable.
4. Event decoding: FORMAT_DESCRIPTION (checksum length), ROTATE (file changes), TABLE_MAP (column types), WRITE/UPDATE/DELETE_ROWS (row images), XID (commit boundaries), QUERY (DDL detection).

Column names come from information_schema over a second connection, because binlog row events identify columns only by ordinal — and the dump connection cannot run queries while streaming. When the server runs binlog_row_metadata = FULL, the names in TABLE_MAP are used directly.

Positions instead of slots

MySQL has no server-side position tracking. pg2osync stores (binlog file, position) in the checkpoint and resumes from it; a replay after a crash is harmless because writes are idempotent.

The engine orders positions as (file index << 32) | offset, so a rotation always compares greater than any offset in the previous file.

Consequence: the server must still hold the binlog you stopped at. Past binlog_expire_logs_seconds the position is gone and the next start runs a full initial load. Keep enough retention to cover your worst expected outage — MySQL's automatic purge does not spare files a consumer still needs.

The same token is each document's version at the target, which is what lets the initial load run beside the stream. A version only ever goes up, so a binlog history that restarts under a running pipeline — RESET BINARY LOGS AND GTIDS, or the same address answered by a different server — leaves the target holding versions from a numbering that no longer exists. pg2osync refuses to start in that case rather than writing into silence; the fix is a fresh index name, since a reload cannot undo versions already in the target.

MySQL vs MariaDB wire differences

Handled transparently:

CI runs MySQL 8.0 and 8.4, and MariaDB 10.6 and 11.8, over both dialects.

That last row is the one with teeth: a MariaDB group's final position is not known until the group is written, and not needing it per event is what lets the checksums be computed in advance. binlog_legacy_event_pos restores the old behaviour and is documented as costing binlog scalability, so pg2osync tracks the position itself instead — a stated position wins wherever one appears, a zero advances by the event size, and the two events that state a position without having moved the stream (the heartbeat, which is not even in the file, and the format description of a file resumed into the middle of) move nothing.

Type mapping

Both readers decide from the declared type rather than from what the wire carries, because neither wire format says enough. A binlog row image gives a string column no charset — char and binary share a type code, as do text and blob — and gives an enum an ordinal with its labels nowhere; the text protocol the initial load reads gives every value as bytes and nothing else. The shape is resolved from information_schema once and consulted by both.

An index built before this holds the older shapes for TEXT (base64 when it came from the stream), BIT, ENUM and SET, and for BINARY/VARBINARY a base64 of mangled text. There was no consistent value to preserve, so those columns are only correct after a rebuild.

Decimals stay strings on purpose: a float round-trip loses precision on money. A decimal inside a JSON document is the exception: MySQL renders it as a bare number in the JSON text the initial load reads, so the streamed value matches that rather than the column rule.

JSON columns

binlog_row_value_options = PARTIAL_JSON is refused at startup. It makes the server log a JSON update as a diff in an event type of its own, which is not decoded here; refusing says so rather than dropping those updates silently.

MySQL stores JSON in its own binary form, which the binlog carries verbatim. It is decoded here, so a row keeps the same shape whether it arrived through the initial load or through an update. Dates, times and decimals that JSON has no type for are rendered exactly as the initial load renders them; anything else opaque is base64.

Two things worth knowing before pointing this at a target:

• A document that cannot be decoded is stored as __mysql_json_hex:<hex> and logged. The bytes stay recoverable rather than being guessed at, and the log names the size so the row can be found.
• OpenSearch's dynamic mapping rejects some perfectly valid JSON — an array mixing scalars and objects, or an integer larger than a long. That is a target-side limit, not a decoding one, and it applies to the initial load just as much. Define the mapping yourself, or map the field as {"type": "object", "enabled": false} to store it without indexing.

MariaDB is unaffected: it stores JSON as LONGTEXT, which already arrives as text.

TRUNCATE and DROP

TRUNCATE is logged as a statement rather than as row events, so it is read out of the SQL and turned into an index clear, ordered against the writes queued before it — the same behaviour as the PostgreSQL source.

DROP TABLE is only warned about. Clearing the index would be presumptuous when the table may be about to be recreated, but a dropped table whose index still holds its documents is worth saying out loud.

DDL

An ALTER or RENAME in the binlog invalidates the cached schema, so the next row event resolves column names from the catalog again. What that re-resolution changed is compared against the shape the pipeline had been decoding rows with, logged as changed shape: added/removed/retyped … and counted as pg2osync_schema_drift_total{table} — the same report PostgreSQL makes. The change itself is never applied: documents written before it keep the old shape until the index is rebuilt.

If the binlog still reports a different column count than the catalog, the schema is resolved once more from information_schema — MySQL DDL is not transactional, so a row event under a new shape means the statement has already committed and a fresh read sees that shape. Only if the two still disagree does the process stop with a clear error rather than write shifted values. Restart it to resynchronize.

Column renames and drops need a re-index: existing documents keep the old field names. A fields entry in the config can absorb a source-side rename (new_column = "old_field") so the index keeps its field name without one.

Nested children

[[sync.x.children]] works here as it does for PostgreSQL: the parent document embeds the collection as an array, refreshed whenever the parent or any of its children changes. Child tables are added to the streamed set automatically, and their row events resolve to a parent instead of becoming documents of their own.

Two things are easier here than on PostgreSQL:

• A deleted child is always locatable. binlog_row_image = FULL is already a requirement, so a delete carries its whole before-image and the foreign key is always present. PostgreSQL needs REPLICA IDENTITY FULL on the child for the same guarantee and warns when it is missing.
• There is no TOAST equivalent, so no value ever arrives as a marker that has to be completed from the target.

The array is built from ordinary rows rather than by JSON_ARRAYAGG(JSON_OBJECT(…)), which would not agree with the rest of the pipeline. JSON_OBJECT renders a varbinary as base64:type15:… on MySQL and as raw escaped bytes on MariaDB, a set as "a,b" rather than an array, a decimal as a number rather than a precision-preserving string, and a bit as invalid JSON on MariaDB — its own JSON_VALID says so. Reading the rows and converting them with the same code that builds a parent document means a value inside an array is the same JSON as the same value on its own. The cost is unchanged: one query per collection per batch, with the server still doing the ordering, the cap and the count.

Surviving a failover

A binlog file name and offset only mean anything on the server they were read from, so a checkpoint also records which transactions have been consumed:

mysql-bin.000003:2278;gtid=3c63db20-a0cd-11f1-bc85-32cf1c33a72f:1-14

With that, pointing the pipeline at a promoted replica resumes rather than reloading. Two things make it work, and both need the server's cooperation:

• GTIDs have to be on. MySQL needs gtid_mode = ON; ON_PERMISSIVE is not enough, because a transaction may then be written with no GTID at all and a position built from the stream would silently omit it. MariaDB always has them, and needs nothing.
• The replica has to write its own binlog — log_replica_updates = ON — or there is nothing to stream from it once it is promoted.

The new primary's coordinates are a different, usually lower, numbering than the one the target already holds versions from. Rather than refuse to continue, pg2osync opens a new generation: the version becomes base + coordinate with a base past everything already written, and the log says so — versioning documents from a new generation at …. Documents written after the promotion therefore outrank what came before them, which is what stops a failover from leaving the index quietly stale.

dev/failover-probe.sh builds a primary and a replica, promotes the replica and asserts both halves: that the stream resumes without an initial load, and that a row only the new primary ever had actually lands in the index.

If the GTID position has been purged from the new primary's binlogs, the server refuses the request rather than starting somewhere else, and the pipeline stops with what the server said. A fresh initial load is then the only honest repair.

Without GTIDs, a checkpoint still resumes exactly — but only against the server it was written on. A coordinate behind the checkpoint stops the pipeline instead of reloading into silence, because the target's versions come from a numbering that no longer exists.

Known limitations

Verifying your setup

docker run -d --name mysql-test -p 13306:3306 \
  -e MYSQL_ROOT_PASSWORD=secret -e MYSQL_DATABASE=appdb mysql:8.0

MYSQL_CONTAINER=mysql-test MYSQL_PORT=13306 \
  MYSQL_ROOT_PASSWORD=secret ./dev/e2e-mysql-test.sh

The suite covers the snapshot, live CRUD, decimal fidelity, projections, transforms, the checkpoint format and crash recovery. For MariaDB add MYSQL_CLIENT=mariadb.

The GTID section needs a server that has them. MySQL's image starts with them off, so a run that exercises it wants the flags:

docker run -d --name mysql-gtid -p 13308:3306 \
  -e MYSQL_ROOT_PASSWORD=secret -e MYSQL_DATABASE=sourcedb mysql:8.0 \
  --log-bin=mysql-bin --binlog-format=ROW --binlog-row-image=FULL \
  --server-id=77 --gtid-mode=ON --enforce-gtid-consistency=ON

Against a server without them the section says it skipped and why, rather than passing without having tested anything. MariaDB needs none of this.

OpenSearch sink (default)

The primary, most battle-tested target. The full dev/e2e-test.sh suite runs against OpenSearch 2.19.6 on every pull request (see compatibility).

[target]
url = "http://localhost:9200"
# username = "admin"                # basic auth
# password_env = "OS_PASSWORD"
# tls_verify = true                 # set false only for self-signed dev certs

Behavior

• Writes batches via _bulk with index/delete operations; document _id is the row's primary key → idempotent replay-safe writes.
• The checkpoint is one document per stream in a hidden .pg2osync_meta index, named <source>-<slot_name> or <source>-<server_id>. Deleting it forces a full initial load on the next start, which is safe but expensive.
• TRUNCATE runs as _delete_by_query with a refresh first, so a write that has not been refreshed yet cannot survive the truncate.
• Unmodified TOASTed columns are completed before the write — from the old tuple under REPLICA IDENTITY FULL, otherwise by reading the current document back — so a document is never written with a hole in it.
• Transient failures (429, 5xx, connection resets) are retried with exponential backoff per [engine] retry_max and retry_backoff_ms. A permanent rejection stops the pipeline instead of skipping the document.

Amazon OpenSearch Serverless

Not supported, deliberately. A provisioned OpenSearch domain works — including the AWS-managed kind — but a Serverless collection does not, and a url ending in .aoss.amazonaws.com is refused at startup rather than left to answer 403 to everything.

Three things would have to change, and each is a real piece of work:

• SigV4 is the only authentication a collection accepts. There is no basic-auth path, so pg2osync would have to sign every request with service name aoss — an AWS credential chain and a signing implementation, for one target.
• A custom document id works only on a search collection. Every document here carries its row's primary key as _id, because that is what makes a replay overwrite instead of duplicate. Time-series and vector collections reject it outright, so they could never work at all.
• The service owns refresh and index settings. Suspending refresh for an initial load, refreshing before a TRUNCATE, and /synced all depend on calls the service rejects. Each would need a documented degradation rather than a silent skip.

None of that is impossible; it simply has never been asked for, and carrying a flag that had never been run against the service was a support claim nobody could stand behind. If you need it, open an issue — with SigV4 done properly rather than a proxy the operator has to run, and verified against a real collection before the matrix says anything.

Index naming rules

Enforced at config load (fails fast):

• lowercase letters, digits, _, - only
• must start with a lowercase letter
• must not start with _ or . (reserved)

Two tables may map to the same index as a join pair, where every document carries its own routing (see Join fields), or once every section feeding it declares an id (see Sharing an index).

index may also carry {column} placeholders — events-{tenant}, see Per-row indices — and the rules above then apply to the rendered name: a row that renders an uppercase letter, an empty value or a NULL halts the pipeline. Two things are different on this side of a templated table:

• An index a row chooses is created on demand, when the first document for it is written, with the section's mapping_file if one is set — the same mapping a fixed index gets at startup, applied later because the name is not known until the row is.
• A TRUNCATE of a templated table clears the glob the template claims (events-* for events-{tenant}): one search over the pattern, then one versioned bulk delete per hit under the hit's own _index, so a row committed after the truncate survives it. That is why a template must have a literal part: {tenant} alone would claim *.

Health & monitoring

• pg2osync validate checks reachability and version before you commit to a run.
• Watch pg2osync_sink_errors_total and pg2osync_position_confirmed on /metrics. Errors with a stalled confirmed position mean the target is unhappy: disk full, a mapping conflict, or expired credentials.

Mappings

Indices are created with dynamic mapping if they do not exist. For anything beyond the defaults — analyzers, keyword subfields, explicit date formats — create the index (or an index template) yourself before running; pg2osync only creates what is missing and never modifies an existing mapping.

A document that conflicts with an existing mapping is a permanent rejection and stops the pipeline, by design.

A join field is compared like any other field at startup, relations included: an index whose relation names disagree with the mapping_file is reported as a conflict, because a wrong name produces documents no has_child query can find rather than documents the target refuses. Dynamic mapping cannot invent a join field, so an index a join pair writes to has to be created by pg2osync from the parent's mapping_file, or by an index template that declares the join, before the first document lands.

Retention

Deleting old indices is the cluster's job, not pg2osync's: an ISM policy whose ism_template matches the index names attaches itself to every index created after it, a {column} template's indices included, with nothing to configure here. See Retention.

Ingest pipelines

Semantic and hybrid search on OpenSearch go through the neural-search plugin: a text_embedding processor in an ingest pipeline reads a text field and writes the vector into a knn_vector field the mapping declares. pg2osync computes no embedding of its own; a section names the pipeline (pipeline = "embed-products") and every document it writes carries that name on its bulk action, so the target runs the pipeline on the way in. validate refuses a pipeline the target does not have. See Ingest pipelines for the mapping the pipeline fills and what follows from the pipeline being per section.

Elasticsearch sink

Same pipeline, Elasticsearch REST dialect. Select with:

[target]
flavor = "elasticsearch"
url = "http://localhost:9200"
username = "elastic"
password_env = "ES_PASSWORD"
# api_key_env = "ES_API_KEY"       # alternative to user/password

Behavior

Identical contract to the OpenSearch sink:

• _bulk writes keyed by primary key (at-least-once and idempotent).
• The checkpoint is one document per stream in a hidden .pg2osync_meta index, in the same format the OpenSearch sink writes.
• TRUNCATE runs as _delete_by_query?refresh=true&conflicts=proceed, after an explicit refresh so unrefreshed writes cannot outlive it.
• Retries follow [engine] retry_max and retry_backoff_ms.
• reconcile walks an index with search_after in primary-key order, keeping each hit's _routing so a stray join child is deleted from the shard it lives on.
• switch-alias reads the alias's current holders and swaps them in a single _aliases request, so the alias never resolves to nothing mid-swap.

Differences from OpenSearch are limited to REST dialect details (error response shapes, refresh semantics) that the sink abstracts away — config and operational behavior are the same.

Retention

Deleting old indices is the cluster's job, not pg2osync's: name an ILM policy in mapping_file's settings block as index.lifecycle.name and every index created from that mapping — including one a {column} template renders on demand — is managed from creation. See Retention.

Version targeting

The full dev/e2e-test.sh suite runs nightly against Elasticsearch 8.19.20 with security disabled (see compatibility). The sink speaks raw REST (_bulk, _mget, _delete_by_query, _refresh, _doc) rather than using the official client, which keeps a second HTTP stack out of the binary.

7.x is untested. If you hit a bulk or mapping incompatibility there, open an issue — the fix is usually small.

Security notes

• Prefer api_key_env over basic auth for long-running deployments.
• tls_verify = false is for local development only; never in production.

Meilisearch sink

Meilisearch support for search use cases that don't need the full OpenSearch query DSL.

[target]
flavor = "meilisearch"
url = "http://localhost:7700"
# api_key_env = "MEILI_API_KEY"    # optional
state_dir = "/var/lib/pg2osync"    # checkpoint fallback directory

Key difference: file-based checkpoints

Meilisearch has no arbitrary document storage, so the hidden .pg2osync_meta checkpoint index can't live there. pg2osync persists its checkpoint to a local JSON file in state_dir instead.

Operational consequences:

• The checkpoint is tied to one machine. Don't run two pg2osync instances against the same Meilisearch target with a shared state dir.
• Persist state_dir (a volume mount in Docker, persistence.enabled in the Helm chart). Losing it triggers a full re-sync, which is safe but expensive.
• The file is written to a temporary name and renamed into place, so a crash mid-write cannot leave a truncated checkpoint that silently restarts the pipeline from zero.
• Deletes still propagate — pg2osync issues explicit delete calls; nothing depends on tombstones inside Meilisearch.

Behavior

• On startup pg2osync creates each configured index with id as its primary key, and leaves an index that already exists alone — so a restart, a resume or a second pipeline over the same index starts normally.
• Documents are upserted with the primary key as Meilisearch's document id.
• Writes are asynchronous server-side tasks; the sink waits for each task to complete before acknowledging, so the checkpoint only advances after durable acceptance.
• TRUNCATE deletes all documents of the index and waits for that task too.
• Meilisearch has no mappings; searchable and filterable attributes are yours to configure on the index.

Rebuilding an index

There are no mappings here, so a rebuild is never about a field type. What it is for is an index settings change that only applies to documents indexed after it, or a decoding bug whose wrong values are already written — cases where the documents have to be built again while the live name keeps answering.

pg2osync reindex -c pg2osync.toml --table public.users --alias users

• --alias must be the index the section already writes to. There is no alias namespace on this target: the name readers use is an index uid, so there is nothing to point somewhere else. Any other value is refused rather than filling an index nothing reads.
• The switch is POST /swap-indexes, which exchanges the contents of two uids in a single task — atomic in the same sense an alias move is, and the reason a rebuild is possible here at all.
• Afterwards <index>-<unix seconds> holds the previous documents, not the new ones: the swap runs both ways. That index is the rollback — swap it back to undo the rebuild — and --drop-old deletes it instead.
• No config edit follows, unlike the other targets: the section's index never changed, so the only step left is starting the pipeline again.
• Stop the pipeline first; the command refuses to run beside it. The checkpoint file in state_dir does not move, so the restart replays everything committed since the rebuild started, which is what proves the contents — the count only proves how many.
• The checkpoint lives outside the uid namespace entirely, so no swap can touch it.

Version targeting

dev/e2e-meili-smoke.sh runs nightly against Meilisearch v1.53.1 (see compatibility). It is a smoke suite rather than the full dev/e2e-test.sh, because that suite asserts over mappings, join fields and per-row indices — the three things this target does not have. What it does cover is the initial load, live INSERT/UPDATE/DELETE, the file checkpoint resuming after a restart, and a rebuild swapping a fresh index into the live name.

Compatibility

Which versions CI actually runs, as opposed to which ones are expected to work. "Not tested" is not "known broken" — it is a version no job exercises, so a regression there would ship unnoticed.

What the nightly suite runs

.github/workflows/compat.yml builds the release binary once and hands it to every cell. Three scripts do the work:

• dev/e2e-test.sh — the full PostgreSQL suite. TARGET_FLAVOR picks OpenSearch or Elasticsearch; everything else is identical, because the two differ only in REST dialect details the sink hides.
• dev/e2e-mysql-test.sh — the full MySQL/MariaDB suite. MYSQL_CLIENT picks the client binary the container ships.
• dev/e2e-meili-smoke.sh — Meilisearch. Not the full suite: that one asserts over mappings, join fields and per-row indices, none of which Meilisearch has. The smoke suite covers the initial load, live INSERT/UPDATE/DELETE, the file-based checkpoint resuming after a restart, and a reindex swapping a rebuilt index into the live name.

Two cells are marked advisory, because the first nightly matrix found a bug in each of them. The Elasticsearch suite reaches reconcile, which that sink cannot run (#118), so everything after that section is unverified there. The Meilisearch smoke suite reaches the restart, which fails because that sink cannot start twice against one index (#122). Both cells are kept red rather than trimmed: the gap is the finding.

The matrix also runs on a pull request that touches the workflow or those scripts, so a change to the matrix is tested before the night it would break.

./dev/ci-local.sh runs the same six cells on your machine, and runs them automatically for exactly the changes a pull request would; --matrix forces them. Each cell is a throwaway container on a port of its own — PostgreSQL 15433, OpenSearch 9201, Elasticsearch 9202, Meilisearch 7701, MySQL/MariaDB 13307 — so the dev stack on 15432/9200/13306 keeps running beside it, and the containers are removed however the cell ends.

When a nightly run fails

The workflow opens one issue labelled nightly-compat and comments the run URL and the failed cells on it every night it stays red, rather than opening a new issue each time. Fix the cell or, if the version genuinely is not supported, say so here and in the README — a claim no job checks is the thing this page exists to prevent.

Proxies and connection poolers

pg2osync opens two kinds of connection to the source. Which of them a proxy can carry follows from the wire protocols, not from a test: nothing here has been run against a proxy, and CI does not run one. A "yes" below means the protocol does not forbid it.

The stream connection must be direct

After START_REPLICATION the server answers with CopyBothResponse and streams WAL until the client ends COPY mode (streaming replication protocol); COM_BINLOG_DUMP requests "a Binlog Network Stream" that runs until the connection closes (MySQL internals). Neither is a query with a result set; both need one backend for the life of the connection.

• Transaction and statement pooling cannot carry it. A backend "is assigned to a client only during a transaction" (PgBouncer features), and the stream is not one. RDS Proxy multiplexes "after each transaction" (concepts) and says so outright: "RDS Proxy currently doesn't support streaming replication mode" (PostgreSQL limitations). Its MySQL limitations say nothing about the binlog dump; treat it the same way.
• Query-parsing routers do not know the command. Pgpool-II "does not recognize replication protocol"; its maintainer's advice for pg_basebackup, connect to PostgreSQL directly, applies here too (pgpool-general, April 2016). ProxySQL's query layer rejects binlog clients; the maintainer's answer is mysql_users.fast_forward=1 for that user alone (issue #3580), which "bypasses the query processing layer (rewriting, caching) and passes through the query directly to the backend server" (mysql_users).
• PgBouncer 1.23.0 and later proxies replication connections (changelog, 2024-07-03), bypassing pooling whatever pool_mode says: client and server connection "form a strong pair, as soon as one is closed the other is closed too", and are never cached (PR #876). Earlier versions reject them.
• TCP pass-throughs carry it because they carry anything: HAProxy in mode tcp, where "no layer 7 examination will be performed" (manual), and MySQL Router's connection routing, where "MySQL packets are routed in their entirety without inspection" (docs), with connection_sharing left at its default 0 (options). A TCP connection cannot move between backends, so the stream is pinned for its life; when the proxy or its backend changes, the connection drops and pg2osync reconnects.

The SQL connection may be pooled, but must reach the primary

It is ordinary SQL, so a pooler can carry it: in session mode without conditions, in transaction mode only where the pooler handles the named prepared statements the PostgreSQL driver issues — PgBouncer 1.21 and later with max_prepared_statements (config). It also has to land on the server the stream reads, and that is the primary: the initial load versions every range with the position it reads here, and pg_current_wal_lsn() "cannot be executed during recovery" (backup control functions), while SHOW BINARY LOG STATUS on a replica names a binlog the stream never uses; a child re-fetch reads the parent row right after its change arrived on the stream, and on a lagging replica the row is old or missing; and the publication and slot are created here for the stream to open.

So anything whose purpose is to send reads elsewhere — Pgpool-II load balancing (load_balance_mode), MySQL Router's read-only or read/write-splitting ports, a reader endpoint — must stay out of this connection's path. An RDS Proxy default endpoint is fine on that count: a proxy "can associate only with the writer DB instance, not a read replica" (limitations).

Summary

What pg2osync does not do

Nothing proxy-specific: it does not detect a proxy, set pooler parameters, or pin anything itself, and no test runs against one — dev/e2e-test.sh and dev/failover-probe.sh dial the database directly. A proxy restart is a dropped stream like any other: the pipeline is rebuilt from the last checkpoint with backoff, up to [source] reconnect_max attempts (operations); the slot on PostgreSQL, or the GTID in the checkpoint on MySQL, keeps the position, so it is a reconnect, not a loss. A reconnect that lands on a different server is a failover, covered in surviving a failover.

Deployment

pg2osync is a single process with no local state (except the Meilisearch checkpoint file). Run one instance per replication slot.

One instance per slot. Two processes streaming the same PostgreSQL slot fight over its position and undo each other's progress. To scale, split tables across instances, each with its own slot_name and publication.

Container image

docker run --rm \
  -e PG2OSYNC_SOURCE_URL="postgres://user:pass@db:5432/appdb" \
  -e PG2OSYNC_TARGET_PASSWORD="…" \
  -v "$PWD/pg2osync.toml:/etc/pg2osync/pg2osync.toml:ro" \
  -p 9100:9100 \
  ghcr.io/kennywillbe/pg2osync:1.3.0

The image runs as UID 10001 with a read-only root filesystem and no capabilities. The default command is run -c /etc/pg2osync/pg2osync.toml; override it to use another subcommand:

docker run --rm … ghcr.io/kennywillbe/pg2osync:1.3.0 \
  validate -c /etc/pg2osync/pg2osync.toml

Build it yourself with docker build -t pg2osync:local ..

A compose example lives in deploy/docker-compose.yml.

Kubernetes with Helm

The chart lives in deploy/helm/pg2osync.

helm install pg2osync deploy/helm/pg2osync \
  --namespace pg2osync --create-namespace \
  -f my-values.yaml

A minimal my-values.yaml:

config:
  source:
    url_env: PG2OSYNC_SOURCE_URL
    slot_name: pg2osync
    publication: pg2osync_pub
  target:
    url: http://opensearch.search.svc:9200
    username: pg2osync
    password_env: PG2OSYNC_TARGET_PASSWORD
  metrics:
    bind: 0.0.0.0:9100          # 127.0.0.1 is unreachable for probes
  sync:
    users:
      table: public.users
      index: users
      exclude_columns: ["password_hash"]

# one JSON object per log line, for the cluster's log collector
logFormat: json

# Production: create this Secret with External Secrets / Vault / SOPS and
# reference it instead of putting credentials in values.
existingSecret: pg2osync-credentials

Key values:

config.sync is intentionally empty in the chart defaults: Helm merges maps, so a default table would survive your override and sync a table you never asked for.

The chart hashes the rendered config into a pod annotation, so helm upgrade restarts the pod when the configuration changes.

Repeated TOML tables (nested children) cannot be expressed in the values tree; put them in extraConfig:

extraConfig: |
  [[sync.customers.children]]
  table = "public.orders"
  field = "orders"
  foreign_key = "customer_id"

Verify before installing:

helm lint deploy/helm/pg2osync
helm template pg2osync deploy/helm/pg2osync -f my-values.yaml

Kubernetes without Helm

Plain manifests with a Kustomization are in deploy/kubernetes:

kubectl apply -k deploy/kubernetes

What they set up:

Before applying either variant:

1. Replace the credentials in secret.yaml, or delete the file and have External Secrets, Vault Agent or SOPS create a Secret named pg2osync-credentials with the same keys.
2. Point [target] url in the ConfigMap at your search cluster and set the [sync.*] sections for your tables.
3. Set [metrics] bind = "0.0.0.0:9100" — the default 127.0.0.1 is not reachable by kubelet probes or Prometheus.
4. Pin the image tag to a release, not latest, so a restart cannot silently change versions.

Probes

replicas: 1 plus strategy: Recreate prevents two instances from briefly overlapping during a rollout.

The startup probe allows up to ten minutes because the initial load of a large table happens before the pipeline reaches its steady state. Raise failureThreshold if your initial load takes longer — a liveness probe alone would restart the pod mid-load, forever.

Verifying a rollout

kubectl -n pg2osync logs deploy/pg2osync -f
kubectl -n pg2osync port-forward svc/pg2osync-metrics 9100:9100
curl -s localhost:9100/metrics | grep pg2osync_position

pg2osync_position_lag staying near zero means the pipeline is keeping up. A lag that grows steadily means the sink cannot absorb the write rate, and PostgreSQL will retain WAL until it catches up.

systemd

[Unit]
Description=pg2osync
After=network-online.target
Wants=network-online.target

[Service]
Type=simple
User=pg2osync
# credentials live in a root-owned 0600 file, not in the unit
EnvironmentFile=/etc/pg2osync/env
ExecStart=/usr/local/bin/pg2osync run -c /etc/pg2osync/pg2osync.toml
Restart=always
RestartSec=5
NoNewPrivileges=true
ProtectSystem=strict
ProtectHome=true
PrivateTmp=true
# only needed for the Meilisearch state directory
ReadWritePaths=/var/lib/pg2osync

[Install]
WantedBy=multi-user.target

Upgrades and rollbacks

The checkpoint format is forward-compatible: a newer version reads checkpoints written by an older one. Rolling back across a format change is not supported — an older binary that cannot parse the checkpoint ignores it and starts a full initial load, which is safe but expensive.

A stop — SIGTERM from docker stop or Kubernetes, SIGINT from a terminal — finishes the requests already sent to the target and writes a final checkpoint before exiting, which takes well under a second normally and at most one target request timeout (30 s) when the target has stopped answering; the Kubernetes default terminationGracePeriodSeconds of 30 s covers that, docker stop's default of 10 s does not, so pass -t 30 if the target may be unhealthy.

Stopping for a while is safe as long as the source retains its history: PostgreSQL keeps WAL for an inactive slot (watch disk usage with pg2osync status), and MySQL keeps binlogs for binlog_expire_logs_seconds. Past that window the position is gone and the next start does a full initial load.

Operational checklist

• One instance per slot, and the slot name is unique per environment
• Credentials come from the environment, not from the config file
• [metrics] bind reachable by your scraper, and lag is alerted on
• Disk alert on the source: an inactive slot retains WAL indefinitely
• pg2osync drop-slot runs when an instance is decommissioned for good

Operations

Metrics

GET http://<bind>/metrics returns Prometheus text exposition. Defaults to 127.0.0.1:9100; set [metrics] bind = "0.0.0.0:9100" in a container.

Moving off loopback is what containers and Kubernetes need, and it is also the moment the endpoint becomes reachable by anything that can route to the pod. The exposition holds no credentials and no row data, but it does name every table being synced and how far behind the pipeline is. Two ways to close that, and they compose:

• [metrics] token_env = "PG2OSYNC_METRICS_TOKEN" requires a bearer token on /metrics. The process warns at startup when it is bound off loopback without one.
• A NetworkPolicy that admits only the Prometheus namespace to port 9100. This is the baseline Kubernetes pattern and is worth having either way.

Probes use /healthz, which is never authenticated.

Dashboard

deploy/grafana/pg2osync.json is a Grafana dashboard built from the series above: Dashboards → New → Import, paste the file, pick a Prometheus data source. It asks for nothing else — the data source and the instance are dashboard variables.

Five rows, in the order an incident is read: overview (connected, lag, the slot's wal_status, retained WAL against its safe size, reconnects), throughput, latency quantiles, target rejections, and source health. The thresholds are the alert values below, so a red tile and a firing alert mean the same thing.

The Helm chart can ship it as a ConfigMap for the Grafana sidecar to pick up:

helm upgrade pg2osync deploy/helm/pg2osync --set grafanaDashboard.enabled=true

What to alert on

# nothing has been checkpointed for five minutes while events keep arriving
increase(pg2osync_events_total[5m]) > 0
  and increase(pg2osync_position_confirmed[5m]) == 0

# the pipeline is falling behind instead of catching up
deriv(pg2osync_position_lag[10m]) > 0

# a permanent rejection stops the pipeline
increase(pg2osync_sink_errors_total[5m]) > 0

# a table changed shape: nothing broke, but the index now holds documents in
# the old shape and only a rebuild closes that
increase(pg2osync_schema_drift_total[1h]) > 0

# a slot is pinning WAL and growing: this is what fills the source's disk
deriv(pg2osync_slot_retained_bytes[30m]) > 0 and pg2osync_slot_retained_bytes > 5e9

# the source has already given up on a slot, so resuming it means a full reload
pg2osync_slot_wal_status{status="lost"} == 1

# nothing is reading a slot that exists
pg2osync_slot_active == 0

# the process is gone
up{job="pg2osync"} == 0

# the source has been disconnected rather than briefly interrupted
pg2osync_source_connected == 0

# it keeps losing the connection instead of settling
increase(pg2osync_reconnects_total[15m]) > 5

Alert on source disk too. An unconsumed PostgreSQL replication slot retains WAL indefinitely; that fills the database's disk long before it inconveniences pg2osync.

Set max_slot_wal_keep_size before you need it:

ALTER SYSTEM SET max_slot_wal_keep_size = '10GB';
SELECT pg_reload_conf();

It is the one setting that turns a full disk into a recoverable incident. Past the limit PostgreSQL invalidates the slot instead of retaining more WAL, and pg2osync then falls back to a full initial load — expensive, but the database stays up. PostgreSQL 13+.

The initial load watches the same signal. While the slot is past its budget (wal_status anything but reserved) the load pauses and lets the change stream have the throughput, logging pausing the load: slot … is at wal_status = …. A load that takes noticeably longer than expected with that line in the log is telling you the target cannot absorb the copy and the stream at once: give the target more capacity, or accept the slower load. If the slot is invalidated anyway the load fails with wal_status = lost and says what to raise, rather than continuing into a gap.

Day-to-day commands

pg2osync init --table users          # write a starter config, checked against the source
pg2osync setup-sql -c pg2osync.toml  # the SQL a DBA needs, from your config
pg2osync reconcile -c pg2osync.toml  # find index documents whose row is gone
pg2osync validate -c pg2osync.toml   # config, connectivity, server settings
pg2osync status   -c pg2osync.toml   # checkpoint vs the source's position
pg2osync status   -c pg2osync.toml --max-retained-mb 10240   # exit 1 over 10 GB
pg2osync bootstrap -c pg2osync.toml  # create slot/publication/indices, then exit
pg2osync switch-alias -c pg2osync.toml --alias users   # point an alias here
pg2osync drop-slot -c pg2osync.toml  # teardown; --publication drops that too

status output:

checkpoint: source=postgres stream=pg2osync position=0/C174158
slot pg2osync (configured): active=true retained_wal=4 kB
slot pg2osync_old: active=false retained_wal=3 GB

1 inactive slot(s) not named in this config: pg2osync_old
each holds WAL until it is dropped. If one is a former slot_name of this
pipeline: SELECT pg_drop_replication_slot('pg2osync_old');

retained_wal growing over hours means the target is not keeping up, or the process is not running while the slot still exists. A slot the server has given up on says so — wal_status=lost — and cannot be resumed from at all: a pipeline using it starts with a full initial load.

The metrics above cover this while the pipeline runs. While it does not — which is the dangerous case, since nobody is reading logs for a process that is not there — --max-retained-mb makes the same check something a scheduler can run:

pg2osync status -c pg2osync.toml --max-retained-mb 10240   # exits 1 over 10 GB

It looks at every slot on the server, not only the configured one, because an orphan fills the same disk.

Every logical slot on the server is listed, not only the configured one. Changing slot_name leaves the old slot behind, still pinning WAL with nothing reading it, and that orphan is invisible to anyone who only asks about the name in the config. drop-slot only ever touches the configured slot, so an orphan is dropped with the SQL above — deliberately, since a slot may belong to another consumer.

Reconciling an index against its source

pg2osync reconcile -c pg2osync.toml            # report only
pg2osync reconcile -c pg2osync.toml --delete   # and remove them

Each index is paged in primary-key order and each page of keys is checked against the table. A document whose row is gone is named; with --delete it is removed. Only keys move between the two sides, which is what makes this far cheaper than a reindex.

Reporting is the default because a wrong primary_key would otherwise empty an index, and that is not a recoverable mistake.

Run it when the pipeline is caught up. A row inserted seconds ago and not yet indexed looks exactly like an orphan, so a reconcile mid-load will name rows that are simply on their way.

It is the tool for three situations: poll mode, which cannot see a hard delete at all; after an incident where the index and the database might have diverged; and answering "are these two actually in step" at all, which nothing else here does. PostgreSQL sources only for now.

Logging

RUST_LOG=pg2osync=info    # default
RUST_LOG=pg2osync=debug   # per-event decisions
RUST_LOG=pg2osync::sink=debug,pg2osync=info   # narrow to one component

Targets: pg2osync::source, ::engine, ::sink, ::checkpoint, ::backfill, ::catalog, ::config, ::metrics, ::run. Credentials are never logged.

PG2OSYNC_LOG_FORMAT=json writes one JSON object per line instead of the default text, with the timestamp, level, target and the event's own fields as top-level keys — what Loki, Datadog and CloudWatch parse without a regex. Any other value is refused at startup.

PG2OSYNC_LOG_FORMAT=json pg2osync run -c pg2osync.toml
{"timestamp":"2026-08-30T09:12:44.318271Z","level":"INFO","message":"streaming from 0/1A2B3C8","target":"pg2osync::run"}

Failure modes

A permanent rejection stops the pipeline on purpose: skipping the document would be silent data loss, and every batch after it would widen the divergence. It stops by making no progress rather than by exiting — the attempt fails and is retried like any other, so the position never passes the document and fixing the mapping lets the next attempt through without a restart.

Carrying on past one refused document

One malformed row otherwise stops replication for every table. With

[engine]
on_permanent_rejection = "quarantine"
max_rejects = 100

the refused document is recorded in a hidden .pg2osync_rejects index — with its position and the write itself — and the pipeline continues. pg2osync_rejected_total moving is the signal to alert on: it means data is in the quarantine store and not in the index.

pg2osync rejects -c pg2osync.toml            # what was refused, where, and why
pg2osync rejects -c pg2osync.toml --replay   # after fixing the mapping

A replay submits each document again with its original position as its version, so a row the source has changed since loses to the newer value, and a record is cleared only once the target has accepted it. Anything still refused is reported and left in place.

Two things this deliberately does not do. It does not acknowledge a position before the document behind it is either written or recorded — if the quarantine store cannot be written, the pipeline halts and the source replays the batch. And it does not carry on for ever: past max_rejects it halts anyway, because that many refusals is a mapping problem rather than a bad row. Nothing is lost when it does — whatever is not recorded is also not acknowledged, so the source sends it again once you have fixed the mapping.

What retries and what does not

A broken stream — a dropped connection, a failover, a terminated backend — is retried in process. The pipeline is rebuilt from the last checkpoint each time, with exponential backoff capped at 30 seconds, and the attempt counter resets once a connection has lasted longer than that cap. After [source] reconnect_max consecutive failures (10 by default, roughly five minutes) the process exits and hands over.

A MySQL failover is the case where reconnecting is not enough by itself: the address may now point at a server whose binlog file names and offsets mean nothing here. With GTIDs on, the checkpoint carries a position that any member of the topology can honour, and the pipeline resumes rather than reloading — see surviving a failover for what the server needs and what the log says when it happens.

Configuration and privilege problems are not retried: wal_level, publication drift, a missing table, insufficient privileges and target setup all fail at startup and stay fatal. Retrying those is a crash loop wearing a costume.

A clean shutdown finishes the requests already sent to the target, writes a final checkpoint at the last acknowledged position, and exits; the next start resumes there rather than replaying an interval's worth of work.

A supervisor is still worth having — it just no longer has to catch every network blip.

Recovery

Process crashed. Start it again. It resumes from the last checkpoint and replays at most checkpoint_interval_ms worth of work. Replays overwrite documents by primary key, so they are invisible in the result.

Target lost its data. Delete the checkpoint and restart to re-index from scratch:

# the document is named after the stream: <source>-<slot_name|server_id>
curl -XDELETE "$OS/.pg2osync_meta/_doc/postgres-pg2osync"   # OpenSearch / Elasticsearch
rm .pg2osync-state/checkpoint-postgres-pg2osync.json        # Meilisearch

Source history expired (WAL recycled, binlog purged). The next start detects the unusable position and runs a full initial load. Nothing to do, but expect the load time.

Suspected divergence. Compare counts, then re-index if they disagree:

psql -c "SELECT count(*) FROM public.users"
curl -s "$OS/users/_count"

Counts can differ transiently while a batch is in flight; check twice before concluding anything.

One table is wrong. Read it again, without reloading everything else:

pg2osync resnapshot -c pg2osync.toml --table public.users
pg2osync resnapshot -c pg2osync.toml --table public.users --where "tenant_id = 42"

Safe to run while the pipeline is streaming, and it never moves the checkpoint — its rows carry the position they were read at, so a change committed after that wins as it always does. Use it after a mapping change, or after fixing something that wrote a wrong value.

Three things it does not do:

• It does not delete. A row gone from the source keeps its document; deciding that is reconcile's job, and keeping the two apart keeps each explainable.
• It does not resume. An interruption means running it again, which costs the read and nothing else. Recording progress would leave bookkeeping the next pipeline start would read as an unfinished initial load.
• It does not overwrite a document whose version is above the position it read at. That is the same rule that lets it run beside the stream. Documents the pipeline wrote are always at or below the current position, so this only bites if you have edited the index by hand: a plain PUT or DELETE through the target's own API uses internal versioning, which leaves a version one past the source's current position, and the re-snapshot declines it until the source moves on. One write to the table is enough — or reach for the zero-downtime re-index below, which builds a fresh index and has nothing to argue with.

Zero-downtime re-index

The index name is configuration, so a re-index is a second instance. This sequence has been rehearsed end to end against the dev stack with a reader polling the alias throughout:

There are two ways in. A second instance, which is what the rehearsal below covers, or — when the mapping is the only thing changing and a gap in freshness is acceptable — a re-snapshot into the new index name, which needs no second slot:

# point a copy of the config at users_v2, then fill it from the source
pg2osync resnapshot -c users-v2.toml --table public.users
pg2osync switch-alias -c users-v2.toml --alias users

That leaves the new index static from the moment the re-snapshot finished, so it suits a one-off rebuild rather than a live cutover.

Or, when the pipeline can be stopped for the length of a load, the same rebuild as one command:

pg2osync reindex -c pg2osync.toml --table public.users --alias users

It builds users-<unix seconds> from the section's mapping_file, checks what it wrote against the source's row count, and moves the alias in one atomic request. It refuses to run while the stream is live, and that refusal is the point: the fresh index is one the stream is not writing to, so a row that changed during the load would have nothing there to lose to. The checkpoint never moves, so setting index to the new name and starting the pipeline again replays everything committed since. The old index is kept unless --drop-old says otherwise — it is the rollback, one alias flip away.

For a live cutover with no freshness gap at all:

# 1. Copy the config. Change `index` to users_v2 and `slot_name` to a new
#    value. Keep the same publication: both instances read it.
# 2. Start it. It runs its own initial load, then streams.
pg2osync run -c users-v2.toml

# 3. Wait for it to catch up — an exit code rather than a metric to watch.
pg2osync status -c users-v2.toml --caught-up --timeout 300

# 4. Move the alias. This is one atomic request: a reader resolving it never
#    sees a moment where it points nowhere.
pg2osync switch-alias -c users-v2.toml --alias users

# 5. Stop the old instance, then drop its slot.
pg2osync drop-slot -c users-v1.toml

Four things the rehearsal turned up, all of which are now handled:

• The two instances must not share a checkpoint. They no longer do — each stream keeps its own document in the target. Before that they overwrote each other, and whichever restarted first found a checkpoint belonging to the other, rejected it, and re-ran a full initial load.
• drop-slot no longer drops the publication. Both instances read the same one, so dropping it with the old slot took it out from under the new pipeline. Pass --publication when you really are decommissioning.
• A publication dropped under a running slot is not repaired by recreating it. Logical decoding reads the catalog as it was at the position being replayed, and at that position the new publication does not exist. The recovery is to drop the slot and let the pipeline run a fresh initial load.
• Two slots mean two lots of retained WAL until the old one is dropped, so do step 5 promptly. pg2osync status lists every slot and what each retains.

Two instances must never share a slot — each needs its own slot_name (or server_id for MySQL).

Upgrades

Checkpoints are forward-compatible: a newer binary reads what an older one wrote. Rolling back across a format change makes the older binary ignore the checkpoint and re-run the initial load, which is safe but expensive.

Capacity notes

• Memory is bounded by the channel depths and batch_max_bytes; measured at ~90 MB resident while loading 200K docs, and lower at steady state.

• One instance is single-threaded in effect — the source, engine and sink tasks pipeline but do not shard. Scale by splitting tables across instances.

• Table count is not what makes you split. Measured, a table costs about 46 ms of fixed setup in the initial load and nothing per table afterwards: memory does not grow with the count, and a commit touching fifty tables propagates in 0.19 s. What makes you split is aggregate write volume — one instance is one stream and one write path, so the number to compare against is the sustained rate in the section above, not how many tables you have.

• The initial load's cost is dominated by the target's indexing throughput, not by pg2osync. dev/benchmark.sh reproduces the numbers in the README against a local stack.

• Give the pipeline one core. Measured with dev/resource-limits.sh, which runs it in its own container under a --cpus cap against a 500,000-row load:

  cap	throughput	peak memory
  0.25 cores	10,500 rows/s	9 MB
  0.5 cores	30,900 rows/s	8 MB
  1 core	51,900 rows/s	12 MB
  2 cores	54,500 rows/s	25 MB

  One core reaches the target's own ceiling; past that the extra cores wait alongside it. Below it the pipeline slows down and nothing else happens — at a quarter of a core it is five times slower on 9 MB of memory, because the bounded channels mean falling behind costs latency and retained WAL rather than growing memory. That is the number to put in a deployment: one core, two if it also serves /synced, and not on the database's cores — the 40% figure in database-impact is what co-location costs the database.

• That ceiling is what [engine] write_concurrency moves. One request open at a time is the default and is what the figures above are measured at; the source is not the constraint, since a single COPY hands over rows more than twenty times faster than the pipeline consumes them. On the dev stack a 2M-row load went 43,000 → 87,000 rows/s from one open request to four, with little left past that. Raise it against your own target and watch its indexing load, not ours: the setting multiplies concurrent requests to a cluster that may be serving queries at the same time.

• Sustained writes: dev/load-test.sh drives concurrent writers and reports where the pipeline stops keeping up, what happens while the target is unavailable, and whether a kill -9 at load loses anything. On an 8-core laptop against a single-node stack it keeps up with ~11,800 rows/s of single-row transactions and ~57,700 rows/s at a hundred rows per transaction. At the first figure the writers ran out of speed before the pipeline did, so it is a floor rather than a ceiling.

• Past that limit nothing grows without bound. The bounded channels held memory at 38 MB through a paused target, and the backlog accumulated as retained WAL on the source instead — which is the pressure max_slot_wal_keep_size caps.

What pg2osync costs your database

Every number here was measured with dev/db-impact.sh against dockerized PostgreSQL 17 on an Apple M2. Re-run it against your own instance before trusting any of it for capacity planning.

How it connects

pg2osync opens ordinary client connections plus one replication connection. It holds them for the life of the process; there is no pool and no reconnect storm.

Measured: 2 connections in steady state, 3 with nested children configured, 3 at the peak of the initial load. max_connections is not a concern; max_wal_senders and max_replication_slots must have room for one each per instance.

MySQL is the same shape: one connection for COM_BINLOG_DUMP and one for information_schema lookups.

All PostgreSQL connections share one TLS configuration ([source] sslmode), so a source cannot end up with an encrypted query connection and a plaintext replication stream.

Privileges

Two different things are needed, and they are usually held by different roles.

To run the pipeline (stream, initial load, index):

CREATE USER pg2osync WITH REPLICATION PASSWORD '…';
GRANT CONNECT ON DATABASE appdb TO pg2osync;
GRANT USAGE ON SCHEMA public TO pg2osync;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO pg2osync;

To create the publication and slot, PostgreSQL additionally requires:

• CREATE on the database, and
• ownership of every published table — a GRANT cannot substitute for it.

That is a deliberate PostgreSQL restriction, not something pg2osync can work around. Verified: with REPLICATION and SELECT but without ownership, CREATE PUBLICATION fails with must be owner of table users.

So on a database whose tables are owned by someone else, a privileged role creates the objects once:

CREATE PUBLICATION pg2osync_pub FOR TABLE public.users
  WITH (publish_via_partition_root = true);
SELECT pg_create_logical_replication_slot('pg2osync', 'pgoutput');

…and from then on the sync role only consumes them. Verified end to end: with the objects pre-created, a role holding just REPLICATION, CONNECT, USAGE and SELECT completes the initial load and replicates inserts, updates and deletes.

pg2osync validate reports exactly which of these you are missing and prints the statements to hand to a DBA. It no longer passes just because it could read the tables.

MySQL: SELECT, REPLICATION SLAVE, REPLICATION CLIENT, and a mysql_native_password user. Nothing has to be created server-side, so there is no ownership requirement.

Load while streaming

What a busy database pays: about 0.2 of a core, and no measurable throughput. Measured with dev/db-load-impact.sh: pgbench, 8 clients, 30 seconds a phase, against a database whose hot table is the one being replicated.

The second row is the one to quote, and the difference between it and the third is the point of measuring both. It is pg_recvlogical doing byte-for-byte the same decoding through the same publication with its output going to /dev/null — so it is what the database pays, and it is under half a percent. The walsender burned 5.7 s of CPU over 30 s, 0.19 of a core, to decode a workload of ~14,000 transactions a second.

The 40% below it is not replication's cost. It is one laptop running the database, the pipeline and OpenSearch on the same eight cores; the walsender's own CPU only rises from 0.19 to 0.32 cores between those rows, which is nowhere near 40% of anything. A deployment where the pipeline and the target are not on the database's cores does not pay it — and if yours is that deployment, the control row is your number.

Two things follow for capacity planning. Give the pipeline its own cores — one is enough, measured — and expect the database side of CDC to cost a fraction of a core per ~10,000 transactions a second, growing with write volume rather than with table size.

What a distant source or target costs is still unmeasured, and deliberately so rather than by omission: on a single machine the attempt reproducibly says that adding 50 ms of network delay makes the initial load faster, which is contention relieving itself and not a latency result. dev/resource-limits.sh carries the harness and the reasoning; the number needs hardware where the target is not already the bottleneck.

A table costs about 46 ms, whatever it holds. Measured with dev/many-tables.sh: the same 500,000 rows loaded once as a single table and once spread over fifty.

The 2.3 s difference over fifty tables is the fixed cost of a table: one boundary sample, one column lookup, one index to create and one progress document to write, once each, however few rows it holds. A child collection adds roughly 100 ms more for that table, since its COPY aggregates per parent row.

Two things it is worth noticing in that table. The cost is linear — five hundred tables would be around 23 s of setup, not a wall — and memory is lower with fifty tables than with one, because a single large table keeps more rows in flight in the copy channel. Nothing here grows with the number of tables.

Nor does the streaming side: fifty relations written round-robin keep up, and a single commit touching all fifty propagates in 0.19 s, so per-transaction bookkeeping does not fan out either.

Idle streaming costs nothing measurable. Over 20 seconds with no writes, pg2osync issued 0 queries. Logical replication is push-based: the server sends changes over the replication connection, and there is no polling.

pg2osync writes no WAL. It only reads. The WAL your writes generate is charged to the database whether pg2osync runs or not — with one exception below.

Nested children cost one query per changed parent, and nothing extra during the initial load. The load reads each child collection once, aggregated, and joins it to the parent in the same COPY:

Measured: loading 20,000 parents with one child collection issued 20,000 child queries before this was fixed and zero afterwards.

The join compares the key in its own type. That matters more than the query count: casting either side to text makes the index unusable. On 50,000 parents, the same work took 165s with a text cast and 74ms without it. The live re-fetches compare in their own type for the same reason — index the child's foreign key, or every changed parent scans the whole child table.

Without children, the initial load runs exactly one COPY per table plus a handful of catalog queries:

1x  SELECT setting FROM pg_settings WHERE name = 'wal_level'
1x  SELECT pubname FROM pg_publication WHERE pubname = $1
1x  SELECT confirmed_flush_lsn::text FROM pg_replication_slots …
1x  COPY (SELECT … FROM public.users) TO STDOUT (FORMAT text)

Cost of REPLICA IDENTITY FULL

pg2osync recommends REPLICA IDENTITY FULL on child tables (a delete otherwise carries no foreign key) and for tables whose primary keys change. It is not free: the whole old row goes into the WAL on every update.

Measured on a table with a 200-byte text column, 5,000 updates:

The multiplier grows with row width. Set it per table where you need it, not database-wide.

Initial load impact

The load reads a table in primary-key pieces, each its own short statement, so the longest read view it holds is one piece rather than the whole load — one COPY per range on PostgreSQL, one keyset SELECT per chunk on MySQL. That matters for exactly one reason: a read view open across a long load stops the engine reclaiming anything that died after it started, and the load is the operation that takes an hour.

The cost is worse on MySQL than on PostgreSQL, which is why the MySQL load was changed too. A pinned xmin horizon delays VACUUM; a long InnoDB read view makes purge block, and the undo it cannot discard accumulates in the buffer pool — Percona measured 382,969 of ~391,000 buffer-pool pages given over to undo on a 1B-row table, with foreground throughput down to single-digit TPS for as long as the view lived. MySQL's own manual warns about this for read-only transactions unprompted.

What it costs the source:

• One max_connections slot for the duration.
• Sequential reads that compete for I/O with your workload, taking no locks that block writers.
• PostgreSQL: one cheap pg_class lookup and one TABLESAMPLE read per table to decide where to cut the ranges, plus one pg_current_wal_lsn() per range.
• MySQL: nothing to decide — the cursor comes out of the rows already read — plus one SHOW BINARY LOG STATUS per chunk.

Measured: 20,000 rows loaded in under a second; a 200,000-row table read in six ranges at ~55,000 rows/s, no single transaction lasting longer than a range.

The load also runs beside the stream rather than before it, which is what keeps retained WAL bounded on a long one — see architecture. The cost to the source is that the copy and the change stream compete for the same target, so a PostgreSQL load under WAL pressure deliberately pauses and takes longer. The MySQL load never pauses, because there is no retention of ours to protect and waiting would only widen the window for a purge.

[source] load_workers is the other direction: it costs the source one concurrent COPY per worker for the duration of the load. Worth paying only where the server is doing per-row work for the read, which in practice means a table with a nested collection — its COPY runs an aggregate subquery per parent row, and more backends run them in parallel. Measured on the dev stack, 200,000 parents with five children each: 27,400 parents/s with one reader, 42,100 with four. On an ordinary table the same four readers buy 5–8%, which is not worth four times the read load.

[engine] write_concurrency costs the source nothing and the target proportionally: it is how many write requests stay open at once, so raising it to four means four concurrent bulk requests against a cluster that may be serving queries as well. The source read is untouched — it was never the limit, and one COPY already outruns the pipeline by more than twenty times.

A PostgreSQL table smaller than one range, or one with a composite primary key, is still read in a single COPY, so the common case has none of the extra round trips. On MySQL a composite key is chunked like any other — that is what the expanded cursor comparison is for — and only a key whose text is not a faithful literal (binary, blob, bit, geometry, float) falls back to one statement.

The one real risk: retained WAL

An active pipeline retains only what it has not yet confirmed — measured at 176 kB while streaming.

A slot with nothing consuming it retains WAL forever, and that fills the database's disk. This is the failure mode to alert on.

What it costs, measured

A row of about 110 bytes retains 238 bytes of WAL — the row plus its overhead, and the same figure whether the writes arrive as one transaction of 100,000 rows or as 20,000 separate ones. So for a workload of that shape, with nothing reading the slot:

Wider rows cost proportionally more, and REPLICA IDENTITY FULL multiplies the update half by about 1.5. The point of the table is the shape: retention is linear in write volume and unbounded in time, so the question is never whether a stopped pipeline fills the disk but when.

Alerting on it

pg2osync reports this itself, for every slot on the server and not only its own:

pg2osync_slot_retained_bytes{slot="pg2osync"}      4096
pg2osync_slot_wal_status{slot="pg2osync",status="lost"} 0
pg2osync_slot_active{slot="pg2osync"}              1

pg2osync_slot_safe_wal_size_bytes appears only when max_slot_wal_keep_size is set: the server leaves it null when nothing bounds the slot, so its absence says the retention is unbounded. The same numbers by hand:

SELECT slot_name, active, wal_status,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained
FROM pg_replication_slots;

The awkward case is a pipeline that has been down for a week, because nothing is running to report anything. For that, pg2osync status --max-retained-mb exits non-zero when any slot is over the limit, which makes it something a cron job or a Kubernetes CronJob can check without the pipeline being up.

• Alert when retained grows over hours, or when active is false for a slot you expect to be running.
• Run pg2osync drop-slot when you decommission an instance for good.
• Set max_slot_wal_keep_size (PostgreSQL 13+) as a backstop: a full disk becomes a recoverable incident instead, and the initial load now watches the same signal — while the slot is past its budget the copy pauses and gives the stream the throughput, so the load is what slows down rather than the slot being invalidated. Without the setting there is nothing to watch and nothing to protect: wal_status stays reserved however much WAL piles up.

MySQL has no equivalent: it keeps binlogs on its own schedule (binlog_expire_logs_seconds), and its automatic purge does not spare a file a consumer still needs. The trade-off is reversed — nothing accumulates because of pg2osync, but if it is down longer than the retention window the position is gone and the next start re-runs the initial load. That is also why the MySQL load does not pause the way the PostgreSQL one does: holding the load back cannot protect a position MySQL was never keeping for us, and it lengthens the window in which the file we still need can be purged.

Reproducing these numbers

docker compose -f dev/docker-compose.yml up -d
cargo build --release
ROWS=20000 ./dev/db-impact.sh

The script prints connections, per-query call counts from pg_stat_statements, WAL deltas and retained WAL.

Architecture

The pipeline

  source task            engine task              sink task
┌──────────────┐  mpsc  ┌──────────────┐  mpsc  ┌──────────────┐
│ decode WAL / │ 10k    │ buffer until │ 64     │ bulk write   │
│ binlog into  ├───────►│ COMMIT, then ├───────►│ + truncate,  │
│ ChangeEvents │  ▲     │ batch + map  │        │ in order     │
└──────────────┘  │     └──────┬───────┘        └──────┬───────┘
       ▲          │            │                       │
       │      backpressure     ▼                       ▼
       │                 checkpoint task ◄──── acknowledged position
       └──────── durable position (clamps what may be acked)

Three tokio tasks joined by bounded channels. The bounds are the entire backpressure mechanism: a slow sink fills the batch channel, which blocks the engine, which fills the event channel, which stops the source from reading — so the database retains its log instead of the process growing without limit.

Source task

Owns one replication connection and turns the wire protocol into core::ChangeEvent values: Row (insert/update/delete), TableTruncated, and Transaction(Commit) boundaries carrying a position token.

Everything protocol-specific stays here. PostgreSQL pgoutput decoding lives in crates/source, MySQL binlog decoding in crates/source-mysql.

A source error tears the pipeline down and rebuilds it from the last checkpoint — new channels, a new engine task, a fresh connection. Rebuilding fully is the point rather than the cost: a partially buffered transaction is invalid once the stream repositions, and the teardown is what discards it. Retries back off exponentially and stop after [source] reconnect_max consecutive failures, so a real outage still reaches whatever supervises the process instead of being hidden by an endless retry loop.

Engine task

Source-agnostic by construction — it knows ChangeEvent, the Sink trait, and nothing else. It:

1. buffers rows until their Commit arrives, so a partial transaction is never handed to a sink;
2. splits a transaction that exceeds batch_size or batch_max_bytes across requests (safe because every write is idempotent, and the commit position lands on the final piece), and conversely lets whole transactions accumulate for up to 10 ms when more events are already waiting — a commit is what forces a batch, so without it a stream of single-row transactions costs one request each. The batch's highest position stays the last commit in it, so an ack can never run past a transaction that was not fully written;
3. filters rows against the table's where predicate on the raw row, then applies column projection, transforms, field renames and constants;
4. completes unchanged-TOAST columns by reading the previously indexed documents — for a whole group of rows in one request, not one round-trip per row;
5. maps (schema, table) to a target index.

Sink task

Executes writes and truncates in the order the engine produced them. Both travel through the same channel: running a truncate directly would let writes still queued ahead of it land afterwards and resurrect rows the source has already dropped.

Checkpoint task

Every checkpoint_interval_ms it persists the highest acknowledged position. Only after that write succeeds does the durable position advance — and that value is what the source is allowed to acknowledge upstream.

Positions

The engine treats a source position as an opaque, monotonically increasing u64 token:

Packing the file index into the high bits makes a rotation compare greater than any offset in the previous file, so ordering holds across rotations. The binary supplies a closure that renders the token into the source's own textual form, which is what a restart parses to resume.

Delivery semantics

• At-least-once. A crash between a sink write and the checkpoint replays the last batch on restart. Correctness rests on idempotent writes: a document's _id is its row's primary key — or the id [sync.x] id renders from the row's raw values, and for a fan_out table one id per array element — so a replay overwrites to the same value.
• Never acknowledge early. The position reported to the source is clamped to the durable checkpoint. Acknowledging further would let the database recycle history for rows that are not indexed yet — the classic way CDC pipelines lose data on crash-restart.
• Written or recorded, before acknowledged. A document the target refuses permanently stops the pipeline by default. Where on_permanent_rejection = "quarantine" is set it is written to a hidden .pg2osync_rejects index before its batch's position is acknowledged, and a failure to record it halts instead — so the position can never sit past a document that was neither written nor kept. Dead-lettering while the offset advances regardless is how other pipelines lose the document.
• Ordering is guaranteed per row. Across tables there is none, as with any CDC system without global serialization.
• TRUNCATE clears the target index, ordered against pending writes.
• A changed primary key is a move: the document is written at its new identity and the old one is deleted, in that order. A crash between the two leaves a duplicate that the replay repairs, where the reverse order would leave a document nothing ever collects. An id derived from columns outside the key moves the same way when those columns change, which is why such a table requires the row's before-image from the source.
• A fan_out row owns one document per array element, id rendered from the merged child. Updates diff the element sets and deletes come from the before-image, all as ordinary versioned ops; the sink never learns the difference.

Read-your-writes

The pipeline is asynchronous, so a caller that just committed cannot assume its change is searchable. GET /synced closes that gap on request: it waits until the acknowledged position passes the source's position and, with refresh=true, until the target has made the writes searchable.

Waiting on the acknowledged position rather than the checkpoint is deliberate. The checkpoint exists for crash recovery and is written on an interval; the acknowledgement is the moment the target accepted the write, which is what the caller actually cares about.

Two source-specific details make this work at all:

• PostgreSQL skips transactions that touch no published table. The position a caller reads from pg_current_wal_lsn() therefore includes activity the pipeline will never see, and on a quiet database the gap never closes. When the endpoint finds itself behind, it emits a logical decoding message (pg_logical_emit_message) — a marker the stream does carry, written without touching any table and without needing DDL privileges.
• MySQL's binlog is server-wide, so the position a caller reads is reached by the commit's own XID event. A heartbeat period is requested on the dump connection as a fallback for a genuinely idle server.

Measured on the dev stack: 20 writes each followed immediately by a search, zero misses, /synced returning in 5 ms at the median. MySQL and MariaDB the same.

Crash safety

On startup pg2osync reads the checkpoint and refuses to use one that does not belong to this stream — a different source kind, slot or server_id means a full initial load instead of resuming into the wrong position space.

For PostgreSQL it additionally compares the checkpoint with the slot's confirmed_flush_lsn. A checkpoint behind the slot is unusable: streaming would resume at the slot's position and the gap between them would be lost, so the initial load runs again.

dev/e2e-test.sh verifies this by SIGKILLing the process, writing rows during the downtime, restarting, and asserting nothing is missing.

Initial load

PostgreSQL: the slot is created first, then the load runs beside the stream — not before it — on a second connection, reading each table in primary-key ranges, each range one COPY (SELECT … WHERE key >= a AND key < b) TO STDOUT (FORMAT text) in its own short transaction. No transaction spans the load: one that did would pin the xmin horizon for its whole duration, and autovacuum could clean nothing that died meanwhile.

What makes that safe is not snapshot consistency. The slot exists before the first range and nothing advances it during the load, so streaming afterwards resumes from a position that predates every range — anything a range missed or read stale is still in the WAL and replays onto an idempotent write.

Ranges are cut at boundaries sampled from the table itself (percentile_disc over TABLESAMPLE SYSTEM), so they follow the real key distribution and work for any orderable key type. They are read unordered on purpose: ORDER BY key LIMIT n forbids a bitmap heap scan, so a row-estimate miss degrades to a sort per range, and index order costs random heap access on any key that is not physically correlated. A table below the range size, or one with a composite key, is read in one piece as before.

MySQL: the binlog coordinate is read before the first chunk, then each table is read in primary-key chunks — WHERE key > cursor ORDER BY key LIMIT n, one statement each, no transaction spanning them. InnoDB's clustered index is the table, so that walks the rows in key order and each chunk's last key is the next chunk's cursor; the comparison is expanded rather than written as a row constructor, which MySQL plans without a usable key. A key column whose text form is not a faithful literal — binary, blob, bit, geometry, float — is read in one statement instead.

Rows produced by an initial load carry position token 0, which flushes batches without ever advancing the checkpoint — they have no position of their own. They do carry a document version: the source position read before their range or chunk, so a row that was already stale when it was copied cannot overwrite the streamed change that superseded it.

Why the load and the stream overlap

Loading first and streaming afterwards has a failure that gets worse the larger the table is: nothing acknowledges a position for the load's whole duration, so the slot's retained WAL grows monotonically until the load ends. Past max_slot_wal_keep_size PostgreSQL invalidates the slot (wal_status = lost), which is unrecoverable and forces exactly the full reload that was in progress.

Running both at once is what removes that, and it needs one thing to be safe: every document carries the position it became visible at, so a copied row that was already stale loses to the streamed change at the target regardless of which arrives first. Without that, a chunk read at position 100 and written after a streamed event at position 150 for the same key leaves the row silently stale until something touches it again.

Four rules keep the overlap from turning into a different problem:

• Change events have strict priority over copy rows. They arrive on separate channels and the engine drains the stream first. WAL is retained until it is consumed, so the stream cannot wait; a copy range can.
• The copy yields under source pressure. Before each range the slot's wal_status is checked, and while it is anything but reserved the copy pauses and lets the stream have the throughput. That is PostgreSQL's own signal rather than a threshold of ours — with no max_slot_wal_keep_size configured the status stays reserved, which is honest: there is no line to stay behind, and no protection either. wal_status = lost fails the load with an explanation instead of continuing into a gap.
• A write the stream has already removed is dropped, not offered. This is where versioning alone is not enough. A versioned delete leaves a tombstone rather than nothing, the target keeps it for index.gc_deletes (60s by default), and once it is gone external_gte accepts any version at all — so a copy row starved past that would put the document back. The engine remembers what the stream removed for the length of one chunk and drops such a row itself. The window closes at each load mark, which is sound because the load waits for its mark before reading the next chunk.
• Pausing happens between ranges, never inside one. A COPY held mid-stream would keep its snapshot open for the length of the pause, which is the long transaction this design exists to avoid. A range is under a second of work at measured rates, so waiting for one to finish costs nothing worth having.

Measured on the dev stack, 1M rows (361 MB) loading while a writer churned a second table throughout: retained WAL oscillated and fell while the load was still running — 83 MB down to 46 MB — which cannot happen in the sequential design. With max_slot_wal_keep_size deliberately cut to 48 MB the slot went unreserved, the load paused for 29 s, the slot recovered to 6.5 MB and reserved, and the load then finished: 1,000,000 rows indexed, every streamed update intact. The incident stayed recoverable, which is the whole point.

MySQL overlaps too, and deliberately never pauses. The middle rule has no analogue there because the hazard is reversed: a slot retains WAL until it is consumed, so a slow consumer is what invalidates it, while MySQL purges binlogs on its own time and space policy and ignores consumers entirely. Nothing accumulates because of pg2osync, and the thing that can go wrong — the file we still need being purged — is made likelier by holding the load back. There is also nothing to keep open between chunks: each chunk is one statement, and the session runs READ COMMITTED so no read view outlives it.

Measured on the dev stack, 1,048,576 rows in 53 chunks while a writer churned a second table throughout: 17.2 s at ~61,000 rows/s, no transaction older than 0 s at any sample, and History list length oscillating between 11 and 49 rather than climbing — the purge keeps up, which is the whole claim. The old snapshot held one read view for the entire load, and a blocked purge is what Percona measured filling 382,969 of ~391,000 buffer-pool pages with undo on a 1B-row table.

For the duration of the load refresh_interval is suspended on every configured index, so ordinary searches see nothing new while it runs even though the stream is live. /synced forces a refresh, so read-your-writes still works.

Resuming an interrupted load

Progress is recorded per chunk in the target, one document per stream and table in .pg2osync_meta. PostgreSQL stores the boundaries the table was cut at and how many leading ranges are durably written; MySQL stores the last key written, which is all a keyset cursor needs. Both carry whether the table finished. The order is strict — rows, then a mark the sink reports once they are written, then the progress document — so a crash can lose forward progress and redo a range, but can never claim a range that was not written.

PostgreSQL's boundaries are stored rather than recomputed because they come from a random sample: a second run would cut the table elsewhere, and "two ranges done" would then name a different span of rows. A keyset cursor has no such problem — the key it names is the key it names — which is why MySQL stores nothing else.

A checkpoint is not proof that the load finished. Startup checks both, and an unfinished table is carried on even when a checkpoint exists — trusting the checkpoint alone is how a pipeline silently skips its load and reports success. dev/e2e-test.sh and dev/e2e-mysql-test.sh each kill a load mid-chunk, change the source while nothing is watching, restart, and assert both that the load resumed and that the index matches the source exactly.

Target-side cost

One read of the table, and the rest of the cost on the target: 15M rows at the default batch_size of 500 is one COPY and roughly 30,000 bulk requests.

For the duration of the load refresh_interval is set to -1 and number_of_replicas to 0, and both are put back afterwards — the standard bulk-load recipe. Nothing searches an index that is still being filled, so refreshing it every second and writing replicas is work nobody is waiting for. Two honest caveats:

• It is not always worth anything. Measured on a single-node development cluster loading 200k narrow rows, it made no measurable difference: with one node the replicas are unassigned and a two-second load spans two refreshes. The work it removes only bills on a cluster that really has replicas and a load that runs for minutes.
• An interrupted load leaves refresh suspended, and the symptom is nasty: writes are accepted, the pipeline looks healthy, and searches return nothing. A later load treats -1 as "no saved value" rather than restoring it, and startup warns about any configured index still in that state.

Checkpoints

One document per stream in the target, named <source>-<slot_name> for PostgreSQL and <source>-<server_id> for MySQL. Per stream rather than one shared document, because two pipelines writing to the same target — a zero-downtime re-index, or tables split across instances — otherwise overwrite each other's position, and either one restarting then finds a checkpoint belonging to the other and re-runs a full initial load.

A checkpoint written before that change lives under default, and is still read when a stream has no document of its own, so an upgrade does not re-load.

Row fidelity

• Unchanged TOAST (PostgreSQL): an UPDATE omits large unchanged columns. If the table has REPLICA IDENTITY FULL the old tuple supplies the value; otherwise the previously indexed document is read back through Sink::get_documents.

  The engine takes every row already waiting on its channel before doing that, so the read is one request for the group rather than one per row. It waits for nothing, so a row arriving alone is unaffected. dev/toast-cost.sh measures it: 20,000 updates to a table with an 8 kB out-of-line column ran at 1,800 rows/s when each row read on its own and 4,800 rows/s batched, against 4,400 rows/s for the same table at REPLICA IDENTITY FULL. The read-back is therefore no longer a reason to pay FULL's 1.5x WAL, and the counter pg2osync_toast_readbacks_total says how often it happens.

• Types. numeric and decimal become JSON strings, because a float round-trip loses precision. bytea, MySQL blobs and geometry become base64. json/jsonb are parsed into real JSON. Unknown types fall back to strings.

• Partitioned tables (PostgreSQL): publications are created with publish_via_partition_root = true, so events arrive under the parent relation and match the configuration.

• MySQL row images require binlog_row_image = FULL. The null bitmap is indexed by position among present columns, which is what makes partial images decode correctly at all.

Extension points

Adding a target means implementing Sink (ensure_ready, get_documents, write, truncate_index, write_checkpoint, read_checkpoint, health). No engine code changes, and the engine never matches on a sink kind.

Adding a source means producing ChangeEvents with position tokens. The engine cannot tell the difference.

Rationale for these boundaries is in decisions.md.

Design decisions

Why pg2osync is built the way it is. Code that contradicts a decision here is a bug: change this document first, then the code.

Change capture

Read the replication log, not triggers or timestamps. Logical replication (PostgreSQL) and the binlog (MySQL) see every change including deletes, add no write overhead to the source, and are available on managed databases. Triggers tax every transaction; timestamp polling cannot see deletes at all — which is why poll mode exists only as a documented fallback.

pgoutput, not a plugin. PostgreSQL's built-in output plugin needs nothing installed server-side, unlike wal2json or decoderbufs.

Protocol code is ours

Transport is a dependency, decoding is not. PostgreSQL replication uses the pgwire-replication crate purely as a transport that hands us raw XLogData frames; tokio-postgres does not support replication. For MySQL the whole wire protocol is implemented in-house, because the only Rust binlog client is unmaintained, blocking, and cannot handle split packets.

Everything above the transport — decoding, slot and publication management, transaction buffering, checkpointing — is ours, behind our own boundary. No CDC framework, and the transport stays swappable.

Boundaries

core depends on nothing. It holds ChangeEvent, the Sink trait, checkpoint types and the error taxonomy. Everything else depends on it and not on each other, so the compiler enforces the architecture instead of discipline.

The Sink trait lives in core, not in the sink crate. The engine must never import a sink implementation; the contract has to sit next to the shared types for that to hold.

The engine is source-agnostic. It knows ChangeEvent and an opaque u64 position token. Whether that token is a WAL LSN or a packed binlog coordinate is the source's business, and the binary injects a closure that renders it back to text for the checkpoint. This is what made adding MySQL a small change rather than a fork.

New targets implement Sink. No match sink_kind anywhere in the engine.

Correctness

At-least-once with idempotent writes. Exactly-once across two systems without a shared transaction is a fiction. Every document's _id is its row's primary key, so a replay overwrites to the same value. Duplicates are therefore invisible, which is what makes the snapshot-then-stream overlap safe.

The id is configurable; the default is still the primary key. (#62.) [sync.x] id = "tenant-{tenant_id}-{id}" renders an id from literals and placeholders; a table that configures nothing is filed under its key byte-for-byte as before, so no existing index needs a rebuild. Three rules make that safe, and they are the reason the feature was slow to arrive: identity renders from the row's raw values — before projections and before transforms, because identity is a property of the row, not of the projected document; exactly one place mints ids (materialize and completion_key in the engine), so the stream, the load, the re-snapshot and poll can never disagree; and a NULL in any column an id names halts the pipeline rather than inventing a name. An id that references columns outside the key additionally needs the row's before-image to delete and move its documents, which is why run refuses such a table unless PostgreSQL reports REPLICA IDENTITY FULL — on MySQL binlog_row_image = FULL already guarantees it.

One row can fan out into many documents. (fan_out, #62.) A JSON-array column can be indexed as one document per element — each the parent-minus-array document merged with the element — so a search can match a single tag without the whole parent. The documents of one row then share a key, and identity has to say something about that too, which is why the element id is a second template rendered from the merged document. Deletes and update-diffs are computed from the row's before-image and issued as ordinary per-document versioned writes: delete_by_query was rejected because it cannot carry an external version and would have to act as a barrier, breaking write_concurrency. The Sink trait is unchanged. Reconcile and re-snapshot refuse fanned tables for now — both page by key, and one row is no longer one document.

Parent-child can be a join field, not only an embedded array. (join, #60.) An embedded array is one document and one write, and the right answer nearly always; a join field is for children that are many, change far more often than the parent, or must be searched in their own right — re-fetching a 50,000-row collection because one child changed is the cost it avoids. What it costs: the two tables share an index and a shard, so routing rides on every operation that touches a child — bulk actions, the _mget behind TOAST completion, reconcile's deletes, a quarantined document's replay — and a parent delete cascades through a search, refreshed first, rather than an id list the engine could have built, because the engine does not know which children the target holds. A join child needs REPLICA IDENTITY FULL unless its parent column is part of its key: routing comes from the same place identity does. Exactly one parent, one shared field, no fan-out on a join table, and a parent id naming anything outside its key is refused at config load — the child holds one column and computes the parent's id from it alone. Ids must be unique across the shared index, which config cannot see, and TRUNCATE on either table clears its relation only — the join field is what tells the halves apart. A table that is both an embedded child and a section of its own is warned about at startup rather than refused — a load-once index is a legitimate thing to want — because the runner reads its rows only as a re-fetch of the owner, so its own index receives the initial load and no streamed change.

A column can route a document; the rule is the id rule. (routing, #109.) Shard co-location arrived as a by-product of join, where a child has to live on its parent's shard. It is worth having on its own: hundreds of small tenants in one index, each query reading one shard. An index per tenant is the alternative, and it is the wrong one at that shape — every index costs shards, and shards cost memory whether they hold ten documents or ten million. A bare column name, not a template: a routing value has no grammar to check, nothing downstream parses it, and a composite routing key is a use case nobody has. The value is read from the raw row, before projection and transforms, because a projection must not be able to move a document to another shard — and NULL, missing or empty halts, since the target rejects an empty routing and a silent fallback to the default shard would hide the document from every routed query. A routing column can change, which makes it identity's twin: the document is written under the new routing and deleted under the old, through the same comparison a changed id or a changed index template goes through, and a non-key routing column therefore needs REPLICA IDENTITY FULL for the same reason a non-key id does. Refused together with join, which already owns the child's shard; refused on Meilisearch, which ignores routing. reconcile is not refused: it never derives a routing, it reads each hit's _routing, so the only thing it cannot see is a duplicate left under a stale routing — and that document's row is still there, which is precisely what reconcile does not collect.

Several tables can feed one index once each declares its identity. (#61.) An index built before pg2osync is usually a union of several tables, and what the old refusal protected against was never the union: it was _id inherited from each table's own key, where two tables with a row 1 become one document by accident. An explicit id on every section sharing the index is the declaration that removes the accident — user-{id} on both sections still collides, but now it is something the operator wrote down, and nothing checks the values because nothing can see them. Two things cannot be recovered: reconcile pages an index by one table's key column, so every other table's document would look like an orphan, and it refuses; and TRUNCATE clears an index, which would wipe tables the source never truncated. It is not halted on — a halt would replay the same event from the slot at every restart with nothing the operator could change — but skipped, logged and counted, and the truncated table's documents stay until cleared by hand. A join pair escapes that: its relation name is exactly the set of documents to clear.

A row can choose its index; the rule is the id rule. (#69.) index = "events-{tenant}" renders the index from a column, and a name derived from a column is the same problem as an id derived from one: the column can change, and the document is then in the old index. So it is the same template, rendered from the same raw row, through the same ladder — the row, else the before-image, else the bare key, else halt — and (old index, old id) != (new index, new id) is the move id already handles. The index is created on demand, at the first document that needs it, rather than ahead of time: pre-creating means enumerating a column's values, which nothing can do without querying the source for a search concern, and recording the glob and creating on first use keeps the mapping the operator configured. reconcile is refused because it pages one index by its key column, and a templated table's documents are spread over every index the template renders — there is no single index to page. And a template must carry a literal, because TRUNCATE clears what the template claims, and a claim of * is a claim on the cluster.

Vectors are the target's to compute. (pipeline, #68.) Semantic search needs an embedding per document, and the obvious design — an embedding client inside pg2osync — puts a network hop and a rate limit inside every batch, adds a second failure mode that backpressure would have to respect on top of the target's, and makes a model choice that is not this project's to make. An ingest pipeline gets the same result for one config field: the section names the pipeline, every document it writes carries "pipeline": "<name>" on its bulk action, and the target — which already owns the model, the plugin and the knn_vector field — computes the vector on the way in. The document still travels the one write path, so a quarantined document replayed later goes through the pipeline again instead of landing without its vector. It is per section, not per index, because the pipeline rides on the operation rather than on the index: two tables feeding one index can embed different columns. A delete carries none, since an ingest pipeline runs on index actions only. Meilisearch has no ingest pipelines, and refuses the option at config load rather than ignoring it.

A table without a key syncs as insert-only, under a content hash. (append_only, #70.) The key requirement was right for a mutable row — an update or a delete has to find the document the row already owns, and only a key says which — and wrong for an event log or an audit trail, which never updates or deletes and was refused for a case that never arises. Declared append_only, a table files each row under a sha256 of its raw values as canonical JSON. Not the source position: the initial load has no per-row position, and the same row has to hash the same on every path — COPY, WAL, poll, MySQL load and binlog — which key-sorted JSON of the raw row gives and a position never could. Two identical rows are therefore one document, and that is the at-least-once guarantee restated: a replayed row is the same document, and a duplicate the source itself cannot tell apart is not one the index should invent a difference for. An UPDATE or DELETE that arrives anyway halts the pipeline, naming the table, rather than being missed: nothing can say which document it addresses. init writes the flag for a keyless table instead of refusing it, so the smallest config still runs and the declaration sits where the operator will read it.

A column can be renamed in the target; the rename is the last step. (fields, #66.) The source name is the one namespace the operator already knows, and the one every other check — projection, transform, id, primary_key, soft_delete, poll_column — is written against, so it stays the name those options use. Renaming last, after identity, projection and transforms, means none of them has to know a rename exists. The one place the target name leaks back in is TOAST completion, which reads the stored document and so translates the column through the same map. A rename onto a name another surviving column already has is refused wherever it can be seen — at config load and by validate against the live catalogue; at write time the renamed value wins.

A document can carry fields that come from no column. (constants, #67.) The alternative is a generated column: DDL on someone else's production table for a search concern, the same objection this project raises against event triggers and signal tables. Constants only, no expressions: the README promises no transformation language, and a language is a parser, an evaluator and a null semantics to own forever, where an entity tag or a {schema}.{table} origin marker is the whole of what was asked. The two placeholders render once at startup, so the engine inserts literal JSON and stays source-agnostic. Constants are added last because columns would otherwise strip a field that is not a column; written last, a constant wins a collision — so every collision the configuration can see is refused at load, and the one only the catalogue can see is refused by validate.

Transforms are fixed, named reshapes, not a language. (#63.) Six ops — hash, redact, json, split, number, date — and the README's promise holds: a closed set, one literal parameter at most, no chaining, nothing evaluated at run time, for the same reason constants carry no expressions. A value an op cannot convert is indexed as it arrived and counted, neither halted on nor nulled: halting turns a data-quality problem into an availability problem, and a NULL is silent loss. The target's mapping is the arbiter of what a field holds, quarantine already catches what it refuses, and pg2osync_transform_unconverted_total plus one warning per (table, column) keeps the rest visible. A value already in the target shape is not a failure, so every op is idempotent under at-least-once replay. split cannot feed fan_out, because fan-out reads the raw row — identity is a property of the row, as the id paragraph says. chrono, already in the build through the OpenSearch client, parses the dates: a strptime calendar is not protocol code.

A row filter is SQL the database also runs, evaluated once more in the engine. (where, #64.) A subset, because a stream has no query to push a predicate into: the engine evaluates it on every WAL, binlog and polled row, and everything it accepts is valid on both sources, so the load pushes it down unchanged. Three-valued logic is SQL's — NULL is unknown, only TRUE matches — so the two evaluations agree. Strings compare byte-wise, exact for equality and for ASCII and ISO 8601 order, so created_at >= '2024-01-01' holds against a textual timestamp; a number against a string holding one compares numerically, because numeric/DECIMAL arrive as strings on purpose. A row that leaves the filter is deleted the way a moved id is, the before-image naming a fanned row's element documents. An insert that never matched still costs one idempotent not-found delete; the alternative is remembering what was written. Poll mode does not push the predicate down: a row that left the filter has to keep arriving to become the delete it now is. It travels the ordinary op path, so the guard against a load resurrecting a removed document holds. A filter selects rows and computes no values: no transformation language.

Never acknowledge a position before it is durable. The value reported to the source is clamped to the persisted checkpoint. Acknowledging further lets the database recycle history for rows that are not indexed yet — the classic way a CDC pipeline loses data on crash-restart.

Buffer until commit. Rows are held until their commit boundary so a partial transaction is never presented as complete. A transaction that exceeds the batch limits is split, which is a deliberate, documented exception: an unbounded buffer is a worse failure than briefly observable partial state.

Writes and truncates share one ordered channel. Executing a truncate directly would let writes still queued ahead of it land afterwards and resurrect rows the source has already dropped. The target is also refreshed before the truncate, because delete_by_query only removes documents a search can see.

Stop on permanent rejection, unless told to quarantine. A document the target will never accept halts the pipeline by default. Skipping it would be silent data loss, and every later batch would widen the divergence. Halting means making no progress, not exiting: the attempt fails and is retried, so the position never passes the document and a mapping fix is picked up without a restart.

The cost of that default is that one malformed row stops replication for every table until someone edits a mapping, so on_permanent_rejection = "quarantine" records the refused document — with its position and the operation itself, in a hidden .pg2osync_rejects index — and carries on. What it must never become is Airbyte's Elasticsearch destination, which dead-letters a document while the offset advances anyway: the rule is that a position may be acknowledged only once the document behind it was written or durably recorded as refused, which is why quarantining happens before the acknowledgement and a failure to quarantine halts.

Quarantining a document is a partial transaction, and that is the trade. "No partial transactions" is otherwise an invariant here; skipping one row while its siblings land breaks it for that transaction. It is why the option is off by default and why it is named after what it does rather than after being resilient.

Quarantine is bounded. max_rejects (default 100) counts what the store actually holds, read at startup rather than kept in memory, so a crash loop cannot hand the budget back. One bad row is worth carrying on past; a mapping that refuses a whole table is not, and the pipeline halts naming the limit. Nothing is lost either way: the batch that reaches the limit has its refusals recorded first, and a batch arriving once the limit is already spent is left unacknowledged, so the source sends it again when the mapping is fixed.

A rejected document is replayed through the ordinary write path. pg2osync rejects --replay submits it again with its original position as its version, so a row the source has since changed loses to the newer value by the same rule that orders everything else, and the record is cleared only once the target has taken it.

A checkpoint is bound to its stream. It records the source kind, the slot or server_id, and the publication. A checkpoint from another stream is rejected rather than used to resume into an unrelated position space.

Initial load

No exported snapshot; short transactions and replay instead. The obvious design exports one transaction snapshot and reads every chunk from it, which keeps a read view open for the whole load: VACUUM cannot then clean anything that died after it started, and on MySQL a long read view makes purge block rather than lag. What makes our load safe is not snapshot consistency but that streaming resumes from a position that predates every chunk: on PostgreSQL because the slot exists before the first range and nothing advances it during the load, on MySQL because the binlog coordinate is read before the first chunk. Anything a chunk missed or read stale is still in the log and replays onto an idempotent write. Two conditions that argument rests on: writes are whole-document upserts keyed by the row's primary key, and an update whose unchanged TOASTed columns arrive as markers is completed from the stored document — without that, a replayed update would erase a value a range read correctly. A completed value is copied as it is stored, already transformed, and is not put through the transforms again: a hash of a hash would drift from what a fresh write of the same row produces.

The table is cut the way the storage engine reads it. PostgreSQL's heap order says nothing about the key, so ranges are sampled in advance and read unordered: ORDER BY key LIMIT n forbids a bitmap heap scan, and index order costs random heap access on any key that is not physically correlated. InnoDB's clustered index is the table, so MySQL does the opposite — WHERE key > cursor ORDER BY key LIMIT n walks the rows themselves, nothing is sampled, and each chunk's last key is the next chunk's cursor. That also makes the MySQL resume point exact, where PostgreSQL has to store its boundaries because a second sample would cut the table elsewhere.

The cursor comparison is never a row constructor. (a, b) > (x, y) says exactly what the expanded (a > x) OR (a = x AND b > y) says, and MySQL plans it as type: index with no usable key while the expansion plans as type: range. Measured on a composite key, for 1000 rows returned: 1000 rows read expanded, 2000 read as a row constructor, restarting at the head of the index every chunk — so the multiplier grows with how far the cursor has travelled. MySQL bug #111952, closed as not-a-bug with a worklog in its place. No IS NOT NULL guard accompanies the comparison even though MySQL sorts NULLs first: a PRIMARY KEY column is NOT NULL whether it was declared so or not.

Every document carries the position it became visible at, as a target document version. Streamed rows carry their commit position, copied rows the position read before their range. A copied row that is already stale therefore loses to the streamed change at the target, whichever order the two arrive in, and a version conflict is success rather than a rejection. It is deliberately separate from the checkpoint token: a copied row needs a version and must never advance a position. Poll mode, which has no position at all, writes no version and relies on ordering alone.

MySQL versions by its binlog coordinate, not by a GTID. (file index << 32) | offset is monotonic across rotation and was already the ordering token, and a transaction's events are written to the binlog as one group at commit — so no position inside a group can predate a coordinate a reader saw earlier, which is what makes an event's own offset a sound version. A GTID could not be one: it is source_uuid:N with N restarting at 1 for each UUID, so a GTID set has no order as an integer. MariaDB is the exception that proves the rule — its sequence number is one monotonic 64-bit counter per replication domain — and one version scheme for both servers is worth more than exploiting that.

The version carries a generation, so the coordinate space can change. The version is base + ((file index << 32) | offset), with base persisted beside the checkpoint. That space is per server and per binlog history, and a failover moves to a different one: the new coordinate may be lower than what the target already holds, and external_gte would then refuse every write and leave the index quietly stale. So when the source is behind the checkpoint and there is a GTID position to resume the stream from, a new generation opens at stored token + 2^40 instead, and every later version outranks everything written under the old numbering.

The margin has to exceed the highest version written but not yet acknowledged. That gap is bounded by how much binlog one unacknowledged transaction can span — a few file rotations, so a few multiples of 2^32. 2^40 is a thousand rotations of headroom and still leaves room for millions of generations in a u64.

Without a GTID position the refusal stands: a coordinate behind the checkpoint then means we can neither continue the stream nor trust the numbering, and reloading into silence is the one outcome worth refusing.

GTID is the resume position, never the version. Binlog file names and offsets are per server, which is why MySQL's own GTID_ONLY exists to stop persisting them; a checkpoint holding only a coordinate cannot resume anywhere but the server it came from. So the checkpoint carries a GTID position as well, inside the source's own position text — core says that text is the source's business and nothing else parses it.

The set is accumulated from the stream, one GTID per commit, and never read from @@GLOBAL.gtid_executed: that describes what the server holds, including transactions we have not consumed, so resuming from it would skip data.

The two servers share no mechanism for asking. MySQL has COM_BINLOG_DUMP_GTID carrying the set in binary; MariaDB has no such command at all and switches into GTID mode on the presence of @slave_connect_state alone. Both are implemented rather than one being emulated, because the difference is in the server and neither is a dialect of the other.

Anything that would leave the set incomplete refuses to use it rather than checkpointing a lie: a tagged GTID event, which MySQL 8.4 gives a type of its own, and an anonymous transaction under gtid_mode = ON_PERMISSIVE, which has no GTID to record at all.

A write the stream has already removed is dropped, not offered. Versioning alone does not make the overlap safe, and this is the one place it does not. A versioned delete leaves a tombstone carrying the delete's version, the target keeps that tombstone only for index.gc_deletes — 60s by default — and once it is gone external_gte accepts any version, including one below the delete's. So a copied row starved behind a busy stream for longer than that would put the document back. Measured against a real target at gc_deletes = 1s: the same write is refused with a 409 immediately and accepted two seconds later. TRUNCATE has the same shape, since it clears an index with versioned deletes.

The engine therefore remembers what the stream removed and drops a copied row that is older than the removal, rather than asking the target for a comparison it cannot make. This is the move DBLog makes for the same problem — it buffers a chunk and removes every key the log touched between two watermarks — except that watermarks exist there to substitute for versions, and versions already order everything else here, so only the case they cannot express needs it.

What bounds the state is the load's own protocol: a chunk's rows, then a mark, then a wait for that mark to be written. When a mark arrives, every row of its chunk has been handed over and none of the next chunk can exist yet, so the window closes and the memory is one chunk's worth of deletes rather than the load's. A loader that sent the next chunk before its mark was confirmed would reopen the hole silently, which is why the ordering is stated in both places.

Raising index.gc_deletes for the duration of the load was the alternative and is rejected: it moves the window instead of closing it, costs target heap for every tombstone it holds, and an interrupted load would leave the setting raised the way one already leaves refresh_interval at -1.

The load runs beside the stream, not before it. Loading first means nothing acknowledges a position for the load's whole duration, so retained WAL grows monotonically and a large enough table invalidates the slot — which forces the full reload the load was trying to finish. Alternating copy and catch-up phases does not fix it either: on PostgreSQL a paused consumer freezes restart_lsn whether it detaches or merely stops reading, so the only thing that releases WAL is continuing to consume it. Document versioning is what makes the overlap safe; change events take strict priority over copy rows, and the copy pauses between ranges while the slot's wal_status is anything but reserved.

On MySQL the load overlaps the stream but never pauses for it. The hazard runs the other way there: a slot retains WAL until it is consumed, so a slow consumer is what invalidates it, while MySQL purges binlogs on its own time and space policy and ignores consumers entirely. Nothing accumulates because of us, and what can go wrong — the file we still need being purged — is made likelier by holding the load back, not less. So there is no wal_status analogue to watch and deliberately no pause.

Load progress is recorded per range, in the target, behind a durability barrier. The order is strict: rows, then a mark the sink reports once they are written, then the progress document. A crash can therefore lose forward progress and redo a range, which idempotent writes make free, but can never claim a range that was not written. What the progress says depends on how the table was cut: PostgreSQL stores its sampled boundaries alongside a count of finished ranges, because recomputing them would cut the table elsewhere and the count would name a different span of rows; MySQL stores the last key written, which needs nothing else to be exact. A checkpoint alone is not proof the load finished — the two are separate facts, and conflating them is what silently skips a load.

The load reads in waves, and only for the tables where reading is the cost. Parallel readers were the obvious answer to a slow load and are worth almost nothing on an ordinary table: measured, four readers buy 8% on a narrow table and 5% on a wide one, because the target is what the pipeline waits for. On a table with a nested collection they buy 53% — there the COPY runs an aggregate subquery per parent row, so the server is doing per-row work and more backends do it in parallel. That is the whole justification, and [source] load_workers stays at 1 because outside that case it multiplies the read load on someone's production database for single digits.

Waves rather than a free-running pool, and that is not a matter of taste. The engine forgets its record of stream-removed keys on every load mark, which is only safe while nothing from before that mark is still in flight; with a pool, one worker's confirmed mark says nothing about the others. And progress is a count of leading ranges written, which out-of-order completion cannot advance. A wave satisfies both by construction: it is contiguous, and it is finished before its mark is sent. The cost is the skew inside a wave, which sampled ranges of equal row counts keep small.

The tombstone window is therefore bounded by a wave instead of a chunk — the same argument, load_workers times wider.

The load is made faster on the write side, because that is where the limit is. The obvious move is parallel readers, and it would have bought nothing. Measured on an 8-core laptop against the dev stack, 2M rows: one COPY hands over rows at ~1,050,000 a second, while the whole pipeline ran at 43,000 and spent 63% of its wall clock idle. The target was the reason — one bulk request open at a time tops out near 52,000 documents a second, and its size makes no difference at all (50,500 at 500 documents a request, 52,100 at 20,000), while four requests open at once reach 114,000. Refresh and replicas are already suspended for the load, so concurrency was the only variable left.

Opening more requests is therefore the whole change, and it delivers: 43,000 rows a second at one, 67,000 at two, 87,000 at four, 96,000 at eight. The process's CPU share over the same runs went from 37% of wall clock to 102%, which says plainly what happened — it stopped waiting and started working. At 10M rows the same shape holds and the numbers barely move — 42,700 at one, 90,100 at four — so this is not an artefact of a table that fits in cache.

Wide rows do not change the answer. A TOAST-heavy table reads at 11,200 rows a second through a client and gets slower with parallel readers, because what saturates is transporting the data, not the backend producing it; server-side the same read scales to 141,000 with four readers, so PostgreSQL is not the problem and neither is our connection count.

Write requests are open concurrently and completed in order. Concurrency that reordered completions would break three things at once, so it does not: a position is acknowledged only after every batch sent before it is durable, a refused document is filed before the position covering it passes, and a failure acknowledges nothing behind it. Load marks, truncates and bare positions are barriers that wait for the open writes to finish — for a load mark that is required rather than tidy, because the engine forgets its record of stream-removed keys on one, and that is only safe while the mark still means every copy row before it is durable.

It stays at one request by default. Raising it multiplies the load placed on someone's production target, which is not a default anyone should inherit unmeasured, and it needs a target that decides between two writes by their version: Meilisearch keeps whichever landed last, so it refuses the setting outright rather than reordering writes quietly.

A re-snapshot is a subcommand, not a signal table. Debezium triggers an ad-hoc snapshot by writing to a table in the user's database. pg2osync will not write to the source, and the CLI is already where operator actions live, so pg2osync resnapshot --table reads one table again into the index it is mapped to. It is the initial load's chunked reader with a scope, going through the whole ordinary write path — mapping, projections, transforms, children, id derivation — because a document it writes has to be indistinguishable from one the load wrote, and a second write path would drift immediately.

It cannot move the checkpoint by construction rather than by care: its rows carry position 0, so nothing acknowledges a position and the checkpoint task has nothing to persist. That is what makes it safe beside a running pipeline, together with the versioning that already orders a copied row against a streamed change.

It records no progress. An interruption means running it again; the alternative is bookkeeping under the key the initial load uses, which the next pipeline start would read as an unfinished load — the silent skip the load's own progress documents exist to prevent. It also leaves refresh_interval alone, unlike the initial load: it repairs an index that is in use, so hiding new writes for its duration would be the wrong trade.

It adds and updates but never deletes. reconcile is the other half, and keeping them apart is what keeps each one explainable.

A rebuild is a fresh index and an alias flip, not an in-place rewrite. (#107.) A mapping cannot be changed on a live index for anything that matters, so rebuilding one means writing a new one; rewriting in place would leave an index that is half old and half new searchable throughout, which is the outage the exercise exists to avoid. pg2osync reindex --table T --alias A therefore creates <index>-<unix seconds> with the section's mapping, loads the table into it, checks the count against the source, and moves the alias onto it in one atomic request.

It refuses to run while the stream is live, which is the one place it differs from a re-snapshot. A re-snapshot is safe beside the stream because a copied row and a streamed change meet at the target and the higher position wins — a comparison between two documents in one index. A fresh index the stream is not writing to has no second operand: a row updated during the load would be permanently wrong there and the count would still match. So a rebuild closes the window the way the initial load does — its rows carry position 0, the checkpoint does not move, and restarting the pipeline against the new index replays everything committed since. The refusal is positive evidence rather than a flag: an active replication slot on PostgreSQL, and on either source a checkpoint that moves while it is watched. There is no --force.

The alias is flipped before the restart because it costs nothing to: the old index is exactly as stale as the new one at that moment, and both catch up from the same replay. A live cutover with no freshness gap at all is still two instances, as operations.md describes — dual-writing from one process is not on the table.

refresh_interval is suspended for the duration here, unlike a re-snapshot's: nothing searches an index no alias points at yet, so there is no visibility to trade away. The old index is kept unless --drop-old says otherwise, because it is the rollback — one alias flip back — and a --keep-old that defaults to on would be an option that does nothing. The count proves the number of rows, not their contents; what proves the contents is the replay the restart runs.

An alias is a contract, not an API call. (#108.) Sink::switch_alias names an outcome — after it returns, the name readers use resolves to the documents the rebuild wrote, and it never resolved to nothing in between — and leaves the mechanism to the target. OpenSearch and Elasticsearch have an alias namespace and move a pointer inside it. Meilisearch has none: the name readers use is an index uid, and its atomic operation is POST /swap-indexes, which exchanges the contents of two uids in one task. Modelling that as "this target has no aliases" was the easy reading and the wrong one; the contract holds there, so the sink implements it.

Two things follow from the swap, and the command says both out loud. --alias on Meilisearch has to be the index the section already writes to, because that is the only name a reader is using; any other value is refused rather than quietly creating an index nobody reads. And the exchange runs both ways, so once it is done the timestamped <index>-<unix seconds> holds the previous documents, not the new ones — it is the rollback, --drop-old deletes it, and the kept-index message says which name it is. No config edit follows a rebuild here, only the restart, because the section's index never changed. The checkpoint is a file in state_dir rather than a document in the target, so it sits outside the uid namespace entirely and no swap can touch it.

A rebuild on this target is therefore never about a mapping — Meilisearch has no field types to declare, and ensure_ready refuses a spec that carries a mapping. What it is for is an index settings change that only applies to documents indexed after it, or a decoding bug whose wrong values are already written: both need the documents built again, and both want the live name to keep answering until they are.

Children resolve in the source, once per transaction. The engine is source-agnostic and runs no SQL against the source, so the only place that can group child lookups is the source's own decode loop — which already knows where a transaction begins and ends. Rows of tables with no children go straight out; the rest are held, and at the commit the distinct parents they affect are read in one query per collection. A child row holds nothing but the parent key it names, so a transaction touching a thousand children of one parent holds one key rather than a thousand rows, and writes one document rather than a thousand identical ones.

Measured on 2,000 child rows across 20 parents in one transaction: 2,000 parent re-reads and 2,001 child fetches became 1 and 2, documents written fell from 2,000 to 20, and throughput went from 845 to 2,829 rows/s. Every competitor breaks on this cost model — PGSync asks the index which documents a child row affects and was measured at 108s per batch; asking the source, once per batch, is the whole difference.

A MySQL child array is aggregated in Rust, not by JSON_ARRAYAGG. The obvious tool is the wrong one, measured on both servers: JSON_OBJECT renders a varbinary as "base64:type15:AP8Q" on MySQL and as raw escaped bytes on MariaDB where the pipeline says "AP8Q", a set as "a,b" where the pipeline says ["a","b"], a decimal as a JSON number where the pipeline keeps the string so the precision survives, and a bit as base64 on MySQL and as invalid JSON on MariaDB — its own JSON_VALID returns 0 for it.

Casting each column (TO_BASE64, CAST(… AS CHAR), CAST(… AS UNSIGNED)) gets closer and still fails: TO_BASE64 wraps at 76 characters, so any value over 57 bytes disagrees with the pipeline's base64, and a set cannot become an array without JSON_TABLE per row. And where it does work it means writing the type mapping a second time, in SQL, for the two to agree.

So child rows come back as ordinary rows and go through the same build_document that builds a parent. A value inside an array is then identical to the same value as a document because it is the same code, and the cost stays one query per collection per batch — the server still does the ordering, the cap and the count.

The child aggregation is built in one place. The initial load's COPY and the streaming re-fetch use the same subquery, so the array's contents, order and cap cannot drift between them. Two builders would disagree the moment either changed, and the disagreement is invisible until someone re-snapshots.

The array is ordered by the child's primary key. Without an order it is a set in arbitrary order, so the two paths could embed the same children differently and a re-snapshot would rewrite documents for no reason. With a cap it decides which children are kept, so the same subset has to come back every time.

No cap on an embedded collection by default, and truncation says so. A cap loses data, and the bound that matters is already the target's: past index.mapping.nested_objects.limit (10,000) OpenSearch refuses a nested document outright, which is reported and quarantined rather than lost. So the default embeds everything and logs an array past that limit, naming the parent. Where max_rows is set, the document carries <field>_truncated and <field>_total — a consumer cannot otherwise tell a short array from a complete one, and handing over part of a collection as if it were all of it is worse than either extreme. Data Prepper's equivalent defaults to 1000 and documents no overflow behaviour at all, which is the version of this not worth copying.

A one-to-one child is an object, and a second row is a warning, not a choice. single = true unwraps the collection after the aggregation, in core, rather than reading it with a LIMIT 1 of its own: each source keeps exactly one aggregation builder, so the initial load and the per-transaction re-fetch cannot embed different shapes, and the ordering, counting and capping machinery is untouched. A second matching row does not fail the run — a duplicate that exists for the length of a migration must not halt an index — and it is not silently resolved either: the batch logs one line naming the collection, how many parents matched twice and the worst of them. The row that stands is the lowest-keyed one, not the newest: primary-key order is what both the load and the re-fetch already promise, so a re-snapshot embeds the same row rather than rewriting the document. No metric counts it: neither source crate holds a Metrics handle, and a warning already names what to fix.

Checkpoints

State lives in the target. A hidden .pg2osync_meta index holds one document per stream; per-document atomicity gives the crash safety for free, with no compare-and-swap. Per stream rather than one shared document, because two pipelines writing to the same target otherwise overwrite each other's position — which is what a zero-downtime re-index runs, and what splitting tables across instances means. A local file breaks on ephemeral containers, and a table in the source database pollutes the user's schema and risks replicating itself. Meilisearch has nowhere to put an arbitrary document, so it uses a write-then-rename state file — the documented exception.

One position format for every source. The document stores an ordering token plus the source's own textual position. Documents written by earlier versions, which stored only an LSN, are still readable: refusing them would force a full re-index on upgrade.

Types

numeric and decimal become JSON strings. A float round-trip loses precision, and these columns are usually money. MySQL decimals keep their declared scale so a streamed value matches what the initial load read. transform = "number" is the operator's explicit opt-out — an index that sorts or range-queries on money asks for it, and accepts the double.

Unknown types become strings. Domains, ranges and composites are passed through as text rather than guessed at.

bytea, blobs, binary and geometry become base64. Binary cannot go into JSON any other way.

On MySQL both readers decide from the declared type, not from the wire. Neither format is self-describing where it matters. A binlog row image gives a string column no charset, so char and binary share a type code and so do text and blob; it gives an enum an ordinal and a set a bitmask with the labels nowhere. The text protocol has the opposite gap: every value is bytes and only the declared type says whether they are characters. So the shape is resolved from information_schema once — column_type alongside data_type, because that is where the labels live — and both decoders consult it. Deciding per format instead is what made text arrive as base64 from the stream and as a string from the load, and varbinary arrive as mangled text from both.

A MySQL enum is its label, a set is an array of its labels, and a bit is a number. The alternatives are what the wire happens to carry — an ordinal, a bitmask, a byte string — and none of them is searchable, which is the only reason the document exists. A set is an array rather than a joined string so each label matches on its own. bit fits a number because MySQL caps it at 64 bits.

Operating limits

Retention is reported, never capped by us. A slot nothing is reading pins WAL until the disk fills, and max_slot_wal_keep_size is the one setting that turns that into a recoverable incident. pg2osync still will not set it: it is a server-wide setting, and writing to the source's configuration is the same refusal as not running DDL and not writing a signal table. What is owed instead is that the number cannot be missed — pg2osync_slot_retained_bytes for every slot on the server, the server's own wal_status beside it, and a startup warning naming what an idle slot already holds.

Measured, so the risk is a number rather than a caution: a 110-byte row retains 238 bytes of WAL, which is ~820 MB an hour at a thousand writes a second.

The check has to work while the pipeline is down. That is the case that takes a database out — a process stopped on Friday, nobody reading logs for something that is not running, metrics unscraped because nothing is serving them. So pg2osync status --max-retained-mb exits non-zero over a limit, which makes it something a cron job can own, and it looks at every slot rather than the configured one: an orphan from a former slot_name fills the same disk.

No Amazon OpenSearch Serverless. It looks like one more OpenSearch endpoint and is a different target: SigV4 is the only authentication a collection accepts, a custom document id works only on a search collection, and the service owns refresh and index settings. The first rules out talking to it at all without a signing implementation, the second would make the _id-is-the- primary-key rule fail on two of the three collection types, and the third removes the load's refresh suspension and /synced.

A serverless = true flag existed from the first commit and was never run against the service. That is a support claim nobody could stand behind, so it is gone and the url is refused instead. Nothing in the competitive set advertises Serverless either — the tools that do are log shippers and AWS's own ingestion pipeline, not database-to-index replication — so this closes no gap.

Scope

One-way replication only. No bidirectional sync, no conflict resolution.

Schema drift is reported, never applied. pg2osync will not run DDL on the target's behalf. A publication that does not match the configuration is an error, not something to silently fix. A table whose columns change under a running pipeline is logged, naming what was added, removed or retyped — the index and the database now disagree about what a row looks like, and only a rebuild closes that. Which is why the index name is configuration.

It is also counted, as pg2osync_schema_drift_total{table}. A log line is not alertable: an operator who does not read logs never learns the index and the table stopped agreeing, and "reported" that nobody can be paged on is barely reported at all. The report reaches the counter through the change-event channel, as a positionless SchemaDrift event the engine counts and drops — the same path rows and truncates already take, so both sources report drift the same way, neither of them holds a Metrics handle, and nothing PostgreSQL- or MySQL-specific reaches the engine. Carrying no position is what keeps it inert: a drift event can never flush a batch, acknowledge a position or move a checkpoint. On MySQL the comparison is between the catalog's answer before a DDL and its answer after, since the binlog says a statement ran but not what it did to a column layout.

A binlog shape the catalog cannot match is skipped, not fatal. MySQL's TABLE_MAP describes the table as it was when the row was written, and information_schema only ever answers for now. A crash-restart resumes from the last durable checkpoint, so any DDL that committed after that checkpoint is replayed: the rows before it carry a column count the catalog no longer has, and re-reading the catalog cannot bring the old layout back. Refusing to continue there looks safe and is not — the reconnect resumes from the same checkpoint, reaches the same event and fails again, so the pipeline stops replicating everything rather than the handful of rows it cannot decode. Those rows are therefore counted as drift, named in the log and left undecoded, which is the same bargain the rest of this section makes: the index and the table disagree about a shape that changed, and only a rebuild closes that.

No event trigger in the user's database. The attractive version of DDL detection puts a CREATE EVENT TRIGGER in the source, which writes each schema change into the WAL as a logical message so it arrives inline and correctly ordered ahead of the data that depends on it. pgstream does this, and the machinery is already here — /synced emits logical messages and the decoder already advances on them.

It is still refused, for two reasons that were measured rather than assumed.

The first is that pgoutput already does the ordering. PostgreSQL re-sends a RELATION message whenever a replicated table's shape changes, before the first row event that depends on it, and column_drift reports exactly what changed. Verified against a live database: an ALTER TABLE ... ADD COLUMN between two row events logs added later_col and the next document carries the new column, in order, with no trigger involved. The problem the trigger exists to solve is not one we have.

The second is the cost. CREATE EVENT TRIGGER needs superuser — PostgreSQL's own documentation says so plainly — which many managed providers do not grant, and it would put an object of ours inside the user's database. That is the same refusal as not running DDL on the source and not writing a signal table into it, and the refusal is itself something people choose this tool for.

What the trigger would add over what pgoutput gives us is the DDL text, earlier notice on a table nobody is writing to, and DDL that does not touch a replicated table's shape at all. None of those change what a document looks like, which is the only thing the index can disagree with the database about.

Worth revisiting only if pg2osync ever applies schema changes to the target — a different product than this one, and the point at which knowing the statement rather than the resulting shape starts to matter.

No relational sources beyond PostgreSQL and MySQL/MariaDB, and no non-relational sources. The value is depth on these, not breadth.

Nested children stay one level deep. Anything deeper is the application's to shape before it reaches the database, and there is no view route around that: only base tables are eligible (relkind = 'r' on PostgreSQL, table_type = 'BASE TABLE' on MySQL) because the WAL and the binlog carry base-table rows, and a view has none to stream.

Implementation choices

Hand-rolled metrics endpoint. Six counters and one summary do not justify a Prometheus client plus an HTTP framework in a binary that advertises having no dependencies.

Batch reads with COPY … (FORMAT text). Text parsing measured fast enough (~21k docs/s end to end) that binary format's complexity is not yet justified.

Secrets from the environment. Every secret has an *_env form. Inline values still work but warn, because config files end up in version control.

Errors: thiserror in libraries, anyhow only in the binary. Callers get matchable variants; the CLI gets readable messages.

YAGNI on configuration. An option that does nothing is worse than a missing one, because it implies a guarantee. Options that had no effect were removed rather than documented.

Advisories are reviewed, not muted. cargo audit runs in CI when the dependencies move, and every entry in .cargo/audit.toml carries the argument for why the advisory does not reach this binary — for the rsa sidechannel, that the process holds no private key to leak. An advisory with no such argument is a bug to fix, not a line to add there.

Cutting a release

Three commands, and a pull request you merge in between. Nothing here happens automatically: the version, the changelog and the tag are each an explicit act.

How it works

Every push to main runs release-please, which reads the Conventional Commits since the last release and keeps one open pull request up to date — the release PR. The package it releases is the repository itself — the . key in release-please-config.json. That is the whole point of the .: a package configured with a path only ever sees the commits that touched files under that path, so pointing it at crates/bin silently dropped every change made in crates/source, crates/sink, crates/engine, deploy/ or docs/ — half a release's worth of feat: and fix: commits, absent from the notes without a warning anywhere.

What that package versions still lives in the binary crate. extra-files bumps crates/bin/Cargo.toml and the pg2osync entry of Cargo.lock — the lock has to move with it, because every release build runs --locked and a bump without the lock fails the build — and the entry is written into crates/bin/CHANGELOG.md, next to the crate whose version it describes rather than at the repository root, so the version, the notes and the code that was released sit together. The library crates are internal to the workspace and are not published, so their versions are not tracked at all: one package, one tag, one release, one changelog.

merge a feature PR   →  release PR updated. Nothing released.
merge a feature PR   →  release PR updated. Nothing released.
merge the release PR →  versions bumped, changelog written, tag pushed,
                        binaries and the container image published.

The version comes from the commit types: fix: bumps the patch, feat: the minor, and either a ! after the type or a BREAKING CHANGE: trailer bumps the major. To force a number regardless, put Release-As: 1.3.0 in a commit body.

A batch release is the normal mode rather than a special case: whatever accumulated since the last release goes out as one version.

What you have to do

Merge the open pull request titled chore: release …. That is the whole procedure.

Every other pull request is squash-merged, and its title becomes the commit subject on main. That is what release-please reads, so the title has to be a conventional commit — a required check enforces it. Merge commits are switched off: the one that did land (Merge pull request #52 …) made release-please consider zero commits and propose nothing.

Watch the Release workflow afterwards. It builds static binaries for Linux and macOS on x86-64 and arm64, attaches them with checksums, and pushes ghcr.io/kennywillbe/pg2osync tagged with the version and with major.minor. The release body starts as the changelog entry release-please wrote; once the binaries are attached, the workflow appends GitHub's "What's Changed" pull request list under it, once.

The one thing to set up

A fine-grained personal access token belonging to a repository admin, stored as the repository secret RELEASE_PLEASE_TOKEN:

• Repository access: only this repository.
• Permissions: Contents read and write, Pull requests read and write, and Issues read and write. The last one is not obvious and is required: the labels release-please puts on its own pull request (autorelease: pending) go through the issues API, which is how GitHub models labels on a pull request. Metadata read-only is added for you and is mandatory.
• Account permissions: none.
• Expiry: whatever you are willing to renew. When it expires, release pull requests silently stop appearing — the workflow keeps passing because there is nothing for it to do. Put the expiry date somewhere you will see it.

The default GITHUB_TOKEN cannot do this job, and the workflow fails loudly rather than half-releasing if the secret is missing. Two independent reasons:

• The v* tag rule allows only a repository admin to create a tag, and a personal repository cannot grant that bypass to the Actions app — GitHub refuses with "Actor GitHub Actions integration must be part of the ruleset source or owner organization". A token owned by an admin passes.
• GitHub does not start a workflow from an event created by its own GITHUB_TOKEN. A tag pushed that way would appear with nothing built behind it — no binaries, no image, a release that looks finished and is empty. A tag pushed with a personal access token triggers the build normally.

Because the token is yours, the tag and the release are attributed to you rather than to a bot.

Conventional Commits, briefly

Only the subject line is constrained. The body stays whatever the change needs, which for anything non-obvious here means saying why.

feat(mysql): resume from a GTID position

A binlog coordinate only means something on the server it was read from, so a
failover could not resume …

The last row is the one that matters most: what a major version promises here is that a pg2osync.toml keeps loading and a running pipeline does not re-read a table from the start. Breaking either is a major, whatever else the change looks like.

The documentation site

The Docs workflow builds this book out of docs/ with mdBook and publishes it to GitHub Pages on every push to main that touches the documentation. Pull requests build it without publishing, so a broken page or a chapter missing from SUMMARY.md fails there rather than on the live site.

Publishing is switched off while the repository is private, because Pages on this plan is public whatever the repository is, and the docs must not lead the code out. Once the repository is public, one setting starts it:

gh api -X POST repos/kennywillbe/pg2osync/pages -f build_type=workflow

Or Settings → Pages → Source: GitHub Actions. The site is then https://kennywillbe.github.io/pg2osync/, and nothing else has to be done again.