On 02/18/2015 04:26 AM, Guillaume Drolet wrote:
2015-02-17 17:14 GMT-05:00 Adrian Klaver <adrian.klaver@xxxxxxxxxxx
<mailto:adrian.klaver@xxxxxxxxxxx>>:
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.
So is E:\ a network drive shared by both machines?
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?
I would say 1 would be more feasible then 2. If you use 2, delete the
symlink and do the backup, what happens with any dependencies between
objects in the default tablespace and the one you cut out? Also the
pg_basebackup will be taking a backup of one part of the cluster at one
point in time and the copy of the remote tablespace will possibly be at
another point in time. I do no see that ending well.
Thanks.
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
