Thread: RPMS for 7.3 beta.
Having not seen anyone asking about the progress on the 7.3beta RPMset, I thought I would give a statement as to where things stand. I am waiting the result of the pg_dump from 7.2.x to 7.3 restore discussion. The structure of the entire packaging depends upon knowing how the upgrade will be performed, since the rest of the packaging is just getting a good build, and excising the gborged clients, which will then have to have their own RPMs built. But I'll get the core built first, then I'll work on the clients. I have a basic build running, but it's not releasable. I haven't had time to go through it with the properly fine-toothed comb that I want to as yet. I would expect to be able to release an RPMset for beta 2 if that is a week or two off. I'll try to keep everyone who cares updated periodically. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11
Lamar Owen <lamar.owen@wgcr.org> writes:
> I have a basic build running, but it's not releasable. I haven't had time to
> go through it with the properly fine-toothed comb that I want to as yet. I
> would expect to be able to release an RPMset for beta 2 if that is a week or
> two off.
Sounds good. I think the earliest we could be ready for beta2 is the
end of this week; sometime next week may be more realistic.
Given that we'll be forcing an initdb for beta2 anyway, those who use
RPMs may be just as happy to have missed beta1.
> I am waiting the result of the pg_dump from 7.2.x to 7.3 restore discussion.
Right. We clearly have to support loading of 7.2 dumps; the only issue
in my mind is exactly how we kluge that up ;-). I just talked to Bruce
about this a little bit, and we came to the conclusion that there are
two plausible-looking paths:
1. Relax CREATE LANGUAGE to accept either LANGUAGE_HANDLER or OPAQUE as
the datatype of the function (ie, make it work more like CREATE TRIGGER
does).
2. Hack CREATE LANGUAGE so that if it's pointed at an OPAQUE-returning
function, it actually updates the recorded return type of the function
in pg_proc to say LANGUAGE_HANDLER.
If we go with #1 we're more or less admitting that we have to support
OPAQUE forever, I think. If we go with #2, then dumps out of 7.3 or
later would be OPAQUE-free, and we could eventually remove OPAQUE a few
release cycles down the road. So even though #2 looks mighty ugly,
I am leaning in that direction.
Whichever way we jump, I think the same behavior should be adopted for
all three contexts where OPAQUE is relevant: language handlers,
triggers, and user-defined-datatype I/O functions. Either we accept
OPAQUE forever, or we proactively fix the function declarations when
an old dump is loaded.
Another interesting thought is that if we do the OPAQUE-to-HANDLER
update thing, we could at the same time coerce the stored path for
the PL's shared library into the preferred '$libdir/foo' format,
rather than the absolute-path form it's likely to have if we're dealing
with a pre-7.2 dump. This would not help anything immediately (if you
got past the CREATE FUNCTION then you gave a valid shlib path) but it'd
very possibly save people trouble down the road.
Comments?
regards, tom lane
On Tuesday 17 September 2002 03:59 pm, Tom Lane wrote: > Lamar Owen <lamar.owen@wgcr.org> writes: > > as yet. I would expect to be able to release an RPMset for beta 2 if > > that is a week or two off. > Given that we'll be forcing an initdb for beta2 anyway, those who use > RPMs may be just as happy to have missed beta1. Hmmm. Any idea if any more initdb forcings are going to happen? :-) > > I am waiting the result of the pg_dump from 7.2.x to 7.3 restore > > discussion. > Right. We clearly have to support loading of 7.2 dumps; the only issue > in my mind is exactly how we kluge that up ;-). I just talked to Bruce > about this a little bit, and we came to the conclusion that there are > two plausible-looking paths: > Comments? From a user/packager viewpoint: the exact mechanics on the internal level, while nice to know (so that I know what to look for in bug reports), are rather irrelevant when it comes to 'how do I package?'. What I am looking at is whether the user will have to run 7.3's pg_dump in order to migrate older data. If so I, and Oliver, will have to kludge up dependencies and linkages in ways that I'm not happy with, but can do if need be. And migration is 'need be' if ever there were 'need be'. I think that I will be able to just build a 'postgresql-olddump' package or similar that contains 7.3's pg_dump in a 7.2.2-friendly form, and let the various distributors worry about building that for older system libraries. :-) This is just a possibility -- it may not be nearly as hard as I fear it will be -- best case is I do virtually nothing and let people upgrade the postgresql-libs and the main package (which includes pg_dump anyway), leaving the existing postgresql-server package in place. They then dump, erase the old server package, and install the new server package. I have disabled rpm upgrades for the server subpackage as of 7.2.2, so that portion I know is doable. I'll just have to try it. I may be overanalyzing the situation. :-) -- Lamar Owen WGCR Internet Radio 1 Peter 4:11
Lamar Owen <lamar.owen@wgcr.org> writes:
> ... What I am looking
> at is whether the user will have to run 7.3's pg_dump in order to migrate
> older data.
AFAIK this is not *necessary*, though it may be *helpful*. Aside from
the OPAQUE issue, which we will fix one way or another, I am aware of
these issues for loading a pre-7.3 dump:
* A reloaded dump will fail to GRANT EXECUTE TO PUBLIC on functions, likewise fail to GRANT USAGE TO PUBLIC on
procedurallanguages. This may not bother some people, but for those it does bother, it's not that hard to issue the
GRANTsmanually after loading the dump.
* A reloaded dump will not create dependencies between serial columns and sequence objects, nor between triggers and
foreignkey constraints, thus 7.3's nifty new support for DROP CONSTRAINT won't work, nor will dropping a table make its
associatedsequences go away. However, this can be boiled down to saying that it still works like it did before.
There are of course the same old same old issues regarding pg_dump's
ability to choose a good dump order, but these are not worse than before
either, and would bite you just as badly if you tried to reload your
dump into 7.2.*.
Using 7.3's pg_dump would help you with the GRANT issue, but AFAIR it
won't do anything for reconstructing serial or foreign-key dependencies.
And it definitely wouldn't help on the ordering issue. So it's probably
not worth the trouble if you can't do it trivially, which you can't in
an RPM-upgrade context. (We do advise it for people who are building
from source, since it's not difficult for them.)
In short, I'm not sure why you and Oliver are so unhappy. We may not
have made the world better than before for upgrade scenarios, but I
don't think we've made it worse either.
regards, tom lane
> Using 7.3's pg_dump would help you with the GRANT issue, but AFAIR it > won't do anything for reconstructing serial or foreign-key dependencies. The below perl script can help with both of those. http://www.rbt.ca/postgresql/upgrade/upgrade.tar.gz Explanation URL: http://www.rbt.ca/postgresql/upgrade.shtml Doesn't deal with DEFERRED triggers. -- Rod Taylor
Tom Lane wrote: > Lamar Owen <lamar.owen@wgcr.org> writes: > > ... What I am looking > > at is whether the user will have to run 7.3's pg_dump in order to migrate > > older data. > > AFAIK this is not *necessary*, though it may be *helpful*. Aside from > the OPAQUE issue, which we will fix one way or another, I am aware of > these issues for loading a pre-7.3 dump: > > * A reloaded dump will fail to GRANT EXECUTE TO PUBLIC on functions, > likewise fail to GRANT USAGE TO PUBLIC on procedural languages. > This may not bother some people, but for those it does bother, > it's not that hard to issue the GRANTs manually after loading the dump. > > * A reloaded dump will not create dependencies between serial columns > and sequence objects, nor between triggers and foreign key > constraints, thus 7.3's nifty new support for DROP CONSTRAINT won't > work, nor will dropping a table make its associated sequences go away. > However, this can be boiled down to saying that it still works like it > did before. These seem like poor reasons for using 7.3 pg_dump on 7.2 databases. Item #1 can be easily fixed via an SQL command issued after the load, if desired, and #2 is really not something specific to the RPM issue. We may be better writing a script that uses the names of the triggers/sequences to create dependency information automatically. Has anyone looked at that? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
> * A reloaded dump will not create dependencies between serial columns > and sequence objects, nor between triggers and foreign key > constraints, thus 7.3's nifty new support for DROP CONSTRAINT won't > work, nor will dropping a table make its associated sequences go away. > However, this can be boiled down to saying that it still works like it > did before. Remember that Rod Taylor's written a script to fix at least the foreign key issue above. I think it'd be neat if that script were perfected and did serials as well and then we could recommend its use... Chris
On Tuesday 17 September 2002 04:40 pm, Tom Lane wrote: > Lamar Owen <lamar.owen@wgcr.org> writes: > > ... What I am looking > > at is whether the user will have to run 7.3's pg_dump in order to migrate > > older data. > AFAIK this is not *necessary*, though it may be *helpful*. Aside from > the OPAQUE issue, which we will fix one way or another, I am aware of > these issues for loading a pre-7.3 dump: Helpful is good. If it proves not too hard I'm going to try that route. And the more I think about the less difficult I think it will be. I've about given up on the upgrade ever really being easy. > In short, I'm not sure why you and Oliver are so unhappy. We may not > have made the world better than before for upgrade scenarios, but I > don't think we've made it worse either. It's a long-term pain, Tom. With brief paroxysms worthy of appendicitis. I've been caught by it -- I lost data due to bad RPM packaging coupled with the dump/restore cycle. That's what motivated me to start doing this in the first place, three years ago. I just want people to not get bit in a bad way and decide they don't want to use PostgreSQL after all. And with the new features of 7.3, lots of users who might have begun with 7.2 are going to want to upgrade -- but if it's too painful.... Sorry, it's just a sore spot for me, this whole upgrade issue. I know Oliver has the same problem, with slightly different presentation. I'm not meaning to be a pain; just trying to prevent some for someone else. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11
> I just want people to not get bit in a bad way and decide they > don't want to > use PostgreSQL after all. And with the new features of 7.3, lots > of users > who might have begun with 7.2 are going to want to upgrade -- but > if it's too > painful.... Sorry, it's just a sore spot for me, this whole > upgrade issue. > I know Oliver has the same problem, with slightly different presentation. IS there any solution to Postgres's upgrade problems? I mean, ever? With the complex catalog design, etc - how is it every possible for us to do a plug-n-play major version upgrade (assuming datafile format doesn't change anymore) How does pg_upgrade work? Chris
Christopher Kings-Lynne wrote: > > I just want people to not get bit in a bad way and decide they > > don't want to > > use PostgreSQL after all. And with the new features of 7.3, lots > > of users > > who might have begun with 7.2 are going to want to upgrade -- but > > if it's too > > painful.... Sorry, it's just a sore spot for me, this whole > > upgrade issue. > > I know Oliver has the same problem, with slightly different presentation. > > IS there any solution to Postgres's upgrade problems? I mean, ever? With > the complex catalog design, etc - how is it every possible for us to do a > plug-n-play major version upgrade (assuming datafile format doesn't change > anymore) > > How does pg_upgrade work? pg_upgrade sort of worked for 7.2 but I got to it too late and I didn't properly expand the pg_clog files. In 7.3, the file format has changed. If we don't change the format for 7.4, I can do it, but I have to add schema stuff to it. Shouldn't be too hard. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
> > How does pg_upgrade work? > > pg_upgrade sort of worked for 7.2 but I got to it too late and I didn't > properly expand the pg_clog files. In 7.3, the file format has changed. > If we don't change the format for 7.4, I can do it, but I have to add > schema stuff to it. Shouldn't be too hard. I mean - how does it actually _work_? Chris
On Tuesday 17 September 2002 10:27 pm, Christopher Kings-Lynne wrote: > Lamar Owen wrote: > > Sorry, it's just a sore spot for me, this whole > > upgrade issue. > IS there any solution to Postgres's upgrade problems? I mean, ever? With > the complex catalog design, etc - how is it every possible for us to do a > plug-n-play major version upgrade (assuming datafile format doesn't change > anymore) While I should know better, I'm going to reply.....:-) The system catalog has poor system-user separation. Better separation might help the issue. Putting all the stuff that belongs to system into the 'system' catalog and then putting the user's customizations into a 'user' catalog, with a set of SQL scripts to upgrade the user portion if columns or other metadata changed in the user portion. This statement is vastly simplified. Then you can blow out the system portion and reinit it without disturbing the user data and metadata. The problem I believe would be enforcing a strict enough demarcation to make that possible. Then there's the nontrivial issue of where the point of demarcation lies. But I should let someone better versed in the system catalog structure answer that. <heresy> I'd give up a few extensibility features for solid upgrading. If I didn't have so much invested in PostgreSQL I might take a hard look at MySQL 4, since data migration has heretofore been one of their few real strengths. But I've got three years of RPM maintenance and five years of infrastructure built on PostgreSQL, so migrating to something else isn't a real palatable option at this point. </heresy> > How does pg_upgrade work? If I am not mistaken pg_upgrade attempts to do just exactly what I described above, moving data tables and associated metadata out of the way, initdb, and move the data back, rebuiding the system catalog linkages into the user metadata as it goes. And it works in a state where there is mixed metadata. At least that's what I remember without looking at the source code to it -- the code is in contrib/pg_upgrade and is a shell script. For laughs I have the source code in another window now, and it is rather involved, issuing a number of queries to gather the information to relink the user metadata back in. It then vacuums so that losing the transaction log file (!!) isn't fatal to the upgrade. It then stops postmaster and moves things out of the way, then an initdb is performed. The schema is restored; the transaction statuses are restored, and data is moved back in, into the proper places. Moving back into the proper places is nontrivial, and the existing code makes no attempt to rollback partial upgrades. That failing could be fixed, however. Then: # Now that we have moved the WAL/transaction log files, vacuum again to # mark install rows with fixed transaction ids to prevent problems on xid # wraparound. Like I said, it's involved. I'm not sure it works for a 7.2.2-> 7.3 upgrade. If the on-disk binary format has changed, tough cookie. It won't help us, since it doesn't make any effort to convert data -- it's just moving it around and recreating the metadata linkages necessary. Now if a binary data converter could be paired with what pg_upgrade is currently doing, it might fly. But scattered in the code is the discouraging comment: # Check for version compatibility. # This code will need to be updated/reviewed for each new PostgreSQL release. Keeping abreast of the changing formats and the other 'gotchas' is just about going to be a full-time job, since changes are made to the system catalogs, syntax, semantics, and data format with little regard as to how it will impact data migration. IOW, migration/upgrading shouldn't be an afterthought if it's going to work right. I wish (in a somewhat wistful, yet futile manner) that each change was accompanied by data migration strategies for that change, but I'm not holding my breath, since the core developers have more important things to do. (Not being sarcastic -- just observing a fact). Oh well. Chris, you got me wound up again... :-( I wish I had the time and funding to go after it, but I have a full-time job already as a broadcast engineer, and while we use PostgreSQL in a mission critical role here, I can't justify diverting other monies for this purpose. Money is tight enough already. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11
This is a better description that I could make. If you look at the script it is very well commented so you should be able to see it works. Also, read the manual page first. In summary, doing any kind of data changes is quite involved (smaller tuple header for 7.3) and because it has to be redone for every release, it is quite a pain. Also, considering commercial databases don't do much better, I fell pretty OK about it. However, we do make releases more frequently than commercial folks, so the pain is more consistent. MySQL hasn't changed their base table format in perhaps 10 years, so yea, that is a real win for them. Of course, they don't shoot out features as fast as we do so that helps. You could pretend you are using MySQL and just not upgrade for 5 years. ;-) --------------------------------------------------------------------------- Lamar Owen wrote: > On Tuesday 17 September 2002 10:27 pm, Christopher Kings-Lynne wrote: > > Lamar Owen wrote: > > > Sorry, it's just a sore spot for me, this whole > > > upgrade issue. > > > IS there any solution to Postgres's upgrade problems? I mean, ever? With > > the complex catalog design, etc - how is it every possible for us to do a > > plug-n-play major version upgrade (assuming datafile format doesn't change > > anymore) > > While I should know better, I'm going to reply.....:-) > > The system catalog has poor system-user separation. Better separation might > help the issue. Putting all the stuff that belongs to system into the > 'system' catalog and then putting the user's customizations into a 'user' > catalog, with a set of SQL scripts to upgrade the user portion if columns or > other metadata changed in the user portion. This statement is vastly > simplified. Then you can blow out the system portion and reinit it without > disturbing the user data and metadata. The problem I believe would be > enforcing a strict enough demarcation to make that possible. Then there's > the nontrivial issue of where the point of demarcation lies. But I should > let someone better versed in the system catalog structure answer that. > > <heresy> > I'd give up a few extensibility features for solid upgrading. If I didn't > have so much invested in PostgreSQL I might take a hard look at MySQL 4, > since data migration has heretofore been one of their few real strengths. > But I've got three years of RPM maintenance and five years of infrastructure > built on PostgreSQL, so migrating to something else isn't a real palatable > option at this point. > </heresy> > > > How does pg_upgrade work? > > If I am not mistaken pg_upgrade attempts to do just exactly what I described > above, moving data tables and associated metadata out of the way, initdb, and > move the data back, rebuiding the system catalog linkages into the user > metadata as it goes. And it works in a state where there is mixed metadata. > At least that's what I remember without looking at the source code to it -- > the code is in contrib/pg_upgrade and is a shell script. For laughs I have > the source code in another window now, and it is rather involved, issuing a > number of queries to gather the information to relink the user metadata back > in. > > It then vacuums so that losing the transaction log file (!!) isn't fatal to > the upgrade. > > It then stops postmaster and moves things out of the way, then an initdb is > performed. The schema is restored; the transaction statuses are restored, > and data is moved back in, into the proper places. Moving back into the > proper places is nontrivial, and the existing code makes no attempt to > rollback partial upgrades. That failing could be fixed, however. > > Then: > # Now that we have moved the WAL/transaction log files, vacuum again to > # mark install rows with fixed transaction ids to prevent problems on xid > # wraparound. > > Like I said, it's involved. I'm not sure it works for a 7.2.2-> 7.3 upgrade. > > If the on-disk binary format has changed, tough cookie. It won't help us, > since it doesn't make any effort to convert data -- it's just moving it > around and recreating the metadata linkages necessary. > > Now if a binary data converter could be paired with what pg_upgrade is > currently doing, it might fly. But scattered in the code is the discouraging > comment: > # Check for version compatibility. > # This code will need to be updated/reviewed for each new PostgreSQL release. > > Keeping abreast of the changing formats and the other 'gotchas' is just about > going to be a full-time job, since changes are made to the system catalogs, > syntax, semantics, and data format with little regard as to how it will > impact data migration. IOW, migration/upgrading shouldn't be an afterthought > if it's going to work right. > > I wish (in a somewhat wistful, yet futile manner) that each change was > accompanied by data migration strategies for that change, but I'm not holding > my breath, since the core developers have more important things to do. (Not > being sarcastic -- just observing a fact). > > Oh well. Chris, you got me wound up again... :-( I wish I had the time and > funding to go after it, but I have a full-time job already as a broadcast > engineer, and while we use PostgreSQL in a mission critical role here, I > can't justify diverting other monies for this purpose. Money is tight enough > already. > -- > Lamar Owen > WGCR Internet Radio > 1 Peter 4:11 > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
> <heresy> > I'd give up a few extensibility features for solid upgrading. If > I didn't > have so much invested in PostgreSQL I might take a hard look at MySQL 4, > since data migration has heretofore been one of their few real > strengths. > But I've got three years of RPM maintenance and five years of > infrastructure > built on PostgreSQL, so migrating to something else isn't a real > palatable > option at this point. > </heresy> I do notice that I think MySQL requires you to run a script for some upgrades... Chris
On Tue, 2002-09-17 at 21:40, Tom Lane wrote: > In short, I'm not sure why you and Oliver are so unhappy. We may not > have made the world better than before for upgrade scenarios, but I > don't think we've made it worse either. I'm unhappy because I know that I will get bug reports that I will have to deal with. They will take time and effort and would not be necessary if we had a seamless upgrade path. The more PostgreSQL gets used, the more it will be used by 'clueless' users; they just install binary packages and expect them to work. That may currently be an unrealistic expectation, but I would like it to become a goal of the project. It has always been my goal as Debian maintainer, but I don't think I can achieve it for this release. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "Give, and it shall be given unto you; good measure, pressed down, and shakentogether, and running over, shall men pour into your lap. For by your standard of measure it will be measuredto in return." Luke 6:38
Lamar Owen <lamar.owen@wgcr.org> writes:
>> How does pg_upgrade work?
> [ pretty good description ]
You missed a key point, which is that pg_upgrade does not even try to
cope with version-to-version system catalog changes. It assumes it can
use pg_dump to dump and reload the database schema. So there is no
hope, ever, that it will be more reliable than pg_dump. All pg_upgrade
tries to do is short-circuit the moving of the bulk data.
The bald fact of the matter is that we are still a good ways away from
the point where we might be willing to freeze the system catalogs. PG
is evolving and improving by a substantial amount with every release,
and the implication of that is that there *will* be some upgrade pain.
If you don't like that ... well ... you're welcome to keep using PG 6.1
... but I haven't got a better answer.
regards, tom lane
On Wed, 2002-09-18 at 04:22, Bruce Momjian wrote: > > In summary, doing any kind of data changes is quite involved (smaller > tuple header for 7.3) and because it has to be redone for every release, > it is quite a pain. Is it feasible to make a utility to rewrite each table, shortening the headers and making any other necessary changes? (Taking for granted that the database has been vacuumed and the postmaster shut down.) This could build up over successive releases, with an input section appropriate to each older version and an output section for the current version. Then an upgrade from any older version to the current one could be done by pg_upgrade. Is this even worth considering? -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "Give, and it shall be given unto you; good measure, pressed down, and shakentogether, and running over, shall men pour into your lap. For by your standard of measure it will be measuredto in return." Luke 6:38
Oliver Elphick wrote: > On Tue, 2002-09-17 at 21:40, Tom Lane wrote: > > In short, I'm not sure why you and Oliver are so unhappy. We may not > > have made the world better than before for upgrade scenarios, but I > > don't think we've made it worse either. > > I'm unhappy because I know that I will get bug reports that I will have > to deal with. They will take time and effort and would not be necessary > if we had a seamless upgrade path. This last line gave me a chuckle. It is like "software wouldn't be necessary if computers could read people's minds". :-) The issue with modifying the data files is that if we have to modify the large binary data file we may as well just dump/reload the data. If we don't change the on-disk format for 7.4 I will try again to make pg_upgrade work. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
On Tuesday 17 September 2002 11:22 pm, Bruce Momjian wrote: > This is a better description tha[n] I could make. If you look at the > script it is very well commented so you should be able to see it works. > Also, read the manual page first. I don't know how, but this time looking at the script, I just grokked it. Maybe that's because it finally clicked in my mind what was happening; regardless, thanks for the compliment; feel free to use that, edited as necessary, in any documentation you might desire. But you are certainly correct about the comments...some of which are more than a little tongue in cheek... # Strip off the trailing directory name and store our data there # in the hope we are in the same filesystem so 'mv 'works. :-) > However, we do make releases > more frequently than commercial folks, so the pain is more consistent. Well, for me and Oliver it comes in waves -- every major release has its paroxysm. Then things cool off a little until next cycle. These one year cycles have, in that way, been a good thing..... :-P You know, if the featureset of the new releases wasn't so _seductive_ it wouldn't be nearly as big of a problem... > You could pretend you are > using MySQL and just not upgrade for 5 years. ;-) Don't say that too loudly, or my production 6.5.3 database that backends the larger portion of my intranet will hear you....I'm just now moving the whole shooting match over to 7.2.2 as part of our delayed website redesign to use OpenACS. That dataset started with 6.1.2 over five years ago, and it was the 6.2.1->6.3.2 fiasco Red Hat created (by giving no warning that 5.1 had 6.3.2 (5.0 had 6.2.1)) that got my dander up the first time. I lost a few thousand records in that mess, which are now moot but then was a bad problem. Since there wasn't an official Red Hat RPM for 6.1.2, that installation was from source and didn't get obliterated when I moved from Red Hat 4.2 to 5.0. I was able to run both 6.1.2 and 6.2.1 concurrently, and the migration went smoothly -- but there were less than ten thousand records at that point. So I _do_ have a three-year old database sitting there. Rock solid except for one or two times of wierd vacuum/pg_dump interactions, solved by making them sequential. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11
On Tuesday 17 September 2002 11:51 pm, Tom Lane wrote: > Lamar Owen <lamar.owen@wgcr.org> writes: > >> How does pg_upgrade work? > > [ pretty good description ] > You missed a key point, which is that pg_upgrade does not even try to > cope with version-to-version system catalog changes. It assumes it can > use pg_dump to dump and reload the database schema. So there is no > hope, ever, that it will be more reliable than pg_dump. All pg_upgrade > tries to do is short-circuit the moving of the bulk data. Yes, this is a key point and one that shouldn't be overlooked. If the metadata belonging to the user's data didn't have to be pg_dumped, but was decoupled somewhat from the system metadata about types, operators, classes, and the like, the schema (great, another overloaded term) wouldn't need dumping but would travel with its data. > The bald fact of the matter is that we are still a good ways away from > the point where we might be willing to freeze the system catalogs. Not talking about a freeze. Talking about separation of system/feature metadata from user metadata that wouldn't change in the upgrade anyway -- table names, fields, user types, views, triggers, etc, that belong to this database and not to the installation as a whole. If columns need changed or added to the user data's metadata, have the upgrade script run the appropriate ALTER commands and UPDATES necessary. The hard parts, I know, are the details behind the broad 'appropriate'. > PG > is evolving and improving by a substantial amount with every release, > and the implication of that is that there *will* be some upgrade pain. Why is it a given conclusion? It should not be axiomatic that 'there *will* be upgrade pain if we improve our features.' That's fatalistic. We have innovative solutions in PostgreSQL that solve some pretty hairy problems. WAL. MVCC. The subselect code (made my day when I heard about that one -- but then had to wait seven months before Red Hat saw fit to provide an RPM that I wasn't expecting.....the other reason I began RPM building, even though it was two cycles later before I got up the nerve to tackle it...). The PL's. Foreign keys. TOAST (now that's a prime example of a 'sideways' solution to a head-on problem). This is just a different challenge: how to keep the loosely dynamic system catalog structure while at the same time allowing the possibility of smooth data migration so people can more easily take advantage of the improved system catalog structure. And yes I know that such a change is not for 7.3. Too late for that, and maybe too late for 7.4 too. But unlike Bruce I winced at Oliver's last line -- it hit a little too close to home and to many multitudes of bug reports and nastygrams directed my way for something I have tried to kludge around in the past. Yes, nastygrams, in the grand old alt.flame tradition. When you maintain RPM's, you find yourself the point man for the entire project in some people's eyes. The bug report about my RPM's trashing a fellow's RPM database was an extreme example of that. I get two-three dozen e-mails a week that I redirect to the web site and/or the mailing lists. I'm sure Oliver is nodding his head in understanding on this one. I don't think seamless upgrading is a pipe dream. And I think that dismissing it out of hand as 'impossible' is a self-fulfilling prophecy. But I do think it won't work well if it's just tacked-on. But, like Tom, I really don't have more of an answer than that. I do understand pg_upgrade much better now, though. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11
Lamar Owen <lamar.owen@wgcr.org> writes:
> On Tuesday 17 September 2002 11:51 pm, Tom Lane wrote:
>> The bald fact of the matter is that we are still a good ways away from
>> the point where we might be willing to freeze the system catalogs.
> Not talking about a freeze. Talking about separation of system/feature
> metadata from user metadata that wouldn't change in the upgrade anyway --
But the system catalogs *store* that metadata. Whether the user's
metadata is changing or not in a high-level sense doesn't prove much
about what's happening to its low-level representation.
>> PG
>> is evolving and improving by a substantial amount with every release,
>> and the implication of that is that there *will* be some upgrade pain.
> Why is it a given conclusion? It should not be axiomatic that 'there *will*
> be upgrade pain if we improve our features.' That's fatalistic.
I prefer "realistic" :-). It is probably true that with an adequate
amount of effort directed towards upgrade issues we could make upgrading
less painful than it's usually been for PG. (I didn't say "zero pain",
mind you, only "less pain".) But where is that effort going to come
from? None of the key developers care to spend their time that way;
all of us have other issues that we find more interesting/compelling/fun.
Unless someone of key-developer caliber comes along who *likes* spending
time on upgrade issues, it's not going to get better. Sorry to be the
bearer of bad news, but that's reality as I see it.
regards, tom lane
On Wednesday 18 September 2002 12:55 am, Tom Lane wrote: > Lamar Owen <lamar.owen@wgcr.org> writes: > > Not talking about a freeze. Talking about separation of system/feature > > metadata from user metadata that wouldn't change in the upgrade anyway -- > But the system catalogs *store* that metadata. They _currently_ store the user's metadata. But that's my point -- does the user metadata that isn't typically substantially different after going through a dump/reload _have_ to coexist with the system data which is intrinsic to the basic backend operation? Yes, I know I'm talking about refactoring/renormalizing the system catalogs. And I know that's neither interesting nor 'fun'. And a major undertaking. > from? None of the key developers care to spend their time that way; > all of us have other issues that we find more interesting/compelling/fun. > Unless someone of key-developer caliber comes along who *likes* spending > time on upgrade issues, it's not going to get better. Sorry to be the > bearer of bad news, but that's reality as I see it. Quoting myself from my reply a couple of hours ago to Chris: -> While I should know better, I'm going to reply.....:-) [snip] -> I wish (in a somewhat wistful, yet futile manner) that each change was -> accompanied by data migration strategies for that change, but I'm not -> holding my breath, since the core developers have more important things -> to do. (Not being sarcastic -- just observing a fact). You're not telling me something I don't already know in your paragraph, Tom. Data migration of real users isn't interesting, compelling, or fun. That's been made abundantly clear the last ten times the subject of upgrading has come up. What's a real-world user to do? Find it interesting, compelling, and fun to work around our shortcoming? (here comes one of those paroxysms that will keep me awake tonight....) I for one am not doing _this_ because I find it to be 'fun'. Quite the opposite -- you try to help people who end up cussing you out for something you can't control. (And I see all those messages addressed to Tom coming through the lists, so I'm sure Tom is no stranger to this portion of the project, either) I'm doing _this_ to try to help people not go through what I went through, as well as to try to help the project in general, for both selfish and selfless reasons. If I were able to spend enough time on the issue I am quite confident I could find a solution, in a year or so. But I find it compelling, if nothing else, to put food on my kids' plates, which precludes me working much on this particular issue. But I do what I can, if nothing else. But it is _necessary_ to migrate data for one reason or another. Lack of distributed backports for security patches, that are official releases, is one quite compelling reason to go through an upgrade. Chris, this is why I was somewhat reticent to reply before. I've been down this dead-end road before. To distill Tom's comments: It is technically feasible to make a better (not perfect) upgrade path, but nobody that can do it wants to. What good is an interesting, compelling, fun, featureful, new version if nobody ugrades to it due to migration difficulties? This release could be the harbinger of further difficulties, I fear. So, that's why I'm unhappy, to answer a question asked quite a while back in the thread. Back on topic: I'll work towards using the 7.3 pg_dump unless the 7.2 dump can be easily restored. Given the desireability for opaque to go away soon, if the 7.3 pg_dump Does The Right Thing and creates an opaque-free dump, that in itself is enough reason to go that route, as it helps the user create a nonambiguous data dump. If it helps the user it is typically a Good Thing, and I am willing to put the effort into that. And it may prove to not be that bad -- I'll know in a few days, hopefully. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11
On Wed, 2002-09-18 at 05:02, Bruce Momjian wrote: > Oliver Elphick wrote: > > I'm unhappy because I know that I will get bug reports that I will have > > to deal with. They will take time and effort and would not be necessary > > if we had a seamless upgrade path. > > This last line gave me a chuckle. It is like "software wouldn't be > necessary if computers could read people's minds". :-) Not really! We know what the formats are before and after. We want PostgreSQL to be the best database. Why on earth can we not have the same ambition for the upgrade process? -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "Give, and it shall be given unto you; good measure, pressed down, and shakentogether, and running over, shall men pour into your lap. For by your standard of measure it will be measuredto in return." Luke 6:38
> Remember that Rod Taylor's written a script to fix at least the foreign key > issue above. I think it'd be neat if that script were perfected and did > serials as well and then we could recommend its use... It does do serials (adds pg_depend entry -- which is just enough), as well as changes unique indexes into unique constraints. As I had a few items I didn't want to upgrade, it asks the user if they want to do each one (-Y to fix 'em all). -- Rod Taylor
Lamar Owen <lamar.owen@wgcr.org> writes:
> On Wednesday 18 September 2002 12:55 am, Tom Lane wrote:
>> But the system catalogs *store* that metadata.
> They _currently_ store the user's metadata. But that's my point -- does the
> user metadata that isn't typically substantially different after going
> through a dump/reload _have_ to coexist with the system data which is
> intrinsic to the basic backend operation?
I think we're talking at cross-purposes. When I said we can't freeze
the system catalogs yet, I meant that we cannot freeze the format/schema
in which metadata is stored. That affects both system and user entries.
You seem to be envisioning moving user metadata into a separate set of
tables from the predefined entries --- but that will help not one whit
as far as easing upgrades goes.
> Given the desireability for opaque to go away soon, if
> the 7.3 pg_dump Does The Right Thing and creates an opaque-free dump,
The present proposal for that has the 7.3 backend patching things up
during reload; it won't matter whether you use 7.2 or 7.3 pg_dump to
dump from a 7.2 database.
> And it may prove to not be
> that bad -- I'll know in a few days, hopefully.
If you find that it's not too painful then I do agree with doing it.
There will doubtless be future cycles where it's more valuable to be
able to use the up-to-date pg_dump than it is in this one.
regards, tom lane
I am working on a README and will add this to /contrib. Thanks. --------------------------------------------------------------------------- Rod Taylor wrote: > > Using 7.3's pg_dump would help you with the GRANT issue, but AFAIR it > > won't do anything for reconstructing serial or foreign-key dependencies. > > The below perl script can help with both of those. > > http://www.rbt.ca/postgresql/upgrade/upgrade.tar.gz > > Explanation URL: > http://www.rbt.ca/postgresql/upgrade.shtml > > > Doesn't deal with DEFERRED triggers. > > -- > Rod Taylor > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
> Sounds good. I think the earliest we could be ready for beta2 is the > end of this week; sometime next week may be more realistic. > > Given that we'll be forcing an initdb for beta2 anyway, those who use > RPMs may be just as happy to have missed beta1. If an initdb is planned - did that split->split_part or whatever change make it in? Chris
Christopher Kings-Lynne wrote: > > Sounds good. I think the earliest we could be ready for beta2 is the > > end of this week; sometime next week may be more realistic. > > > > Given that we'll be forcing an initdb for beta2 anyway, those who use > > RPMs may be just as happy to have missed beta1. > > If an initdb is planned - did that split->split_part or whatever change make > it in? Yes, it did, and in fact if you didn't initdb after the patch was applied, you would see regression failures. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Oliver Elphick writes: > We want PostgreSQL to be the best database. Why on earth can we not > have the same ambition for the upgrade process? We do have that ambition. We just don't have enough clues and time to follow up on it. -- Peter Eisentraut peter_e@gmx.net
OK, I have added this script to /contrib/adddepend with an appropriate README. It seems to work quite well. It does require Pg:DBD. I will add a mention of the script in the release notes. --------------------------------------------------------------------------- Rod Taylor wrote: > > Using 7.3's pg_dump would help you with the GRANT issue, but AFAIR it > > won't do anything for reconstructing serial or foreign-key dependencies. > > The below perl script can help with both of those. > > http://www.rbt.ca/postgresql/upgrade/upgrade.tar.gz > > Explanation URL: > http://www.rbt.ca/postgresql/upgrade.shtml > > > Doesn't deal with DEFERRED triggers. > > -- > Rod Taylor > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
On Tue, 17 Sep 2002, Tom Lane wrote: > > I am waiting the result of the pg_dump from 7.2.x to 7.3 restore discussion. > > Right. We clearly have to support loading of 7.2 dumps; the only issue > in my mind is exactly how we kluge that up ;-). I just talked to Bruce > about this a little bit, and we came to the conclusion that there are > two plausible-looking paths: > > 1. Relax CREATE LANGUAGE to accept either LANGUAGE_HANDLER or OPAQUE as > the datatype of the function (ie, make it work more like CREATE TRIGGER > does). > > 2. Hack CREATE LANGUAGE so that if it's pointed at an OPAQUE-returning > function, it actually updates the recorded return type of the function > in pg_proc to say LANGUAGE_HANDLER. Stupid question, but why not just create an upgrade script that does any required translations external to the database?