Currently we are using postgres streaming replication to sync/replicate databases on Primary and replica server. We are planning to use one of the application to sync data from our secondary or replica server to our data warehouse which needs logical replication to be enabled for tracking the changes and syncing the data from our replica server to data warehouse. Can we enable logical replication on top of streaming replication ? Is it possible or good practice to enable both on the same server or database ? If so, will there be any performance impact or what are the considerations or best practices to be followed?
Add a comment
|
1 Answer
There is no problem with using streaming (physical) replication and logical replication at the same time, but a physical standby server cannot be a logical primary. So you will have to use the same server as primary server for both physical and logical replication. But that shouldn't be a problem, since streaming replication primary and standby are physically identical anyway.
4 Comments
Kris
Thanks for the response Laurenz Albe . Will there be any performance impact or any other special considerations to be taken if we are implementing both ?
Laurenz Albe
That's a pretty general question. If you are wondering if the performance impact will be big, the answer is no.
Kris
Thanks Laurenz Albe. For setting up both streaming (physical) replication and logical replication at the same time on primary server , do we need to set wal_level = logical , wal_level = replica both at the same time or wal_level = logical , replica?
Laurenz Albe
Setting
wal_level to logical is the correct way, since that contains everything that replica does and more.