Postgres engine
Postgres is replicare's first and reference engine — the engine the engine-neutral copy / apply / delta / reseed / pipeline / observability layers were designed around. It replicates Postgres → Postgres with trigger-based, no-WAL CDC. MySQL and Redis reuse those same neutral layers; this page documents the Postgres specifics. The shared docs (getting-started, configuration, operations, troubleshooting) describe the baseline behavior using Postgres, so treat them as this engine's extended manual.
Status: shipped. Trigger capture, chunked parallel initial copy (text-faithful
COPY), FK-ordered streaming apply (including cyclic components), delta bloat/retention/reseed, full observability, and the daemon lifecycle all run end to end, and Postgres syncs coexist with MySQL and Redis syncs in one daemon. Try it with the Postgres demo.
Supported versions
- Source floor: PostgreSQL 9.6 (best-effort). Target: a modern major (12+). The
CI matrix runs source 9.6 → target 17. See
postgres-version-support.mdfor the rationale (float fidelity on pre-12, no-native-partition path, old PL/pgSQL portability). - Source SQL — trigger functions, capture DDL, introspection — stays conservative
and version-tolerant so it runs against genuinely old servers.
The target is assumed modern, so the apply path uses
INSERT … ON CONFLICT.
How CDC works (the relational baseline)
Postgres has a durable change stream (the WAL), but replicare deliberately does
not use it — logical replication needs a REPLICATION role and a wal_level
change that managed and locked-down servers won't grant. Instead:
- Capture — in a dedicated
replicareschema on the source, replicare installs per-table delta and track tables andAFTER INSERT/UPDATE/DELETEtriggers. The trigger records only the changed row's primary key (PK-only capture), not the full row image. A key-changingUPDATEenqueues both the old and new PK (= delete-old + upsert-new). - Reconcile — at sync time replicare reads the unconsumed delta rows, re-reads
the current values of those PKs from the live source table, and applies them to
the target. This is self-healing: it always copies the latest value, so
overlapping copy/stream windows converge naturally. Consumption is a
delta MINUS trackset-difference (a dirty-key set, not an ordered log), deleting bydelta_id— which sidesteps the commit-order-≠-assignment-order hazard that a scalar high-water-mark cursor would fall into. - Apply — idempotent upserts keyed on the PK (
INSERT … ON CONFLICT DO UPDATE) and deletes by PK, FK-dependency-ordered within each FK component (upserts parent→child, deletes child→parent), with a retry fallback for cycles. - Checkpoint — progress lives in the Postgres StateStore; a crash safely replays from the last confirmed cursor (applies are idempotent).
Capture is enabled first, then a chunked parallel initial copy runs while changes queue — so there is no single frozen snapshot and "too big to snapshot" is not a problem.
Faithful transport (never transform)
Values move verbatim as text COPY and are validated by the target's own input
functions — replicare never coerces, truncates, casts, or "repairs" data; on a
type/constraint error it halts loud rather than corrupting silently. To keep text round-trippable it pins formatting-only session GUCs on
every connection (no privilege needed): DateStyle=ISO,YMD, TimeZone=UTC,
extra_float_digits=3 (critical for float fidelity on pre-12 sources),
IntervalStyle=postgres, bytea_output=hex, client_encoding=UTF8. A pre-flight
compatibility check classifies every source↔target column pair: block on
incompatible/missing types, warn on risky-lossy (e.g. int8→int4), ok on
identical/widening.
Requirements
- Every replicated table needs a primary key or usable unique key. Tables without one are skipped with a loud warning surfaced in logs and telemetry.
- The target schema must pre-exist. replicare replicates data only — no live DDL. You create/migrate target tables with your own tooling; it introspects them to build correct, name-matched apply statements and never installs or alters target types/extensions/enum labels.
- Cyclic / self-referential FKs are handled by case: a nullable
cycle uses a NULL-then-fill two-pass; a
DEFERRABLEcycle loads underSET CONSTRAINTS … DEFERRED; aNOT NULLnon-deferrable cycle is genuinely unloadable without disabling constraints (which replicare won't do), so pre-flight fails loud and asks you to make the FKDEFERRABLEor break the cycle. - A link column inside a unique index where empty values count as equal (for
example
UNIQUE NULLS NOT DISTINCT (email, parent), or a unique index on an expression such asCOALESCE(parent, 0), or one with aWHEREfilter) cannot be emptied during the two-pass load — rows that differ only by that column would clash and the copy would stop with a duplicate-key error. replicare spots these indexes on both the source and the target (bareCREATE UNIQUE INDEXincluded) and handles them without any schema change:- Table that points at itself (an account tree, a category tree): the column is kept at its real value and the whole table is copied in a single statement, which Postgres accepts because it checks this kind of FK only when the statement ends. Live changes keep the real value too. The trade-off: that one table is copied in one piece rather than in parallel chunks, and a restart re-copies it from the start.
- Loop across two or more tables: there is no safe load order, so pre-flight
stops the sync before anything is copied and asks for the FK to be made
DEFERRABLE.
Installing capture on a busy source
Adding a trigger locks the table for an instant, but if something else already holds a
conflicting lock — a long VACUUM, a migration, or an application session left idle in
transaction — the trigger change has to wait, and while it waits Postgres queues every new
query on that table behind it. replicare therefore:
- leaves an already-correct trigger alone on restart, so a restart takes no lock on any application table;
- waits at most 2 seconds for a lock when it does need to add or replace a trigger, then steps back, retries with a growing pause, and after repeated failures stops with an error that names the table, rather than holding the application up.
If that error appears, look for the session holding the table (pg_stat_activity /
pg_locks), let it finish, and restart replicare.
The Postgres advantage: effectively-exactly-once
replicare's delivery is at-least-once with idempotent apply. For a Postgres target, there is a bonus: when the StateStore is the target database, a cursor advance can ride the same transaction as its apply batch — yielding effectively-exactly-once apply per batch. This is unique to Postgres-target syncs: MySQL and Redis targets are strictly at-least-once because their StateStore is a different engine/server, so no single transaction spans both. (At-least-once is still correct and convergent everywhere — a crash may re-apply the last batch, which is harmless because applies are idempotent upserts.)
Delta bloat, retention & reseed
Because capture writes delta tables to a source you may not own, bounding the
footprint is first-class: per-table delta tables (isolated
bloat/contention), a batched, consumption-gated DELETE purge plus aggressive
per-table autovacuum reloptions set at install time (no superuser, no global
change), and bounded retention (age/size) with forced reseed of an over-cap
target to protect the source. The reseed handoff — marking a target needs-reseed,
resetting its track, re-copying, and resuming without gaps — is specified in
reseed-state-machine.md. Per-target delta backlog,
oldest-unconsumed age, purge rate, and reseed events are all surfaced as telemetry
(see operations).
Least privilege
replicare needs no superuser, no REPLICATION attribute, and no wal_level
change. Ready-to-edit grant SQL ships in
../deploy/grants-source.sql and
../deploy/grants-target.sql.
- Source:
USAGEon each replicated schema;SELECT+TRIGGERon each replicated table; and, to hold the capture machinery, eitherCREATEon the database or a pre-createdreplicareschema owned by the daemon role (the no-database-grant option). Teardown asymmetry:CREATE TRIGGERneeds only theTRIGGERprivilege, butDROP TRIGGER(part ofreplicare capture remove) requires table ownership — a least-privilege role can install and run capture but needs ownership (or the owner's help) to fully uninstall the trigger. Dropping replicare's own delta/track tables and functions works with the daemon role since it owns them. - Target: just
SELECT, INSERT, UPDATE, DELETEon the pre-existing target tables. If the StateStore lives on the target database, also grant itCREATEon that database (or pre-create thereplicare_stateschema owned by the role).
Configuration
A Postgres sync is the engine-neutral envelope with a typed postgres: block per
endpoint. The state store is Postgres too (v1's only StateStore backend) and may be
the target, the source, or a separate database:
state_store: # replicare's own bookkeeping (Postgres)
engine: postgres
postgres: { host: state-db, port: 5432, database: replicare_state, user: replicare, password: "${PW}", sslmode: require }
sources:
app:
engine: postgres
postgres: { host: app-db, port: 5432, database: app, user: replicare, password: "${PW}", sslmode: verify-full }
targets:
warehouse:
engine: postgres
postgres: { host: wh-db, port: 5432, database: warehouse, user: replicare, password: "${PW}", sslmode: require }
syncs:
- name: app-to-warehouse
source: app
targets: [warehouse]
include: ["public.*"] # schema.table globs
exclude: ["*_audit"]
- The Postgres block uses
sslmode(libpq'sdisable | allow | prefer | require | verify-ca | verify-full), matching Postgres's own vocabulary — MySQL and Redis use atlsfield with the same spectrum. databaseis the connection default; selection globs areschema.tableand may span other schemas. FK components are computed over the selected tables and copied/streamed with intra-component ordering and cross-component parallelism; a dominating "giant component" is warned about with a config override available.- Extra libpq parameters can be passed via
params. - The single-engine rule is enforced: a Postgres source's targets must all be Postgres — never cross-engine.
- All neutral knobs (worker/connection caps, drain interval, batch sizes, retention, observability endpoints, logging, env-var overrides) are documented in configuration.
Validating
replicare validate config.yml
connects to both sides, classifies every column pair (block on incompatible,
warn on risky-lossy), reports the FK components with giant-component and
dangling-FK (FK→excluded table) warnings, flags tables without a usable key, and
detects unloadable NOT NULL cyclic FKs — all before any sync starts. See
cli.md for the full command surface (run, validate, status,
capture, reseed).