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