Re: Add pre-existing tablespace to new installation

Hi Roland,

On Mon, Jun 08, 2009 at 12:23:06PM -0500, Roland Hughes wrote:

> I have had a question for some time and cannot seem to find an answer.
>
> Is there a way to add pre-existing tablespace to a fresh Postgres install?
>
> Typically I create tablespace on some TB drives and place all databases there.
> The default OpenSuSE 64-bit and Ubuntu 64-bit installations have Postgres
> looking at the root drive. I don't have a problem with that, but do want the
> ability to add tablespace (including all of its stored data) which was already
> in existence prior to the re-install/new-install.
>
> I can do this with commercial products like RDB on OpenVMS.
>
> I'm trying to avoid the pain of unload/recreate/reload when upgrading OS
> versions. In many cases, they don't even change the Postgres version.
> Unloading multiple TB of binary data to text then reloading is a major
> tactical problem.

We usually do not use the prebuilt PostgreSQL binaries from any
distributions, we just compile our own and install them in
/opt/postgresql-$version/, then we add an init-script (easy) and a file
in /etc/profile.d set PATH etc. That works reasonably well and we've got
not problems with upgrading the OS since it wont touch our files at all.
Then you're free to initdb -D /wherever/you/want and have your whole
tablespace somewhere else.

It should also be easy to point the default tablespace of the standard
installations to another place.

And, BTW, if you're just upgrading within major version (e.g. 8.3.x ->
8.3.y) you shouldn't need to do anything. The OS upgrade should not
touch your existing table space in any way and the "fresh install" will
happily use it as it is.

HTH,

Tino.

--
"What we nourish flourishes." - "Was wir nähren erblüht."

www.lichtkreis-chemnitz.de
www.craniosacralzentrum.de