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

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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. +