Menu
I'm not exactly a DBA, so I would appreciate easy to understand responses. I have to provide replication to our DB and pgpool seems more convenient because if one postgresql instance fails, the clients are not required to change anything to keep on working, right? So, in this case, makes more sense to use pgpool, but the configuration part seems (to me) a lot more complicated and confusing. For instance, do I need to set up WAL on both postgresql servers? Or this is only needed if I want to set up postgresql replication? The more I try to get an answer to these questions, the less clear it becomes. Maybe I forgot how to google...
978
†: pgpool-II can use its own replication capabilities or those provided by other software, but it is often recommended to use PostgreSQL's streaming replication. Streaming replication is a feature that replicates databases by shipping transaction logs (WALs) of PostgreSQL (primary) to multiple instances of PostgreSQL (standby).
While it will be possible to replicate from PostgreSQL 10 to PostgreSQL 11 using the logical replication, pglogical supports any version of PostgreSQL since 9.4 so for an existing installation, it can provide a way to do on-line upgrades or replication in heterogeneous environments.
For Pgpool-II, we tested both when the Pgpool-II instance was installed on the same machine as PostgreSQL (on box column), and when it was installed on a different machine (off box column). As expected, the performance is much better when Pgpool-II is off the box as it doesn’t have to compete with the PostgreSQL server for resources.
Here we’ll cover two types of replication you could implement: Replication between PostgreSQL 10 and 11 versions using built-in logical replication. Replication between PostgreSQL 9.4 or (< PG 11) to PostgreSQL 11 using an extension named pglogical.
The built-in replication, provided by PostgreSQL itself, includes streaming replication, warm standby, and hot standby. These options are based on shipping Write-Ahead Logs (WAL) to all the standby servers. Write statements (e.g., INSERT, UPDATE) will go to the master, and the master will send logs (WALs) to the standby servers (or other masters, in the case of master-master replication).
pgpool, on the other hand, is a type of statement-based replication middleware (like a database proxy). All the statements actually go to pgpool, and pgpool forwards everything to all the servers to be replicated.
One big disadvantage with pgpool is that you have a single point of failure; if the server running pgpool crashes, your whole cluster fails.
The PostgreSQL documentation has some basic info on the various types of replication that are possible: https://www.postgresql.org/docs/current/different-replication-solutions.html
57
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
