Re: Hard limit on WAL space used (because PANIC sucks)

On Jun 12, 2013 4:56 PM, "Robert Haas" <robertmhaas(at)gmail(dot)com> wrote:
>
> On Sat, Jun 8, 2013 at 10:36 AM, MauMau <maumau307(at)gmail(dot)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.

Wouldn't that encourage people to do local archiving, which is almost
always a bad idea?

I'd rather improve the experience with pg_receivexlog or another way that
does remote archiving...

> 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.

I guess archiving to a nfs mount or so isn't too bad, but archiving and
using a cronjob to get the files off is typically a great way to loose
data, and we really shouldn't encourage that by default, Imo.

/Magnus