PostgreSQL Inc. Open Sources Replication Solution

Martin Marvinski writes "PostgreSQL Inc, the commercial company providing replication software and support for PostgreSQL, open sourced their eRServer replication product. This makes PostgreSQL one step closer to being able to replace Oracle as the de facto RDBMS standard. More information can be found on PostgreSQL's website."

The defacto standard

[jon323456]

There is an enormous distance between "viable alternative" and "defacto standard" and the path between them is not paved with features.

Re:The defacto standard

[timbloid]

Very true...

I have had experience with both Oracle and Postgres, and I would never go back to Oracle...

Maybe I was not using all of it's "Enterprise features", but I find Postgres to be fast, and reliable... Plus I am not constantly bombarded with Oracle spam, like I was when I registered for an oracle devnet account...

Re:WOOHOO!!

[timbloid]

You would still have to pay boatloads for support...even with postgres... Open Source does not mean 24/7 Support calls...

Re:Good Thing(tm)

[msgmonkey]

Implementing replication at the application layer is about as much fun as implementing table locking at that layer.

Synchronous Replication?

[Anonymous Coward]

While this is a nice step forward, the real reason large sites utilize Oracle is because of synchronous replication.

The replication needs to be able to keep all data consistent across multiple servers, without any conflicts. Then, if a particular server goes down, the DNS can simply fail over to a second server.

Once the above has been achieved, then we have a viable alternative to Oracle.

Re:Terminate RDBMSs

[Anonymous Coward]

Hehe, you're silly.

Where I work, we have a data warehouse, which grows about 8 GB per day, and holds 13 months of live data. Lets see you hold that in RAM !

IANADBA

[WeirdKid]

I like PostgreSQL, and Open Source deserves capitalization, but I'd like to hear an enterprise DBA's perspective on if this really compares to Oracle's configurability, clustering capabilities, or the seamless swapping of redundant database packages when deployed on, say, an EMC 1000, for reliability and failover. BTW, for this request, "enterprise" = Fortune 100, not Joe's Web Hosting.

Like the subject says, I'm not a DBA, but I know some pretty heavy-duty ones that say nothing beats Oracle running on HP Superdomes with EMC storage.

Re:mySQL gets more publicity

[calethix]

"Even though PostgreSQL has more features and is more promising and powerful, mySQL gets more publicity. This means that mySQL will be the open source database that will replace most commercial databases."

Um, no it doesn't. MySQL's popularity has absolutely nothing to do with it's ability to replace most commercial databases. Even if MySQL is the only open source option, if it doesn't have the features that companies need then they won't switch.

Re:OOS vs. Oracle

[rcs1000]

PostgreSQL and mySQL are real, undeniable threats to Oracle. Not threats in the sense that Oracle's business will disappear, or that either of these databases will become de facto standards, but threats in that they will indisputably affect Oracle's ability to grow its database revenues.

My forecast, for what it's worth, is that Oracle's database business is in secular decline.

It doesn't matter that mySQL or PostgreSQL can't do everything that Oracle 9i does. That they do some of it, do it better and do it cheaper is what is key.

Five years ago, if you wanted to build a web-based application of any size, you probably went with Oracle. Now there is a free option. At the very least corporate purchasers will use this as a way to extract price concessions from their Oracle salesperson. More likely, in a few places (at first) PostgreSQL and mySQL will work their way into the corporate psyche by being used. Maybe for internal stuff, where budgets are tight. Or where projects are being "hidden" from management. But slowly, open source databases are taking hold.

The next stage is for the enterprise application vendors (SAP, PeopleSoft, Siebel) to start supporting OSS databases. They'll want to, because it lowers the cost of projects to clients, while safeguarding their (falling) application license revenues. Hence, SAP "donating" SAP DB to mySQL. My forecast (number two) is that we'll see all three of the application vendors at least trialling OSS databases with beta customers by end '03. (Yes, I know SAP is already doing it, and that PeopleSoft has issued press releases but no product.)

And in this way, in the same way Linux slipped quietly into corporates, OSS databases will take off.

My only hope is that PostgreSQL, which is a much superior product to mySQL, will get the publicity it richly deserves.

*r

enterprise application support

[FreeBSDbigot]

Until SAP, PeopleSoft, and Oracle applications support it, which will happen, respectively, probably no time soon, probably never, and never, it won't "replace Oracle as the de facto RDBMS standard."

Re:The defacto standard

[Sir Runcible Spoon]

And there is no Java Stored Procedures/SQLJ yet.

Not important to some, but it is important to those of us that support the same product across many RDBMS.

MS SQL Server - Re:The defacto standard

[Malc]

By the time PostgreSQL is capable of replacing Oracle as the de facto standard, MS SQL Server will already have done the job and be the new standard to beat. No matter how much people despise MS SQL Server, nor how much they over rate Oracle, it is now the standard for companies needing a low or mid range solution. As with all MSFT products, it improves with each iteration. Our needs aren't high-end, but it handles our 300GB databases with 170 million row tables remarkably well.

Nice to see OSS moving forward

[ajs]

So now we have two major OSS databases with 99% of the features that commercial offerings have, and lots of features that they don't (I'm a MySQL guy, so I know what those extras are for that database, but knowing OSS development paces, I'm sure the same is the same for PostgreSQL).

I listen to folks at work talk about why we "need to move to a *real* database at some point", and it always comes down to the fact that they've bought into the marketting, and when they examine their reasons (if they are willing to), solutions like PostgreSQL or MySQL are a whole lot better choices than the "real" database choices out there.

Bravo guys!

Re:SAPDB relicenced to MySQL

[IANAAC]

This will probably mean that PostgreSQL will have a very hard time competing with MySQL ! I'm not so sure... Most places I know that already use PostgreSQL (my place of employ included) have no intention of switching because it works as advertised. I guess my real point is just because you hear more about MySQL doesn't necessarily mean that PostgreSQL isn't being deployed in businesses. Most of the time when I hear of MySQL it's in a web-site deployment context. The type of stuff we use PostgreSQL for goes beyond that context.

Re:OOS vs. Oracle

[AxelTorvalds]

The only real threat I've seen to Oracle supremacy is Microsoft's SQL Server but, of course, that's only in MS shops.

cough.. db2. cough

You watch, oracle will be looking at MSSQL and DB2 will bitch slap them silly.

Re:OOS vs. Oracle

[Anonymous Coward]

You watch, oracle will be looking at MSSQL and DB2 will bitch slap them silly

DB2 yes, but MS SQL? Get real, back in 2001, the guy heading the clustering part of SQL Server had a chat on technet that stated "clustering that works". MS Sql doesn't have any where near the scalability of DB2 or Oracle. Even with ADO.NET, it still can't handle tens of thousands of concurrent active queries. DB2 and Oracle can. I could be wrong. there could be some one out there handling 3K concurrent active queries to Sql Server with 10% updates. Sql Server still won't have a HA when yukon is released. The only kind of replication that seems to work reliably is one way. From a master to a slave DB. Oracle 9i already has grid support and can dynamically move a table over to another database. Can MS Sql do that? It will probably take 10-15 yrs before they are ready to build those features into MS Sql server. which means 18 yrs before it is released.

PostgreSQL vs MySQL

[mnmn]

Even though the article is about an improvement in the PostgreSQL community, the comments are mostly pgsql vs mysql. People in the bazaar need to have personal motivations to work on opensource projects, mostly to have something against Microsoft, but increasingly, it is becoming a series of team wars. Linux vs BSD, then we had KDE vs GNOME and now qmail vs postfix and mysql vs pgsql. More than a decade ago we had vi vs emacs and BSD vs SYSV.

What the posters here need to realize is that it is exactly this competition that is driving the projects. If MySQL was not given the press and did not have its cult following, we would not see this pace of development for pgsql. The developments for FreeBSD really improved to compete with Linux although their developers claim they are not competing... they do have the fear Linux will supplant them.

What is interesting to note is that in most of these project wars, both projects really survive and get two different niches of their own. This was true of bash vs csh, BSD vs SYSV, BSD vs Linux, KDE vs GNOME, and now MySQL will become the standard entry level database and pgsql the higher level.

I use pgsql because my databases have complicated requirements that MySQL cannot meet. Yet MySQL is the quick and dirty solution when I have to set things up fast. For all new learners I always suggest MySQL. For people thinking of replacing or duplicating their ERP systems, I always suggest pgsql. I even know how to program in sleepycat's db and know where it should replace mysql in smaller embedded systems and where the mysql license cannot be used.

I believe this competition is coming to a close since pgsql has taken a big lead over MySQL in features, and therefore made itself more difficult to deal with especially for newbies. All I can say about the postgresql replication is bravo, and hope MySQL doesnt follow suit so it remains the simple fast and easy database in its own niche.

Re:MS SQL Server - Re:The defacto standard

[RealisticWeb.com]

I know people are going to eviscerate you for saying that, but I have to agree with you. I have always been against most microsoft products, but I just can't lie to myself anymore when it comes to MSSQL. Enterprize manager is the best GUI I have ever seen for SQL management and it makes my job MUCH easier. It lets you pump out all the raw SQL you want to make queries, but you can also use a visual tool that has all the ease of use that access has, but with the enterprize power of MSSQL. And what about the installers! I haven't used Oracle in a couple of years, but I will never forget how much it sucked to try and get 9i installed. Nothing but a java installer! I HAD to put java on my db server or 9i wouldn't install, no command line option at all. How dumb is that? Another caveat, make sure the numlock key is on, or the installer won't work anyway. There was a big fat button that said "install", but didn't do a thing because we had the num lock off. Spent 12 hours on the phone with Oracle and they could't figure out what was wrong. We happened accross the bug report on their web page months later. MSSQL on the other hand? Never a probelm so far on any of the hardare that I have tried it on.

No I don't work for MS, no I'm not in bed with thier marketing department, no I'm not afraid of the command line, etc. I just can't deny that it is a good product. In my opinion the best product MS has ever released, and much cheaper than Oracal.

Granted, I still don't trust MS to be secure, so I never let it be internet facing. To get around that you let the web server be internet facing and only allow connections to the db from that one box. They would have to comprimise you from the inside first, or take control of the web server. And there is nothing stopping you from using Linux and Apache on that web server. We do the same thing with Exchange. I don't like exchange nearly as much as I like MSSQL but the VP's demand it so we just put it behind the firewall and relay all the outbound mail to a Unix-based mailserver in the DMZ.

Unix security on the outside, MS useability on the inside.

Go ahead and flame me now, I'm ready for you.

Re:OOS vs. Oracle

[bobaferret]

OSS in the server room is very different from OSS on the desktop. It folks tend to be educated, and PHB look at the bottom line.

Oracle is not as established as MS. They even note this. That is why they are trying to pull an IBM and turn themselves into a service company, not a DB company. What they sell is support. DB2 has just as strong a foothold.

You can't put aside the technical considerations. This isn't about the desktop and perception. This is about the server room and services. All there is here are the technical considerations.

And SQL server has never been a threat to oracle. Comoditization of the market is their onl;y real threat.

Re:The defacto standard

[Trifthen]

No shit, not that it could even compare in features... The day Postgres replaces Oracle as the defacto standard, I'll start looking at the temperature in hell. I've used both, administered both, and I'm sorry but Postgres comes nowhere near even Oracle 7.3, never mind 8, 8i or 9i.

One thing I like to point out is that if you have high turnover data, not only do you need frequent vacuums to get good performance, but you also have to *reindex your database* because vacuum doesn't reclaim freed index space. This means if you don't reindex, you could possibly run out of *disk space*. The postgres developers like to reference their laudable MVCC Multi Version Concurrency Control system, but that very row reuse is the cause of the problem in the first place.

I mean, I like Postgres and all, but until they get rid of reindex and vacuum and add a whole lot of extra functionality, there's no way in hell it will replace Oracle.

Places SQL Server can never go

[emil]

SQL Server will never run on any version of UNIX. AFAIK, there aren't even (MS-supported) SQL Server client libraries for non-windows platforms. I realize that FreeTDS is available, but such a library would never be used in a highly critical sector.

If you have to integrate multiple platforms, you cannot use SQL server. Closest similar product is Sybase ASE, but Microsoft broke Sybase compatibility on purpose.

I think if people understood how irrationally obstinate SQL Server's platform dependence was, they would look elsewhere. I hope that this attitude holds them below 10% penetration - it certainly has up to now. They are a bit player.

In any case, there is a cheap, new version of DB2 out for $500/copy.

You know there can be more than one.

[LWATCDR]

This is not Highlander there can be more than one.
MySQL is a good solution for some tasks. Postgres is a good solution of some other tasks. I swear people are so odd. There can be room for more than one OS, Database, Office Suite, and CPU. I really like Postgres and use it for our in house database. I use Mysql for our website's database. Why? because it is what our ISP provides and it works.

How about this... Learn Both.

Re:The myth of transactions

[slamb]

One of my favorite ways to do this is with a table that consists only of a primary key and a "dirty bit". [...] When you select data of of your database, you just make sure that you don't pull any data that's dirty.

So when data are in the process of changing, they just don't show up in queries? That's horrible. And what happens if the system goes down in the process of changing? You can't roll back; the old data are lost. And you have to manually go in and clear the dirty bit for the broken, half-new data to be even remotely accessible. Or code logic checking for this everywhere, which would be a nightmare of duplicated, unnecessary code.

With this method, you will eventually run into a case where you will have to pull an old backup because a transient failure caused your stupid method to corrupt a lot of data. Transactions are pretty much essential to ensure correctness; you'd have to reimplement the transaction system to get its guarantees, and it's much smarter to use the one that's already there and tested.

And what about procedures that need a consistent view of the database? There's more in ACID than the "A". Transation isolation is necessary for a lot of applications. They can ensure that multiple queries were run on the same set of data.

If people aren't thinking about their data and doing stupid things, that's entirely separate from their using transactions. There are methods for rigorously proving that your transaction use is correct. I strongly recommend to you that you study them. You sound like you care a lot about correctness. You're not achieving it at all now. If you used transactions, you could.

Re:The myth of transactions

[Troll_Kamikaze]

By forcing myself to deal with those consequences manually by doing my own locking and my own data-integrety management, I find that I can rely on my data far more than most people can, and the likelihood that one of my programs is going to "go bad" and rip out whole transactional units just because an non-essential field was initialized oddly is much, much lower.

And since your ad hoc locking and transaction management code never contains bugs (unlike the piddly implementation in the RDBMS that's only been tested by millions of users), everyone lives happily ever after.

Compitition

[WindBourne]

I find it funny that so many journalists always say that KDE/GNOME or Postgres/MySQL should be stopped and only 1 solution should be allowed. Yet, at the same time, they quickly point to all the different applications in the windows space.
Myself, I use Linux/KDE/pgsql ~ 95% of the time. But there are times where I like BSD (awesome security), Gnome (i like their simple interface and their apps are nice in a number of areas), and Mysql (want a fast mostly read DB? Nothing beats Mysql in the true relational arena (dbm/gdbm/sleepycat can for simpler relations)).
Lets hope that real compitition never stops.

Re:mySQL gets more publicity

[Bromrrrrr]

I think the popularity of MySQL has A LOT to do with the surge in DB-driven websites and little-shop-of-horror IT shops that want to offer them without any know-how.

With MySQL they hardly need to. Whatever gripes you might have with MySQL, it does have this: it is a breeze to administer and work with.

I once heard a fellow programmer say to an intern, in the stern voice of experience: "normalization? I don't even know what it means and I've been working with databases for years, you don't need it!". Not an a attitude I apploud, but one that made MySQL very popular I think.

MySQL IS the popular database and they're working very hard to catch-up featurewise (I bless them for it). But it has a user base that is not really interested in the 'real' RDBMS features.I can't see why their popularity now means anything in the long run.

Before any flames apear :-), I have used both extensively and love both for different reasons. Postgres for it's maturity and features, and MySQL for it's simplicity and ease of use (you really don't need postgres to make a guestbook :-)).

The important thing to remember is that in the end we'll have a fully mature free RDBMS. Either it's MySQL catching-upl, Postgres slowly moving toward it with a headstart.

Re:Postgre sucks!

[MattRog]

Before we start, remember: a database is a collection of data. A DBMS is the system used to store, retrieve, maintain, etc. the data IN the database. A Relational Database Management System is a DBMS which relies on predicate logic, logical data independence, and set theory to maintain the data in the database. Codd invented this in (I think) 1969/70.

This makes no difference if you write your application correctly and check your data going in for VALIDITY. It shouldn't be the DB server's job to enforce this...

No. No, no, no, No, NO, NO. This backwards, uneducated thinking is why DB-driven applications are increasingly becoming more bloated, buggy, and just plain wrong.

The Relational Database Management System (RDBMS) is NOT just a place to stick data in. Codd, fed up with basically inventing a DBMS every time an application was written and being forced to implement checking and other things in the application, penned his ideas on Relational Theory.

The RDBMS, through application of predicate logic, guarantees correctness (consistency) of your data. It's that simple.

If you don't allow the RDBMS to guarantee your data is correct, then you are a fool, ignorant, or both.

the database shouldn't know that such and such is not a valid doohicky because the wotsit is set to foo.

*jumping up and down mad*

(ignoring the misuse of 'database') YES IT SHOULD! That's the WHOLE POINT of a DBMS! Certainly a good portion of logic should live in your application. But 'business rules', constraints, anything that deals with your data etc. BELONG in the RDBMS, because how else are you to ensure that the data you pull out of it is correct? You can't.

OraSlave

[stmiller]

Let me be up front: I am an Oracle slave. I have not and do not work for Oracle, but my Oracle dba and development expertise is vast and is the focal point of my entire career, which essentially rides on Mr. Ellison's coattails. I have been wanting to check out PostgreSQL for some time now, but I suffer major panic attacks whenever I start to download it. I promise to work closely with my therapist to overcome this disability. Perhaps the news about its new replication features will be just the thing I need to overcome my "issues".

I feel better getting that off my chest. Now let me school you little punk beotch newbie dba-wannabes on the what's and wherefore's of enterprise class database administration. Rule #1: Protect Your Data! There is no rule #2. The real reason so many corporations use Oracle is that, if you know what you're doing (and so many of you obviously haven't the first clue), Oracle RDBMS will always be able to recover any committed transaction no matter how severe or catastrophic the failure. Can you say, "archive log mode?" If not, I cannot in good conscience say that you can protect a companies most valuable asset--it's data.

And another thing, while I'm hot on a rant, protecting the data against server or disk failure is one thing, but protecting it against the vagaries of doofus programmers is quite another thing entirely. A good dba accomplishes this via something called database constraints, at which Oracle excels. Can you say, "foreign key?" If not, eventually your database will resemble swiss cheese, with less referential integrity than President Bush's Iraqi WMD speeches.

Finally, everyone is always comparing MySQL and PostgreSQL's runtime performance to Oracle's. Please keep in mind that a database with only a handful of tables and no referential integrity is little more than a file system, and I do not care how many millions of records you stuff into the tables. Grownup databases contain hundreds of tables with multiple schemas and very complex data models. Can MySQL or PostgreSQL handle thousands of requests per second on a grownup database? I would really like to find out for myself.

Finally finally, does PostgreSQL's feature list truly compare with Oracle's? Here is a short list of Oracle features that I cannot live without:

• RMAN/hot backup
• Log Miner
• rollback
• PL/SQL
• triggers
• archive log mode
• duplex control, redo and archive log files
• buffer cache
• row-level locking
• automatic undo management
• constraints
• inline views, WHERE EXISTS, subqueries, et. al.

Re:OraSlave

[Bromrrrrr]

While I apreciate your loyalty and I sincerely hope that both you and Mr. Ellison live long and productive lives, comments realy are a lot better when you have at least passing knowledge of the subject at hand.

Re:Don't let Postgres die or GPL vs BSDL

[Anonymous Coward]

Uh... IBM used BSD for YEARS before Linux.

It's called AIX.

They're now pushing Linux because it's buzzword-compliant.

Likewise, Apple seems to be having no problems protecting their source with MacOS X...

It's not the license. It's the hype.