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

On Tuesday 09 June 2009 08:55:18 am Tino Schwarze wrote:

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

"Fresh Install" means shiny new root volume.

> 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).

>

While it shouldn't be much data, it is also non-robust. With robust databases, you can create as many databases as you want in as many places as you want. A "fresh install" means that you only have to "tell" the database monitor about those places again. Everything is still there and usable.

It's really sad that PostgreSQL doesn't have an ADD TABLESPACE command or the functionality built into CREATE TABLESPACE to recognize a tablespace already exists and simply recreate the entry for it in the default tablespace. When one creates an entire database in this new tablespace, PostgreSQL should need nothing other than to know the tablespace exists as everything else should be stored in that tablespace.

> 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

--

Roland Hughes

President

Logikal Solutions

(815)-949-1593 voice

(630)-205-1593 cell