Re: Starting new cluster from base backup

On 02/18/2015 04:26 AM, Guillaume Drolet wrote:
>
>
> 2015-02-17 17:14 GMT-05:00 Adrian Klaver <adrian.klaver@aklaver.com
> <mailto:adrian.klaver@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.

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@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com