Logical Replication to different schema name

Question

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.

Answer 1

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.

Answer 2

this feature is scheduled on PostgreSQL 15

Logical replication support for publishing all tables of a schema added (commit 5a283246)

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=5a2832465fd8984d089e8c44c094e6900d987fcd

Allow publishing the tables of schema.

A new option "FOR ALL TABLES IN SCHEMA" in Create/Alter Publication allows one or more schemas to be specified, whose tables are selected by the publisher for sending the data to the subscriber.

The new syntax allows specifying both the tables and schemas. For example: CREATE PUBLICATION pub1 FOR TABLE t1,t2,t3, ALL TABLES IN SCHEMA s1,s2; OR ALTER PUBLICATION pub1 ADD TABLE t1,t2,t3, ALL TABLES IN SCHEMA s1,s2;

A new system table "pg_publication_namespace" has been added, to maintain the schemas that the user wants to publish through the publication. Modified the output plugin (pgoutput) to publish the changes if the relation is part of schema publication.

Updates pg_dump to identify and dump schema publications. Updates the \d family of commands to display schema publications and \dRp+ variant will now display associated schemas if any

Answer 3

Renaming the schemas in the original databases to prod_one and prod_two instead of public would seem to be the only possibility here.

Answer 4

As @jjanes mentioned it is not possible.

But I think, creating a typical 'Logical Replication' in different databases and then using foreign data wrappers to link databases in one database, but in different schemas, would be the most simple solution, but not much better than replicating tables in one database's different schemas.