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

On Tuesday 09 June 2009 09:17:10 am Tino Schwarze wrote:

> On Tue, Jun 09, 2009 at 09:10:20AM -0500, Roland Hughes wrote:

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

>

> Ok.

>

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

>

> Then just consider the data directory of Postgres to be the root table

> space. You may put it wherever you want.

>

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

>

> I doubt that you can do that with, say, Oracle. A lot of information

> (like users and schema) will be stored in the main tablespace - I'm not

> 100% sure, though (don't know enough of Oracle). So, if you wipe your

> main database installation, don't be surprised, that a lot is lost.

>

With RDB on OpenVMS, designed correctly, you lose nothing. Users and rights identifiers are provided by the OS as it integrates into the AUTHORIZE system. Single and multi-file databases are scattered about the entirety of the Files-11 storage system. You simply need to tell the RDB monitor about them again if you want them opened and available at system boot. If not, your application opens them on-demand.

With less than enterprise quality solutions, in particular, those that roll their own security and don't participate with the OS provided distributed transaction manager, you are quite screwed.

> Yes, it might be nice and useful to be able to import an tablespace. But

> I guess that would require some internal restructuring...

>

It shouldn't require any restructuring. Earlier in this thread someone said that if the PG root was still there when a new version was installed it was used and not initialized. So, part of the logic already exists. The only piece which needs development is the piece which skips the "init" portion of CREATE TABLESPACE when an existing tablespace is found, but continues on adding it to the default tablespace.

In short, one just needs the option to "skip a step" when issuing CREATE TABLESPACE.

> 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