zillablog

One common discussion we see on the postgresql mailing lists is that of how to track changes and do versioning of schema within a database. One of the common solutions offered is the idea of grabbing schema from the system catalogs, writing it to a file, and then committing that to svn. In theory you could write an elegant tool for such a task, but given the number of times someone has asked me to send them a copy of the script we use at OmniTI, I'm guess that even the hacky script we use will probably be helpful. To that end, I've finally gone ahead and put a public copy of our getddl tool up on labs. Be aware that the script has some issues; I've include a TODO of some items that I'd like to see improved, but it meets our current needs, so you're better off writing patches than waiting for a new release (;-)), but hopefully it will still be of some value.

• Getddl Project Page


• getddl.pl script

Well, I booked my tickets this morning for PGCon 2008. Against my better judgement, I've booked tickets through Dulles, an airport which I really do not like, but which offers direct flights to Ottowa, something I can't get from BWI (Although BWI did offer a connecting flight through Atlanta...). I'll be arriving Monday night if anyone is in town that early, if not guess I will have time to work on my slides.

On an administrative note, the lightning talks are looking good, we'll have talks from guys named Greg, Robert, and Gavin, just to name a few. I do have 1 or 2 slots that I could squeeze someone into if your interested; I'd really like to see a quick talk on PostgreSQL use in other open source ecosystems.

One last thing, Dan showed me the nifty conference map with pointers to all the things around the conference, if you haven't seen it you'll want to bookmark for the upcoming trip. See you soon!

Over the weekend I had a chance to catch up on some of my favorite database oriented blogs, and I noticed a number of them were mentioning the upcoming Kickfire engine/appliance based around MySQL. Always a sucker to read about Yet Another MySQL Engine, I looked through what little details there were (Kickfire had previewed their system to some of the more popular MySQL blogs in order to create some initial buzz ahead of the MySQL users conference; good ploy). I think the breakdown goes like this:

• Designed for Data Warehousing


• Uses Hardware Based Acceleration


• Appliance System


• Does "Stream Processing"


• Involves lots of buzzwords like "ground breaking" and "revolutionary"


• They have brought Web 2.0 Marketing to the Database World

Eh? Yeah, that last bit is my own assessment. Why? Well, Baron Schwartz had this to say about Kickfire when he previewed it:

even if this product isn’t what it claims to be, they’ll prove the concept and there will be a competitive rush into this space

And one of the commentors on his blog said:

Again, I’m surprised it hasn’t been done before. Particularly given the money big vendors like Oracle and IBM sink into their DB development every year.

The thing is, it has been done before, and if you want the power of SQL streams in a production ready system, you can have it today. You just need to Google on "Truviso".

I had mentioned to a few people our TB+ disaster recovery scheme at the PG-East conference last week, with hopes that we would be doing a full on recovery test in early April. Lucky for us, we've been able to do a rough run through, so I wanted to report some results. First, a quick recap of why most of the common backup solutions suck for our needs:

• pg_dump is pretty much a joke with 1TB+ of data, and especially on our system which has constant data churn, and enough mutating schema to make getting a consistent snapshot unworkable.


• pitr would be nice for failover, but it isn't a real disaster recovery system. The key problems are issue with either corrupted xlogs making thier way to the slave, or data corruption issues getting propogated into your "backup". If you don't have a static snapshot, you can hose yourself in some un-fun ways.


• slony (or bucardo, or other replication systems) also suffers from the issue of data corruption getting propogated onto your slaves, with no method to get back to a legitimate copy of your work. Again, this is fine when trying to solve failover, but not always the right answer for backups.

So, what we need is to make a copy of the database, and stick that some place safe and secure, so in case something goes horribly wrong (for really scary versions of horribly), we can get back to data that we know is good. The basic scheme goes like this:

Doesn't seem like this received much chatter, but there are a couple of reasons why Compiere loosening it's bond with Oracle is interesting. On the EnterpriseDB side, it's interesting because to date, I haven't heard of any Oracle apps that have been ported to their software, and now we have one that is out in the open, not just as an announcement, but according to the press release it is already installed at a customer site. Given the cost difference between EDB and Oracle, you can be sure this will lead to further adoption. On the Compiere side, there has always been talk that while they called themselves an open source company, they would never port their product to an open source option (remember fyracle anyone?). Yes, that hasn't changed with this announcement (PPAS is a closed fork of PostgreSQL), getting this running against a second database is the first crack in the wall. Yeah, they still make buckets on reselling Oracle, and now they will make smaller buckets reselling EnterpriseDB, but it might lead to further options down the line. Even if not, Compiere is an accepted tool in the "enterprise" world, and for whatever amount it's user base becomes interested in using EnterpriseDB, this will help open up the community of database professionals to having at least a passing familiarity with PostgreSQL (by way of Postgres Plus Advanced Server). So maybe this isn't big news, but it will have ripples.

Just a heads up that I'll once again be heading to Portland this summer for OSCon 2008. I'll be giving an updated version of my "Pro PostgreSQL" tutorial, which aims to help anyone who has been handed the task of maintaining one or more postgresql servers some firm ground to stand on and pointers on where to go from there.