Thread: question on warm standby

Hi,
My problem probably is that I need to look at the forest but am
hyper-focusing on a maple twig.  I have been looking at this for 2 days now
And would appreciate some feedback...

I am running postgres 8.3.4 on master(mymachine) and slave(hummer).  I am
attempting to implement warm standby.
  1.  On mymachine I have the following archive_command:
      Scp %p postgres@hummer:/var/backups/archlog/%f
          (scp has been set up with ssh keys so that password
               Is not required for postgres user)
    This works fine.  Copies wal logs to the /var/backups/archlog directory.

  2. Before I attempt pg_standby or some other wait script on hummer I have
      Used a simple cp command in my recovery.conf file to manually restore
       My first set of updates:

     restore_command = 'cp /var/backups/archlog/%f  %p'

       From the doc I am assuming that %f should be the file name(s) of the
        Wal log in /var/backups/archlog/ to be copied into the
         /*/*/*/*/pg_xlog directory referenced by %p
        But %f seems to be referencing current wal log names in
         /*/*/*/*/pg_xlog.  I am getting messages like:

cp: cannot stat `/var/backups/archlog/0000000600000003000000D2':
      No such file or directory

      0000000600000003000000D2 is an actual wal log in hummer's
       /*/*/*/*/pg_xlog directory.

  How do I get the %f macro to reference the file names in the
    /var/backups/archlog directory?

Once I get past this, then I can play around with pg_standby and get
Real warm_standby started.

Thanks for your help,


Committed to Creating @utoEnthusiasts. Please provide us with your feedback.

________________________________

Mark Steben│Database Administrator│ @utoRevenueT
95 Ashley Ave. West Springfield, MA., 01089
413-243-4800 x1512 (Phone) │ 413-732-1824 (Fax)
A Division of Dominion Enterprises

"Mark Steben" <msteben@autorevenue.com> writes:
> I am running postgres 8.3.4 on master(mymachine) and slave(hummer).  I am
> attempting to implement warm standby.
>   1.  On mymachine I have the following archive_command:
>       Scp %p postgres@hummer:/var/backups/archlog/%f
>           (scp has been set up with ssh keys so that password
>                Is not required for postgres user)
>     This works fine.  Copies wal logs to the /var/backups/archlog directory.

Okay ...

>   2. Before I attempt pg_standby or some other wait script on hummer I have
>       Used a simple cp command in my recovery.conf file to manually restore
>        My first set of updates:

>      restore_command = 'cp /var/backups/archlog/%f  %p'

This looks fine too, perfectly standard.

>        From the doc I am assuming that %f should be the file name(s) of the
>         Wal log in /var/backups/archlog/ to be copied into the
>          /*/*/*/*/pg_xlog directory referenced by %p
>         But %f seems to be referencing current wal log names in
>          /*/*/*/*/pg_xlog.  I am getting messages like:

> cp: cannot stat `/var/backups/archlog/0000000600000003000000D2':
>       No such file or directory

Are you sure that's an error?  As per the docs, the restore_command will
sometimes be asked for files that aren't there.  I'd expect one or two
such failures in a restore session.

>       0000000600000003000000D2 is an actual wal log in hummer's
>        /*/*/*/*/pg_xlog directory.

Yeah, but is it in hummer's /var/backups/archlog ?

            regards, tom lane

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thursday, November 13, 2008 10:06 PM
To: Mark Steben
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] question on warm standby

"Mark Steben" <msteben@autorevenue.com> writes:
> I am running postgres 8.3.4 on master(mymachine) and slave(hummer).  I am
> attempting to implement warm standby.
>   1.  On mymachine I have the following archive_command:
>       Scp %p postgres@hummer:/var/backups/archlog/%f
>           (scp has been set up with ssh keys so that password
>                Is not required for postgres user)
>     This works fine.  Copies wal logs to the /var/backups/archlog
directory.

Okay ...

>   2. Before I attempt pg_standby or some other wait script on hummer I
have
>       Used a simple cp command in my recovery.conf file to manually
restore
>        My first set of updates:

>      restore_command = 'cp /var/backups/archlog/%f  %p'

This looks fine too, perfectly standard.

>        From the doc I am assuming that %f should be the file name(s) of
the
>         Wal log in /var/backups/archlog/ to be copied into the
>          /*/*/*/*/pg_xlog directory referenced by %p
>         But %f seems to be referencing current wal log names in
>          /*/*/*/*/pg_xlog.  I am getting messages like:

> cp: cannot stat `/var/backups/archlog/0000000600000003000000D2':
>       No such file or directory

Are you sure that's an error?  As per the docs, the restore_command will
sometimes be asked for files that aren't there.  I'd expect one or two
such failures in a restore session.

>       0000000600000003000000D2 is an actual wal log in hummer's
>        /*/*/*/*/pg_xlog directory.

Yeah, but is it in hummer's /var/backups/archlog ?

[Mark Steben] No it's not. But there are wal logs in there
Copied successfully from the master that I expected to be
Applied to the database and were not

            regards, tom lane

"Mark Steben" <msteben@autorevenue.com> writes:
>> Yeah, but is it in hummer's /var/backups/archlog ?

> [Mark Steben] No it's not. But there are wal logs in there
> Copied successfully from the master that I expected to be
> Applied to the database and were not

Well, that would require some deeper investigation than whether the
restore_command throws an expected error ...

            regards, tom lane