PostgreSQL Replication to a Different Schema

Question

I want to consolidate 3 different production databases into a single reporting database.

Let's say we have 3 production databases:

1. Prod 1
2. Prod 2
3. Prod 3

What I want to do is to replicate data from those 3 databases into a reporting database into 3 different schemas (1 schema for each database - there are tables with the same name across databases, so different schema names are required).

All the databases are Postgres 12 instances.

My question is basically the same as this question posted 7 years ago. The answer there suggests to wait for Logical Replication. So it's now released but it doesn't support replication to a different schema.

There is also this question which is again the same as mine. The answer there suggests to rename the schemas on source databases which is not possible in my case.

Is there a 3-rd party tool which will allow to logically replicate data to a different schema? I'm open to using any third-party tools for this, even paid ones.

Answer 1

You cannot do that with logical replication, because logical replication requires that schema name, table name and column names are identical on the primary and the standby server.

I don't know if Slony-I supports that, you could have a look.

• Denis Arharov: It seems from this old thread that it's not possible in Slony as well