From: | Guillaume Drolet <droletguillaume(at)gmail(dot)com> |
---|---|
To: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
Cc: | PostgreSQL General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Starting new cluster from base backup |
Date: | 2015-02-18 12:26:51 |
Message-ID: | CAOkiyv6KiXB1m_AW4xRRdx1QT3Z8LcmDyxW_jmDz=okP7_6Yig@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
2015-02-17 17:14 GMT-05:00 Adrian Klaver <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.
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?
>
>
>> Thanks.
>>
>>
>>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>
From | Date | Subject | |
---|---|---|---|
Next Message | Rémi Cura | 2015-02-18 12:57:46 | Re: Failure loading materialized view with pg_restore |
Previous Message | Alvaro Herrera | 2015-02-18 12:19:43 | Re: postgresql93-9.3.5: deadlock when updating parent table expected? |