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