Re: Add pre-existing tablespace to new installation

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