pgl_ddl_deploy
automated ddl deployment using pglogical
Overview
| Package | Version | Category | License | Language |
|---|---|---|---|---|
pgl_ddl_deploy | 2.2.1 | ETL | MIT | C |
| ID | Extension | Bin | Lib | Load | Create | Trust | Reloc | Schema |
|---|---|---|---|---|---|---|---|---|
| 9520 | pgl_ddl_deploy | No | Yes | No | Yes | No | No | pgl_ddl_deploy |
| Related | pglogical pglogical_origin pglogical_ticker ddlx pg_permissions pg_failover_slots pgactive wal2json decoderbufs |
|---|
Version
| Type | Repo | Version | PG Ver | Package | Deps |
|---|---|---|---|---|---|
| EXT | PGDG | 2.2.1 | 1817161514 | pgl_ddl_deploy | pglogical |
| RPM | PGDG | 2.2.1 | 1817161514 | pgl_ddl_deploy_$v | pglogical_$v |
| DEB | PGDG | 2.2.1 | 1817161514 | postgresql-$v-pgl-ddl-deploy | postgresql-$v-pglogical |
Install
You can install pgl_ddl_deploy directly. First, make sure the PGDG repository is added and enabled:
pig repo add pgdg -u # Add PGDG repo and update cache
Install the extension using pig or apt/yum/dnf:
pig install pgl_ddl_deploy; # Install for current active PG version
pig ext install -y pgl_ddl_deploy -v 17 # PG 17
pig ext install -y pgl_ddl_deploy -v 16 # PG 16
pig ext install -y pgl_ddl_deploy -v 15 # PG 15
pig ext install -y pgl_ddl_deploy -v 14 # PG 14
dnf install -y pgl_ddl_deploy_17 # PG 17
dnf install -y pgl_ddl_deploy_16 # PG 16
dnf install -y pgl_ddl_deploy_15 # PG 15
dnf install -y pgl_ddl_deploy_14 # PG 14
apt install -y postgresql-17-pgl-ddl-deploy # PG 17
apt install -y postgresql-16-pgl-ddl-deploy # PG 16
apt install -y postgresql-15-pgl-ddl-deploy # PG 15
apt install -y postgresql-14-pgl-ddl-deploy # PG 14
Create Extension:
CREATE EXTENSION pgl_ddl_deploy CASCADE; -- requires: pglogical
Usage
Transparent DDL replication for PostgreSQL 9.5+ supporting both pglogical and native logical replication. Automatically propagates DDL changes (CREATE TABLE, ALTER TABLE, etc.) to subscribers.
Enabling
CREATE EXTENSION pgl_ddl_deploy;
Configuration
Insert configuration into the pgl_ddl_deploy.set_configs table:
-- Replicate DDL for all user schemas and auto-add new tables
INSERT INTO pgl_ddl_deploy.set_configs (set_name, include_schema_regex, driver)
VALUES ('default', '.*', 'native'::pgl_ddl_deploy.driver);
-- Or with pglogical driver
INSERT INTO pgl_ddl_deploy.set_configs (set_name, include_schema_regex, driver)
VALUES ('default', '.*', 'pglogical'::pgl_ddl_deploy.driver);
-- Maintain only specific tables already in replication (ALTER TABLE only)
INSERT INTO pgl_ddl_deploy.set_configs (set_name, include_only_repset_tables, driver)
VALUES ('my_tables', TRUE, 'native'::pgl_ddl_deploy.driver);
Deploy Event Triggers
After configuring, deploy the event triggers:
SELECT pgl_ddl_deploy.deploy(set_config_id) FROM pgl_ddl_deploy.set_configs;
Key Configuration Options
driver:nativeorpglogicalset_name: publication name or pglogical replication set nameinclude_schema_regex: regex to match schemas for DDL replicationinclude_only_repset_tables: if true, only ALTER TABLE for tables already in replicationlock_safe_deployment: if true, DDL executes in a low lock_timeout loop on subscriberallow_multi_statements: if true, multi-statement DDL can be propagatedqueue_subscriber_failures: if true, failed DDL on subscriber is queued for retryddl_only_replication: replicate schema only without auto-adding tables to data replication
Monitoring
-- View unhandled DDL events
SELECT * FROM pgl_ddl_deploy.unhandled;
-- View failed subscriber DDL
SELECT * FROM pgl_ddl_deploy.subscriber_logs WHERE NOT succeeded;
-- Retry failed DDL on subscriber
SELECT pgl_ddl_deploy.retry_all_subscriber_logs();
Checking Resolved Schemas
SELECT pgl_ddl_deploy.resolved_regex_include_schemas(set_config_id)
FROM pgl_ddl_deploy.set_configs;
Feedback
Was this page helpful?
Thanks for the feedback! Please let us know how we can improve.
Sorry to hear that. Please let us know how we can improve.