Postgresql – Logical Replication to different schema name

postgresqlreplication

We have PostgreSQL 11 database with structure one database one schema (public).

dbname : production_one
schema : public

dbname : production_two
schema : public

I would like to set up a logical replication, so that it will have structure One database, multiple schema as below:

dbname : prod_slave
schema : prod_one, prod_two

is it possible to do logical replication as above?

I have successful replication using streaming replication, but the situation need us to use logical replication as above.

Best Answer

This does not seem to be possible with built-in logical replication: https://www.postgresql.org/docs/current/logical-replication-subscription.html

The tables are matched between the publisher and the subscriber using the fully qualified table name. Replication to differently-named tables on the subscriber is not supported.

Since it uses fully qualified names, a different schema would count as a different name, and is not supported.

pub/sub is just a user friendly wrapping around a more general facility, you might be able to create decoding plugins to give you finer control. I doubt that doing so is for the faint of heart, though.

Related Question