Re: Add pre-existing tablespace to new installation

On Mon, Jun 8, 2009 at 1:23 PM, Roland Hughes
<roland(at)logikalsolutions(dot)com>wrote:

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

In postgres, there is no concept of a 'transportable tablespace' (to use a
term for another commercial RDBMS), however, if your data directory, and I
mean the whole thing, not just one tablespace, survives the OS upgrade, all
you need to do is start the db server against that data directory.

By data directory, I mean the whole thing, i.e.

global
base
pg_xlog
pg_clog
postgresql.conf
......

You cannot run a new 'initdb' and then have an external tablespace copied
in.

--Scott