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

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.

Greetings,

Andres Freund

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