Re: basebackups during ALTER DATABASE ... SET TABLESPACE ... not safe?

On 2015-01-22 16:38:49 -0500, Stephen Frost wrote:
> Andres,
> 
> * Andres Freund (andres@2ndquadrant.com) wrote:
> > 1) Make do_pg_start_backup() acquire a SHARE lock on
> >    pg_database. That'll prevent it from starting while a movedb() is
> >    still in progress. Then additionally add pg_backup_in_progress()
> >    function to xlog.c that checks (XLogCtl->Insert.exclusiveBackup ||
> >    XLogCtl->Insert.nonExclusiveBackups != 0). Use that in createdb() and
> >    movedb() to error out if a backup is in progress.
> > 
> >    Not pretty, but sounds doable.
> > 
> >    We've discussed trying to sleep instead of erroring out in movedb(),
> >    while a base backup is in progress, but that's nontrivial. I also
> >    don't think ALTER DATABASE is ever intentionally run at the time of a
> >    base backup.
> > 
> > 2) Make movedb() (and possibly created(), not sure yet) use proper WAL
> >    logging and log the whole copied data. I think this is the right long
> >    term fix and would end up being much more reliable. But it either
> >    requires some uglyness during redo (creating nonexistant database
> >    directories on the fly during redo) or new wal records.
> > 
> >    Doable, but probably too large/invasive to backpatch.
> > 
> > Thanks for Alvaro and Petr for discussing the problem.
> > 
> > I lean towards doing 1) in all branches and then doing 2) in master.
> 
> I'm trying to figure out why you'd do '2' in master when in discussion
> of '1' you say "I also don't think ALTER DATABASE is even intentionally
> run at the time of a base backup."  I agree with that sentiment and am
> inclined to say that '1' is good enough throughout.

Because the way it currently works is a major crock. It's more luck than
anything that it actually somewhat works. We normally rely on WAL to
bring us into a consistent state. But around CREATE/MOVE/DROP DATABASE
we've ignored that.

My point about not intentionally running it at the same isn't that it's
not happening, it's that it's not intended to happen at the same
time. But most sane sites these days actually do use automated
basebackups - making it much harder to be safe against this.



And. Hm. The difficulty of the current method is evidenced by the fact
that so far nodoby recognized that 1) as described above isn't actually
safe.  It fails to protect against basebackups on a standby as its
XLogCtl state will obviously not be visible on the master.

For exclusive basebackups (i.e. SELECT pg_start/stop_backup()) we can't
rely on locking because these commands can happen in independent
sessions. But I think we can in the builtin nonexclusive basebackups, as
it's run in one session. So I guess we could rely on recovery conflicts
not allowing the XLOG_DBASE_CREATE/DROP to replicate. It's nasty that a
basebackup can suddenly stop replication from progressing though :(.
Additionally it'd not protect stuff like pgespresso (an extension for
nonexclusive standby basebackups).

> The other question is- what about AT .. ST?  That is, ALTER TABLE .. SET
> TABLESPACE.  Do we do things differently there or is there a similar
> issue?

No issue, because it actually is implemented halfway sanely sanely and
uses WAL logging.  I personally don't like at all that it uses
FlushRelationBuffers() and reads the tables on the smgr level instead of
using the buffer manager and a bulk state, but ...

Greetings,

Andres Freund

-- Andres Freund                       http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training &
Services