Weekly Edition
Return to the Development pageSponsored link
Vyatta –
Linux & Open Source
Alternative to Cisco –
Advanced Routing,
Firewall, VPN, QoS..
Free Download ->
| ||||||||||||||||
PostgreSQL 8.3 beta 1 released
The PostgreSQL Global Development Group today released the long-awaited
first beta of version 8.3. Thanks to an unprecedented number of new
patches, this version introduces more new and improved features than any
previous one. Of course, more new features means that 8.3 needs more
user testing than any previous version, so we're counting on you to
download it and test it with development versions of your applications.
Among the features in the new version are:
-- Greatly improved performance consistency, through HOT, Load
Distributed Checkpoint,
JIT bgwriter, Asynchronous Commit, and other features.
-- TSearch2 full text search integrated into the core code with improved
syntax
and ease of adding custom dictionaries.
-- SQL:XML syntax.
-- Logging to database-loadable CSV files.
-- Automated rebuilding of cached plans.
-- ENUMs, UUIDs and arrays of complex types.
-- GSSAPI and SSPI authentication support.
... and many others. See the release notes
(http://www.postgresql.org/docs/8.3/static/release-8-3.html) for a more
complete list of new features.
How soon this beta turns into a release depends on testing by our
community of users. As soon as possible, please help us with community
testing:
-- Test installing & configuring the new version.
-- Try out the new features, alone or in combination.
-- Test porting your old applications.
-- Test integration with drivers and other database tools.
-- Run performance tests on the new database.
-- If you are a Windows developer, try compiling using our new Visual
C++ support.
See the 8.3 Beta Page (http://www.postgresql.org/developer/beta) for
more information on downloads, testing, documentation, and reporting
bugs. Get started downloading the beta at:
Source code: http://www.postgresql.org/ftp/source/v8.3beta1/
Win32 Binaries: http://www.postgresql.org/ftp/binary/v8.3beta1/win32/
Your help with testing will ensure that this version of PostgreSQL is as
secure, reliable and bug-free as previous versions -- and that we get
the beta done quickly!
---------------------------(end of broadcast)---------------------------
-To unsubscribe from this list, send an email to:
pgsql-announce-unsubscribe@postgresql.org
(Log in to post comments)
drop, wait a month, reload Posted Oct 8, 2007 16:38 UTC (Mon) by jwb (subscriber, #15467) [Link] Some of these improvements actually sound useful. Unfortunately I'm afraid I will have to watch 8.3 pass me by as long as each minor revision requires a drop and reload. Is there any practical way to upgrade a large PostgreSQL instance, where by large I mean a heap in excess of 1TB? Slony doesn't cut it because it is too intrusive. And then there's the small matter that I'd have to hire someone to audit all my code to make sure that none of the hundreds of subtle changes in their SQL language affect my projects.
Say what you will about MySQL, but at least the on-disk format and the query language have been essentially static for 10 years.
drop, wait a month, reload Posted Oct 8, 2007 16:47 UTC (Mon) by jwb (subscriber, #15467) [Link] Sorry to follow up to myself, but Slony provides the perfect example of what I'm taking about. Here are entries from the Slony 1.2.11 changelog:
* During subscription, do UPDATE to pg_class.relhasindex *after* the TRUNCATE because, in 8.2+, TRUNCATE resets this attribute
Version 8.2 was released a year ago, and Slony is still rooting out all the incompatibilities caused by subtle SQL changes. And they've only just begun to address the changes coming up in 8.3.
For any sane admin to even consider upgrading a large instance to 8.2, they have to wait a year or until they become satisfied that Slony has reached a good level of compatibility, then audit their entire source base to make sure that their SQL still works the same way on 8.3, and then perhaps that system can be moved over.
drop, wait a month, reload Posted Oct 8, 2007 17:00 UTC (Mon) by jwb (subscriber, #15467) [Link] Replying to myself quite tastelessly for a second time:
* Some users have set up replication on replication sets that are tens to hundreds of gigabytes in size, which puts some added "strain" on the system, in particular where it may take several days for the COPY_SET event to complete.
There you have it: "test of gigabytes" is a "very large replication set". It's clear that those of us with very large databases are extremely rare.
drop, wait a month, reload Posted Oct 8, 2007 22:16 UTC (Mon) by hisdad (subscriber, #5375) [Link] You would have the same issues of compatibility on any commercial db and you would have to perform the same stringent tests and audits.
As to performing an in place upgrade on an important system.
MySQL way ? Posted Oct 9, 2007 6:58 UTC (Tue) by khim (subscriber, #9252) [Link] You can just start new binary without changing the database structure, test if everything works and then rollback if there are a problems. Of course you still need audit and other checks, but if some small problems were overlooked you'll only lose few minutes (hours at most), not days... Other commercial databases offer similar functionality. That is: you can use any version with TWO on-disk database formats: tailored for version N-1 and "tailored for version N" (sometimes versions N-2 and versions N-3 are supported too). Thus you can switch to "version N" after FEW tries and only then convert the databases once you know everything is "version N" compatible...
drop, wait a month, reload Posted Oct 8, 2007 17:24 UTC (Mon) by tomd (subscriber, #881) [Link] > Unfortunately I'm afraid I will have to watch 8.3 pass me by as long as each minor revision requires a drop and reload.
Someone started a project to do an in-place upgrade, but I'm not sure what the status of it is. Part of the problem is that a number of the performance gains require changes to the on-disk format.
> And then there's the small matter that I'd have to hire someone to audit all my code to make sure that none of the hundreds of subtle changes in their SQL language affect my projects.
Or, you could read the release notes which list the backwards-incompatible changes. I counted 14, but some are a bit obscure; if you're using all those features you're doing something pretty interesting in your application.
> Say what you will about MySQL, but at least the on-disk format and the query language have been essentially static for 10 years.
Given the issues that I'm currently having at work dealing with MySQL changing behaviour and sometimes completely breaking a feature just between 5.0.x releases, advocating MySQL is just comedy coming from someone complaining about subtle changes.
I'm curious, though. You have TB sized MyIsam tables? That alone would give me insomnia. :)
drop, wait a month, reload Posted Oct 8, 2007 17:45 UTC (Mon) by jwb (subscriber, #15467) [Link] >> And then there's the small matter that I'd have to hire someone to audit all my code to make sure that none of the hundreds of subtle changes in their SQL language affect my projects.
> Or, you could read the release notes which list the backwards-incompatible changes. I counted 14, but some are a bit obscure; if you're using all those features you're doing something pretty interesting in your application.
Add to that all of the changes in 8.1 and 8.2, including any that might by buried in a point release. I'm sure I'm not using all of the changed features, but I might be using at least one of them, and the point is that I have to audit the software to determine which and how many.
You're right about MySQL, but since the on-disk format is backwards and forwards compatible, I can simply snapshot the MySQL volume and mount it using the new version for testing. With PostgreSQL there is no equivalent convenience. I'm required to dump and reload the whole instance, which can take weeks.
drop, wait a month, reload Posted Oct 8, 2007 18:13 UTC (Mon) by pizza (subscriber, #46) [Link] >Add to that all of the changes in 8.1 and 8.2, including any that might by buried in a point release. I'm sure I'm not using all of the changed features, but I might be using at least one of them, and the point is that I have to audit the software to determine which and how many.
To which I have to respond... what else would you expect? If you're running an important production system, it would be highly unprofessional to upgrade a component as critical as a database system without extensively testing it to make sure nothing has broken.
Given the PITA factor involved in said testing, one has to ask whether or not the new features are worth the hassle. Full-text searching looks to be something that is emphatically worth it, at least for me.
And FWIW, the PostgreSQL folks are meticulous about not breaking things (behavior or disk format) for point releases.
drop, wait a month, reload Posted Oct 8, 2007 22:06 UTC (Mon) by drag (subscriber, #31333) [Link] Because it's important to do testing on production systems doesn't mean that it has to be difficult. I think that is his point.
With Mysql he can just snapshot it/run it and test it that way. With Postgresql he can't since doing a simple upgrade to do the testing in the first place requires to much time and hardware.
Also when doing the final upgrade, after testing, it will require a whole new upgrade cycle (since the data would of changed since he first started testing it.) which compounds the issue and adds significant amount of downtime (which itself is expensive).
That's not to say, of course, that Mysql is godlike or you won't ever run into problems or you should not check stuff before you upgrade.
drop, wait a month, reload Posted Oct 9, 2007 0:40 UTC (Tue) by yodermk (subscriber, #3803) [Link] > I'm required to dump and reload the whole instance, which can take weeks.
Weeks? Really? To dump/import a TB?
Just curious, can something like this cut down on the time:?
I'm not 100% sure if that would work or not, it's just a question. Will pg_dump output transactions that happened after it started? If so I see no reason why it wouldn't work.
drop, wait a month, reload Posted Oct 9, 2007 9:43 UTC (Tue) by 0660 (subscriber, #29706) [Link] Slony-I can be used to achieve all of the above with PostgreSQL. Instructions are included in the Slony-I documentation.
drop, wait a month, reload Posted Oct 9, 2007 10:47 UTC (Tue) by pizza (subscriber, #46) [Link] Is that true even if the two instances of postgresql have different versions? The intention of this particular use case is to speed up upgrades, after all.
drop, wait a month, reload Posted Oct 9, 2007 14:28 UTC (Tue) by kleptog (subscriber, #1183) [Link] Absolutly. It one of the specific use-cases of Slony, to do online upgrades. You install a new version on a new server, attach it as a slave and the system will start copying your data over to the new version. You can then test your system on the new version and when you're satisfied, switch masters and voila, you're done.
drop, wait a month, reload Posted Oct 9, 2007 4:27 UTC (Tue) by alankila (subscriber, #47141) [Link] I accept both breakage and incompatibilities as the necessary features for progress. But it's easy for me to do: my postgresql installations are tiny in comparison and the dump and reload are not a problem.
Here's to hoping that postgres guys will take the time at some point to write more efficient migration/upgrade programs. Exporting to flat file and importing that is always the generic solution and very safe, but hardly optimal by any other measure.
As to incompatibilities in SQL: I imagine any changes are made _for good reason_. When the stability demands override the needs to make changes to fix bugs or bring new features in, the project is heading for the graveyard...
drop, wait a month, reload Posted Oct 9, 2007 6:28 UTC (Tue) by jhubbard83 (guest, #21750) [Link] > Unfortunately I'm afraid I will have to watch 8.3 pass me by as long as each minor revision requires a drop and reload. You should learn about their release process. Releases of the form x.y.0 ARE major releases that typically break file compatibility. There are no compatibility guarantees with these unless specified. The x only changes if there are major new features that developers feel warrant a bump in the x number. Minor releases are of the form x.y.z and are backwards compatibile. They are typically bug fixes. Would you have preferred that it be called X.0 everytime they break file format compatibility? If that were the case, it would be, by my informal count, PostgreSQL v 16.0. Does major version number changes really make you that warm and fuzzy on the inside? The PostgreSQL release notes that document chages from release to release can be found here. I don't know that much about MySQL. By looking at the overview for 6.0/Falcon, there are 3 different backends that can be used to store your data. This suggests that I can't just flick a switch and have a new backend storage system with some new speedy feature without having to dump and reload the database. To get potentially new features, such as ACID, you have to choose a different storage engine. InnoDB provides ACID over ISAM. MySQL is obscuring, not purposefully, the fact to get a new feature that only comes with a new engine that you have to dump and reload.
drop, wait a month, reload Posted Oct 9, 2007 8:01 UTC (Tue) by drag (subscriber, #31333) [Link] It seems to me that MySQL is now becomming more and more 'middleware' type thing. With MySQL providing abstraction between your application and various different storage mechanisms, while trying to balance the fact that to get the most of out the various storage backends you will still have to do some application-to-storage specific stuff. (probably very difficult approach, I suppose)
So it's becoming increasingly difficult to compare MySQL vs PostgreSQL (in fact it seems to me that it's been quite pointless for a couple years now)
It's PostgreSQL vs MySQL + Innodb
So on and so forth.
Otherwise you can say MySQL is ACID compliant, MySQL does not support clustering, MySQL is not ACID compliant, MySQL supports clustering in the same sentance and be perfectly correct.
I bet also that the storage engines themselves have various upgrade incompatabilities between versions that the default MySQL setup won't have or otherwise their policies to upgrades and such will be different.
So I bet if you pick and choose you can find instances were the situation is much worse then Postgresql in terms of upgrading.
drop, wait a month, reload Posted Oct 9, 2007 9:06 UTC (Tue) by yodermk (subscriber, #3803) [Link] > This suggests that I can't just flick a switch and have a new backend storage system with some new speedy feature without having to dump and reload the database.
Actually, that pretty much *is* how you switch storage engines in MySQL.
mysql> alter table whatever engine 'InnoDB';
And there you have it. You don't have to take the DB down, or even set it to read-only. The engine will convert MyISAM to InnoDB in the background, transparently.
The Postgres folks really should aim for this when they change file formats. Sure, you'd have to stop PG and start the new version, but it would only have to take a few seconds.
drop, wait a month, reload Posted Oct 9, 2007 11:02 UTC (Tue) by jhubbard83 (guest, #21750) [Link] That is a pretty cool feature (search for alter table). After reading the description, I don't know that I would trust it. It even mentions that they'll be problems if you try to mix transaction safe and non safe engines.
My cursory glance at the docs didn't indicate that you could continue to have users access the database. Seems to me that you're probably still going to halt access to the database while it does the conversion. If you have to shutdown access and do the conversion, it's doing the same basic activity as a pg_dump/restore operation except MySQL has some syntatic sugar that makes it easy or at least look easy. Would the original poster feel comfortable issuing this command to his database?
The biggest win that you are not FORCED to "alter table" Posted Oct 9, 2007 13:01 UTC (Tue) by khim (subscriber, #9252) [Link] You can switch to new engine (Innodb or Falcon or whatever) piecemeal: first you install new version of MySQL (lengthly and complex process but you can test new version on snapshot's and "full-stop-for-a-few-days" is never required), then you starting conversion process on the table-by-table basis. It it's even needed (tables can be kept in the old format for years). This is pretty cool feature of MySQL... Usually you need pretty extensive conversion of "mysql" database (administrative database with accounts, access rights, etc) - but it's small, rarely even few megs in size so it's not a problem...
drop, wait a month, reload Posted Oct 9, 2007 15:36 UTC (Tue) by Los__D (subscriber, #15263) [Link] After reading the description, I don't know that I would trust it. It even mentions that they'll be problems if you try to mix transaction safe and non safe engines. You can combine transaction-safe and non-transaction-safe tables in the same statements to get the best of both worlds. However, although MySQL supports several transaction-safe storage engines, for best results, you should not mix different storage engines within a transaction with autocommit disabled. For example, if you do this, changes to non-transaction-safe tables still are committed immediately and cannot be rolled back. For information about this and other problems that can occur in transactions that use mixed storage engines, see Section 12.4.1, “START TRANSACTION, COMMIT, and ROLLBACK Syntax”.
drop, wait a month, reload Posted Oct 9, 2007 14:25 UTC (Tue) by kleptog (subscriber, #1183) [Link] Is that so? When OSM recently changed some tables from myISAM to innoDB it took days. During that time the system was not usable. Maybe the tables are read-only in the meantime, but that's no use in a big system. Maybe it's changed in more recent versions.
|
||||||||||||||||