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 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.
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, Pennsylvania 19073
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
* 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, Pennsylvania 19073How 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, Pennsylvania 19073<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 shaken together, and running over,
shall men pour into your lap. For by your standard of
measure it will be measured to 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 shaken together, and running over,
shall men pour into your lap. For by your standard of
measure it will be measured to 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, Pennsylvania 19073On 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 shaken together, and running over,
shall men pour into your lap. For by your standard of
measure it will be measured to 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.shtmlDoesn'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, Pennsylvania 19073Christopher 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, Pennsylvania 19073
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.shtmlDoesn'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, Pennsylvania 19073
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?