Re: Starting new cluster from base backup

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

> On 02/18/2015 11:51 AM, Guillaume Drolet wrote:
>
>>
>>
>> 2015-02-18 13:40 GMT-05:00 Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com
>> <mailto: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>
>> <mailto: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.
>>
>
> So if I understand correctly you have:
>
> 1) On source machine a directory E:\Data\Database.
> 2) On the source machine in Postgres you have a created a tablespace that
> points at E:\Data\Database.
> 3) On destination machine you have an E:\ drive also.
>
> You're correct

> Then have you tried:
>
> 1) Create \Data\Database directory under E:\ on the destination machine.
>
2) Do the pg_basebackup.
>

I'm not sure I understand why, at this moment in the sequence of operation,
I would create \Data\Database under E:\ on the destination machine.
pg_basebackup, when run on the source DB on the source machine, has no idea
about the destination machine. Maybe you're confused with the F:\ drive,
which is the drive on which I tried to save my base backup with the command:

pg_basebackup -D "F:\208376PT\db" -X stream -l "208376PT17022015" -U
postgres -P

This drive (F:\) is not the destination machine, it's a swappable disk I
use to move my base backup from one machine (the source) to another (the
destination).

>
>>
>> Can't the dependency issue be fixed by creating a new junction in
>> data/pg_tblspc that would point to the relocated tablespace?
>>
>
> The docs say you can:
>
> http://www.postgresql.org/docs/9.3/static/manage-ag-tablespaces.html
>
> "The directory $PGDATA/pg_tblspc contains symbolic links that point to
> each of the non-built-in tablespaces defined in the cluster. Although not
> recommended, it is possible to adjust the tablespace layout by hand by
> redefining these links. Under no circumstances perform this operation while
> the server is running. Note that in PostgreSQL 9.1 and earlier you will
> also need to update the pg_tablespace catalog with the new locations. (If
> you do not, pg_dump will continue to output the old tablespace locations.)"
>
>
> I have not done it and I see the "Although not recommended.." part above,
> so I would say that is a last resort solution.

I confirm this method works. I've done it in the past using the steps in
this blog and its comments:

http://www.databasesoup.com/2013/11/moving-tablespaces.html

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