pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedHow do you manage cluster replication and failover ?
13+ messages / 8 participants
[nested] [flat]
* How do you manage cluster replication and failover ?
@ 2017-04-06 09:17 Robin LUCBERNET <rlucbernet@maltem.com>
0 siblings, 2 replies; 13+ messages in thread
From: Robin LUCBERNET @ 2017-04-06 09:17 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: How do you manage cluster replication and failover ?
@ 2017-04-06 16:16 Poul Kristensen <bcc5226@gmail.com>
parent: Robin LUCBERNET <rlucbernet@maltem.com>
1 sibling, 1 reply; 13+ messages in thread
From: Poul Kristensen @ 2017-04-06 16:16 UTC (permalink / raw)
To: Robin LUCBERNET <rlucbernet@maltem.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: How do you manage cluster replication and failover ?
@ 2017-04-07 12:12 Robin LUCBERNET <rlucbernet@maltem.com>
parent: Poul Kristensen <bcc5226@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: Robin LUCBERNET @ 2017-04-07 12:12 UTC (permalink / raw)
To: Poul Kristensen <bcc5226@gmail.com>; +Cc: pgsql-admin
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<mailto: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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: How do you manage cluster replication and failover ?
@ 2017-04-07 12:36 stevenchang1213 <stevenchang1213@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: stevenchang1213 @ 2017-04-07 12:36 UTC (permalink / raw)
To: Robin LUCBERNET <rlucbernet@maltem.com>; pgsql-admin
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@maltem.com> 日期： 2017/4/6 17:17 (GMT+08:00) 至： pgsql-admin@postgresql.org 主旨： [ADMIN] 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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: How do you manage cluster replication and failover ?
@ 2017-04-07 12:50 Poul Kristensen <bcc5226@gmail.com>
parent: stevenchang1213 <stevenchang1213@gmail.com>
0 siblings, 0 replies; 13+ messages in thread
From: Poul Kristensen @ 2017-04-07 12:50 UTC (permalink / raw)
To: stevenchang1213 <stevenchang1213@gmail.com>; +Cc: Robin LUCBERNET <rlucbernet@maltem.com>; pgsql-admin
Replication manager is a choise.
/Poul
2017-04-07 14:36 GMT+02:00 stevenchang1213 <stevenchang1213@gmail.com>:
>
> 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@maltem.com>
> 日期： 2017/4/6 17:17 (GMT+08:00)
> 至： pgsql-admin@postgresql.org
> 主旨： [ADMIN] 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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: How do you manage cluster replication and failover ?
@ 2017-04-07 13:13 bricklen <bricklen@gmail.com>
parent: Robin LUCBERNET <rlucbernet@maltem.com>
0 siblings, 0 replies; 13+ messages in thread
From: bricklen @ 2017-04-07 13:13 UTC (permalink / raw)
To: Robin LUCBERNET <rlucbernet@maltem.com>; +Cc: pgsql-admin
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).
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: How do you manage cluster replication and failover ?
@ 2017-04-07 13:26 Lazaro Garcia <lazaro3487@gmail.com>
parent: Robin LUCBERNET <rlucbernet@maltem.com>
1 sibling, 1 reply; 13+ messages in thread
From: Lazaro Garcia @ 2017-04-07 13:26 UTC (permalink / raw)
To: 'Robin LUCBERNET' <rlucbernet@maltem.com>; pgsql-admin
You can use repmgr because it performs automatic failover, promotes a master
mores closer to replica and follows other slaves to new master.
Then pgpool detects the new master promoted by repmgr.
Regards.
De: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] En nombre de Robin LUCBERNET
Enviado el: jueves, 6 de abril de 2017 05:18 a. m.
Para: pgsql-admin@postgresql.org
Asunto: [ADMIN] 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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: How do you manage cluster replication and failover ?
@ 2017-04-07 15:33 Robin LUCBERNET <rlucbernet@maltem.com>
parent: Lazaro Garcia <lazaro3487@gmail.com>
0 siblings, 3 replies; 13+ messages in thread
From: Robin LUCBERNET @ 2017-04-07 15:33 UTC (permalink / raw)
To: Lazaro Garcia <lazaro3487@gmail.com>; pgsql-admin
Hey, thanks for your feedbacks.
I do not know about Patroni. I will have a look at it.
Could someone confirm me that all features of pgpool (connection pooling + automatic failover) can be achieve using pgbouncer (connection pooling) + repmgr (automatic failover) ? Is it not overkill to user repmgr + pgpool ? Am I missing something ?
Robin.
________________________________
De : Lazaro Garcia <lazaro3487@gmail.com>
Envoyé : vendredi 7 avril 2017 15:26:41
À : Robin LUCBERNET; pgsql-admin@postgresql.org
Objet : RE: [ADMIN] How do you manage cluster replication and failover ?
You can use repmgr because it performs automatic failover, promotes a master mores closer to replica and follows other slaves to new master.
Then pgpool detects the new master promoted by repmgr.
Regards.
De: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] En nombre de Robin LUCBERNET
Enviado el: jueves, 6 de abril de 2017 05:18 a. m.
Para: pgsql-admin@postgresql.org
Asunto: [ADMIN] 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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: How do you manage cluster replication and failover ?
@ 2017-04-07 16:01 Lazaro Garcia <lazaro3487@gmail.com>
parent: Robin LUCBERNET <rlucbernet@maltem.com>
2 siblings, 0 replies; 13+ messages in thread
From: Lazaro Garcia @ 2017-04-07 16:01 UTC (permalink / raw)
To: 'Robin LUCBERNET' <rlucbernet@maltem.com>; pgsql-admin
You can user pgbouncer + repmgr but pgbouncer is not a load balancer.
When repmgr performs failover you can configure the property promote_command
in repmgr.conf and build an script for updating the pgbouncer configuration
with the new master.
Repmgr + pgpool would be very usefull when you have more than 2 nodes
because pgpool does not knows how is the best candidate node for promoting
to master.
Regards.
De: Robin LUCBERNET [mailto:rlucbernet@maltem.com]
Enviado el: viernes, 7 de abril de 2017 11:34 a. m.
Para: Lazaro Garcia; pgsql-admin@postgresql.org
Asunto: RE: [ADMIN] How do you manage cluster replication and failover ?
Hey, thanks for your feedbacks.
I do not know about Patroni. I will have a look at it.
Could someone confirm me that all features of pgpool (connection pooling +
automatic failover) can be achieve using pgbouncer (connection pooling) +
repmgr (automatic failover) ? Is it not overkill to user repmgr + pgpool ?
Am I missing something ?
Robin.
_____
De : Lazaro Garcia < <mailto:lazaro3487@gmail.com> lazaro3487@gmail.com>
Envoyé : vendredi 7 avril 2017 15:26:41
À : Robin LUCBERNET; <mailto:pgsql-admin@postgresql.org>
pgsql-admin@postgresql.org
Objet : RE: [ADMIN] How do you manage cluster replication and failover ?
You can use repmgr because it performs automatic failover, promotes a master
mores closer to replica and follows other slaves to new master.
Then pgpool detects the new master promoted by repmgr.
Regards.
De: pgsql-admin-owner@postgresql.org
<mailto:pgsql-admin-owner@postgresql.org>
[mailto:pgsql-admin-owner@postgresql.org] En nombre de Robin LUCBERNET
Enviado el: jueves, 6 de abril de 2017 05:18 a. m.
Para: pgsql-admin@postgresql.org <mailto:pgsql-admin@postgresql.org>
Asunto: [ADMIN] 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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: How do you manage cluster replication and failover ?
@ 2017-04-07 16:02 michael@sqlexec.com <michael@sqlexec.com>
parent: Robin LUCBERNET <rlucbernet@maltem.com>
2 siblings, 0 replies; 13+ messages in thread
From: michael@sqlexec.com @ 2017-04-07 16:02 UTC (permalink / raw)
To: Robin LUCBERNET <rlucbernet@maltem.com>; +Cc: Lazaro Garcia <lazaro3487@gmail.com>; pgsql-admin
You could use pgpool2 for connection pooling, load balancing and HA
failover or use pgbouncer for connection pooling (lesser footprint than
pgpool), pgpool2 for load balancing and failover (using watchdog). But
I don't see the point of combining repmgr with pgpool2.
With pgpool2 and load balancing, care must be taken with application
backends that use queuing like rabbitq. Logical units of work from a
client application server endpoint may encompass multiple transactions
across multiple connections. This could break load balancing if you are
not using PG 9.6 where synchronous_commit can be set to *remote_apply.
*Got stabbed in the foot by this in the past.
*
*
Regards,
Michael Vitale
> Robin LUCBERNET <mailto:rlucbernet@maltem.com>
> Friday, April 7, 2017 11:33 AM
>
> Hey, thanks for your feedbacks.
>
>
> I do not know about Patroni. I will have a look at it.
>
>
> Could someone confirm me that all features of pgpool (connection
> pooling + automatic failover) can be achieve using pgbouncer
> (connection pooling) + repmgr (automatic failover) ? Is it not
> overkill to user repmgr + pgpool ? Am I missing something ?
>
>
> Robin.
>
> ------------------------------------------------------------------------
> *De :* Lazaro Garcia <lazaro3487@gmail.com>
> *Envoyé :* vendredi 7 avril 2017 15:26:41
> *À :* Robin LUCBERNET; pgsql-admin@postgresql.org
> *Objet :* RE: [ADMIN] How do you manage cluster replication and
> failover ?
>
> You can use repmgr because it performs automatic failover, promotes a
> master mores closer to replica and follows other slaves to new master.
>
> Then pgpool detects the new master promoted by repmgr.
>
> Regards.
>
> *De:*pgsql-admin-owner@postgresql.org
> [mailto:pgsql-admin-owner@postgresql.org] *En nombre de *Robin LUCBERNET
> *Enviado el:* jueves, 6 de abril de 2017 05:18 a. m.
> *Para:* pgsql-admin@postgresql.org
> *Asunto:* [ADMIN] 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
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: How do you manage cluster replication and failover ?
@ 2017-04-07 23:43 stevenchang1213 <stevenchang1213@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: stevenchang1213 @ 2017-04-07 23:43 UTC (permalink / raw)
To: Robin LUCBERNET <rlucbernet@maltem.com>; Lazaro Garcia <lazaro3487@gmail.com>; pgsql-admin
you are talking about product features. I suggest u study by urself. None will confirm u anything here. All is free charge here , you should think about what it means.
steven
-------- 原始訊息 --------自： Robin LUCBERNET <rlucbernet@maltem.com> 日期： 2017/4/7 23:33 (GMT+08:00) 至： Lazaro Garcia <lazaro3487@gmail.com>, pgsql-admin@postgresql.org 主旨： Re: [ADMIN] How do you manage cluster replication and failover ?
<!-- P {margin-top:0;margin-bottom:0;} -->
Hey, thanks for your feedbacks.
I do not know about Patroni. I will have a look at it.
Could someone confirm me that all features of pgpool (connection pooling + automatic failover) can be achieve using pgbouncer (connection pooling) + repmgr (automatic failover) ? Is it not overkill to user repmgr + pgpool ? Am I missing something ?
Robin.
De : Lazaro Garcia <lazaro3487@gmail.com>
Envoyé : vendredi 7 avril 2017 15:26:41
À : Robin LUCBERNET; pgsql-admin@postgresql.org
Objet : RE: [ADMIN] How do you manage cluster replication and failover ?
You can use repmgr because it performs automatic failover, promotes a master mores closer to replica and follows other
slaves to new master.
Then pgpool detects the new master promoted by repmgr.
Regards.
De: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org]
En nombre de Robin LUCBERNET
Enviado el: jueves, 6 de abril de 2017 05:18 a. m.
Para: pgsql-admin@postgresql.org
Asunto: [ADMIN] 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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: How do you manage cluster replication and failover ?
@ 2017-04-10 08:48 Jehan-Guillaume de Rorthais <ioguix@free.fr>
parent: Robin LUCBERNET <rlucbernet@maltem.com>
2 siblings, 0 replies; 13+ messages in thread
From: Jehan-Guillaume de Rorthais @ 2017-04-10 08:48 UTC (permalink / raw)
To: Robin LUCBERNET <rlucbernet@maltem.com>; +Cc: Lazaro Garcia <lazaro3487@gmail.com>; pgsql-admin
Hello Robin,
Sorry to jump in this thread so lately, I was on holidays :)
Considering the failover subject, did you check the PAF project as well? It
relies on Pacemaker, the industry standard for high availability under Linux.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: How do you manage cluster replication and failover ?
@ 2017-04-11 05:38 Steven Chang <stevenchang1213@gmail.com>
parent: stevenchang1213 <stevenchang1213@gmail.com>
0 siblings, 0 replies; 13+ messages in thread
From: Steven Chang @ 2017-04-11 05:38 UTC (permalink / raw)
To: Robin LUCBERNET <rlucbernet@maltem.com>; Lazaro Garcia <lazaro3487@gmail.com>; pgsql-admin
alternative :
t
tar
Output a tar-format archive suitable for input into
pg_restore.
The tar format is compatible with the directory format:
extracting a tar-format archive produces a valid
directory-format archive. However, the tar format does not
support compression. Also, when using tar format the relative
order of table data items cannot be changed during restore
example :
pg_dump -U postgres -h faiserver -F t -f aaa.tar -d warehouse_db
Actually, in my experience, compress would finish more quickly due to
much smaller disk consumption size under modern cpu model.
Steven
2017-04-08 7:43 GMT+08:00 stevenchang1213 <stevenchang1213@gmail.com>:
> you are talking about product features. I suggest u study by urself. None
> will confirm u anything here. All is free charge here , you should think
> about what it means.
>
> steven
>
> -------- 原始訊息 --------
> 自： Robin LUCBERNET <rlucbernet@maltem.com>
> 日期： 2017/4/7 23:33 (GMT+08:00)
> 至： Lazaro Garcia <lazaro3487@gmail.com>, pgsql-admin@postgresql.org
> 主旨： Re: [ADMIN] How do you manage cluster replication and failover ?
>
> Hey, thanks for your feedbacks.
>
>
> I do not know about Patroni. I will have a look at it.
>
>
> Could someone confirm me that all features of pgpool (connection pooling +
> automatic failover) can be achieve using pgbouncer (connection pooling) +
> repmgr (automatic failover) ? Is it not overkill to user repmgr + pgpool ?
> Am I missing something ?
>
>
> Robin.
>
> ------------------------------
> *De :* Lazaro Garcia <lazaro3487@gmail.com>
> *Envoyé :* vendredi 7 avril 2017 15:26:41
> *À :* Robin LUCBERNET; pgsql-admin@postgresql.org
> *Objet :* RE: [ADMIN] How do you manage cluster replication and failover ?
>
>
> You can use repmgr because it performs automatic failover, promotes a
> master mores closer to replica and follows other slaves to new master.
>
>
>
>
>
> Then pgpool detects the new master promoted by repmgr.
>
>
>
> Regards.
>
>
>
> *De:* pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@
> postgresql.org] *En nombre de *Robin LUCBERNET
> *Enviado el:* jueves, 6 de abril de 2017 05:18 a. m.
> *Para:* pgsql-admin@postgresql.org
> *Asunto:* [ADMIN] 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
>
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2017-04-11 05:38 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-04-06 09:17 How do you manage cluster replication and failover ? Robin LUCBERNET <rlucbernet@maltem.com>
2017-04-06 16:16 ` Poul Kristensen <bcc5226@gmail.com>
2017-04-07 12:12 ` Robin LUCBERNET <rlucbernet@maltem.com>
2017-04-07 13:13 ` bricklen <bricklen@gmail.com>
2017-04-07 13:26 ` Lazaro Garcia <lazaro3487@gmail.com>
2017-04-07 15:33 ` Robin LUCBERNET <rlucbernet@maltem.com>
2017-04-07 16:01 ` Lazaro Garcia <lazaro3487@gmail.com>
2017-04-07 16:02 ` michael@sqlexec.com <michael@sqlexec.com>
2017-04-10 08:48 ` Jehan-Guillaume de Rorthais <ioguix@free.fr>
2017-04-07 12:36 Re: How do you manage cluster replication and failover ? stevenchang1213 <stevenchang1213@gmail.com>
2017-04-07 12:50 ` Poul Kristensen <bcc5226@gmail.com>
2017-04-07 23:43 Re: How do you manage cluster replication and failover ? stevenchang1213 <stevenchang1213@gmail.com>
2017-04-11 05:38 ` Steven Chang <stevenchang1213@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox