PostgreSQL Replication – How to Replicate to Subscriber with Different Name

postgresqlreplication

Is it possible in PostgreSQL to set up the following replication scenarios?

  1. Replication between 2 servers with different database name. For example, replicate FROM server_A (primary) database name primary_product TO server_B (slave) database name primary_product_analytics

  2. Replication between 2 servers which consist of several databases in Server_A, need to be transferred into single database with multiple schemas (custom schema name) on Server_B.

I have successfully setup replication using WAL but I would like guidance on implementing the above scenarios if possible.

Best Answer

WAL-based replication is physical replication, i.e., it replicates all the changes made on the disk of a database cluster. In other words, all databases and their contents are exactly the same.

If you want replicate with different database names, you can use logical replication, which replicates changes made to specified tables.

However, logical replication cannot change the schema name. If you want to copy changes to a table in a different schema, you could do it manually with triggers and postgres_fdw.

Related Question