Re: archive_command vs. cp -i

Hi all:

Corrected the top posting.

On Fri, Jun 17, 2011 at 12:55:07PM -0400, Tim wrote:
> On Fri, Jun 17, 2011 at 12:32 PM, Kevin Grittner
> Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> > "Martin Münstermann"<mmuenst(at)gmx(dot)de> wrote:
> > > while setting up a new linux postgresql server, I checked my
> > > archive_command. I noticed that the usual "cp -i f1 f2 <
> > > /dev/null" did NOT as expected: It did not overwrite the file
> > > (PASS), but it returned zero (FAIL, should return error).
> > >
> > > This could be reproduced on CentOS 5.6, RHEL4, and Ubuntu 10.04.
> > I've confirmed on SLES 10 and Ubuntu 9, too.

Not really surprising since choosing to not copy a file isn't really
an indication of an error when copying a file so...

I get the same result (0 exit status) on solaris 8 and solaris 10 with
/bin/cp. I wonder what platform the example worked on.

> > > Should the BACKUP-ARCHIVING-WAL doc be changed?
> >
> > I think so. Given the wide variety of platforms on which the
> > example could silently cause data loss, I *really* don't think we
> > want that in our docs. Someone could blithely copy it into
> > production without testing and not know they had it wrong until a
> > backup failed to restore.

For what it's worth I strongly agree.

> Technically the documentation covers its self by saying:
> "This is an example, not a recommendation, and might not work on all
> platforms."
> What would be nice is if the documentation could be made more useful by
> providing some more complex example scripts.
> What do our readers use for WAL archiving?

'test ! -f /var/bak/pgsql/%f && cp %p /var/bak/pgsql/%f'

Which does have a slight race condition as the output file could be
created between the test finishing and the cp starting. But the only
way that is likely to happen is if postgresql tried to run two
identical archive commands in parallel, and I think I would have other
issues if that was happening.

The following may be safer if you are using a shell with noclobber, or
it may not be. Depends on the race conditions in how the shell
implements the noclobber code.

'sh -c "set -o noclobber; cat %p > /var/bak/pgsql/%f"'

--
-- rouilj

John Rouillard System Administrator Renesys Corporation