Re: How do you manage cluster replication and failover ?

The challenge is not whether to use the difference tools as you mentioned.
The challenge is to make to optimal database design the get the optimal
backup/restore or disaster/recovery.
If you have just one database per Postgres installation and then just
handle user/role security using schema's in one database then it is a good
choice in my opinion as only one installation to maintain.
If you have multiple databases I think that you have to be aware of issues
concerning the recovering of one database in an easy way. Furthermore be
aware of the possibilty of corrupted databases. A regularly dump will
help. If using Debian's hdparm -I /dev/.. is usefull on Debian.

Write performance: I have succeded writing 10 mill. records in 50seconds
using 2 virtual servers with streaming replication. I haven't yet tried
physical servers yet.

Hope this is usefull.

BR
Poul

2017-04-06 11:17 GMT+02:00 Robin LUCBERNET <rlucbernet(at)maltem(dot)com>:

> 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