I tried running pg_basebackup in plain format with option -X stream (pg_basebackup -D "F:\208376PT\db" -X stream -l "208376PT17022015" -U postgres -P) but I got the message:
pg_basebackup: directory "E:\Data\Database" exists but is not empty"
I creatde a tablespace using CREATE TABLESPACE at the location mentioned in the message. According to what I read online about this, this message is issued when a tablespace was created under PGDATA. In my case, only the directory junction pointing to my tablespace (on a different drive than PGDATA) exists under PGDATA, not the tablespace itself.
The only way I can run pg_basebackup with WAL files is with option -Ft and -X fetch. I'd much prefer using plain mode since my 670 GB tablespace takes a lot of time to extract when tarred. Is there another way to approach this?
All I can come up with at the moment
So what is the path on the original machine and can it be replicated on the new machine, at least temporarily?
The path on the original (i.e. source) machine is: "E:\Data\Database\PG_9.3_201306121\.."
I'm thinking if the path can be replicated, let pg_basebackup write to it and then create the tablespace you want and do ALTER TABLE SET TABLESPACE to move the tables. You would also need to do this for indexes.
Not sure I understand when you say "let pg_basebackup write to it". This tablespace already exists on the source machine so cannot be written over. It needs to be written in the backup so that I can than recreate it on the destination machine.
Anyway, in the end I want to move the database that's in that tablespace back to pg_default. I see two possibilities:
1) Moving it now, before taking the base backup, using ALTER DATABASE mydb SET TABLESPACE pg_default; Then I assume I should be able to use -X stream and plain format with pg_basebackup.
Or
2) Delete the symbolic link in data/pg_tblspc, use pg_basebackup with -X stream and plain format, copy the tablespace from the source to the destination machine. Create a new symbolic link in data/pg_tblspc on the new machine and point it to the copied tablespace.
