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

From: Stephen Frost <sfrost(at)snowman(dot)net>
To: Andres Freund <andres(at)2ndquadrant(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: basebackups during ALTER DATABASE ... SET TABLESPACE ... not safe?
Date: 2015-01-22 21:38:49
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers


* 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



In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Alvaro Herrera 2015-01-22 21:43:06 Re: alter user/role CURRENT_USER
Previous Message Andreas Karlsson 2015-01-22 21:31:44 Re: PATCH: Reducing lock strength of trigger and foreign key DDL