Re: Starting new cluster from base backup

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

> On 02/18/2015 10:24 AM, Guillaume Drolet wrote:
>
>>
>>
>> 2015-02-18 11:06 GMT-05:00 Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com
>> <mailto:adrian(dot)klaver(at)aklaver(dot)com>>:
>>
>>
>
>> 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.
>>
>
> So is there an E:\ drive available on the destination machine?

Yes there is an E:\ drive available on the destination machine. But for
now, these two machines don't communicate. I take the backup on a hot swap
disk (F:\) and then swap it into the destination machine. Ultimately when
my database will be running on the destination machine, I'll connect to it
from other machines in my local network.

>
>
>
>>
>>
>> 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.
>>
>
> Except Postgres performs tasks behind the scenes, so changes are
> happening. There is also still the dependency issue.

Can't the dependency issue be fixed by creating a new junction in
data/pg_tblspc that would point to the relocated tablespace?

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