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

<p dir="ltr"><br /> On Jun 12, 2013 4:56 PM, "Robert Haas" <<a
href="mailto:robertmhaas@gmail.com">robertmhaas@gmail.com</a>>wrote:<br /> ><br /> > On Sat, Jun 8, 2013 at
10:36AM, MauMau <<a href="mailto:maumau307@gmail.com">maumau307@gmail.com</a>> wrote:<br /> > > Yes, I feel
designingreliable archiving, even for the simplest case - copy<br /> > > WAL to disk, is very difficult.  I know
thereare following three problems<br /> > > if you just follow the PostgreSQL manual.  Average users won't notice
them.<br/> > > I guess even professional DBAs migrating from other DBMSs won't, either.<br /> > ><br />
>> 1. If the machine or postgres crashes while archive_command is copying a WAL<br /> > > file, later
archiverecovery fails.<br /> > > This is because cp leaves a file of less than 16MB in archive area, and<br />
>> postgres refuses to start when it finds such a small archive WAL file.<br /> > > The solution, which
IIRCTomas san told me here, is to do like "cp %p<br /> > > /archive/dir/%f.tmp && mv /archive/dir/%f.tmp
/archive/dir/%f".<br/> > ><br /> > > 2. archive_command dumps core when you run pg_ctl stop -mi.<br /> >
>This is because postmaster sends SIGQUIT to all its descendants.  The core<br /> > > files accumulate in the
datadirectory, which will be backed up with the<br /> > > database.  Of course those core files are garbage.<br
/>> > archive_command script needs to catch SIGQUIT and exit.<br /> > ><br /> > > 3. You cannot know
thereason of archive_command failure (e.g. archive area<br /> > > full) if you don't use PostgreSQL's server
logging.<br/> > > This is because archive_command failure is not logged in syslog/eventlog.<br /> > ><br />
>><br /> > > I hope PostgreSQL will provide a reliable archiving facility that is ready<br /> > > to
use.<br/> ><br /> > +1.  I think we should have a way to set an archive DIRECTORY, rather<br /> > than an
archivecommand.  And if you set it, then PostgreSQL should<br /> > just do all of that stuff correctly, without any
helpfrom the user.<p dir="ltr">Wouldn't that encourage people to do local archiving, which is almost always a bad idea?
<pdir="ltr">I'd rather improve the experience with pg_receivexlog or another way that does remote archiving... <br /><p
dir="ltr">>Of course, some users will want to archive to a remote machine via ssh<br /> > or rsync or
what-have-you,and those users will need to provide their<br /> > own tools.  But it's got to be pretty common to
archiveto a local<br /> > path that happens to be a remote mount, or to a local directory whose<br /> > contents
aresubsequently copied off by a batch job.  Making that work<br /> > nicely with near-zero configuration would be a
significantadvance.<p dir="ltr">I guess archiving to a nfs mount or so isn't too bad, but archiving and using a cronjob
toget the files off is typically a great way to loose data, and we really shouldn't encourage that by default, Imo. <p
dir="ltr">/Magnus