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

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.We'd have to somehow cover that same scenario on replicas too.
 

Perhaps there isn't really an issue here; I suspect ADAT is very rarely used. What I'm worried about though is that
someoneis using it regularly for some reason, with non-trivial databases, and this is going to completely hose them.
 
-- 
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com