2015-02-18 13:40 GMT-05:00 Adrian Klaver <adrian.klaver@xxxxxxxxxxx>:
On 02/18/2015 10:24 AM, Guillaume Drolet wrote:
2015-02-18 11:06 GMT-05:00 Adrian Klaver <adrian.klaver@xxxxxxxxxxx
<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?
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.
Except Postgres performs tasks behind the scenes, so changes are happening. There is also still the dependency issue.
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.
Can't the dependency issue be fixed by creating a new junction in data/pg_tblspc that would point to the relocated tablespace?
