Обсуждение: Master-slave failover question
You can use pgpool to do this. Or you can use failover manager by enterpriseDB.
Regards
Sameer
PS: Sent from my Mobile device. Pls ignore typo n abb
You can use pgpool to do this. Or you can use failover manager by enterpriseDB.
Regards
SameerPS: Sent from my Mobile device. Pls ignore typo n abb
Check their site. Www.enterprisedb.com
Can you share you failover command here? You may use ifconfig to switch a vitrual IP while doing a failover.
BTW why are you reluctant to use pgpool?
Regards
Sameer
PS: Sent from my Mobile device. Pls ignore typo n abb
Check their site. Www.enterprisedb.com
Can you share you failover command here? You may use ifconfig to switch a vitrual IP while doing a failover.
BTW why are you reluctant to use pgpool?
Regards
SameerPS: Sent from my Mobile device. Pls ignore typo n abb
Hi,PGPool is a bit of a blackbox. I am confident in the use of VRRP but I have not seen any documentation on how to combine this with pgpool to eliminate single points of failure.Can you share a few more details about how I can use a virtual IP?On Fri, Jan 3, 2014 at 1:12 PM, Sameer Kumar <sameer.kumar@ashnik.com> wrote:Check their site. Www.enterprisedb.com
Can you share you failover command here? You may use ifconfig to switch a vitrual IP while doing a failover.
BTW why are you reluctant to use pgpool?
Regards
SameerPS: Sent from my Mobile device. Pls ignore typo n abb
As of now (v9.3 of postgreSQL), you need to rebuild old (lost) master from new master.
Glad you could discover the magic of pgpool :-)
You can either use pgpoll admin to do this rebuilding in a single click or you can write your own shell script (like I had done) to resotre a lost node (do remember to test a scenario where slave is lost and you have to rebuild that).
Regards
Sameer
PS: Sent from my Mobile device. Pls ignore typo n abb
As of now (v9.3 of postgreSQL), you need to rebuild old (lost) master from new master.
Glad you could discover the magic of pgpool :-)You can either use pgpoll admin to do this rebuilding in a single click or you can write your own shell script (like I had done) to resotre a lost node (do remember to test a scenario where slave is lost and you have to rebuild that).
Regards
SameerPS: Sent from my Mobile device. Pls ignore typo n abb
If you are only relying on streaming replication (and not using archive shipping/hybrid replication), the probablity of 'catching-up' is as high as your wal_keep_segment.
Regards
Sameer
PS: Sent from my Mobile device. Pls ignore typo n abb
If you are only relying on streaming replication (and not using archive shipping/hybrid replication), the probablity of 'catching-up' is as high as your wal_keep_segment.
Regards
SameerPS: Sent from my Mobile device. Pls ignore typo n abb
If the other node node is not available for a few day then??? Esp if it is slave node?
I would keeping it (wal_keep_segment) fair and rebuilding the lost node if I ever lose one.
Let's see what others have to suggest.
Regards
Sameer
PS: Sent from my Mobile device. Pls ignore typo n abb
On Fri, Jan 3, 2014 at 3:18 AM, Athanasios | ZenGuard <athanasios@zenguard.org> wrote: > Dear list, > I hope that everyone has a nice holiday. Currently, I am researching > master-slave automated promotion. I have one question. Let's assume that the > clients connect to the master 192.168.1.1 for read/write traffic and I have > a slave at 192.168.1.2. Assume that the master goes down and that slave gets > promoted to a slave (this I have found out how to do automatically). How > will the clients know to connect to the new master? Any ideas on this one? One of the ideas, and BTW my favorite method, is to use PgBouncer to redirect queries from master to slave with pause. The full description of the process is by the link below. https://github.com/grayhemp/pgcookbook/blob/master/switching_to_another_server_with_pgbouncer.md -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gray.ru@gmail.com
One of the ideas, and BTW my favorite method, is to use PgBouncer toOn Fri, Jan 3, 2014 at 3:18 AM, Athanasios | ZenGuard
<athanasios@zenguard.org> wrote:
> Dear list,
> I hope that everyone has a nice holiday. Currently, I am researching
> master-slave automated promotion. I have one question. Let's assume that the
> clients connect to the master 192.168.1.1 for read/write traffic and I have
> a slave at 192.168.1.2. Assume that the master goes down and that slave gets
> promoted to a slave (this I have found out how to do automatically). How
> will the clients know to connect to the new master? Any ideas on this one?
redirect queries from master to slave with pause. The full description
of the process is by the link below.
https://github.com/grayhemp/pgcookbook/blob/master/switching_to_another_server_with_pgbouncer.md
--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA
http://www.linkedin.com/in/grayhemp
+1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979
gray.ru@gmail.com
--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
On Tue, Jan 7, 2014 at 8:16 AM, Sameer Kumar <sameer.kumar@ashnik.com> wrote: >> > promoted to a slave (this I have found out how to do automatically). How >> > will the clients know to connect to the new master? Any ideas on this >> > one? >> >> One of the ideas, and BTW my favorite method, is to use PgBouncer to >> redirect queries from master to slave with pause. The full description >> of the process is by the link below. >> >> https://github.com/grayhemp/pgcookbook/blob/master/switching_to_another_server_with_pgbouncer.md > > I checked it out. Looks like the process involves manual identification of > failure(?). But this is a nice method for manual switchovers. Correct, this is about manual detection of failure. There were a lot of discussions around automatic failover but I have never seen anyone defined a really working (for me) criteria to detect the failure. > As a suggestion, won't it be a good idea to run the pgbouncer on Application > Server [so that even if the Master Server has to be shutdown, IP address > changes or starting pgbouncer on slave is not needed]? It depends. In some cases it is worth doing if you have a single application server (and do not planning to have more), or the overhead of a high number of app->db network connections per second is significantly high in comparison to a local one. However, modern architectures usually use multiple application servers and persistent connections, and in this case installing pgbouncer on database servers is preferable. I also do not recommend to install it on a separate machine as it brings another point of failure in any case. And again, it depends on your situation. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gray.ru@gmail.com
> I checked it out. Looks like the process involves manual identification ofCorrect, this is about manual detection of failure. There were a lot
> failure(?). But this is a nice method for manual switchovers.
of discussions around automatic failover but I have never seen anyone
defined a really working (for me) criteria to detect the failure.
> As a suggestion, won't it be a good idea to run the pgbouncer on ApplicationIt depends. In some cases it is worth doing if you have a single
> Server [so that even if the Master Server has to be shutdown, IP address
> changes or starting pgbouncer on slave is not needed]?
application server (and do not planning to have more), or the overhead
of a high number of app->db network connections per second is
significantly high in comparison to a local one.
However, modern
architectures usually use multiple application servers and persistent
connections, and in this case installing pgbouncer on database servers
is preferable.
I also do not recommend to install it on a separate
machine as it brings another point of failure in any case.
And again,
it depends on your situation.
Best Regards,
Sameer Kumar | Database Consultant
ASHNIK PTE. LTD.
101 Cecil Street, #11-11 Tong Eng Building, Singapore 069533
M : +65 8110 0350 T: +65 6438 3504 | www.ashnik.com
www.facebook.com/ashnikbiz | www.twitter.com/ashnikbiz
This email may contain confidential, privileged or copyright material and is solely for the use of the intended recipient(s).
Вложения
Sameer Kumar wrote > - SO far I have used pgpool in HA mode to cater to all these points. I > have > run into split brain scenario but once detected, you just need to rebuild > slave and attach it back. Despite split brain my application continues to > use original master (since Virtual IP has not shifted). Just curious - once pgpool switches to the slave, it will mark it as primary (I did not use virtual IPs.) So spli-brain scenarion will not happen,right? -- View this message in context: http://postgresql.1045698.n5.nabble.com/Master-slave-failover-question-tp5785199p5785808.html Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
Hi Sameer,thank you for the quick reply (again!). I am not using pgpool admin so is it possible to share the shell script that does the rebuilding?
> - SO far I have used pgpool in HA mode to cater to all these points. IJust curious - once pgpool switches to the slave, it will mark it as primary
> have
> run into split brain scenario but once detected, you just need to rebuild
> slave and attach it back. Despite split brain my application continues to
> use original master (since Virtual IP has not shifted).
(I did not use virtual IPs.) So spli-brain scenarion will not happen,right?
I am using pgpool with streaming replication option for PostgreSQL, with a failover command (shell script). 2 pgpool instances (with watchdog) on the database nodes. You were referring to split brain for pgpool instances or PostgreSQL instances?
Just curious - once pgpool switches to the slave, it will mark it as primary
(I did not use virtual IPs.) So spli-brain scenarion will not happen,right?
Sameer Kumar wrote > Am I missing something here? or some mode of pgpool which I am not aware > of? I thought you were mentioning split-brain for PostgreSQL. For pgpool, I am using virtual IP. Somehow I got the message that the split-brain/virtual IPs in your mail were with reference to PostgreSQL servers. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Master-slave-failover-question-tp5785199p5785816.html Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
Sameer Kumar wrote> Am I missing something here? or some mode of pgpool which I am not awareI thought you were mentioning split-brain for PostgreSQL. For pgpool, I am
> of?
using virtual IP. Somehow I got the message that the split-brain/virtual IPs
in your mail were with reference to PostgreSQL servers.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Master-slave-failover-question-tp5785199p5785816.htmlSent from the PostgreSQL - novice mailing list archive at Nabble.com.
--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
Hi all, I have setup the load balancing and failover for High Availability in pgpool. I have used the Master-slave replication(stream mode). DB version PPAS 9.4AS(EnterpriseDB) pgpool version 3.3.4 The following is my failover command in pgpool.conf failover_command ='/usr/local/etc/failover.sh %d %P %H %R' Following is the failover script. #!/bin/bash -x FALLING_NODE=$1 # %d OLDPRIMARY_NODE=$2 # %P NEW_PRIMARY=$3 # %H PGDATA=$4 # %R if [ $FALLING_NODE = $OLDPRIMARY_NODE ]; then if [ $UID -eq 0 ] then su enterprisedb -c "ssh -T enterprisedb@$NEW_PRIMARY touch $PGDATA/trigger" else ssh -T enterprisedb@$NEW_PRIMARY touch $PGDATA/trigger fi exit 0; fi; exit 0; To check the failover scenario i have stopped(kill -9) the master db server process.According to the script the present salve will take over the role of master. But the same was not happening in my case. From pgpool end show pool_nodes; o/p: edb=# show pool_nodes; node_id | hostname | port | status | lb_weight | role ---------+------------+------+--------+-----------+--------- 0 | 10.0.0.149 | 5444 | 3 | 0.500000 | standby 1 | 10.0.0.158 | 5444 | 2 | 0.500000 | standby (2 rows) Please help me in Fixing this Issue.Provide me the failover script which is working correctly. Thanks for your time. -- View this message in context: http://postgresql.nabble.com/Master-slave-failover-question-tp5785199p5841392.html Sent from the PostgreSQL - novice mailing list archive at Nabble.com.