Обсуждение: Re: [ADMIN] How do you manage cluster replication and failover ?

Поиск
Список
Период
Сортировка

Re: [ADMIN] How do you manage cluster replication and failover ?

От
Poul Kristensen
Дата:
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@maltem.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

Re: [ADMIN] How do you manage cluster replication and failover ?

От
Robin LUCBERNET
Дата:

Thanks for your answer.


How do you manage the failover with your virtual servers ?

Postgres has a built-in failover mecanism using the trigger_file setting in reconvery.conf.


Do you handle it manually or use another system to automate it ?


Robin.


De : Poul Kristensen <bcc5226@gmail.com>
Envoyé : jeudi 6 avril 2017 18:16
À : Robin LUCBERNET
Cc : pgsql-admin@postgresql.org
Objet : Re: [ADMIN] 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@maltem.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

Re: [ADMIN] How do you manage cluster replication and failover ?

От
bricklen
Дата:

On Fri, Apr 7, 2017 at 5:12 AM, Robin LUCBERNET <rlucbernet@maltem.com> wrote:

How do you manage the failover with your virtual servers ?
Do you handle it manually or use another system to automate it ?


At work we use Patroni with Zookeeper to handle detecting if the master has failed then initiating the failover to a replica.
https://github.com/zalando/patroni
However, we are running with asynchronous replication, and in our current setup we only have a single replica to fail over to (in the same data centre), the disaster recovery replicas in other data centres are failed over to manually (it was easier to set it up that way to prevent any unwanted cross-DC failovers).