From: | Robin LUCBERNET <rlucbernet(at)maltem(dot)com> |
---|---|
To: | Poul Kristensen <bcc5226(at)gmail(dot)com> |
Cc: | "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org> |
Subject: | Re: How do you manage cluster replication and failover ? |
Date: | 2017-04-07 12:12:39 |
Message-ID: | VI1PR0501MB2781DA8BBD2F045570BC4FB2AB0C0@VI1PR0501MB2781.eurprd05.prod.outlook.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(at)gmail(dot)com>
Envoyé : jeudi 6 avril 2017 18:16
À : Robin LUCBERNET
Cc : pgsql-admin(at)postgresql(dot)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(at)maltem(dot)com<mailto: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
From | Date | Subject | |
---|---|---|---|
Next Message | stevenchang1213 | 2017-04-07 12:36:40 | Re: How do you manage cluster replication and failover ? |
Previous Message | Günce Kaya | 2017-04-07 07:11:54 | Re: calculating table and index size |