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.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company