Re: HA warm standby issue with trigger file.

--
Scott Mead

OpenSCG
PostgreSQL, Java & Linux Experts

http://openscg.com

On Wed, Mar 2, 2011 at 9:17 AM, Jason Clark <j(dot)clark(dot)127(dot)0(dot)0(dot)1(at)gmail(dot)com>wrote:

> Hey fellas,
>
> A little stumped here, maybe someone could give me some insight?
>
> I have set up a warm standby situation for testing before rolling it
> out to our production servers, the primary database is archiving
> properly to a samba share archive using the following archive
> command:
>
> 'cp %p /mnt/win/%f'
>
> so thats not an issue, also, the backup server is pulling the WAL
> files and loading them properly, here is my recovery.conf file:
>
> standby_mode = 'on'
> restore_command = 'usr/pgsql-9.0/bin/pg_standby -d -w 300 -t /tmp/
> trigger.5423 /mnt/win/ %f %p 2>>standby.log
> archive_cleanup_command = '/usr/pgsql-9.0/bin/pg_archivecleanup -d /
> mnt/win %r 2>>cleanup.log
>
> everything seems to run great...except when I try to use a trigger
> file to bring the backup server out of standby. From what I have read,
> if the trigger file is present (which according to the standby.log it
> checks for repeatedly) it should switch into recovery mode...loading
> all the available wal files, and then both deleting the trigger file,
> and renaming recovery.conf to recovery.done
>
>
Have you waited at least as long as your -w 300, or left that setting out?

--Scott

> here is where it goes all wonky,
>
> I create the trigger file (manually, haven't written a script for it
> yet) and regardless if the trigger file is for fast or smart failover,
> it gets hung up...first the fast failover:
>
> I create a trigger file containg "fast
> Postgres recognizes it, and the log says fast failover
> then it gets hung in a loop, for some reason it wipes the trigger
> file, making it a smart failover, but never loads the last wal file
> and comes up...just sits in the loop looking for the next wal file
> (which the primary won't send because it is down)
>
> here is the standby.log:
>
> WAL file not present yet. Checking for trigger file...
> WAL file not present yet. Checking for trigger file...
> WAL file not present yet. Checking for trigger file...
> WAL file not present yet. Checking for trigger file...
> Timed out after 304 seconds: fast failover
> WAL file not present yet. Checking for trigger file...
> Trigger file : /tmp/trigger.5423
> Waiting for WAL file : 0000000200000001000000F5
> WAL file path : /mnt/win//0000000200000001000000F5
> Restoring to : pg_xlog/RECOVERYXLOG
> Sleep interval : 5 seconds
> Max wait interval : 300 seconds
> Command for restore : cp "/mnt/win//0000000200000001000000F5"
> "pg_xlog/RECOVERYXLOG"
> Keep archive history : No cleanup required
> WAL file not present yet. Checking for trigger file...
> WAL file not present yet. Checking for trigger file...
> WAL file not present yet. Checking for trigger file...
> WAL file not present yet. Checking for trigger file...
> WAL file not present yet. Checking for trigger file...
> WAL file not present yet. Checking for trigger file...
> WAL file not present yet. Checking for trigger file...
> WAL file not present yet. Checking for trigger file...
> WAL file not present yet. Checking for trigger file...
> WAL file not present yet. Checking for trigger file...
> WAL file not present yet. Checking for trigger file...
> WAL file not present yet. Checking for trigger file...
> WAL file not present yet. Checking for trigger file...
> WAL file not present yet. Checking for trigger file...
> WAL file not present yet. Checking for trigger file...
> WAL file not present yet. Checking for trigger file...
> WAL file not present yet. Checking for trigger file...
> WAL file not present yet. Checking for trigger file...
> WAL file not present yet. Checking for trigger file...
> WAL file not present yet. Checking for trigger file...
> WAL file not present yet. Checking for trigger file...
> WAL file not present yet. Checking for trigger file...
> WAL file not present yet. Checking for trigger file...
> WAL file not present yet. Checking for trigger file...
> WAL file not present yet. Checking for trigger file...
> WAL file not present yet. Checking for trigger file...
> WAL file not present yet. Checking for trigger file...
> WAL file not present yet. Checking for trigger file...
> WAL file not present yet. Checking for trigger file...
> WAL file not present yet. Checking for trigger file...
> WAL file not present yet. Checking for trigger file...
> WAL file not present yet. Checking for trigger file...
> trigger file found: fast failover
> Trigger file : /tmp/trigger.5423
> Waiting for WAL file : 0000000200000001000000F5
> WAL file path : /mnt/win//0000000200000001000000F5
> Restoring to : pg_xlog/RECOVERYXLOG
> Sleep interval : 5 seconds
> Max wait interval : 300 seconds
> Command for restore : cp "/mnt/win//0000000200000001000000F5"
> "pg_xlog/RECOVERYXLOG"
> Keep archive history : No cleanup required
> trigger file found: smart failover
> Trigger file : /tmp/trigger.5423
> Waiting for WAL file : 0000000200000001000000F5
> WAL file path : /mnt/win//0000000200000001000000F5
> Restoring to : pg_xlog/RECOVERYXLOG
> Sleep interval : 5 seconds
> Max wait interval : 300 seconds
> Command for restore : cp "/mnt/win//0000000200000001000000F5"
> "pg_xlog/RECOVERYXLOG"
> Keep archive history : No cleanup required
> trigger file found: smart failover
> Trigger file : /tmp/trigger.5423
> Waiting for WAL file : 0000000200000001000000F5
> WAL file path : /mnt/win//0000000200000001000000F5
> Restoring to : pg_xlog/RECOVERYXLOG
> Sleep interval : 5 seconds
> Max wait interval : 300 seconds
> Command for restore : cp "/mnt/win//0000000200000001000000F5"
> "pg_xlog/RECOVERYXLOG"
> Keep archive history : No cleanup required
> trigger file found: smart failover
> Trigger file : /tmp/trigger.5423
> Waiting for WAL file : 0000000200000001000000F5
> WAL file path : /mnt/win//0000000200000001000000F5
> Restoring to : pg_xlog/RECOVERYXLOG
> Sleep interval : 5 seconds
> Max wait interval : 300 seconds
> Command for restore : cp "/mnt/win//0000000200000001000000F5"
> "pg_xlog/RECOVERYXLOG"
> Keep archive history : No cleanup required
> trigger file found: smart failover
> Trigger file : /tmp/trigger.5423
> Waiting for WAL file : 0000000200000001000000F5
> WAL file path : /mnt/win//0000000200000001000000F5
> Restoring to : pg_xlog/RECOVERYXLOG
> Sleep interval : 5 seconds
> Max wait interval : 300 seconds
> Command for restore : cp "/mnt/win//0000000200000001000000F5"
> "pg_xlog/RECOVERYXLOG"
> Keep archive history : No cleanup required
> trigger file found: smart failover
> Trigger file : /tmp/trigger.5423
> Waiting for WAL file : 0000000200000001000000F5
> WAL file path : /mnt/win//0000000200000001000000F5
> Restoring to : pg_xlog/RECOVERYXLOG
> Sleep interval : 5 seconds
> Max wait interval : 300 seconds
> Command for restore : cp "/mnt/win//0000000200000001000000F5"
> "pg_xlog/RECOVERYXLOG"
> Keep archive history : No cleanup required
> trigger file found: smart failover
> Trigger file : /tmp/trigger.5423
> Waiting for WAL file : 0000000200000001000000F5
> WAL file path : /mnt/win//0000000200000001000000F5
> Restoring to : pg_xlog/RECOVERYXLOG
> Sleep interval : 5 seconds
> Max wait interval : 300 seconds
> Command for restore : cp "/mnt/win//0000000200000001000000F5"
> "pg_xlog/RECOVERYXLOG"
> Keep archive history : No cleanup required
> trigger file found: smart failover
> Trigger file : /tmp/trigger.5423
> Waiting for WAL file : 0000000200000001000000F5
> WAL file path : /mnt/win//0000000200000001000000F5
> Restoring to : pg_xlog/RECOVERYXLOG
> Sleep interval : 5 seconds
> Max wait interval : 300 seconds
> Command for restore : cp "/mnt/win//0000000200000001000000F5"
> "pg_xlog/RECOVERYXLOG"
> Keep archive history : No cleanup required
> trigger file found: smart failover
>
>
> I have found very little documentation on the trigger file for
> pg_standby....and whether it is a fast or smart failover the same
> thing happens every time..it will sit in that loop for
> eternity...never coming up...any ideas on what it could be? Thanks in
> advance guys.
>
> J
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>