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

On 2015-01-22 14:42:18 -0600, Jim Nasby wrote:
> On 1/22/15 1:43 PM, Alvaro Herrera wrote:
> >Andres Freund wrote:
> >
> >>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.
> >
> >Not to mention the extra WAL traffic ...
>
> Yeah, I don't know that we actually want #2. It's bad enough to copy
> an entire database locally

The local copy is pretty much fundamental. Given that tablespaces
usually will be on different filesystems there's not much else we can
do.

If we want a optimization for moving databases across tablespaces if
both are on the same filesystems and wal_level < archive, we can do
that. But that's pretty independent. And I doubt it's worthwile the
developer time.

> , but to then put it's entire contents into WAL? Blech.

Besides actually having a chance of being correct, doing so will save
having to do two checkpoints inside movedb(). I think it's pretty likely
that that actually saves overall IO, even including the WAL
writes. Especially if there's other databases in the cluster at the same
time.

Greetings,

Andres Freund

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