Replicating DDL Changes with Event Triggers

Event triggers let you intercept DDL on the publisher and propagate or alert on it, filling the gap left by core PostgreSQL never replicating schema changes through the logical stream itself — a limitation framed in online schema change management. It matters because manual two-sided coordination does not scale: on a database with dozens of captured tables and frequent migrations, a single forgotten subscriber-side ALTER halts apply, and an automated capture-and-forward path turns that class of outage into a reconciled event.

An EVENT TRIGGER is a server-side hook that fires on DDL rather than on row changes, giving a PL/pgSQL function a chance to inspect what just happened to the catalog. The catch is that PostgreSQL hands you the command’s identity — its tag, the objects it touched — but not a reliable, executable reconstruction of the SQL. That is the deparse problem, and it is the reason DDL replication is still a build-it-carefully pattern or an extension rather than a checkbox on the subscription.

Firing-Point Semantics #

Choosing the firing point is the first design decision, because each exposes a different introspection surface and sees the catalog at a different moment. An event trigger created without a WHEN TAG filter fires for every supported command, so scope it to the command tags you actually replicate.

For capturing schema changes to forward to a subscriber, ddl_command_end is the workhorse: pg_event_trigger_ddl_commands() returns one row per affected object with its object_identity, command_tag, and object type. sql_drop is its complement for DROP, since a dropped object is gone from the catalog by the time ddl_command_end could look. table_rewrite is the safety valve — it is where you refuse a rewriting ALTER COLUMN ... TYPE on a captured table before it floods the slot, using the same rewrite-avoidance logic behind rolling schema migrations with active CDC slots.

The Deparse Problem and a Capture Table #

The reason you cannot simply grab the SQL and replay it is that no in-core function returns a reconstructed, schema-qualified, executable statement from the parse tree. current_query() returns the entire submitted string — which may be several statements, may come from inside a function body, and is not search-path-safe on the subscriber. Robust command deparse (turning the parse tree back into canonical SQL) lives in extensions, not core: the publication and subscription models documentation is explicit that DDL is not part of the logical stream, and reconstructing it faithfully is exactly the hard part.

The pragmatic pattern is a capture table that is itself an ordinary replicated table. The event trigger writes a normalized record of each DDL event into ddl_audit; because that table is in your publication, the record flows to the subscriber through the normal change stream, in commit order relative to the data. A small apply-side worker reads new ddl_audit rows and either executes the reconstructed statement or pages a human.

-- PUBLISHER: an ordinary table that rides the existing logical stream.
CREATE TABLE ddl_audit (
    id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    captured_at  timestamptz NOT NULL DEFAULT clock_timestamp(),
    command_tag  text NOT NULL,
    object_ident text,
    ddl_text     text NOT NULL       -- best-effort reconstruction, see below
);

CREATE OR REPLACE FUNCTION capture_ddl() RETURNS event_trigger
LANGUAGE plpgsql AS $$
DECLARE r record;
BEGIN
    FOR r IN SELECT * FROM pg_event_trigger_ddl_commands() LOOP
        -- Skip our own audit table and anything not in the replicated set.
        IF r.object_identity LIKE 'public.ddl_audit%' THEN CONTINUE; END IF;
        INSERT INTO ddl_audit (command_tag, object_ident, ddl_text)
        VALUES (r.command_tag, r.object_identity, current_query());
    END LOOP;
END;
$$;

CREATE EVENT TRIGGER trg_capture_ddl ON ddl_command_end
    WHEN TAG IN ('ALTER TABLE', 'CREATE TABLE', 'CREATE INDEX')
    EXECUTE FUNCTION capture_ddl();

Storing current_query() is a deliberate compromise: it is readable and good enough to alert a human or to replay in the common single-statement additive case, but it is not safe to auto-execute blindly. Constrain what you auto-apply to additive, non-rewriting tags and route everything else to review. For production-grade automatic deparse and apply, the pgl_ddl_deploy extension builds on exactly this event-trigger mechanism and handles command deparse, filtering, and subscriber execution; pglogical carries a similar facility. Reach for one of those before hand-rolling a full deparser.

Applying Captured DDL on the Subscriber #

The subscriber side reads unseen ddl_audit rows and applies only what is provably safe. Apply must run outside the logical apply worker’s own transaction, and it must respect the additive-first ordering — a captured ADD COLUMN is safe to apply early on the subscriber, a captured DROP COLUMN must wait until the publisher has stopped sending it.

# Subscriber-side DDL applier: idempotent, additive-only auto-apply, else alert.
import psycopg2

ADDITIVE = ("ADD COLUMN",)           # tags safe to apply automatically

def apply_pending_ddl(dsn: str, last_id: int) -> int:
    conn = psycopg2.connect(dsn); conn.autocommit = True
    cur = conn.cursor()
    cur.execute(
        "SELECT id, command_tag, ddl_text FROM ddl_audit "
        "WHERE id > %s ORDER BY id", (last_id,),
    )
    for ddl_id, tag, ddl_text in cur.fetchall():
        if any(k in ddl_text.upper() for k in ADDITIVE):
            try:
                cur.execute(ddl_text)            # additive: safe to replay
            except psycopg2.errors.DuplicateColumn:
                pass                             # already applied → idempotent
        else:
            alert(f"DDL {ddl_id} ({tag}) needs manual review: {ddl_text}")
        last_id = ddl_id
    return last_id

Idempotency matters because the applier will re-see the same ddl_audit rows after a restart: swallow DuplicateColumn/DuplicateTable so a replay converges instead of erroring, and track last_id durably. Anything destructive or rewriting is never auto-executed — it is surfaced to an operator who sequences it with the publisher, because getting that order wrong is what halts the apply worker and pins the slot.

Safety Limits and Version Direction #

Event triggers carry hard constraints you must design around. They require superuser to create; they do not fire for shared-object commands (roles, databases, tablespaces) or for the internal DDL the apply worker performs; and a poorly written trigger that raises an exception will block the DDL that fired it, so a capture bug can freeze all migrations on the publisher. Keep the trigger body minimal, defensive, and fast — an INSERT and nothing that can fail on ordinary DDL. Because triggers fire only on the local node, the subscriber needs its own applier; the trigger does not somehow reach across the connection.

Two operational details prevent capture loops and stuck maintenance. First, the ddl_audit table rides the same commit-ordered stream as the data, so a captured ADD COLUMN arrives at the subscriber in the correct position relative to the rows that use it — you do not have to build your own ordering. But the subscriber must not itself capture the DDL its applier runs, or you create a feedback loop; set session_replication_role = replica in the applier’s session (or on the apply worker), which suppresses ordinary triggers and is also why the logical apply worker’s own writes never re-fire your capture trigger. Second, keep an escape hatch for emergency DDL: ALTER EVENT TRIGGER trg_capture_ddl DISABLE lets a DBA run an out-of-band change without the capture path interfering, and a paired ENABLE restores it — wrap both in your migration tooling so the trigger is never left disabled silently, which would let a schema change slip past the subscriber unrecorded. Test the whole path in staging against the exact command tags you intend to auto-apply; an event trigger that works for ADD COLUMN can still surprise you on a multi-command ALTER TABLE that both adds and drops in one statement.

As of PostgreSQL 17 the core project still does not replicate DDL through the logical stream — the long-running DDL-replication work remains out of tree, though the deparse groundwork it depends on continues to mature. PG 17 does add pg_createsubscriber and failover-slot synchronization, which reduce how often you rebuild subscriptions but do nothing for schema propagation. The practical stance today: rely on additive-first manual coordination for low DDL volume, and adopt an event-trigger extension such as pgl_ddl_deploy once migration frequency makes the manual path an outage risk. Watch the release notes — when in-core DDL replication lands, it will supersede this pattern, but the firing-point semantics above will remain how you reason about what fires when.

Authoritative references #

• PostgreSQL: Event Triggers — firing matrix, WHEN filters, and the introspection functions used above.
• PostgreSQL: Event Trigger Functions — pg_event_trigger_ddl_commands(), pg_event_trigger_dropped_objects(), and the rewrite functions.
• PostgreSQL: CREATE EVENT TRIGGER — supported events, tag filtering, and ownership requirements.

Related #

• Online Schema Change Management — the parent guide on coordinating DDL across publisher and subscriber by hand.
• Rolling Schema Migrations with Active CDC Slots — the expand/contract sequence whose steps an event-trigger capture layer can automate.
• Publication & Subscription Models — why DDL is outside the logical stream and how the replicated table set is defined.