Re: Is full_page_writes=off safe in conjunction with PITR?

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > This is a much simpler fix than people talking about writing their own
> > backup programs.
> 
> Well, it's still not exactly trivial.  The hack that was being proposed
> involved having the admin manually do
> 
>     full_page_writes = ON (ie, edit config file and SIGHUP)
>     pg_start_backup
>     take backup dump
>     pg_stop_backup
>     full_page_writes = OFF (ie, edit config file and SIGHUP)
> 
> with some additions to pg_start_backup/pg_stop_backup to complain if 
> full_page_writes isn't ON.  Aside from being a PITA, this isn't at
> all secure, first for the obvious reason that we're only checking
> full_page_writes at start/stop and not whether it was on for the whole
> interval, and second because SIGHUP is asynchronous.  Backends respond
> to the signal when they feel like it (in practice, upon starting a new
> interactive command) and so it'd be quite possible for a long-running
> query to still be executing with full_page_writes off long after the
> pg_start_backup has occurred.
> 
> If we were to do this, I'd want some more-bulletproof mechanism for
> forcing full_page_writes on during the backup.  We could probably
> keep a "backup in progress" flag in shared memory, and examine that
> along with the GUC variable before deciding to omit a full-page write.
> 
> I seem to recall that there were previous proposals for such a flag,
> which I resisted because I didn't want any macroscopic user-visible
> change in behavior during a backup.  But forcing full-page WAL writes
> is something I could live with as a "backup mode" behavior.

Yes, good point.  The setting has to be seen by all backends at the same
time, so yea, a shared memory variable seems required.

The manual method is clearly a loser.

--  Bruce Momjian   http://candle.pha.pa.us EnterpriseDB    http://www.enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +