PostgreSQL replication options

All we need is an easy explanation of the problem, so here it is.

community!
I am wondering if there are any new extensions/solutions which can replicate not the entire PG cluster but by database?
Logical replication is not an option.
For example, I want my primary database "test" to exist on the secondary server in two incarnations – "testreplica1" and "testreplica2" to be able to promote the first one to do smth with it meanwhile the second one would still be up-to-date with production

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Check out PostgreSQL: High Availability, Load Balancing, and Replication: Comparison of Different Solutions. In summary, the following solutions can provide for per-database and per-table replication: i, logical solutions (e.g., built-in and pglogical), ii, trigger-based solutions (e.g., Slony-I and Londiste), and, iii, certain multimaster replication (e.g., Bucardo) can be configured to provide per-database/per-table replication, as well as, iv, certain proxy solutions (PgBouncer).

For their availability, integrity, granularity, manageability, and portability benefits, Slony-I and pglogical are both fine solutions, the former exposing real-time internals that have proven useful for monitoring and diagnostics and the later natively implementing bi-directional replication (though, Slony-I can facilitate a bi-directional architecture using partitions and/or per-site tables for writes and views for reads, for example).

Note: Whereas I have far more experience with Slony-I, which certainly provides for DDL (and all other statement) replication (and includes "real-time" sequence state replication), I do believe pglogical too replicates most DDL, though with some exceptions.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply