Automated schema changes propagation when using logical replication

[michaljemala]

We would like to replicate data to another Postgres server (both provisioned using Google Cloud SQL) by using the logical replication however, we cannot find an easy way to replicate schema changes too. Both native Postgres logical replication as well pglogical does not support this. We are aware of pglogical's `replicate_ddl_command`, but we cannot change our current migration mechanism to accommodate this.

Is there any other way we to enable automated schema change replication when logical replication is used?

[ms4446 · Accepted solution]

Schema changes in PostgreSQL, such as ALTER TABLE, CREATE INDEX, DROP COLUMN, etc., are not replicated through the native logical replication or pglogical. This is because the logical replication in PostgreSQL is designed to replicate only the data, not the schema.

You've already mentioned pglogical's replicate_ddl_command, which is one of the few options to tackle this problem, but it requires a change in your migration mechanism which you want to avoid.

There are some other options, but they will still require some level of manual intervention or changes to your workflow:

1. Manual DDL Execution: The simplest but most manual solution is to execute the same DDL statements on the replica database as those executed on the primary. This requires careful management to ensure that all changes are applied and applied in the correct order. However, this might not be a feasible solution if you have frequent schema changes or if the process needs to be automatic.

2. DDL Triggers: You can create a DDL trigger that fires whenever a DDL command is issued. This trigger could log these commands into a table. You can then replicate this table to the replica database and have a process on the replica that reads and executes these commands. Note that this process isn't foolproof and can be tricky to manage and debug.

3. Schema Migration Tools: Some schema migration tools, like Flyway or Liquibase, can help manage schema changes and ensure that they are applied consistently across multiple databases. These tools keep a history of schema changes and apply them in order. You could consider adopting such a tool as part of your workflow, although this would be a significant change.

4. BDR (Bi-Directional Replication) for PostgreSQL: BDR is a solution for multi-master replication in PostgreSQL which supports DDL replication, but it's much more complex than logical replication and probably overkill if you just need to replicate schema changes.

In general, automated schema change replication in PostgreSQL is a challenging issue due to the way logical replication is designed. Depending on your specific use case and needs, you may find that a combination of these approaches is needed. If your schema changes are infrequent and well-controlled, manual synchronization might be the most straightforward option. If not, considering a schema migration tool or a DDL trigger-based approach might be more appropriate.