PostgreSQL bucardo schema replication

Question

I want to replicate all tables from specific PostgreSQL schemas. So new tables from these schemas to be added automatically in synchronization.

Is it possible to do with Bucardo? If not, what tool would you recommend?

Answer 1

I'm not aware of any "well known" tool that makes logical replication of DDL statements.

From https://dba.stackexchange.com/a/31276/15606

PostgreSQL native streaming replication reads changes at the block level from the write-ahead logs, so the databases are kept physically the same. Since DDL is, just like DML, recorded in the WAL, DDL and DML are both transparently replicated.

But as you want to select some specific schemas logical replication is needed. Neither native logical replication or common tools like Bucardo, Slony allow to replicate DDL.

For simple cases you should apply the database migrations with your tool of choice, for example sqitch to all databases.

For more complex cases you can try the extension pgl_ddl_deploy. AFAIK this extension is not well know and didn't try it myself.