Thread: Upgrade issue (again).
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 I am loathe to even bring this up, but with two messages today about it, I am going to be short and sweet: We don't have a reasonable upgrade path. ASCII dump->install new->initdb->restore is not a reasonable upgrade. It is confusing to the newbie, and should be fixed. We used to have an upgrade path in pg_upgrade - -- but it no longer works (but it was most definitely a fine effort for its time, Bruce!). Furthermore, the dump/restore cycle is a pain in the neck when tables get larger than a few hundred megabytes. It's worse when the newer version won't properly restore from the old dump -- and you have to edit, potentially by hand, a multi-gigabyte dump to get it to restore. A seamless binary upgrade utility will require Deep Knowledge -- of the kind that very few pgsql-hackers have. Deeper knowledge than I have, that's for sure -- or I would have already done it. I am not going to beat it into the ground this time -- I have argued the issue at length before (some would say for far too long! :-)). But I am going to drop reminders as we get bug reports and compliants. And Iwould remind the group that MySQL does this easily -- it has utilities to migrate between its different table types. - -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.0.4 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE7Augl5kGGI8vV9eERAgBbAKCjS5yOyYFjTYMBEf5+I3s6uvoSTQCeKhCm fyqa45WqVjUgZF26YZ0/M2w= =4TMK -----END PGP SIGNATURE-----
> I am not going to beat it into the ground this time -- I have argued the > issue at length before (some would say for far too long! :-)). But I am > going to drop reminders as we get bug reports and compliants. And Iwould > remind the group that MySQL does this easily -- it has utilities to migrate > between its different table types. It is my understanding that the MySQL heap format hasn't changed since 1991. I think that helps them upgrade. We add features too quickly. :-) -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
On Wed, 16 May 2001, Lamar Owen wrote: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > I am loathe to even bring this up, but with two messages today about it, I am > going to be short and sweet: > > We don't have a reasonable upgrade path. ASCII dump->install > new->initdb->restore is not a reasonable upgrade. It is confusing to the > newbie, and should be fixed. We used to have an upgrade path in pg_upgrade > - -- but it no longer works (but it was most definitely a fine effort for its > time, Bruce!). Furthermore, the dump/restore cycle is a pain in the neck when > tables get larger than a few hundred megabytes. It's worse when the newer > version won't properly restore from the old dump -- and you have to edit, > potentially by hand, a multi-gigabyte dump to get it to restore. Personally ... I just upgraded 13 gig worth of databases using dump/restore, didn't have to end a single file, in less then 1.5hrs from start to finish ... *shrug*
>> I am loathe to even bring this up, but with two messages today about
>> it, I am going to be short and sweet:
>>
>> We don't have a reasonable upgrade path.
This is one of many, many things that need work. It happens to be a
thing that requires a *lot* of work for, well, not so much payback
(certainly not a benefit you'd get every day you use Postgres).
Not to mention that it's a lot of pretty boring work.
So, personally, there are many other things that I will get to before
I worry about this. Sorry that my priorities don't square with yours,
but that's how it is. I'm not standing in the way of someone else
taking up the problem ...
regards, tom lane
At 04:50 PM 16-05-2001 -0400, Lamar Owen wrote: >-----BEGIN PGP SIGNED MESSAGE----- >Hash: SHA1 > >I am loathe to even bring this up, but with two messages today about it, I am >going to be short and sweet: > >We don't have a reasonable upgrade path. ASCII dump->install >new->initdb->restore is not a reasonable upgrade. It is confusing to the >newbie, and should be fixed. We used to have an upgrade path in pg_upgrade >time, Bruce!). Furthermore, the dump/restore cycle is a pain in the neck when >tables get larger than a few hundred megabytes. It's worse when the newer I won't mind a better upgrade method. But so far I pipe the dump into gzip, and the resulting file is of manageable size. What I find annoying is that pg_dumpall doesn't support username and password. So far I just do a pg_dump of the relevant databases, and recreate the users manually when installing. Also to reload the file I do: zcat gzippedusernameandpassword.gz dbfile.gz | psql That's a bit ugly :). Cheerio, Link.
Lincoln Yeoh writes: > What I find annoying is that pg_dumpall doesn't support username and > password. Working on that. Lots of merge failures... -- Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Wednesday 16 May 2001 18:32, Bruce Momjian wrote: > > And Iwould > > remind the group that MySQL does this easily -- it has utilities to > > migrate between its different table types. > It is my understanding that the MySQL heap format hasn't changed since > 1991. I think that helps them upgrade. We add features too quickly. :-) Which is a two-edged sword. :-) The features are great -- commercial grade. The migration isn't so great. - -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.0.4 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE7A/gD5kGGI8vV9eERAsHhAJ9mYGq2pl1gqWjFNkxwAP36xfMyQwCgrUS8 jBjLoYf4UIzPxKdAmHpSbMQ= =Jgar -----END PGP SIGNATURE-----
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Wednesday 16 May 2001 19:34, Tom Lane wrote: > >> I am loathe to even bring this up, but with two messages today about > >> it, I am going to be short and sweet: > >> We don't have a reasonable upgrade path. > This is one of many, many things that need work. It happens to be a > thing that requires a *lot* of work for, well, not so much payback > (certainly not a benefit you'd get every day you use Postgres). > Not to mention that it's a lot of pretty boring work. All the above are a little too true. And I wish, I really wish, I had a ready solution to make it less work on everybody concerned. > So, personally, there are many other things that I will get to before > I worry about this. Sorry that my priorities don't square with yours, > but that's how it is. I'm not standing in the way of someone else > taking up the problem ... No need to apologize -- your top-notch skills are in wide demand all across the backend. :-) As are the particular skills of each of the core and key hackers. As I said, I was not really enjoying the thought of bringing it up, but I felt I had to do my duty to the userbase. - -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.0.4 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE7A/lu5kGGI8vV9eERAvRLAKDDGkLYVthOX5sCGA6DrSy2H6SxEACgqa5R Q7C+14jxqpNY3L4WSdopZUY= =ezlw -----END PGP SIGNATURE-----
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Wednesday 16 May 2001 19:05, The Hermit Hacker wrote: > On Wed, 16 May 2001, Lamar Owen wrote: > > I am loathe to even bring this up, but with two messages today about it, > > I am going to be short and sweet: > > We don't have a reasonable upgrade path. ASCII dump->install > > new->initdb->restore is not a reasonable upgrade. Furthermore, the > > dump/restore cycle is a > > pain in the neck when tables get larger than a few hundred megabytes. > Personally ... I just upgraded 13 gig worth of databases using > dump/restore, didn't have to end a single file, in less then 1.5hrs from > start to finish ... *shrug* And 1.5 hours of downtime wasn't a problem? *raised eyebrow* :-) Or did you migrate to a different box running the new version? Or were you running more than one version on the one box? Some don't have that choice as easy, nor are they as experienced as you and I. Nor do they desire that much downtime. My vision of an ideal upgrade: Stop old version postmaster. Install new verison. Start new version postmaster. New version migrates in place while being able to give access to the old data with the only downtime being stopping the old postmaster and starting the new. This is hard. And, as Tom so well put it: it's a lot of boring work to get it to work right. But, IMHO, this is one of the most gifted and talented set of hackers in any open source project -- surely we could find both a concept and implementation of a way to actually do the inplace seamless upgrade, couldn't we? - -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.0.4 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE7A/sx5kGGI8vV9eERApiwAKDRzYZSmwpcwlsRcexuGovNA77uNACeLIOS g2O3Q0KP4+ODIuqjjvzu3gY= =WRxi -----END PGP SIGNATURE-----
On Thu, 17 May 2001, Lamar Owen wrote: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > On Wednesday 16 May 2001 19:05, The Hermit Hacker wrote: > > On Wed, 16 May 2001, Lamar Owen wrote: > > > I am loathe to even bring this up, but with two messages today about it, > > > I am going to be short and sweet: > > > > We don't have a reasonable upgrade path. ASCII dump->install > > > new->initdb->restore is not a reasonable upgrade. Furthermore, the > > > dump/restore cycle is a > > > pain in the neck when tables get larger than a few hundred megabytes. > > > Personally ... I just upgraded 13 gig worth of databases using > > dump/restore, didn't have to end a single file, in less then 1.5hrs from > > start to finish ... *shrug* > > And 1.5 hours of downtime wasn't a problem? *raised eyebrow* :-) Or > did you migrate to a different box running the new version? Or were > you running more than one version on the one box? Some don't have > that choice as easy, nor are they as experienced as you and I. Nor do > they desire that much downtime. Wasn't a problem ... I pre-warned all our clients, some of which make such heavy use of the DB that we have to run vacuum on it every few hours, and, to them, the benefits outweigh'd the brief bit of downtime ... *shrug*
Best way to upgrade might bee to do something as simple as get the master to master replication working. Old version on one box, new version on another. Fire up for replication -- done automatically -- with both boxes acting as masters. Change all the code to use the new server (or new database on same server), then remove the old one from the queue and turn off replication. 0 down time, no data migration issues (handled internally by postgres replication), etc. Worst part is you'll use double the disk space for the period of time with 2 masters, and the system will run slower but atleast it's up. This is long term future though. Need master to master replication first as both servers have to be able to update the other with the new information while the code that uses it is being changed around. It also means that replication will need to be version independent. Of course, I'm not the one doing this but it sure seems like the easiest way to go about it since most of the features involved are on the drawing board already. -- Rod Taylor ----- Original Message ----- From: "Lamar Owen" <lamar.owen@wgcr.org> To: "The Hermit Hacker" <scrappy@hub.org> Cc: <pgsql-hackers@postgresql.org> Sent: Thursday, May 17, 2001 12:24 PM Subject: Re: [HACKERS] Upgrade issue (again). > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > On Wednesday 16 May 2001 19:05, The Hermit Hacker wrote: > > On Wed, 16 May 2001, Lamar Owen wrote: > > > I am loathe to even bring this up, but with two messages today about it, > > > I am going to be short and sweet: > > > > We don't have a reasonable upgrade path. ASCII dump->install > > > new->initdb->restore is not a reasonable upgrade. Furthermore, the > > > dump/restore cycle is a > > > pain in the neck when tables get larger than a few hundred megabytes. > > > Personally ... I just upgraded 13 gig worth of databases using > > dump/restore, didn't have to end a single file, in less then 1.5hrs from > > start to finish ... *shrug* > > And 1.5 hours of downtime wasn't a problem? *raised eyebrow* :-) Or did you > migrate to a different box running the new version? Or were you running more > than one version on the one box? Some don't have that choice as easy, nor > are they as experienced as you and I. Nor do they desire that much downtime. > > My vision of an ideal upgrade: > Stop old version postmaster. > Install new verison. > Start new version postmaster. > New version migrates in place while being able to give access to the old data > with the only downtime being stopping the old postmaster and starting the new. > > This is hard. And, as Tom so well put it: it's a lot of boring work to get > it to work right. But, IMHO, this is one of the most gifted and talented set > of hackers in any open source project -- surely we could find both a concept > and implementation of a way to actually do the inplace seamless upgrade, > couldn't we? > - -- > Lamar Owen > WGCR Internet Radio > 1 Peter 4:11 > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v1.0.4 (GNU/Linux) > Comment: For info see http://www.gnupg.org > > iD8DBQE7A/sx5kGGI8vV9eERApiwAKDRzYZSmwpcwlsRcexuGovNA77uNACeLIOS > g2O3Q0KP4+ODIuqjjvzu3gY= > =WRxi > -----END PGP SIGNATURE----- > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >
>>>>> "Bruce" == Bruce Momjian <pgman@candle.pha.pa.us> writes: >> I am not going to beat it into the ground this time -- I have >> argued the issue at length before (some would sayfor far too >> long! :-)). But I am going to drop reminders as we get bug >> reports and compliants. And Iwouldremind the group that MySQL >> does this easily -- it has utilities to migrate between its >> different table types. Bruce> It is my understanding that the MySQL heap format hasn't Bruce> changed since 1991. I think that helps themupgrade. We Bruce> add features too quickly. :-) Just to clear this little bit of particularly unconstructive FUD from the air , no they haven't changed NISAM but created a new format :- Upgrading from a 3.22 version to 3.23 ------------------------------------- *MySQL* 3.23 supports tables of the new `MyISAM' type and the old `NISAM' type. You don't have to convert your oldtables to use these with 3.23. By default, all new tables will be created with type `MyISAM' (unless you start `mysqld'with the `--default-table-type=isam' option. You can change an `ISAM' table to a `MyISAM' table with `ALTER TABLE'or the Perl script `mysql_convert_table_format'. Sincerely, Adrian Phillips -- Your mouse has moved. Windows NT must be restarted for the change to take effect. Reboot now? [OK]
The Hermit Hacker wrote: > > On Wed, 16 May 2001, Lamar Owen wrote: > > > -----BEGIN PGP SIGNED MESSAGE----- > > Hash: SHA1 > > > > I am loathe to even bring this up, but with two messages today about it, I am > > going to be short and sweet: > > > > We don't have a reasonable upgrade path. ASCII dump->install > > new->initdb->restore is not a reasonable upgrade. It is confusing to the > > newbie, and should be fixed. We used to have an upgrade path in pg_upgrade > > - -- but it no longer works (but it was most definitely a fine effort for its > > time, Bruce!). Furthermore, the dump/restore cycle is a pain in the neck when > > tables get larger than a few hundred megabytes. It's worse when the newer > > version won't properly restore from the old dump -- and you have to edit, > > potentially by hand, a multi-gigabyte dump to get it to restore. > > Personally ... I just upgraded 13 gig worth of databases using > dump/restore, didn't have to end a single file, in less then 1.5hrs from > start to finish ... *shrug* On the otherhand, I just had a user/adminsitrator decide to upgrade an instance of postgres on a seconadry server for testing. But he was using X and never noticed that he was upgrading our primary production server. In the end, no data was lost. But I had to pick up the pieces, and it took me a somewhat more more than 1.5 hours to do so, as no plans for downtime or transition had been made at all. I would have preferred a clean upgrade path. Of course, the adminstrator should have known better. And you could argue that the sysadmin has demonstrated that he has too many privelges. At least one of those statments is true. But I still would rather have a seamless upgrade. -- Karl
On Thu, May 17, 2001 at 12:43:49PM -0400, Rod Taylor wrote: > Best way to upgrade might bee to do something as simple as get the > master to master replication working. Master-to-master replication is not simple, and (fortunately) isn't strictly necessary. The minimal sequence is, 1. Start a backup and a redo log at the same time. 2. Start the new database and read the backup. 3. Get the new database consuming the redo logs. 4. When the new database catches up, make it a hot failover for the old. 5. Turn off the old database and fail over. The nice thing about this approach is that all the parts used are essential parts of an enterprise database anyway, regardless of their usefulness in upgrading. Master-to-master replication is nice for load balancing, but not necessary for failover. Its chief benefit, there, is that you wouldn't need to abort the uncompleted transactions on the old database when you make the switch. But master-to-master replication is *hard* to make work, and intrusive besides. Nathan Myers ncm@zembu.com