Re: postgresql.conf archive_command example

On 09/02/2011 01:00 PM, Robert Haas wrote:
> On Fri, Sep 2, 2011 at 10:34 AM, Kevin Grittner
> <Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
>> Dimitri Fontaine<dimitri(at)2ndQuadrant(dot)fr> wrote:
>>> Robert Haas<robertmhaas(at)gmail(dot)com> writes:
>>>>> I'm also wondering if providing some shell script examples of a
>>>>> fault-tolerant script to handle archiving would be useful.
>>>> I think it would.
>>> My usual advice is to avoid having to write one if possible,
>>> because it's more complex than it looks. What about recommending
>>> existing solutions, such as walmgr from Skytools?
>>>
>>> Even better, what about including a default archiving tool, that
>>> could be either another script in bin/ or rather an internal
>>> command. The default would accept a location as argument, for
>>> simple needs you mount a remote filesystem and there you go. If
>>> you need something more complex, you still can provide it
>>> yourself.
>> In a green field I might argue for having an archvie_directory GUC
>> instead of archive_command. As it stands, it might be a really good
>> idea to provide a pg_archiveto executable which takes as arguments a
>> directory path and the arguments passed to the archive script. With
>> a little extra effort, the executable could check for some file
>> which would specify what host and path should be writing archives
>> there, to avoid problems with copied database directories
>> accidentally writing to the same location as the source.
>>
>> Such an executable seems like minimal effort compared to the
>> problems it would solve.
>>
>> If there's an existing tool with appropriate licensing which is
>> sufficiently portable and reliable, all the better -- let's ship it
>> and use that for our example archive_command.
> Another thought I have here is to wonder whether we should change
> something on the server side so that we don't NEED such a complicated
> archive_command. I mean, copying a file to a directory somewhere is
> not fundamentally a complex operation. Nor is using ssh to copy it to
> another machine. The fact that archive_commands need to be so complex
> seems like a usability defect. The consensus seems to be that just
> using something like 'cp' for your archive command won't work out
> well, but maybe instead of shipping a more complicated script we
> should be trying to eliminate (or at least reduce) the need for a more
> complicated script.
>

The problem is that the number of ways you might want to do things is
quite large. For example, you might want to copy the archives to more
than one place for safety reasons. I pretty much always set
archive_command to a script which I can then customize to my heart's
content, and it seems to work pretty well. Providing a simple example of
such a script seems like it could be useful.

cheers

andrew