Database Replication

This guide covers the logical replication setup between CRED's PostgreSQL databases and how to resynchronize tables when needed.

Overview

We use pglogical to replicate tables from cred-model (CloudSQL) to cred-model-api-dev.

Only dev is a pglogical subscriber. cred-model-api-staging and cred-model-api-prod have no pglogical extension at all — they are populated by the BigQuery → Postgres ingest, not by this hop. Do not go looking for a subscription there.

Most of this page is now available as a UI: admin.credplatform.com → Data Replication → PGLogical → Postgres shows set membership, per-table sync state, replica identity, slot lag and WAL retained on the provider. It drives four of the operations below — enrol/drop a table on the replication set, toggle the write guard, and resync a table, all against dev only, plus change a table's replica identity, which is the one that is not dev-scoped (it has to land on cred-model as well; see below). The incremental UPDATE sync stays CLI-only: it runs on the provider, which the UI does not write to. The psql procedures are kept as the fallback and as the explanation of what the UI does.

The raw resync procedure below no longer works unchanged on a guarded table

pglogical.alter_subscription_resynchronize_table performs its TRUNCATE in the calling session, so the replica write guard (see below) rejects it with 42501. Use public.resync_replicated_table(...), which lifts the guard for its own call. This is why that helper exists.

Resynchronizing a Table

When a table gets out of sync or needs to be fully reloaded, you can trigger a resynchronization. This process truncates the target table and performs a fresh COPY from the source (cred-model CloudSQL).

1. Resynchronize

Two calls. resync_replicated_table lifts the write guard for its own transaction, truncates the table and starts a fresh COPY from the provider; it does not touch indexes. Dropping them is a separate, optional pre-step:

-- On cred-model-api-dev.
SELECT public.drop_replicated_table_indexes('Person');      -- optional, see below
SELECT public.resync_replicated_table('cred_tables', 'Person');

Both are SECURITY DEFINER functions shipped by the cred-postgres image (sql_scripts/replica-write-guard.sql), so they work for an operator role holding only EXECUTE — DROP INDEX otherwise requires table ownership, which cannot be granted. Both refuse a relation this node does not actually receive from the provider.

Dropping the indexes first is optional but usually right: it stops the COPY paying index maintenance per row. They are recreated automatically — see step 2. Skip it for a small table.

The call returns as soon as pglogical accepts the request; the COPY runs afterwards in the sync worker. Watch progress with:

-- Scoped to the subscription and the relation: this table holds one row per
-- (subscription, relation), plus a subscription-level row with a NULL relname.
SELECT sync_status
FROM pglogical.local_sync_status
WHERE sync_subid  = (SELECT sub_id FROM pglogical.subscription
                      WHERE sub_name = 'cred_tables')
  AND sync_nspname = 'public'
  AND sync_relname = 'Person';

-- Rows copied so far. pg_stat_progress_copy has one row per active COPY
-- backend, and filtering on the relation is NOT enough to isolate the resync:
-- the BQ -> Postgres ingester runs several concurrent COPY FROM backends on this
-- same database. Join pg_stat_activity so you can see whose copy each row is —
-- pglogical's sync worker is a background worker, the ingester is a client
-- backend (observed on model-api-dev: five concurrent client-backend copies with
-- application_name 'PostgreSQL JDBC Driver').
SELECT p.pid, a.backend_type, a.application_name, p.type, p.tuples_processed
FROM pg_stat_progress_copy p
LEFT JOIN pg_stat_activity a ON a.pid = p.pid
WHERE p.command = 'COPY FROM'
  AND p.relid = 'public."Person"'::regclass;

On the deployed version (pglogical 2.4.6; verified against 2.4.8):

sync_status	meaning
r	ready — the table is caught up and streaming. The state to wait for.
y	synchronized at its recorded LSN; the worker is handing over to normal apply.
i s d c w u	init / structure / data / constraints / sync-wait / catch-up — a sync in flight.

A status that stops changing is not the same as one that finished: if it sits on a non-r value, check the subscriber's pglogical worker logs before assuming progress. No row at all means no per-table sync has ever run — streamed changes still arrive, but the table was never seeded by an initial COPY.

Rows, not bytes

pg_stat_progress_copy.bytes_total is 0 for COPY FROM stdin, which is what pglogical's sync worker runs — so a byte percentage is never available. Compare tuples_processed against the provider's row estimate instead.

The raw procedure, for a node without the helper functions

Older subscribers predate the image that ships the helpers. There, generate and review the `DROP INDEX` statements by hand, then call pglogical directly — wrapping it in the escape hatch, or it will be rejected on any guarded table:

-- Non-constraint indexes only: DROP INDEX refuses the others anyway.
SELECT format('DROP INDEX IF EXISTS %I.%I;', n.nspname, i.relname)
FROM pg_index x
JOIN pg_class i ON i.oid = x.indexrelid
JOIN pg_class t ON t.oid = x.indrelid
JOIN pg_namespace n ON n.oid = t.relnamespace
WHERE n.nspname = 'public' AND t.relname = 'Person'
  AND NOT x.indisprimary
  AND NOT EXISTS (SELECT 1 FROM pg_constraint c WHERE c.conindid = i.oid);

BEGIN;
  SET LOCAL cred.allow_replica_write = 'on';
  SELECT pglogical.alter_subscription_resynchronize_table(
    subscription_name := 'cred_tables',
    relation          := 'public."Person"'
  );
COMMIT;

2. Index Recreation

After the COPY completes, indexes are recreated automatically by the scheduled schema sync script:

cred-postgres/scripts/schedule-schema-sync.sh

No manual action is required for index recreation.

Manual Sync (Incremental)

Instead of resynchronizing entire tables from scratch, you can force pglogical to re-replicate only recently changed rows. This is a lightweight approach that triggers replication by performing a no-op update on the updatedAt column, which pglogical detects as a change and replicates to cred-model-api-dev.

1. Generate the Update Queries

Run this on cred-model (CloudSQL). Set the date to the point from which you want to sync changes:

SELECT string_agg(format(
  $fmt$SELECT '%s' AS "table",
       min(date_trunc('day', "updatedAt")) AS updated_at,
       count(*) AS cnt,
       %L || '''' || min(date_trunc('day', "updatedAt"))::varchar || ''';' AS update_query
  FROM %s
  WHERE "updatedAt" >= '2026-03-20'
  GROUP BY 1$fmt$,
  set_reloid::varchar,
  format('UPDATE %s SET "updatedAt" = "updatedAt" WHERE "updatedAt" >= ', set_reloid::varchar),
  set_reloid::varchar
), E'\nUNION ALL\n')
FROM pglogical.replication_set_table
ORDER BY 1;

Tip

Replace '2026-03-20' with the date from which you want to start syncing.

2. Review the Output

The query returns a result set showing each table, its earliest updatedAt, the row count, and the UPDATE statement to execute. Example output:

table	updated_at	cnt	update_query
"Company"	2026-03-20	1500	UPDATE "Company" SET "updatedAt" = "updatedAt" WHERE "updatedAt" >= '2026-03-20 00:00:00+00';
"SoccerPlayer"	2026-03-20	800	UPDATE "SoccerPlayer" SET "updatedAt" = "updatedAt" WHERE "updatedAt" >= '2026-03-20 00:00:00+00';

3. Execute the Update Statements

Copy the update_query values from the output and run them on cred-model (CloudSQL). For example:

UPDATE "Company" SET "updatedAt" = "updatedAt" WHERE "updatedAt" >= '2026-03-20 00:00:00+00';
UPDATE "SoccerPlayer" SET "updatedAt" = "updatedAt" WHERE "updatedAt" >= '2026-03-20 00:00:00+00';
UPDATE "SoccerTeam" SET "updatedAt" = "updatedAt" WHERE "updatedAt" >= '2026-03-26 00:00:00+00';
-- ... remaining tables

These no-op updates touch the updatedAt column without changing its value, which is enough to trigger pglogical to replicate those rows to cred-model-api-dev.

Warning

Review the row counts (cnt column) before executing. Large tables may take longer to process on the publisher side.

Tracking Replication Progress

To quickly check row counts on a table without running a full SELECT count(*) (which can be slow on large tables), use the PostgreSQL statistics estimate:

SELECT relname AS table_name,
       reltuples::bigint AS estimated_rows
FROM pg_class
WHERE relname IN ('Person', 'Company', 'CompanyIdentifier')
ORDER BY relname;

Run this on both cred-model (CloudSQL) and cred-model-api-dev to compare counts and verify replication is catching up.

Tip

reltuples is updated by ANALYZE and after bulk operations like COPY. It may lag slightly behind the actual count, but is fast enough to poll repeatedly during a long-running resync.

reltuples = -1 means never analyzed, not zero

A table that has never been analyzed reports -1, not its real count (PostgreSQL 14+ — older versions report 0 for the same state, which is even easier to misread as "empty"). Read that as 0 and a fully-seeded table looks like it lost every row. ANALYZE "<table>" gives it a real estimate (a SHARE UPDATE EXCLUSIVE lock — concurrent with reads and writes, blocks only another ANALYZE/VACUUM on the same table).

Replica Identity — Change It On BOTH Databases

A table's replica identity decides which columns identify a row in the WAL. It is not a provider-only setting: change it on cred-model alone and replication keeps reporting healthy while quietly losing deletes.

Measured on pglogical 2.4.6/2.4.8 (Postgres 15), provider on REPLICA IDENTITY USING INDEX ("ADCompany_providerUniqueId_key"), dev subscriber left on DEFAULT:

Operation on the provider	Applied on the subscriber?
INSERT	yes
UPDATE of a non-key column	yes
UPDATE that changes the key	no
DELETE	no — silently skipped

The skipped DELETE appears only in the subscriber's Postgres log:

CONFLICT: remote DELETE on relation public."ADCompany"
replica identity index ADCompany_pkey (tuple not found). Resolution: skip.

Everything an operator normally looks at says fine: pglogical.show_subscription_status() returns replicating, replication lag stays at 0, no error is raised to the provider, and no counter increments. The subscriber simply keeps a row the provider no longer has — permanently. Aligning the identity afterwards fixes subsequent deletes but does not remove the rows already missed; only a resync repairs those.

The cause is that the subscriber locates the target row through its own replica identity index, not the provider's.

No safe ordering exists — prefer provider-first

Reversing it does not help — the same test with the subscriber moved to the new index first, provider still on DEFAULT, skips the DELETE in exactly the same way (... replica identity index t3_uid_unique (tuple not found). Resolution: skip.). A record applies correctly only when the provider's identity at the time it was written matches the subscriber's identity at the time it is applied, and two separate ALTERs cannot guarantee that while writes continue.

So: change the table while it is quiet, or accept that a DELETE or key-changing UPDATE in the gap is lost and repair it with a resync.

Change the provider first when you have the choice. It is not safe, only less exposed: anything already in flight still carries the old key and still matches the old subscriber index, so a caught-up subscriber only risks writes made inside the gap. Subscriber-first retroactively poisons the whole in-flight backlog.

Procedure

1. Check every subscriber has a unique index over the same columns first. The index name may differ — subscriber schemas are re-derived, not copied — so match on columns, not names. Create it before starting; a change only one side can use is worse than no change. Run this on each database and compare the key_columns values, not the index names:

   SELECT n.nspname AS schema, t.relname AS "table", i.relname AS index_name,
          string_agg(a.attname, ', ' ORDER BY k.ord) AS key_columns
     FROM pg_index x
     JOIN pg_class i ON i.oid = x.indexrelid
     JOIN pg_class t ON t.oid = x.indrelid
     JOIN pg_namespace n ON n.oid = t.relnamespace
     -- key columns only: an INCLUDE column is not part of the identity.
     JOIN unnest(x.indkey) WITH ORDINALITY AS k(attnum, ord)
       ON k.ord <= x.indnkeyatts
     -- an inner join drops expression keys (attnum 0), which Postgres refuses
     -- as a replica identity anyway.
     JOIN pg_attribute a ON a.attrelid = x.indrelid AND a.attnum = k.attnum
    WHERE t.relname = 'ADCompany'   -- substitute your table
      AND x.indisunique AND x.indisvalid
      AND x.indpred IS NULL         -- non-partial
      AND x.indimmediate            -- non-deferrable
    GROUP BY n.nspname, t.relname, i.relname
   HAVING bool_and(a.attnotnull)    -- every key column NOT NULL
    ORDER BY index_name;
   

   A key_columns value that appears on both sides is a usable identity. Note x.indimmediate is true for a non-deferrable index — inverting it returns nothing. 2. Alter the provider, then each subscriber, back to back. The gap is the exposure. 3. Back the change into cred-model's migrations. The provider's schema is versioned there, so an out-of-band ALTER is undone by the next migration that recreates the table. 4. Watch the subscriber's log for CONFLICT ... Resolution: skip while writes are flowing. That line is the only evidence a row was dropped.

-- Substitute your own table and index: "ADCompany" and
-- "ADCompany_providerUniqueId_key" are examples, and the index must be the one
-- whose columns should identify rows in the WAL (unique, valid, non-partial,
-- non-deferrable, all key columns NOT NULL).
ALTER TABLE public."ADCompany"
  REPLICA IDENTITY USING INDEX "ADCompany_providerUniqueId_key";

-- Verify: 'd' = default (primary key), 'i' = using index, 'f' = full, 'n' = nothing.
SELECT relreplident FROM pg_class WHERE oid = 'public."ADCompany"'::regclass;

Never set FULL or NOTHING on a subscriber — it stops every table

pglogical's apply worker needs an index-based identity on the subscriber — DEFAULT (the primary key) or USING INDEX — to locate the row an UPDATE/DELETE names. NOTHING gives it no identity at all; FULL writes the whole row to the WAL, which helps the provider side but still leaves the subscriber with no index to search by. Either way the first UPDATE/DELETE to arrive for that table kills the worker:

ERROR:  could not find REPLICA IDENTITY index for table T3 with oid 16764
HINT:  The REPLICA IDENTITY index is usually the PRIMARY KEY. ...
LOG:  apply worker [3884] at slot 2 generation 2 exiting with error

pglogical then stops restarting it and the whole subscription goes down — in testing, an untouched second table stopped applying and a table mid-initial-COPY never finished. Restoring an index identity did not heal it. Recovery:

SELECT pglogical.alter_subscription_disable('cred_tables', true);
SELECT pglogical.alter_subscription_enable('cred_tables', true);

after which the backlog drains normally. FULL on the provider, with an index identity on the subscriber, replicates fine — it is a provider-side choice only.

DEFAULT is not safe either, if the subscriber has no real PRIMARY KEY

Same failure, reached without anyone choosing FULL or NOTHING. DEFAULT means "the primary key", and a subscriber table whose primary key is actually a unique index that happens to be named <Table>_pkey has no primary key at all — pg_index.indisprimary is false, so DEFAULT resolves to nothing. This is easy to miss because \d shows an index with the expected name, and the schema-sync loop creates indexes rather than constraints.

Both live examples on model-api-dev were found this way, out of 114 replicated tables:

table	relreplident	problem
public."ARLCompany"	i	nominated index missing (it was dropped)
public."DataDescription"	d	no PRIMARY KEY — DataDescription_pkey is a plain unique index

Find them with:

SELECT n.nspname||'.'||c.relname AS relation, c.relreplident
  FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
 WHERE c.relkind = 'r' AND c.relpersistence = 'p'
   AND ( c.relreplident IN ('n','f')
      OR (c.relreplident = 'i' AND NOT EXISTS (
            SELECT 1 FROM pg_index x WHERE x.indrelid = c.oid AND x.indisreplident))
      OR (c.relreplident = 'd' AND NOT EXISTS (
            SELECT 1 FROM pg_index x WHERE x.indrelid = c.oid AND x.indisprimary)) );

Cross-reference it against pglogical.replication_set_table on the provider to see which ones actually matter — pglogical.local_sync_status on the subscriber is not the set membership and lists far fewer tables.

Recovering a subscription stopped this way

Measured end to end, reproducing DataDescription's exact shape:

operation	result
initial COPY	fine
INSERT	fine — no key needed
UPDATE	not applied, subscription → down
DELETE	not applied
an unrelated table	stopped applying
ALTER … REPLICA IDENTITY USING INDEX alone	still down
… then disable + enable	replicating, backlog drained, everything caught up

Two things follow. Nothing is lost — unlike an identity mismatch, where changes are silently skipped and only a resync repairs them, here the changes stay queued and apply once the identity is fixed. And the ALTER alone is not enough: the apply worker only comes back on alter_subscription_disable + _enable.

The PGLOGICAL → Postgres tab in admin.credplatform.com/data-replication does all of this: it lists each table's eligible unique indexes, marks which subscribers already have a matching one, refuses a change no subscriber can follow, keeps FULL/NOTHING provider-only, and applies the accepted ones provider-first with the smallest gap it can.

Replica Write Guard

A subscriber is a one-way copy: pglogical replicates provider → subscriber only. A row written directly on cred-model-api-dev is invisible to the provider, is silently overwritten the next time the provider touches that row, and — for an INSERT that collides on the primary key — stalls the apply worker with a conflict. The guard turns those writes into a loud error instead of silent drift.

Toggle it from Data Replication → PGLogical → Postgres (select rows → Write guard), or by hand:

-- On the subscriber.
SELECT public.protect_replicated_table('DataFilterSettings');    -- block direct writes
SELECT public.unprotect_replicated_table('DataFilterSettings');  -- allow them again

A guarded write then fails with 42501 and a message naming the provider as the place to write instead.

The escape hatch

For a genuine out-of-band repair, lift the guard for one transaction:

BEGIN;
  SET LOCAL cred.allow_replica_write = 'on';
  UPDATE "DataFilterSettings" SET ... WHERE ...;
COMMIT;

SET LOCAL, not plain SET: these databases are reached through PgBouncer in transaction pooling mode, where a session-level SET lands on an arbitrary backend and leaks to unrelated clients. Any spelling Postgres accepts for a boolean true works (on, true, t, yes, y, 1, any case, surrounding whitespace ignored) — o does not, because it cannot distinguish on from off.

Why it does not break replication

pglogical's apply and initial-COPY workers run with session_replication_role = 'replica'. Triggers created the normal way are ORIGIN triggers, which by definition do not fire in that mode — so replicated changes flow through untouched while ordinary client sessions hit the guard.

This makes pg_trigger.tgenabled load-bearing. Check it after any manual trigger surgery:

SELECT c.relname, t.tgname, t.tgenabled
FROM pg_trigger t
JOIN pg_class c ON c.oid = t.tgrelid
WHERE NOT t.tgisinternal
  AND t.tgfoid = 'public.reject_replicated_table_write'::regproc
ORDER BY 1, 2;

tgenabled	meaning
O	ORIGIN — the only correct value. Blocks client writes, invisible to pglogical.
D	Disabled. The guard is installed but blocks nothing.
A / R	ALWAYS / REPLICA — fires during pglogical apply and will stall the subscription. Fix immediately.

Expect two rows per protected table: the row-level guard (INSERT/UPDATE/DELETE) and a separate statement-level one for TRUNCATE, which row triggers do not cover. One row means half the guard is missing — re-running protect_replicated_table restores both.

Never ALTER TABLE ... ENABLE ALWAYS TRIGGER on a guarded table. Re-running protect_replicated_table repairs a guard left in any of the wrong states. The admin UI surfaces anything that is not O as an incident.

Long table names

Postgres truncates identifiers at 63 bytes. protect_replicated_table shortens the table portion of its trigger names to fit, so the two names stay distinct at any length. The exact thresholds, since the two suffixes differ in length:

table name length	what happens
≤ 40 chars	both names unchanged
41–43 chars	only _reject_direct_truncate (23 chars) is shortened
≥ 44 chars	both are shortened

Do not construct the names yourself by appending the suffix and letting Postgres truncate. From 44 characters both collapse to the same identifier, and since the function drops each name before creating it, the second CREATE TRIGGER silently removes the first — leaving row-level writes unguarded on a table that reports as protected. Multibyte names overflow sooner, because the limit is bytes rather than characters.

---

Related Documentation

• Database Backup Restoration — Point-in-time recovery via CloudSQL Clone
• Connect to Cloud SQL — How to connect to CloudSQL instances
• Data Architecture — Database structure and data management