I encountered a small issue when I run the script in the old-primary. The "SELECT pg_start_backup...." part of the script will fail since the postgres service has been stopped. Is this error meant to happen?
On Fri, Feb 18, 2011 at 11:55 AM, Dean Gibson (DB Administrator) < postgre...@ultimeth.com> wrote: > 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) < > postgre...@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. > >