Re: [GENERAL] pain of postgres upgrade with extensions
Hello everybody, First off all I am new in postgres but allready got some questions. It's possible to wirte in a file from postgres? Kevin Martins -- From: Dave Potts [EMAIL PROTECTED] Sent: Wednesday, March 12, 2008 8:46 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] pain of postgres upgrade with extensions Greg Sabino Mullane wrote: -BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 - dump version N database - create empty version N+1 database - install N+1's version of each needed contrib module into new database - restore dump, ignoring object already exists errors There is a TODO to figure out some cleaner way of handling this sort of thing ... I think I smell a GSOC project I think there is a slight misunderstanding here, I was refering to extensions items such as postgis, plr, pgperl, etc. These have a slight different foot print to the projects in the contrib directory. Dave - -- Greg Sabino Mullane [EMAIL PROTECTED] End Point Corporation PGP Key: 0x14964AC8 200803121533 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkfYMBMACgkQvJuQZxSWSsjmmwCg1JvB0G2py5jSbJdSZpWR8YyV D4YAoLg2ZinEEGoNEU7S2mcL3bqhmNIh =7pvA -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pain of postgres upgrade with extensions
This is not a flame about current or previous release of Postgres. I have just gone through the awful experience of upgrading from Postgres 8.2 to 8.3 with a database that had one of the many Postgres extensions included. The problem comes down to the way that Postgres extensions are packaged up, each extension tends to define some extension specific functions, when you do a dump of the database these functions get include. If upgrade from one version of Postgres to another, you take a dump of the database, which then needs to be upgrade if there have been any changes in the extension. The problem being that there doesn#8217;t seem to be a way of dumping the database with out including extension specific information. There is a possible solution to this problem, move all the extension specific functions to an extension specific schema. That way the contents of the database are kept separate from extensions. For example the postgis function area would change to postgis.area assuming the the schema for postgis extension was call postgis, this would also avoid the problem if two extensions happen to have a function with the same name. D. -- Any views expressed in this message are those of the individual sender, except where the sender specifically states them to be the views of the Pinan Software -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pain of postgres upgrade with extensions
David Potts wrote: This is not a flame about current or previous release of Postgres. I have just gone through the awful experience of upgrading from Postgres 8.2 to 8.3 with a database that had one of the many Postgres extensions included. The problem comes down to the way that Postgres extensions are packaged up, each extension tends to define some extension specific functions, when you do a dump of the database these functions get include. If upgrade from one version of Postgres to another, you take a dump of the database, which then needs to be upgrade if there have been any changes in the extension. The problem being that there doesn#8217;t seem to be a way of dumping the database with out including extension specific information. Is this something that wouldn't be fixed by: - dump 8.2 database - load dump into 8.3 database - for each extension, run the 8.2 drop extension script in 8.2's contrib - for each extension, run the 8.3 install extension script in 8.3's contrib ?? Or is it a matter of easily keeping an inventory of what extension is installed in what db? Paul -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pain of postgres upgrade with extensions
I noticed this immediately when using the PostgreSQL tool for examples of dumps for creating export of database/table structure/data for the MyJSQLView application. I considered implementing a similar copy dump, but seems it would not be handled properly with SQL statements. danap. This is not a flame about current or previous release of Postgres. I have just gone through the awful experience of upgrading from Postgres 8.2 to 8.3 with a database that had one of the many Postgres extensions included. The problem comes down to the way that Postgres extensions are packaged up, each extension tends to define some extension specific functions, when you do a dump of the database these functions get include. If upgrade from one version of Postgres to another, you take a dump of the database, which then needs to be upgrade if there have been any changes in the extension. The problem being that there doesn#8217;t seem to be a way of dumping the database with out including extension specific information. There is a possible solution to this problem, move all the extension specific functions to an extension specific schema. That way the contents of the database are kept separate from extensions. For example the postgis function area would change to postgis.area assuming the the schema for postgis extension was call postgis, this would also avoid the problem if two extensions happen to have a function with the same name. D. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pain of postgres upgrade with extensions
paul rivers [EMAIL PROTECTED] writes: Is this something that wouldn't be fixed by: - dump 8.2 database - load dump into 8.3 database - for each extension, run the 8.2 drop extension script in 8.2's contrib - for each extension, run the 8.3 install extension script in 8.3's contrib The trouble with that is that step 3 also drops anything that depends on the extension. Doesn't work very well for data types, for instance, since you'd lose any user-table columns of that type. The trick that seems to work fairly well (and ought to be better documented) is - dump version N database - create empty version N+1 database - install N+1's version of each needed contrib module into new database - restore dump, ignoring object already exists errors There is a TODO to figure out some cleaner way of handling this sort of thing ... regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pain of postgres upgrade with extensions
Greg Sabino Mullane wrote: -BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 - dump version N database - create empty version N+1 database - install N+1's version of each needed contrib module into new database - restore dump, ignoring object already exists errors There is a TODO to figure out some cleaner way of handling this sort of thing ... I think I smell a GSOC project I think there is a slight misunderstanding here, I was refering to extensions items such as postgis, plr, pgperl, etc. These have a slight different foot print to the projects in the contrib directory. Dave - -- Greg Sabino Mullane [EMAIL PROTECTED] End Point Corporation PGP Key: 0x14964AC8 200803121533 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkfYMBMACgkQvJuQZxSWSsjmmwCg1JvB0G2py5jSbJdSZpWR8YyV D4YAoLg2ZinEEGoNEU7S2mcL3bqhmNIh =7pvA -END PGP SIGNATURE- begin:vcard fn:David Potts n:Potts;David x-mozilla-html:FALSE version:2.1 end:vcard -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pain of postgres upgrade with extensions
On Wed, Mar 12, 2008 at 07:34:03PM -, Greg Sabino Mullane wrote: - dump version N database - create empty version N+1 database - install N+1's version of each needed contrib module into new database - restore dump, ignoring object already exists errors There is a TODO to figure out some cleaner way of handling this sort of thing ... I think I smell a GSOC project The most promising way I remember was to create packages which wrap a collection of types/tables/functions. The normal pg_depend structure would track this and make sure that things didn't get deleted. On the other side it would provide a way for pg_dump it identify the components and not dump them. The SQL standard has something called modules but I don't remember if it was at all compatable. Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ Please line up in a tree and maintain the heap invariant while boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [GENERAL] pain of postgres upgrade with extensions
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 - dump version N database - create empty version N+1 database - install N+1's version of each needed contrib module into new database - restore dump, ignoring object already exists errors There is a TODO to figure out some cleaner way of handling this sort of thing ... I think I smell a GSOC project - -- Greg Sabino Mullane [EMAIL PROTECTED] End Point Corporation PGP Key: 0x14964AC8 200803121533 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkfYMBMACgkQvJuQZxSWSsjmmwCg1JvB0G2py5jSbJdSZpWR8YyV D4YAoLg2ZinEEGoNEU7S2mcL3bqhmNIh =7pvA -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pain of postgres upgrade with extensions
On Mar 12, 2008, at 3:19 PM, Tom Lane wrote: - restore dump, ignoring object already exists errors Couldn't one use the dump listing feature of pg_restore and comment out the extensions when restoring? Not likely to be a big improvement over ignore errors :-) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
