logical_ddl

Overview

Pigsty carries the upstream RAISE WARNING typo fix for 0.1.0.

Version

Build

You can build the RPM / DEB packages for logical_ddl using pig build:

pig build pkg logical_ddl         # build RPM / DEB packages

Install

You can install logical_ddl directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:

pig repo add pgsql -u          # Add repo and update cache

Install the extension using pig or apt/yum/dnf:

pig install logical_ddl;          # Install for current active PG version

pig ext install -y logical_ddl -v 18  # PG 18
pig ext install -y logical_ddl -v 17  # PG 17
pig ext install -y logical_ddl -v 16  # PG 16
pig ext install -y logical_ddl -v 15  # PG 15
pig ext install -y logical_ddl -v 14  # PG 14

dnf install -y logical_ddl_18       # PG 18
dnf install -y logical_ddl_17       # PG 17
dnf install -y logical_ddl_16       # PG 16
dnf install -y logical_ddl_15       # PG 15
dnf install -y logical_ddl_14       # PG 14

apt install -y postgresql-18-logical-ddl   # PG 18
apt install -y postgresql-17-logical-ddl   # PG 17
apt install -y postgresql-16-logical-ddl   # PG 16
apt install -y postgresql-15-logical-ddl   # PG 15
apt install -y postgresql-14-logical-ddl   # PG 14

Create Extension:

CREATE EXTENSION logical_ddl;

Usage

Source: README

logical_ddl captures a limited set of ALTER TABLE changes, writes them into a replicated shadow table, and replays equivalent DDL on logical-replication subscribers.

Supported DDL

• ALTER TABLE ... RENAME TO ...
• ALTER TABLE ... RENAME COLUMN ... TO ...
• ALTER TABLE ... ADD COLUMN ...
• ALTER TABLE ... ALTER COLUMN ... TYPE ...
• ALTER TABLE ... DROP COLUMN ...

Built-in types, arrays, composite types, domains, and enums are supported as column types, but the extension does not replicate the definitions of those custom types themselves.

Publisher and subscriber setup

CREATE EXTENSION logical_ddl;

-- Publisher
INSERT INTO logical_ddl.settings VALUES (true, 'source1');
INSERT INTO logical_ddl.publish_tablelist (relid) VALUES ('table1'::regclass);
ALTER PUBLICATION log_pub_1 ADD TABLE logical_ddl.shadow_table;

-- Subscriber
INSERT INTO logical_ddl.settings VALUES (false, 'source1');
INSERT INTO logical_ddl.subscribe_tablelist (source, relid)
VALUES ('source1', 'table1'::regclass);
ALTER SUBSCRIPTION log_sub_1 REFRESH PUBLICATION;

Main tables

• logical_ddl.settings: declares publisher/subscriber role and source name.
• logical_ddl.publish_tablelist: tables and command kinds to capture.
• logical_ddl.subscribe_tablelist: target tables and command kinds to replay.
• logical_ddl.shadow_table: replicated command log.
• logical_ddl.applied_commands: replay history and failure tracking.

Caveats

• The extension works under superuser privileges.
• USING expressions for type changes, default expressions, constraints, and indexes are not implemented.
• Pigsty notes an upstream RAISE WARNING typo fix for 0.1.0; that does not change the user-facing usage documented here.