Add pre-existing tablespace to new installation

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
17 messages Options
Reply | Threaded
Open this post in threaded view
|

Add pre-existing tablespace to new installation

seasoned_geek

Hello,

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.

--

Roland Hughes

President

Logikal Solutions

(815)-949-1593 voice

(630)-205-1593 cell

Reply | Threaded
Open this post in threaded view
|

Re: Add pre-existing tablespace to new installation

Tino Schwarze-9
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

--
Sent via pgsql-admin mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Reply | Threaded
Open this post in threaded view
|

Re: Add pre-existing tablespace to new installation

Stuart Bishop
In reply to this post by seasoned_geek
On Tue, Jun 9, 2009 at 12:23 AM, Roland
Hughes<[hidden email]> wrote:

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

The standard Ubuntu packages don't recreate your database on minor
database upgrades - everything should be the same after the upgrade
except you are running a less buggy version of PostgreSQL.

Major upgrades (8.2 -> 8.3, 8.3 -> 8.4) require a full dump & reload
on all platforms or using 3rd party replication tools like Slony-I.

I have no idea about OpenSuSE's packages.

--
Stuart Bishop <[hidden email]>
http://www.stuartbishop.net/

--
Sent via pgsql-admin mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Reply | Threaded
Open this post in threaded view
|

Re: Add pre-existing tablespace to new installation

Scott Mead-3
In reply to this post by seasoned_geek


On Mon, Jun 8, 2009 at 1:23 PM, Roland Hughes <[hidden email]> wrote:

Hello,

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.


  In postgres, there is no concept of a 'transportable tablespace' (to use a term for another commercial RDBMS), however, if your data directory, and I mean the whole thing, not just one tablespace, survives the OS upgrade, all you need to do is start the db server against that data directory.

  By data directory, I mean the whole thing, i.e.

     global
     base
     pg_xlog
     pg_clog
     postgresql.conf 
......

   You cannot run a new 'initdb' and then have an external tablespace copied in.

--Scott

Reply | Threaded
Open this post in threaded view
|

Re: Add pre-existing tablespace to new installation

seasoned_geek
In reply to this post by Tino Schwarze-9

Well,

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.

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

Reply | Threaded
Open this post in threaded view
|

Re: Add pre-existing tablespace to new installation

seasoned_geek
In reply to this post by Scott Mead-3

Hmmmm....

something seems wrong with my subscription to this list. I'm getting all emails twice.

On Tuesday 09 June 2009 06:40:06 am Scott Mead wrote:

> On Mon, Jun 8, 2009 at 1:23 PM, Roland Hughes

>

> <[hidden email]>wrote:

> > Hello,

> >

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

>

> In postgres, there is no concept of a 'transportable tablespace' (to use

> a term for another commercial RDBMS), however, if your data directory, and

> I mean the whole thing, not just one tablespace, survives the OS upgrade,

> all you need to do is start the db server against that data directory.

>

> By data directory, I mean the whole thing, i.e.

>

> global

> base

> pg_xlog

> pg_clog

> postgresql.conf

> ......

>

> You cannot run a new 'initdb' and then have an external tablespace

> copied in.

>

> --Scott

--

Roland Hughes

President

Logikal Solutions

(815)-949-1593 voice

(630)-205-1593 cell

Reply | Threaded
Open this post in threaded view
|

Re: Add pre-existing tablespace to new installation

Tino Schwarze-9
In reply to this post by seasoned_geek
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

--
Sent via pgsql-admin mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Reply | Threaded
Open this post in threaded view
|

Re: Add pre-existing tablespace to new installation

seasoned_geek

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

Reply | Threaded
Open this post in threaded view
|

Re: Add pre-existing tablespace to new installation

Scott Mead-3
In reply to this post by seasoned_geek


On Tue, Jun 9, 2009 at 9:05 AM, Roland Hughes <[hidden email]> wrote:


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



   Since you seem to have lost your original data directory, you may be out of luck.  The only thing I can figure would be to use something like Tom's 'data file reader' (the name escapes me) to read through your data files and grab the data in each block.  You'd have to write some scripts to turn the output of that into insert statements, and b/c the rest of the data dir is gone, you may end up with data that's no longer valid, but it may be your only option.  Of course, that's just a shot in the dark, anybody else?

BTW -- What version of PG are / were you using?

--Scott
Reply | Threaded
Open this post in threaded view
|

Re: Add pre-existing tablespace to new installation

Tino Schwarze-9
In reply to this post by seasoned_geek
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.

Yes, it might be nice and useful to be able to import an tablespace. But
I guess that would require some internal restructuring...

Tino.

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

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

--
Sent via pgsql-admin mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Reply | Threaded
Open this post in threaded view
|

Re: Add pre-existing tablespace to new installation

Scott Mead-3
In reply to this post by seasoned_geek

On Tue, Jun 9, 2009 at 10:10 AM, Roland Hughes <[hidden email]> wrote:

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


>

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


    Now now :-)  In all fairness, even something like Oracle (I think one could argue that the industry considers this a 'robust' database) needs certain metadata available to import a tablespace from another instance -- i.e. you have to export metadata, and if you lost the first instance before you do that but save a tablespace, you'd be in the same predicament.

   A fairly important concept in the Oracle world is protection of a control file, in postgres, you really need to preserve control structures like the pg_clog, the control file, etc...    Personally, I don't know anything about RDB, but I would venture a guess that you're going to encounter restrictions there as well.

--Scott

Reply | Threaded
Open this post in threaded view
|

Re: Add pre-existing tablespace to new installation

seasoned_geek
In reply to this post by Tino Schwarze-9

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

Reply | Threaded
Open this post in threaded view
|

Re: Add pre-existing tablespace to new installation

seasoned_geek
In reply to this post by Scott Mead-3

Are you posting twice or is this just something with my setup? I get your messages at least twice.

On Tuesday 09 June 2009 09:18:50 am Scott Mead wrote:

> On Tue, Jun 9, 2009 at 10:10 AM, Roland Hughes

>

> <[hidden email]>wrote:

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

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

>

> Now now :-) In all fairness, even something like Oracle (I think one

> could argue that the industry considers this a 'robust' database) needs

> certain metadata available to import a tablespace from another instance --

> i.e. you have to export metadata, and if you lost the first instance before

> you do that but save a tablespace, you'd be in the same predicament.

>

> A fairly important concept in the Oracle world is protection of a

> control file, in postgres, you really need to preserve control structures

> like the pg_clog, the control file, etc... Personally, I don't know

> anything about RDB, but I would venture a guess that you're going to

> encounter restrictions there as well.

>

> --Scott

--

Roland Hughes

President

Logikal Solutions

(815)-949-1593 voice

(630)-205-1593 cell

Reply | Threaded
Open this post in threaded view
|

Re: Add pre-existing tablespace to new installation

Tino Schwarze-9
In reply to this post by seasoned_geek
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

--
Sent via pgsql-admin mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Reply | Threaded
Open this post in threaded view
|

Re: Add pre-existing tablespace to new installation

Scott Mead-3
In reply to this post by seasoned_geek

On Tue, Jun 9, 2009 at 10:31 AM, Roland Hughes <[hidden email]> wrote:


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.


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 actually not true.  Consider the commit log (pg_clog) as one example.  You would need to manually create the record of every commit for each transaction that exists in your tablespace, you could do this, but you'd need to be sure that you were correct.  There's also the issues of XID's.  All of the XID's in your tablespace are going to be well out of sync with the rest of the rdbms.  Since the MVCC system is based on those xid's, the visibility of your data would be all off, unless you made restructuring to the RDBMS or your data files.

--Scott

> 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


Reply | Threaded
Open this post in threaded view
|

Re: Add pre-existing tablespace to new installation

seasoned_geek
In reply to this post by Tino Schwarze-9

On Tuesday 09 June 2009 09:42:45 am Tino Schwarze wrote:

> >

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

>

I don't know how many installs there are. I know it was the first commercial relational database supporting a DISTRIBUTED environment when it came out in the 1980s. It was so far ahead of everything else out there Oracle sued DEC for the right to buy it. Twenty years later and it still outclasses Oracle's namesake product in just about every way.

--

Roland Hughes

President

Logikal Solutions

(815)-949-1593 voice

(630)-205-1593 cell

Reply | Threaded
Open this post in threaded view
|

Re: Add pre-existing tablespace to new installation

Alvaro Herrera-7
In reply to this post by seasoned_geek
Roland Hughes wrote:
> Hmmmm....
>
> something seems wrong with my subscription to this list.  I'm getting all
> emails twice.

No, it's expected; you get one from the CC and another copy from the
list.  You can either fix it and your end by de-duping (procmail
recipes) or by a setting in Majordomo (see the "mailpref" link at the
bottom) called "eliminatecc" IIRC.


--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

--
Sent via pgsql-admin mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin