Thread: 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’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
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’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
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’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. >
paul rivers <rivers.paul@gmail.com> 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
-----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 greg@turnstep.com End Point Corporation PGP Key: 0x14964AC8 200803121533 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iEYEAREDAAYFAkfYMBMACgkQvJuQZxSWSsjmmwCg1JvB0G2py5jSbJdSZpWR8YyV D4YAoLg2ZinEEGoNEU7S2mcL3bqhmNIh =7pvA -----END PGP SIGNATURE-----
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 greg@turnstep.com > End Point Corporation > PGP Key: 0x14964AC8 200803121533 > http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 > -----BEGIN PGP SIGNATURE----- > > iEYEAREDAAYFAkfYMBMACgkQvJuQZxSWSsjmmwCg1JvB0G2py5jSbJdSZpWR8YyV > D4YAoLg2ZinEEGoNEU7S2mcL3bqhmNIh > =7pvA > -----END PGP SIGNATURE----- > > > >
Attachment
On Wed, Mar 12, 2008 at 07:34:03PM -0000, 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 <kleptog@svana.org> http://svana.org/kleptog/ > Please line up in a tree and maintain the heap invariant while > boarding. Thank you for flying nlogn airlines.
Attachment
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 :-)
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" <dave.potts@pinan.co.uk> 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 greg@turnstep.com >> 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 >