> On Sat, Jun 8, 2013 at 10:36 AM, MauMau <maumau307@gmail.com> wrote:
>> Yes, I feel designing reliable archiving, even for the simplest case - copy
>> WAL to disk, is very difficult. I know there are following three problems
>> if you just follow the PostgreSQL manual. Average users won't notice them.
>> I guess even professional DBAs migrating from other DBMSs won't, either.
>>
>> 1. If the machine or postgres crashes while archive_command is copying a WAL
>> file, later archive recovery fails.
>> This is because cp leaves a file of less than 16MB in archive area, and
>> postgres refuses to start when it finds such a small archive WAL file.
>> The solution, which IIRC Tomas san told me here, is to do like "cp %p
>> /archive/dir/%f.tmp && mv /archive/dir/%f.tmp /archive/dir/%f".
>>
>> 2. archive_command dumps core when you run pg_ctl stop -mi.
>> This is because postmaster sends SIGQUIT to all its descendants. The core
>> files accumulate in the data directory, which will be backed up with the
>> database. Of course those core files are garbage.
>> archive_command script needs to catch SIGQUIT and exit.
>>
>> 3. You cannot know the reason of archive_command failure (e.g. archive area
>> full) if you don't use PostgreSQL's server logging.
>> This is because archive_command failure is not logged in syslog/eventlog.
>>
>>
>> I hope PostgreSQL will provide a reliable archiving facility that is ready
>> to use.
>
> +1. I think we should have a way to set an archive DIRECTORY, rather
> than an archive command. And if you set it, then PostgreSQL should
> just do all of that stuff correctly, without any help from the user.
> Of course, some users will want to archive to a remote machine via ssh
> or rsync or what-have-you, and those users will need to provide their
> own tools. But it's got to be pretty common to archive to a local
> path that happens to be a remote mount, or to a local directory whose
> contents are subsequently copied off by a batch job. Making that work
> nicely with near-zero configuration would be a significant advance.
And there's another example why we need an archive command:
> I'm just setting up pgpool replication on Amazon AWS.
> I'm sending WAL archives to an S3 bucket, which doesn't appear as a
> directory on the server.
From:
http://www.pgpool.net/pipermail/pgpool-general/2013-June/001851.html
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp