>Josh Berkus [mailto:josh@agliodbs.com]
> > wal_archive_policy and enable/disable archiving accordingly. This
> > parameter can only be changed at server start. (This is required
because
> > the initial step of archiving each xlog is performed by the backend;
if
> > this were changeable after boot, then it might be possible for an
> > individual backend to override the wal_archive_policy and choose not
to
> > archive - which would then effect the whole system and all users,
not
> > just the user making that choice). It is considered less desirable
to
>
> Let me voice a real-world exception to this policy. Imagine that you
are
> running an OLAP or decision-support database that analyzes data coming
> from
> an external source. Once a day you load 250MB of data via COPY and
then
> does transformations on that data. While doing the load, you do
*not*
> want
> the archiver running, as it would quickly fill up the WAL partition
and
> backlog the archive tape.
> Under the proposed PITR spec, the only way to handle this would be to:
> 1) Full back up
> 2) Shut down PG
> 3) Restart PG without archiving
> 4) Load the data
> 5) Shut down PG again
> 6) Restart PG with archiving
> 7) Full back-up again.
> DBAs would like it much more if starting/stopping the archiver was
> possible
> via a superuser (not regular user) GUC. This would allow a much
faster
> cycle:
> 1) Full back up
> 2) Stop archiving
> 3) Load the data
> 4) Restart archiving
> 5) Full back-up
The scenario you mention is what I'd like to do, but don't just yet see
how.
I'd welcome input on this point, since I don't fully understand GUCs:
Thinking about this:
1. Since the backends run XLogArchiveNotify(), they must all do so
identically. One slip invalidates all the work of all the others.
GUC Options are:
INTERNAL - not appropriate
POSTMASTER - what I originally envisaged, but not what you want
SIGHUP - seems to allow different parameter settings in each backend
BACKEND - not appropriate
SUSET - maybe what you're looking for???????
USERLIMIT - no
USERSET - absolutely no
2. Maybe have Postmaster run something every so often that looks for
full xlogs and then executes XLogArchiveNotify() for them?
Thoughts anyone?
Best Regards, Simon Riggs