Re: pg_basebackup vs. Windows and tablespaces

On 08/01/2013 12:15 PM, Noah Misch wrote:
> A "pg_basebackup -Fp" running on the same system as the target cluster will
> fail in the presence of tablespaces; it would backup each tablespace to its
> original path, and those paths are in use locally for the very originals we're
> copying. "pg_basebackup -Ft" does not exhibit that hazard, and I typically
> recommend it for folks using tablespaces.
>
> On Windows, we populate pg_tblspc with NTFS junction points. "pg_basebackup
> -Fp" reproduces them, and "pg_basebackup -Ft" stores them in the tar archive
> as symbolic links. Trouble arises for -Ft backups: no Windows tar expander
> that I've found will recreate the junction points. While -Fp backups are
> basically usable, commands that copy files on Windows are inconsistent about
> their support for junction points; duplicating a base backup after the fact is
> error-prone. Windows users of tablespaces are left with limited options: use
> "pg_basebackup -Fp" on a different system, or use -Ft but manually recreate
> the junction points. We can do better; I see a few options:
>
> 1. Include in the base backup a file listing symbolic links/junction points,
> then have archive recovery recreate them. This file would be managed like the
> backup label file; exclusive backups would actually write it to the master
> data directory, and non-exclusive backups would incorporate it on the fly.
> pg_basebackup could also omit the actual links from its backup. Nearly any
> tar or file copy utility would then suffice.
>
> 2. Add a pg_basebackup option like "--destdir" or "--sysroot", meaningful only
> with -Fp; tablespace backups will be stored relative to it. So if the actual
> tablespace path is c:/foo, --destdir=c:/backups/today would backup that
> tablespace to c:/backups/today/c/foo. This facilitates same-server use of -Fp
> on all platforms.
>
> 3. Use path concatenation instead of symbolic links/junction points for
> tablespaces. More invasive, no doubt. For example, we would need to devise a
> way for recovery to get the tablespace path.
>
> I think #1 is a good bet; it's self-contained and fully heals the situation
> for Windows users. By itself, #2 helps less than #1 on Windows. It may have
> independent value. Other ideas, opinions?
>

Thanks for raising this. I agree it's an area that needs work.

I like #1, it seems nice and workable.

I also like the concept of #2, but I think we need to think about it a
bit more. One of the things I like about barman backups is that on
recovery you can map where tablespaces go, on a per tablespace basis
(it's not very well documented, or wasn't when I last looked, but it
does work). I think something like that would be awesome to have for
pg_basebackup. So allowing multiple options of the form

--map-tablespace c:/foo/bar=d:/baz/blurfl

or some such would be great.

cheers

andrew