pgl_ddl_deploy
pgl_ddl_deploy
pgl_ddl_deploy : automated ddl deployment using pglogical
Overview
| ID | Extension | Package | Version | Category | License | Language |
|---|---|---|---|---|---|---|
| 9520 | pgl_ddl_deploy | pgl_ddl_deploy | 2.2.1 | ETL | MIT | C |
| Attribute | Has Binary | Has Library | Need Load | Has DDL | Relocatable | Trusted |
|---|---|---|---|---|---|---|
--s-d-- | No | Yes | No | Yes | no | no |
| Relationships | |
|---|---|
| Schemas | pgl_ddl_deploy |
| Requires | pglogical |
| See Also | pglogical_origin pglogical_ticker ddlx pg_permissions pg_failover_slots pgactive wal2json decoderbufs |
Packages
| Type | Repo | Version | PG Major Compatibility | Package Pattern | Dependencies |
|---|---|---|---|---|---|
| EXT | PGDG | 2.2.1 | 18 17 16 15 14 | pgl_ddl_deploy | pglogical |
| RPM | PGDG | 2.2.1 | 18 17 16 15 14 | pgl_ddl_deploy_$v | pglogical_$v |
| DEB | PGDG | 2.2.1 | 18 17 16 15 14 | postgresql-$v-pgl-ddl-deploy | postgresql-$v-pglogical |
| Linux / PG | PG18 | PG17 | PG16 | PG15 | PG14 |
|---|---|---|---|---|---|
el8.x86_64 | MISS | PGDG 2.2.1 | PGDG 2.2.1 | PGDG 2.2.1 | PGDG 2.2.1 |
el8.aarch64 | MISS | PGDG 2.2.1 | PGDG 2.2.1 | PGDG 2.2.1 | PGDG 2.2.1 |
el9.x86_64 | MISS | PGDG 2.2.1 | PGDG 2.2.1 | PGDG 2.2.0 | PGDG 2.2.0 |
el9.aarch64 | MISS | PGDG 2.2.1 | PGDG 2.2.1 | PGDG 2.2.1 | PGDG 2.2.1 |
el10.x86_64 | MISS | PGDG 2.2.1 | PGDG 2.2.1 | PGDG 2.2.1 | PGDG 2.2.1 |
el10.aarch64 | MISS | PGDG 2.2.1 | PGDG 2.2.1 | PGDG 2.2.1 | PGDG 2.2.1 |
d12.x86_64 | MISS | PGDG 2.2.1 | PGDG 2.2.1 | PGDG 2.2.1 | PGDG 2.2.1 |
d12.aarch64 | MISS | PGDG 2.2.1 | PGDG 2.2.1 | PGDG 2.2.1 | PGDG 2.2.1 |
d13.x86_64 | MISS | PGDG 2.2.1 | PGDG 2.2.1 | PGDG 2.2.1 | PGDG 2.2.1 |
d13.aarch64 | MISS | PGDG 2.2.1 | PGDG 2.2.1 | PGDG 2.2.1 | PGDG 2.2.1 |
u22.x86_64 | MISS | PGDG 2.2.1 | PGDG 2.2.1 | PGDG 2.2.1 | PGDG 2.2.1 |
u22.aarch64 | MISS | PGDG 2.2.1 | PGDG 2.2.1 | PGDG 2.2.1 | PGDG 2.2.1 |
u24.x86_64 | MISS | PGDG 2.2.1 | PGDG 2.2.1 | PGDG 2.2.1 | PGDG 2.2.1 |
u24.aarch64 | MISS | PGDG 2.2.1 | PGDG 2.2.1 | PGDG 2.2.1 | PGDG 2.2.1 |
Source
Install
Make sure PGDG repo available:
pig repo add pgdg -u # add pgdg repo and update cacheInstall this extension with pig:
pig install pgl_ddl_deploy; # install via package name, for the active PG version
pig install pgl_ddl_deploy -v 17; # install for PG 17
pig install pgl_ddl_deploy -v 16; # install for PG 16
pig install pgl_ddl_deploy -v 15; # install for PG 15
pig install pgl_ddl_deploy -v 14; # install for PG 14Create this extension with:
CREATE EXTENSION pgl_ddl_deploy CASCADE; -- requires pglogicalUsage
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;Last updated on