Re: Starting new cluster from base backup

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?

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

>
>
>
>
>
>
>
> Thanks.
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com