Re: How do you manage cluster replication and failover ?

[Poul Kristensen <bcc5226@xxxxxxxxx>]

Replication manager is a choise.  

/Poul

2017-04-07 14:36 GMT+02:00 stevenchang1213 <stevenchang1213@xxxxxxxxx>:

hi robin,

      All ur words is just one thing - pgpool II.

     The project also contains Admin UI.

      U can download and try it.

steven 

-------- 原始訊息 --------

自： Robin LUCBERNET <rlucbernet@xxxxxxxxxx>

日期： 2017/4/6 17:17 (GMT+08:00)

至： pgsql-admin@xxxxxxxxxxxxxx

主旨： How do you manage cluster replication and failover ?

Hello,

We are currently trying to setup a multi hosts databases cluster with goals:
 * replication (no data-loss is "required", replication timing do not needs to be instant)
 * failover
 * load-balancing (bonus)

We tried:
 * synchronious replication (pgpool replication mode) + load-balancing (pgpool) : very interesing as we can theorically failover on any node at any moment. But even after several configuraation tweeks, we never succeed getting good write performance.
 * asynchronious replication (postgres 9.6 streaming replication) : good write performance, good replication timings (< 1 second for small transactions). We could loadbalance select requests using pgpool.

How do you manage your postgresql clusters ? Do you use pgpool ? pgbouncer ? other ?
How do you manage to single access point ? usign pgpool ? pgbouncer ?
Do you use streaming replication ? WAL archiving ? How do you handle to automatic failover ?

Robin

--

Med venlig hilsen / Best regards
Poul Kristensen
Linux-OS/Virtualizationexpert and Oracle DBA