2015-02-17 17:14 GMT-05:00 Adrian Klaver <adrian.kla...@aklaver.com>:
> On 02/17/2015 06:54 AM, Guillaume Drolet wrote: > >> Adrian: thanks for this information. >> >> 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. Are these two approaches feasible? > > >> Thanks. >> >> >> > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
