Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 04AB51337B79 for ; Thu, 17 Feb 2011 23:55:29 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 62151-08 for ; Fri, 18 Feb 2011 03:55:21 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail.mailpen.net (mail.mailpen.net [209.59.217.159]) by mail.postgresql.org (Postfix) with ESMTP id 430BF1337B4F for ; Thu, 17 Feb 2011 23:55:20 -0400 (AST) Received: from [172.20.1.171] (pool-98-117-107-200.sttlwa.fios.verizon.net [98.117.107.200]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by mail.mailpen.net (Postfix) with ESMTP id 2B650114142; Thu, 17 Feb 2011 19:55:18 -0800 (PST) Message-ID: <4D5DED9B.4090407@ultimeth.com> Date: Thu, 17 Feb 2011 19:55:07 -0800 From: "Dean Gibson (DB Administrator)" Organization: UltiMeth Systems User-Agent: Thunderbird 2.0.0.24 (Windows/20100228) MIME-Version: 1.0 To: Selva manickaraja , pgsql-admin@postgresql.org Subject: Re: Trigger File Behaviour References: <4D5D5816.9000900@ultimeth.com> In-Reply-To: Content-Type: multipart/alternative; boundary="------------060404040403010706060800" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-10 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201102/169 X-Sequence-Number: 36808 This is a multi-part message in MIME format. --------------060404040403010706060800 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit On 2011-02-17 17:43, Selva manickaraja wrote: > Does this mean the fail-over will not be auto the moment the primary > DB or Server goes down? Does it require for us to manually intervene > to introduce the trigger_file? Yes, and yes. I depends upon the installation, as to how to detect that that the primary has gone down (eg, lose network connection for how long?). Therefore, it is left to the sysadmin (that's you and me) to write external procedures to detect when it is time to fail-over, and write an appropriate automated script to create the trigger file (or do it manually). I do it manually, because I have multiple slaves and the procedures are slightly more complex than for just one slave. Setting up the configuration files is pretty trivial: 1. On each slave, create a recovery.conf that points to the primary. 2. Optionally, on the primary, create a recovery.done (note the different extension) that points to whichever slave you plan on later switching to a primary in case of a fail-over. In the case of the failure of the primary: 1. Make sure the primary will not come back up (yet). 2. Create the trigger file (which triggers the switch) on whatever slave you wish to become the new primary. 3. If you have only one slave, you are done. If you have more than one slave, you will need to (on each other slave): 3.a. Stop the slave. 3.b. Edit the recovery.conf file to point to the new primary. 3.c. Restart the slave. 3.d. If the slave does not properly sync to the new primary, you may have to (ugh) run the script below to resynchronize the slave to the new primary. When you are ready to have the old primary become a new slave, resync the data on the old primary to the new primary (I use the following script on the old primary): service postgresql stop { cat <<-EOF SELECT pg_start_backup( '$(date --iso-8601)', true ); \! rsync -vaz --delete new.primary.hostname:$PGDATA/ $PGDATA/ SELECT pg_stop_backup(); EOF } | psql -e template1 postgres mv $PGDATA/recovery.{done,conf} service postgresql start Presto! Your old primary is now back up as a slave. > > On Fri, Feb 18, 2011 at 1:17 AM, Dean Gibson (DB Administrator) > > wrote: > > On 2011-02-16 18:10, Selva manickaraja wrote: > > We tried setting the trigger file for fail-over purpose. But > we just can't understand how it works. Each time the secondary > is started the trigger file is removed. How can we introduce > auto fail-over is this happens? > > Thank you. Regards, Selvam > > > That's exactly what is supposed to happen. You will also find > that the recovery.conf file gets renamed when the trigger file is > created by you (and then is promptly deleted by PostgreSQL). > > Don't create the trigger file until you want the hot-standby > server to switch roles and become the primary server (and thusly > accept DB change requests). > -- Mail to my list address MUST be sent via the mailing list. All other mail to my list address will bounce. --------------060404040403010706060800 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit On 2011-02-17 17:43, Selva manickaraja wrote:
Does this mean the fail-over will not be auto the moment the primary DB or Server goes down? Does it require for us to manually intervene to introduce the trigger_file?

Yes, and yes.

I depends upon the installation, as to how to detect that that the primary has gone down (eg, lose network connection for how long?).  Therefore, it is left to the sysadmin (that's you and me) to write external procedures to detect when it is time to fail-over, and write an appropriate automated script to create the trigger file (or do it manually).  I do it manually, because I have multiple slaves and the procedures are slightly more complex than for just one slave.

Setting up the configuration files is pretty trivial:

1. On each slave, create a recovery.conf that points to the primary.
2. Optionally, on the primary, create a recovery.done (note the different extension) that points to whichever slave you plan on later switching to a primary in case of a fail-over.

In the case of the failure of the primary:

1. Make sure the primary will not come back up (yet).
2. Create the trigger file (which triggers the switch) on whatever slave you wish to become the new primary.
3. If you have only one slave, you are done.  If you have more than one slave, you will need to (on each other slave):
3.a. Stop the slave.
3.b. Edit the recovery.conf file to point to the new primary.
3.c. Restart the slave.
3.d. If the slave does not properly sync to the new primary, you may have to (ugh) run the script below to resynchronize the slave to the new primary.

When you are ready to have the old primary become a new slave, resync the data on the old primary to the new primary (I use the following script on the old primary):

        service         postgresql      stop
        {
            cat         <<-EOF
                                SELECT  pg_start_backup( '$(date --iso-8601)', true );
                                \!      rsync   -vaz --delete  new.primary.hostname:$PGDATA/  $PGDATA/
                                SELECT  pg_stop_backup();
                        EOF
        } | psql -e       template1 postgres
        mv              $PGDATA/recovery.{done,conf}
        service         postgresql      start

Presto!  Your old primary is now back up as a slave.


On Fri, Feb 18, 2011 at 1:17 AM, Dean Gibson (DB Administrator) <postgresql@ultimeth.com> wrote:
On 2011-02-16 18:10, Selva manickaraja wrote:
We tried setting the trigger file for fail-over purpose. But we just can't understand how it works. Each time the secondary is started the trigger file is removed. How can we introduce auto fail-over is this happens?

Thank you.  Regards, Selvam

That's exactly what is supposed to happen.  You will also find that the recovery.conf file gets renamed when the trigger file is created by you (and then is promptly deleted by PostgreSQL).

Don't create the trigger file until you want the hot-standby server to switch roles and become the primary server (and thusly accept DB change requests).


-- 
Mail to my list address MUST be sent via the mailing list.
All other mail to my list address will bounce.
--------------060404040403010706060800--