Обсуждение: WAL shipping question

SHARMILA JOTHIRAJAH wrote:
> Hi,
> Im trying to play a bit with log shipping between 2 servers primary and standby. These servers are running versions
8.3betat3.I had set up continuous archiving at the primary server. The manuals' example of archive_command is 
> archive_command = 'cp -i %p /mnt/server/archivedir/%f </dev/null'
>
> This basically archives the data in the primary server itself...right!!!
> But how can I set up continuous archiving from primary to a directory (WAL archive directory)
> on the stand-by server ?

a. Mount the remote directory via NFS/SMBFS/...
b. Use a remote copy, e.g. scp, FTP...

--
   Richard Huxton
   Archonet Ltd

SHARMILA JOTHIRAJAH wrote:
>> Im trying to play a bit with log shipping between 2 servers primary
>  and standby. These servers are running versions 8.3betat3. I had set up
>  continuous archiving at the primary server. The manuals' example of
>  archive_command is
>> archive_command = 'cp -i %p /mnt/server/archivedir/%f </dev/null'
>>
>> This basically archives the data in the primary server
>  itself...right!!!
>> But how can I set up continuous archiving from primary to a directory
>  (WAL archive directory)
>> on the stand-by server ?
>
>>> a. Mount the remote directory via NFS/SMBFS/...
>>> b. Use a remote copy, e.g. scp, FTP...
>
>
> Thanks...
> I tried this command to both copy in the primary server and scp to standby server.
>
> archive_command = 'cp -i "%p" .../archivedir/"%f" &&
> scp -B "%p".../archivedir/"%f"  "%p" user@172.31.0.21:/....archivedir/"%f"'
>
> cp works but scp doesnt work. Is it becoz scp generally asks for password. I can do scp or ssh without password
authenticationbut Im not sure if it is safe to do that. 
>
> What is the alternative for getting this done?

You can generate a public/private key pair on the database server and
store the public version in ~/.ssh/authorized_keys on the remote server.
Google for details.

--
   Richard Huxton
   Archonet Ltd

On Tue, 4 Dec 2007, SHARMILA JOTHIRAJAH wrote:

> This basically archives the data in the primary server itself...right!!!
> But how can I set up continuous archiving from primary to a directory
> (WAL archive directory) on the stand-by server ?

The closest thing to a worked out example of how to do this I'm aware of
is at http://archives.postgresql.org/sydpug/2006-10/msg00001.php

That uses rsync as the transport mechanism for reasons it explains (the
'atomic copy' feature).  You can certainly replicate that using ssh, but
you may have to use a secondary directory to hold files while they're
being transferred so the stand-by doesn't try to do something with the
partial copies.  Mounting filesystems and copying the files over
Samba/NFS/etc. is another approach with its own issues.  It's been my
experience that remote filesystems will hang in odd ways when there's a
connectivity problem, while copying with ssh/scp gives you a more
predictable copied/failed return code without retrying too hard.
PostgreSQL can tolerate the archive_command spitting back an error just
fine and will retry automatically, I prefer not to expose the server to a
situation where the archive_command might not return quickly.

The main thing that's improved in 8.3 is the integration of pg_standby as
a more rugged restore_command than most people were coding on their own:

http://www.postgresql.org/docs/8.3/static/pgstandby.html

You should use it instead of the example restore.sh included in the
message I referenced above.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

On Dec 5, 2007, at 1:39 PM, SHARMILA JOTHIRAJAH wrote:

>
>
> > This basically archives the data in the primary server
> itself...right!!!
> > But how can I set up continuous archiving from primary to a
> directory
> > (WAL archive directory) on the stand-by server ?
>
> >>The closest thing to a worked out example of how to do this I'm
> aware of
> >>is at http://archives.postgresql.org/sydpug/2006-10/msg00001.php
>
> I tried this example
> I have the primary and standby server setup. The primary server is
> archiving the wal segments in the standby server's directory. My
> question is that, as per that example in step 15 the standby server
> should gives messages in its log whenever some WAL activity is
> triggered in the master. I see the archive directory in standby
> server getting filled up, but there are no log messages generated.
> Whay? What am I missing here?

Make sure you're looking in the whatever location you have postgres
configured to log in.  If you're still not seeing log messages there,
then you've done something wrong.

> Also what does step 16 mean in that above example?
> " To initiate a failover from the master to the slave, create the
> 'trigger file':
> touch ~/pg82demo/trigger
> This should immediately cause the slave to finish processing archived
> segments, exit  recovery mode, and come up ready for use "

touch is a *nix utility (maybe on windows, too?) that simply creates
a file if it doesn't exist.  In the example standby implementation in
the linked post, the recovery.sh script looks for the existence of
that file named trigger in pg82demo/ and, if it sees it, brings the
standby out of recovery mode and into normal operation.
>
>
> Does the wal archives are applied to the standby server at this
> point and it has all the
> contents(tables,indexes,tabledata etc) at this point?

If you're not getting log output to the effect of wals being
replayed, then no.

Erik Jones

Software Developer | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com

On Wed, 5 Dec 2007, SHARMILA JOTHIRAJAH wrote:

> I see the archive directory in standby server getting filled up, but
> there are no log messages generated. Whay? What am I missing here?

If you're using pg_standby, you can pass it -d to have it log more
information about what's going on, which may help you figure out your
issue.  It may be that you need to adjust the logging settings in the
postgresql.conf file as well.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

SHARMILA JOTHIRAJAH wrote:

> How do you install pg_standby. I get the following error when i try the Makefile
>
> sj@sys1:~/postgres8.3/pgsql> /export/home/sj/postgresql-8.3beta3/contrib/pg_standby/Makefile

The Makefile is not a shell script.  Run just "make" and then "make
install".

--
Alvaro Herrera                 http://www.amazon.com/gp/registry/CTMLCN8V17R4
"You knock on that door or the sun will be shining on places inside you
that the sun doesn't usually shine" (en Death: "The High Cost of Living")