Ready to replicate

Two Catalog databases. Same tables, same columns, same indexes, and a diff that comes back clean. One takes an UPDATE; the other refuses it. One shows the reporting role every invoice; the other shows it none.

The difference isn’t in what the tables look like. It’s in what they let people do: whether a published table can say which row an update is about, and whether row-level security is on with a policy behind it. Both are PostgreSQL state, both are invisible in a column list, and both can be declared, so the package says it and every Catalog environment agrees.

The write PostgreSQL refuses

Publish a table that has no primary key and try to update it:

ERROR:  cannot update table "audit_event" because it does not have a replica identity and publishes updates
HINT:  To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.

A subscriber has to know which row an update or a delete is about. That’s the table’s replica identity, and by default it’s the primary key. No key means no identity, so PostgreSQL refuses the write. The same table, unpublished, takes it without a word.

Declare it:

"ReplicaIdentity": "INDEX",
"ReplicaIdentityIndex": "ux_audit_event_id"

FULL works too. It writes the whole old row to the WAL on every update, which is the expensive way. INDEX over a unique index on NOT NULL columns is the cheap one. The deploy logs Setting replica identity for public.audit_event, and the next deploy leaves it alone.

Row-level security needs a policy

"RowLevelSecurity": true

On its own, that denies everything. In the lab, pub_reader has SELECT on invoice and sees zero rows. The grant says it may read the table; row-level security with no policy says it may read no rows. A policy says which:

"Policies": [
  {
    "Name": "tenant_read",
    "Command": "SELECT",
    "Roles": "pub_reader",
    "UsingExpression": "tenant_id = current_setting('app.tenant_id')::int"
  }
]

The deploy logs Creating policy public.invoice.tenant_read. Edit the expression and you get Altering policy public.invoice.tenant_read: ALTER POLICY, in place, so the table never has security on with the rule missing. PostgreSQL stores the expression in its own rendering, and SchemaSmith compares expressions, not text, so an unchanged policy stays put.

A publication script that keeps up

Publications are scripts, and here’s the trap:

IF NOT EXISTS (SELECT 1 FROM pg_publication WHERE pubname = 'billing_pub') THEN
  CREATE PUBLICATION billing_pub FOR TABLE public.invoice, public.audit_event;
END IF;

Safe to re-run, and deaf to every edit. Add public.refund to that list, deploy, and the script “runs”, but refund isn’t published, because the CREATE never runs again. The fix is a script that says what the publication publishes, every time:

ALTER PUBLICATION billing_pub SET TABLE public.invoice, public.audit_event, public.refund;

You don’t have to remember the form: SchemaTongs extracts every publication that way, a guarded create for a fresh database followed by ALTER PUBLICATION … SET for its tables and options.

A row that arrives

The lab’s replica profile starts a second PostgreSQL. The same package deploys its tables, because logical replication copies rows, not schema. Then one statement subscribes it:

CREATE SUBSCRIPTION billing_sub
  CONNECTION 'host=postgres port=5432 dbname=catalog_repl user=postgres password=…'
  PUBLICATION billing_pub;

The initial sync copies what’s there. Insert a refund and update an audit event on the publisher, and both show up on the subscriber a moment later. The update was identified by ux_audit_event_id, the identity you declared.

Check yourself: After a release, Catalog's production database starts refusing every UPDATE on a table, with 'does not have a replica identity and publishes updates'. Staging, deployed from the same package, takes updates fine. The schemas diff clean. What's different, and what's the fix that keeps it from happening again?

Production publishes the table and staging doesn’t, or production’s publication gained the table in this release. A published table needs a replica identity, and this one has no primary key, so production refuses updates that staging, where it isn’t published, accepts. The column list is identical, which is why the diff is clean.

The durable fix is to declare it: "ReplicaIdentity": "INDEX" with a ReplicaIdentityIndex over a unique NOT NULL index, or FULL if there isn’t one. Then every environment the package reaches gets the same identity, whether it publishes the table today or not.

A casting can be perfect and still not fit the machine it goes into. Replica identity, row-level security and publications are the fittings: they decide what the table lets people do once it’s in place. Leave them to a script someone ran once and two environments drift apart without a single column changing.

Put them in the package. Then the diff means something, and a row written in one place turns up in the other.

Got a replication setup a deploy didn’t handle? Email me at forgebarrett@schemasmith.com. More’s coming from the forge.

Until then, may your writes find their rows, and your rows find their subscribers.

— Forge

Mark this lesson complete