Thread: Add pre-existing tablespace to new installation
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
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
On Tue, Jun 9, 2009 at 12:23 AM, Roland Hughes<roland@logikalsolutions.com> 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 <stuart@stuartbishop.net> http://www.stuartbishop.net/
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.
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
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
>
> <roland@logikalsolutions.com>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
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
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
> > 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.
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
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.
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
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
>
> <roland@logikalsolutions.com>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
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
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.
> Tino.
>
> --
> "What we nourish flourishes." - "Was wir nähren erblüht."
>
--
Roland Hughes
President
Logikal Solutions
(815)-949-1593 voice
(630)-205-1593 cell
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
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.