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

Hi Roland,

On Tue, Jun 09, 2009 at 09:31:43AM -0500, Roland Hughes wrote:
> > > 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.

Okay, that's one example. And it's one of a very specialised system. I
suppose, there aren't as many OpenVMS installs out there as Postgres
installations? Apart from that, RDB is designed that way and explicitly
supports that kind of setup.

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

That's thought a bit too short. It actually depends on how the data in
the tablespace relates to data in the main installation. I'm quite sure,
the data files in the table space just get named by the OID of the
tables. The OID is stored in pg_database AFAIK. So you need to store
additional metadata information in the tablespace (maybe a copy or dump
of pg_database). Apart from that, when importing the tablespace, you
need to resolve OID conflicts, rename data files, etc. which might
require complex internal restructuring.

As far as I know (I'm not a developer!), Postgres wasn't designed to
support that. The assumption was just that you could create a table
space, then put tables in it. It is still related to the main catalogue
in many and complex ways. A tablespace import would have to resolve
all those relations (some of which might not be resolvable at all given
the current design and data storage).

But I'm not a developer, there is a lot of guessing on my part given the
few bits I understood of Postgres' internals. Maybe someone with more
insight wants to clarify what I wrote.

Tino.

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

www.lichtkreis-chemnitz.de
www.tisc.de