10

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...

nique21
  • 101
  • 1
  • 4
  • I would prefer the built-in replication over pgPool's replication. A very good tool to manage the replication is http://repmgr.org/ Usually people then use a virtual IP to point to the "cluster" but you can also use pgPool to act as a proxy to the replicated server –  Jun 23 '14 at 14:13
  • If you're not experienced, stick to built-in streaming replication with WAL archiving fallover. `repmgr` helps with this. Use a proxy like PgBouncer (with a script that changes the destination server on failover), or something like HAProxy, if you want to make it client-transparent. Don't attempt fully automated failover, use manually triggered scripted failover - and *test it* periodically. – Craig Ringer Jun 23 '14 at 16:39

1 Answers1

9

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

Chris W.
  • 1,680
  • 16
  • 35
Clyde D'Cruz
  • 1,915
  • 1
  • 14
  • 36
  • 1
    Can’t you run multiple instances of pgpool2 with watchdog? I thought this how it was supposed be to avoid single point of failure. – demisx Feb 03 '19 at 03:09