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