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

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.

regards, tom lane