Thread: HA warm standby issue with trigger file.

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

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

On Wed, Mar 2, 2011 at 11:17 PM, Jason Clark
<j.clark.127.0.0.1@gmail.com> wrote:
> 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

If pg_standby is used, standby_mode must be set to off. Otherwise,
as you observed, the trigger file cannot promote the standby.

In 9.0, pg_standby doesn't remove the trigger file automatically,
so if you want that, you need to set recovery_end_command to
the command which removes the trigger file.

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

On Mar 2, 12:02 pm, sco...@openscg.com (Scott Mead) wrote:
> --
> Scott Mead
>
> OpenSCG
> PostgreSQL, Java & Linux Experts
>
> http://openscg.com
>
> On Wed, Mar 2, 2011 at 9:17 AM, Jason Clark <j.clark.127.0....@gmail.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-ad...@postgresql.org)
> > To make changes to your subscription:
> >http://www.postgresql.org/mailpref/pgsql-admin

yes, I have done both...it has timed out before...and does the same
thing...repeats the loop...if I leave the -w option out, it still
repeats.

On Mar 2, 8:37 pm, masao.fu...@gmail.com (Fujii Masao) wrote:
> On Wed, Mar 2, 2011 at 11:17 PM, Jason Clark
>
> <j.clark.127.0....@gmail.com> wrote:
> > 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
>
> If pg_standby is used, standby_mode must be set to off. Otherwise,
> as you observed, the trigger file cannot promote the standby.
>
> In 9.0, pg_standby doesn't remove the trigger file automatically,
> so if you want that, you need to set recovery_end_command to
> the command which removes the trigger file.
>
> Regards,
>
> --
> Fujii Masao
> NIPPON TELEGRAPH AND TELEPHONE CORPORATION
> NTT Open Source Software Center
>
> --
> Sent via pgsql-admin mailing list (pgsql-ad...@postgresql.org)
> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-admin

Perfect! took ages to find that bit. Thank you a ton!