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

On 2015-01-23 13:01:34 -0600, Jim Nasby wrote:
> On 1/23/15 9:10 AM, Andres Freund wrote:
> >On 2015-01-22 22:58:17 +0100, Andres Freund wrote:
> >>On 2015-01-22 16:38:49 -0500, Stephen Frost wrote:
> >>>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.
> >
> >>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.
> >
> >Further evidenced by the fact that the current method isn't
> >crash/shutdown safe at all. If a standby was shut down/crashed/was
> >started on a base backup when a CREATE DATABASE from the primary is
> >replayed the template database used can be in an nearly arbitrarily bad
> >state. It'll later get fixed up by recovery - but those changes won't
> >make it to the copied database.
> 
> I think we all agree that ADAT can't run while a base backup is
> happening, which I believe is what you're describing above.

No, that's not what I'm descirbing in the last paragraph. The problem is
present without any AD ST. When a cluster crashes it's likely not in a
consistent state - we need to replay from the last checkpoint's REDO
pointer to the minimum recovery location to make it so. The problem
though is that if the copied database (both for CREATE DB/SET
TABLESPACE) is copied that record can be replayed well before the
database is in a consistent state. In that case the new copy will be in
a corrupted state as it'll not be fixed up by recovery, in contrast to
the original, which will.

> Perhaps there isn't really an issue here; I suspect ADAT is very
> rarely used. What I'm worried about though is that someone is using it
> regularly for some reason, with non-trivial databases, and this is
> going to completely hose them.

I think if somebody does this regularly on nontrivialy sized databases,
over replication, they'd have hit this bug a long time ago. It's really
not that hard to reproduce.

Greetings,

Andres Freund

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