Re: Starting new cluster from base backup

2015-02-18 11:06 GMT-05:00 Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>:

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

No, E:\ is a local drive on which I created a tablespace, in order to have
enough space for my database. In my current setup on the source machine,
PGDATA is in the default PGSQL installation on the OS disk so space is
limited. On the destination machine, PGDATA will be on a different, larger
disk than the OS disk.

>
>
>> 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.
>

You're probably right about that. My understanding was that, since this is
a single-user database (at least for now) on my machine, if I wasn't
performing any query or task during the backup, then the problem you
mentioned would in fact not be a problem.

>
>>
>>
>>
>>
>> Thanks.
>>
>>
>>
>>
>> --
>> Adrian Klaver
>> adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>>
>>
>>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>