Re: Add pre-existing tablespace to new installation - Mailing list pgsql-admin

On Tue, Jun 09, 2009 at 07:57:34AM -0500, Roland Hughes wrote:

> This has not been the case with 64-bit OpenSuSE.  A fresh re-install of the OS
> required I recreate the database from backup.  Postgres would NOT recognize or
> salvage the directory on a TB drive containing tablespace.

I doubt that. What do you call a "fresh install"? The OpenSUSE packages
will not touch your tablespace (in Postgres' home directory,
/var/lib/pgsql IIRC) if it already exists. If you re-format your root
volume, then you loose your data - that's what it is supposed to do.
If you added tablespaces (as symlinks or mountpoints within Postgres'
data directory), they will become useless after a "fresh install" if you
lose your Postgres' data directory during the process.

You just need to preserve everything from Postgres' data directory
(which shouldn't be much data).

Tino.

> On Tuesday 09 June 2009 04:50:07 am Tino Schwarze wrote:
> > 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
>
> --
> Roland Hughes
> President
> Logikal Solutions
>
> (815)-949-1593   voice
> (630)-205-1593  cell

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

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