PostgreSQL 10逻辑复制：能否自动克隆新增表？

Great question! PostgreSQL 10's logical replication works reliably for data sync, but it doesn't handle automatic DDL (like new table creation) out of the box. Here are practical solutions to automate this process:

1. Custom Trigger-Based DDL Sync #

You can build a custom system to capture DDL on the primary and replay it on the standby. Here's a step-by-step breakdown:

• Step 1: Create a DDL log table on the primary
  This table will store all CREATE TABLE statements for replication to the standby:

  CREATE TABLE ddl_log (
      id SERIAL PRIMARY KEY,
      ddl_stmt TEXT NOT NULL,
      executed BOOLEAN DEFAULT FALSE,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
  );
  

• Step 2: Build an event trigger function on the primary
  This function captures CREATE TABLE events and logs them:

  CREATE OR REPLACE FUNCTION log_create_table_ddl()
  RETURNS event_trigger AS $$
  BEGIN
      IF TG_TAG = 'CREATE TABLE' THEN
          INSERT INTO ddl_log (ddl_stmt)
          VALUES (current_query());
      END IF;
  END;
  $$ LANGUAGE plpgsql;
  

• Step 3: Attach the event trigger on the primary
  This triggers the function whenever a table is created:

  CREATE EVENT TRIGGER capture_create_table
  ON ddl_command_end
  WHEN TAG IN ('CREATE TABLE')
  EXECUTE FUNCTION log_create_table_ddl();
  

• Step 4: Add the ddl_log table to your publication
  Update your existing publication to include this table so it syncs to the standby:

  ALTER PUBLICATION your_publication_name ADD TABLE ddl_log;
  REFRESH PUBLICATION your_publication_name;
  

• Step 5: Build a replay function on the standby
  This function executes the logged DDL statements:

  CREATE OR REPLACE FUNCTION replay_ddl()
  RETURNS TRIGGER AS $$
  BEGIN
      -- Avoid replaying if already executed (prevents loops)
      IF NOT NEW.executed THEN
          EXECUTE NEW.ddl_stmt;
          UPDATE ddl_log SET executed = TRUE WHERE id = NEW.id;
      END IF;
      RETURN NEW;
  END;
  $$ LANGUAGE plpgsql;
  

• Step 6: Attach the replay trigger on the standby
  This triggers the replay function when new rows are added to ddl_log:

  CREATE TRIGGER replay_ddl_trigger
  AFTER INSERT ON ddl_log
  FOR EACH ROW
  EXECUTE FUNCTION replay_ddl();
  

  Note: Add checks to ensure the standby doesn't trigger its own DDL logging (e.g., verify if the instance is a standby via pg_is_in_recovery()).

2. Use the pglogical Extension #

PostgreSQL 10's native logical replication is limited, but the pglogical extension adds built-in DDL synchronization capabilities. Here's the core workflow:

• Install pglogical version 2.x (compatible with PostgreSQL 10) on both primary and standby instances.
• Configure pglogical nodes on both servers to establish replication connectivity.
• Create a publication that includes schema change replication—this extension will automatically sync new table structures and other DDL changes to the standby without manual intervention.

3. Upgrade to a Newer PostgreSQL Version #

PostgreSQL 10 reached end-of-life in November 2022, so upgrading to a supported version (14+) offers better tooling and long-term stability. While native logical replication still doesn't handle DDL sync out of the box, newer versions have improved event trigger capabilities, more robust extension ecosystems, and better replication tooling that simplifies this workflow.

Key Considerations #

• Always test DDL sync in a staging environment first—incorrectly replayed DDL can break replication or corrupt data.
• Ensure permissions are consistent between primary and standby (the standby needs sufficient privileges to execute DDL statements).
• For complex DDL (e.g., tables with foreign keys, sequences), extend the trigger logic to log and replay those dependencies too.

内容的提问来源于stack exchange，提问作者prosto.vint