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

Andres,

* Andres Freund (andres(at)2ndquadrant(dot)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.

Another thought would be to offer both- perhaps an AD .. ST ..
CONCURRENTLY option which would WAL. Or a way to say "my backup is more
important than some AD .. ST ..", which I could see some admins wanting.

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?

Thanks,

Stephen