Slashdot Mirror
PostgreSQL 8.0 Released
Christopher Cashell writes "The PostgreSQL project has released version 8.0 of their well known Object-Relational Database. New features include: Win32 Native Server, Savepoints, Point-In-Time Recovery, Tablespaces, and lots more. Downloads are available via bittorrent for Unix/Linux, and the much anticipated Win32 version, or via ftp (use a mirror!)." (Here's the official announcement.)
Great, but why should I use PostgreSQL when I already have a database, you might ask? Here's why.
One of the most exciting features of 8.0 is plperl, their Perl-based server side language, allowing for triggers and persistent storage. On another note, I wish MySQL would catch up to PgSQL. Even if you don't like MySQL, the competition keeps them innovating. If PgSQL is light years ahead, what's pushing them?
Now all I need is a magical installer which will convert my existing 7.2.4 systems to 8.x without missing a single beat or disrupting the existing programs written against the old libs.... I'm serious!
Converting 200Gb of data is not something you want to try do without downtime.
All that said, it's good to see PostgreSQL always improving. Good one guys.
Hi Folks,
Please take it easy on 'wwwmaster'.
'www' fell over a couple of hours ago, and a couple of mirrors are coming online to round-robin the address.
Can someone please change the the first link ("PostgreSQL project") in the story to point to 'www'?
Thanks.
Goodbye Oracle, hello PostgreSQL. Now I can have a mostly SQL92 compliant database with ACID, transactions and now PITR and tablespaces that I can use on the server and on a win32 desktop.
For those of you wanting a great frontend, try PGAdmin3. It works on Win32 and Linux.
Not only that. Here's the most important link: What's New in 8.0. (To editors: why there are links to torrents, but no link to features?)
Sincerely,
Pan Tarhei Hosé, PhD.
"Homo sum et cogito ergo odi profanum vulgus et libido."
PostgreSQL has had an ODBC driver for quite some time. You could use that in conjunction with SQL Servers DTS tools to copy data from a SQL Server DB to Pg. There might be some pain involved, particular with indices and constraints, but it shouldn't be too awful.
I didn't know the goal of Postgresql was to rid the world of windows.
I wrote a PHP program that tracks my gf's and I's finances with a Postgresql database backend.
It's up to about 600 records now and although I wouldn't say it's mission critical it certainly is vital.
I have to say I'm extremely impressed with pgsql. It's easy to use and consistent in what it does. I have no complaints whatsoever. What I like most (although I'm not sure this is exclusive to pgsql) is the fact that I can at any time get a plain text dump of everything in the database in a format that makes sense. If the worst comes to the absolute worst, I can always mess around with awk and write a script which can convert a pg dump to another format. It gives me piece of mind that my data can always be read.
I've heard that ms sql users are not so fortunate - ie no plain text dumps. Correct me if I'm wrong.
Kudos to the pgsql team for such a fine product and keep up the good work!
"And then I visited Wikipedia
It may, but it really helps for folks who use Windows and want to run a local copy of their database for development purposes.
Myself, I'm absolutely thrilled wiht this new release (and indeed, their new website -- the old one did look a bit naff). I expect PostGreSQL's popularity to increase a fair bit this year, and good luck to them!
This is your sig. There are thousands more, but this one is yours.
To answer the first part of my question, I just found the Supported Platforms part of the manual, and sure enough Mac OS X is there.
The question remains though - are there plans for a Mac OS X installer package?
PostgreSQL offers substantial additional power by incorporating the following additional concepts in such a way that users can easily extend the system:
Other features provide additional power and flexibility:
These features put PostgreSQL into the category of databases referred to as object-relational. Note that this is distinct from those referred to as object-oriented, which in general are not as well suited to supporting traditional relational database languages. So, although PostgreSQL has some object-oriented features, it is firmly in the relational database world.
The difference is explained in this wonderful Wikipedia article on object-relational databases.
A monkey is doing the real work for me.
This guy usually isn't too far behind in creating .pkg for the stable PostgreSQL releases. I have run it on OS X for a number of years and I have been very happy with it.
What happened to the big news about the Java stored prodcedures? Wasn't there some news not to long ago about adding support for this in Postgres? To me that would been a more welcome feature than the rest of the stuff.
I hate it when MySQL fanboys jump into threads like this only to show their ignorance of relational algebra and predicate calculus saying that no one should ever bother with PostgreSQL and ACID-compliance, because MySQL is somehow a "better tool for the job" in the "real world". We already have comments saying that, so people, please read this first: [1] [2] [3] [4] [5] [6] [7] before you post yet another misleading plug for your favorite toy. Thank you. A real relational database is more than just a data store with SQL frontend.
It's got a long way to go as far as enterprise features.
There is no clustering support in PostgreSQL (and I mean real clustering, not some Java hack where transactions are shipped off to two separate DB servers, both of which don't know they're part of a cluster). This is pretty much a show stopper as far as using PostgreSQL in the company I work for, as high availability is a large concern, and any downtime would be serious.
In previous versions of PostgreSQL, the pg_dump and pg_restore tools were not very good - dumps that included tables or views often would fail on reimport because PostgreSQL wouldn't know the order in which to import everything. You also had to pass in a number of options on the command line just to get a dump that made sense, and large object support was kind of clunky.
That said, I still use PostgreSQL for many many projects and have used PostgreSQL for many years. It's a great product, but it isn't near Oracle in terms of enterprise level features.
$45 per U Colocation Special
The standard way of finding MacOS software would have answered this question in a heartbeat: VersionTracker or MacUpdate, both of which list installers.
If you want to migrate away from SQL-Server then you have MS DTS (at least for the time being).
DTS can pump your MS-SQL-database into postgresql with little problem i'd expect. Now getting the logic (triggers, functions) transferred is a whole other question.
Siggy.
This unique sig is intended to make this user more recognisable.
LAPP, maybe, but certainly not WAPP.
Linux + Apache2 + PostgreSQL + Python == kickass webserver
I support the Center for Consumer Freedom
Good to see they're back in the race, coming up with something serious - and Thanks, Oracle! for being a tough competitor providing a solid standard to keep up with. PostgreSQL is, in my opinion, the most presentable of not-too-much-$ database solutions, I sure like to present clients with a new version of P rather than the crummy image of MySQL (by no means an inferior product but not profiled in the same, serious way). Ah and let's see how it runs on Mac OS/X - Darwin, too. Maybe my little 12" will become all the more a p0w4hful tool because of it - and it had better, I haven't paid the thing off, yet! ...
* Signal 15... "Ceterum censeo Microsoftem esse delendam." Cheers cq. BRgds: DrS aka UNIXmafia@ribeco.net
Master-Slave replication is available through Slony1. (This is currently used by Afilias on the .info domain) Slony2 is in progress and will provide multi-master replication.
Vacuuming is still necessary, but it no longer locks tables. The distribution includes a utility called pg_autovacuum which can take care of all the vacuuming tasks on an automated basis if you desire. (The gentoo release automatically installs this with a nice init script :) )
... and it would really really rock!
Said that, PostgreSQL is a really great thing, and being FOSS, I could of course always go ahead and add the named features... .)
I would consider the PostgreSQL gotchas to be of an entirely different category as the MySQL gotchas.
In PostgreSQL the gotchas are all about performance. And while it may be bad that the database crawls if I didn't schedule a job with vacuum and analyze, that does not have any long term effects. I just run the maintenance tasks manually, add them to a cronjob and I am good to go.
In MySQL however, the gotchas are mostly about data integrity. And that means that they can be disasterous because they can lead to dataloss. Unless you can explain to me how to get back whatever was silently truncated by MySQL if I discover the problems a few days later.
I do very much prefer PostgreSQLs focus on data integrity. But of course I actually read the manual so I won't be bitten by them in either database.
Check out the new Slony replication engine:
http://www.slony.info/
It is probably the best master->slave data replication engine for PostgreSQL at the moment. It is free and developed by one of the core developers.
In my last project I was forced to use MySQL due to hosting issues. I still believe that MySQL is the Access of the OSS dtabases (in a non-GUI way).
The dirty secret is that Postgres is actually easier to install and administer than mysql. I don't want to get into a religious argument with mysql users but ... oh hell yes I do.
I have worked with self-educated programmers who did not know how to do simple table joins or even modestly complex SQL queries or transactions, because they had learned what they thought was SQL by using Mysql. There is a whole generation of developers who now think that transactions aren't really necessary in a database application.
Postgres is really an Oracle killer at this point, and I know, having used Oracle. There is quite simply no reason to use any other relational database at this point, especially to back a live web site.
Well, the gig was up after everyone figured out that Postgres-R was just Postgres with a big muffler and wing.
Please help metamoderate.
Some people say that providing open source applications on Windows helps proprietary software and the Windows monopoly, but I don't think so. Software like Apache, PHP, PostgreSQL on the server, and Mozilla Firefox, Thunderbird, and OpenOffice on the client lets Windows users gradually move over to open source applications. I think that's a much better way of getting people to switch than to ask them to do an all-or-nothing switch.
I have several tables with over 3 million rows, and most aggregates return immediately (or nearly immediately). count(*) is the only dog since it reads every row to get the count, but it's acceptable since I rarely count all rows in the table. I've often wondered why the developers couldn't keep an internal count of all active rows, so count(*) would return immediately, but I'm sure they have their reasons.
You've got to be joking about PostgreSQL having a weak optimizer. If it's weak, only the computer can tell.
Your Wiki link spent most of its space praising PostgreSQL for its advanced features, while your intent is clearly to denigrate it. If that represents PosgreSQL's worst facets, then I am very, very happy.
Don't they know that MySQL is the one to use for web backends? :)
Saying MySQL is better than Access is not saying much. MySQL may be excellent when it meets the requirements, but there are a lot of cases where it simply does not.
#!/
7.x compiles right out of the box- in fact, Apple's Remote Desktop system actually installs and uses PostgreSQL for all its data storage (client system data and whatnot; ARD can collect a lot of per-system data). Very slick.
Please help metamoderate.
For lite uses, and many heavy ones, SQLite seems excellent. I haven't used it yet, but whoever writes for the project is an excellent communicator.
In my experience, most open source projects, and almost all commercial products, have a (maybe mostly unconscious) plan: "We will carefully measure how much hassle people will accept, and make sure we don't document anything more than enough to just barely keep people from rejecting us."
It's common to visit an open source project and find that, yes, they have a new version, but the manual is two years old. There are plenty of commericial projects that are the same way, like Netgear's FVS318. Their reference manual is for version 1.4, but the latest version is 2.4.
An advantage of open source projects is that they are usually far more honest than commercial projects. I love this from the PostgreSQL What's New page: "Although tested throughout our release cycle, the Windows port does not have the benefit of years of use in production environments that PostgreSQL has on Unix platforms and therefore should be treated with the same level of caution as you would a new product." Marketing people are generally so dishonest that they would not allow an honest statement like this.
MySQL is a non-standard implementation of SQL. That's a problem that's probably partly caused by not doing good documentation. If they had documented everything as they wrote MySQL, they might have seen what a mess they were making. Bad documentation obscures programming messes.
PostgreSQL has an elaborate documentation system, and the new features are very impressive.
Another garage product also fares badly on COUNT(*) however if you do COUNT() they both do real well. It has to do with the fact that when you do COUNT(*) you have to count every row and that takes a fair bit of time. When you do COUNT(key) you count rows in the index file and that is real quick :-)
If your RDBMS is doing a full table scan just to do a count(*), then it (or your code) is badly broken.
A Pirate and a Puritan look the same on a balance sheet.
In MySQL (with MyISAM tables), the reason things like count(*) are fast is that MyISAM pre-computes those values. It can do this because it locks the table on insert and update. PostgreSQL doesn't lock the table on modifications -- it allows concurrent access via Multi-Version Concurrency Control (MVCC). Basically, each row in the DB has additional information (used internally by PostgreSQL), which stores which transaction created and last modified the row. PG uses this to determine if any given row should be "visible" to the current transaction. Because this informaiton is constantly changing (and varies from transaction to transaction), you can't precompute things like count(*) and sum(*). See http://developer.postgresql.org/pdf/internalpics.p df for more info (start around page 56).
Postgresql is ready to take on the smaller, non-critical databases that oracle used to get. This is significant proportion of the databases out there, and will take revenue away from oracle. (Mysql will actually probably take more revenue away, but it has too many quality problems and functionality gaps to really deserve to.)
But there are many other, more demanding databases that postgresql isn't yet ready for. Oracle, DB2, and even SQL Server 2005 all have very mature & solid: optimizers, replication, partitioning solutions, parallelism, failover/clustering support, etc.
Here are two examples:
Using db2 for example, you can create a view which is automatically populated by the database like a table (MQT). Then any queries against the base tables that could be sped up by hitting this view will be rewritten by the engine to hit the view. Now, this might seem like needless fluff if you're just writing a hobby php app. But if you need to implement a commercial app like SAP with its 6,000 tables - and you have performance issues - you can make adjustments in the database layer this way. Also, if you're provoding adhoc reporting for hundreds of users on a terrabyte of data - this technique can provide *dramatic* performance benefits.
Another example is partitioning. Back to db2 (which I work with the most): you can spread a database across a dozen separate servers using a hashkey. Now, every query will have all dozen servers working independently on its own fraction of the data. On each of those servers, you can then partition again, this time using ranges or values (MDC) - so that data that doesn't apply to a query will be skipped in tablescans of that table. Using these techniques you can get sub-second response to *adhoc* queries against a terrabyte of data - without indexes (notoriously unreliable here).
Lots more examples where the above came from. Sure, you will pay real money for licensing, hardware, and labor to implement these. Then again, the two above features actually save you in hardware costs. Additionally, some problems are big enough that they can easily justify the cost of licensing a product like this. I've seen these techniques used to save companies million, even hundreds of millions of dollars.
Postgres knows how many rows are in the table, but it does not know how many of those you can see. Some of them may be inserted speculatively by another transaction. Postgres needs to go through each row to determine whether or not that row is actually visible to you. It is possible to turn this into an O(1) operation if you're willing to do sufficient work on inserts and deletes. Whether this is a good tradeoff depends on how often you do count(*) compared to how often you do inserts and deletes.
Finally! A year of moderation! Ready for 2019?
playing around with the beta and RCs several items impressed me:
1) GIS support. Very important for what I do. This is probably due to how closely they work with GRASS (which I haven't used yet).
2) Ability to define and bind operators. Very flexible.
3) Much more relationally compliant while also supporting OOP.
4) PGAdmin II is very handy. A few rough points but now there is no excuse for those afraid of a command line.
It just gets better every release. I am currently porting a MMSQL database over and so far so good.
putting the 'B' in LGBTQ+
Oracle will always have more features than any other given database product you care to compare it to, and there will always be those who find some of those Oracle-unique features irresistable. That's what it means to be the central product of a very large, wealthy and reasonably effective software company.
It is not a very meaningful comparison, though. Oracle also has a lot of stuff that nobody would be happy to see tacked on to PostgreSQL. Oracle is notoriously difficult to administer. The download of Oracle's database product is probably 500 times larger than PostgreSQL. (and 20 times larger than Oracle itself was just a few versions ago, which is interesting, and yes I'm guesstimating but still...)
In short, PostgreSQL doesn't need all the features of the Oracle db to be successful, as *nix and NT did not need all the features of a mainframe OS to be successful.
Now before I get modded down, I be to remind whoever might read this that what I am saying is FACT. - bogaboga
Seems to me Hibernate is the closest there is in that field. IIRC its lead is now working on EJB3 specs. It's a Jboss project, and I've used it with Tomcat without problems.
Information: "I want to be anthropomorphized"
Have you used PostgreSQL recently?
Performance hasn't been an issue since at least version 7.
*sigh* back to work...
>> I still believe that MySQL is the Access of the OSS dtabases
> Perhaps, except that it is n* times better. I've run some pretty intense, badly-written db-abusive
> e-commerce sites (we are talking $5 million per year in cash flow) using MySQL without problems.
Hmmm, but wouldn't it be better to use a database that doesn't silently truncate numbers so that you could be a $50 million per year company instead?
Try Slony for full, production quality replication on PostgreSQL:
http://www.slony.info/
If only a simple and integrated replication mechanism would appear, I would consider switching back to PostgreSQL from MySQL.
I miss PostgreSQL, but too many things are made easier by replication.
Performance is still an issue when you don't read the manual and never perform maintenance on your database. No matter how good the planner is, if it doesn't have acurate statistics because nobody ever runs ANAYZE your database will crawl. No matter which version you use, if you never VACUUM dead row versions will accumulate and eventually kill your performance.
I use PostgreSQL on a daily basis and when I change a database schema I will make sure that I run a VACUUM ANALYZE after committing the changes. But many of the people that we host don't bother to read the manual and don't do this. Usually this is no big deal because it gets picked up by the scheduled jobs. But every now and then somebody calls in a panic because their website is slow. And it always turns out to be major schema changes throwing the planner off. A quick VACUUM ANALYZE gets the performance up again and the customer is happy and has (hopefully) learned a valuable lesson.
This is a real gotcha in PostgreSQL. It has made a few of our customers' websites crawl for a few hours on occasion. But it has never caused dataloss.
No matter which version you use, if you never VACUUM dead row versions will accumulate and eventually kill your performance.
Actually, for 7.4 and newer that isn't true, since they include the aptly named "autovacuum" daemon. And yes, autovacuum also does "VACUUM ANALYZE", so no need to worry about that either.
Unfortunately, many (quite a large percentage) of applications running MySQL *do* need MySQL features that don't exist in SQLite. Full text indexing, for one, which is utilized by many Forum web app.
I also suspect the master data is stored in a different place (either in another MySQL machine or other DB). So the MySQL machines that serve finance.yahoo.com are basically for read-only simple queries (but involve lots of rows). MySQL is perfect for this.
And the data is not really mission-critical. It's just a bunch of numbers (exchange rates, stock prices) aggregated from various places. Database crash? Just repopulate by querying the various data sources.
But the autovacuum daemon isn't enabled or even compiled by default. You have to go into contrib, compile and install it, and then turn it loose.
Soon, 8.1 or so, it will be integrated into the backend. It almost made it into 8.0, but missed it by "" - that much.
--- It is not the things we do which we regret the most, but the things which we don't do.
I switched a an open-source project from MySQL to PostgreSQL a year or so back. The application of proper transactions, referential integrity, views and stored procedures really cleaned up the code.
However, PostgreSQL is not necessarily the easiest DBMS to get used to. For one, some platforms such as *BSD will require a recompile of the kernel to support a bit more then 32 concurrent connections. While well documented if you know what your looking for, this can prove to difficult to implement if for example your on a shared hosting system.
Documentation can be cryptic, tending to be more like a reference manual then an actual manual to teach you how something works. When it comes to optimizing the database itself this becomes painfully obvious as certain switches and options in the postgresql.conf file do little more then offer a one line description with no real clue on how a change will affect it.
In MySQL for example there are sample configation files which show a 'typical' configuration for small, mid-sized and dedicated setups. I have yet to see something similiar and such discussions on the newsgroups have generally shot it down because of the exotic configurations out there.
Vacuuming is one of those things that can really confuse people. While in MySQL deleting and altering rows has no real lasting effect for the user, this is not the case with Postgres. When a row is deleted, the information remains, but is unlinked, making the system run less IO but forcing you to juggle vacuuming, re-indexing and server operations.
You could make use of the auto-vacuum daemon available. I found however, that performance suffered greatly when it was being used on my live system.
More often then not, documentation will speak of load testing and tweaking the server at different values to see how it works. This is sound advice if you have the hardware and time necessary to get things going properly.
On light loads, Postgres can be great, but once you start pounding at it it will slow down unless you know what your doing. The learning curve is far greater then MySQL, the documentation could stand to be more descriptive to end-users/non-Oracle DBA's and the tools available for Windows less advanced but the feature set will make it worth while if you can devote some time for trail and error.
I also suspect the master data is stored in a different place
Really, I don't mean to be mean, but of course it is. That's one of the purposes of replication.
MySQL is perfect for this. I agree, I've had a tremendous amount of success with this type of scenario.
And the data is not really mission-critical
So it should be no problem for Jeremy Zawodny to convince Senior Management of this newfound piece of information? I'd have to say he or anyone else would have a really friggen hard time convincing anyone at yahoo (and their finance/my yahoo customers) that the availability of their data is _not_ mission critical.
Database crash? Just repopulate by querying the various data sources.
Though that is entirely possible, I highly doubt that is their disaster recovery plan. Besides, even if they were using Oracle or MSSQL, this plan would still work.
While I think that PostgreSQL is the best open source database out there, there is one very important gotcha wrt: MVCC which can cause data integrity. This only happens when autovacuum is not running, of course.
If you run enough transactions between vacuum runs (iirc a billion), the transaction counter will wrap around and suddenly your data does not have a consistant point of reference regarding visible transactions. Now, if you wait for a billion transactions to run VACUUM, you either:
1) Have extremely poor performance anyway (not to mention having all your stats off so the planner is doing seq scans when it should use an index)
2) Are doing something with the database which I cannot imagine (I guess a huge number of select statements could cause this, but updates cause old tuples to sit around, so you would have bad performance).
Now, I am not aware of this ever actually having happened, but it is in the documentation, so I figure I should point it out. Of course if you let the database get to this point, then you have bigger problems than your data (chief among them being the IT staff and/or management)..
In general, PostgreSQL focuses on data integrity to a degree not seen elsewhere in the open source database world. Even Firebird does not have such a heavy focus in this area, though to be fair it is a different enough product that their focus works well in their target markets.
My company offers application development, remote administration, and implimentation services for PostgreSQL, MySQL, and Firebird. I am very excited about this release because it will enable us to do more with the database manager which makes us most productive.
As a side note, PostgreSQL-Win32 will not run on Win9x because it requires an NTFS filesystem, iirc. So it is not a perfect solution for Windows development yet (until Win9x fades into the distance or until they decide that they should port it to FAT). Of course you could still use the Cygwin installation, I think. But it is better, IMO, to run it on a arguably stable system anyway.
LedgerSMB: Open source Accounting/ERP
Before, to do this, you had to create a temporary table with the changed column type, copy all the data over to it, and then rename the temp table as the old table.
Thank you Postgres team! Now, if we can rename a column, that would be a nice bonus.
You're telling me that MySQL locks the entire table when I insert a row? You must be joking - that would bring a database to it's knees.
I'm an Oracle guy, and this is how they do it:
Oracle has the best technology in the industry, hands down (DB2 didn't even get triggers until v5). Postgres appears to be paying much more attention to Oracle's methods than MySQL.
Guess which database I'd use if I had no money to spend?
http://blog.planetargon.com/archives/36-Installing -PostgreSQL-8.0-alongside-7.4.html
Robby Russell
PLANET ARGON
Robby on Rails
Oxford University announced a while back that they will be scrapping most of their proprietary DBs for PostgreSQL over the course of '05:
http://news.zdnet.co.uk/software/applications/0
*That's* the problem I remember from earlier setups. Basically, you have to *install* it as an administrator so that it can *run* as a non-administrator.
That's a problem for users in a corporate environment where they aren't authorized to create users on their workstations.
The Glass is Too Big: My Take on Things