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.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.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.klaver@aklaver.com