from __future__ import *

Kailash and Friends Kailash Kher Kaipa

The OSCON 2005 PostgreSQL Presentations are up, but a bunch of them are in OpenOffice format. I went through the pain of installing OpenOffice to convert them to PDF. For completeness, I have also included the presentations that were already available as PDF.

Chris Browne:

• Replicating PostgreSQL Databases Using Slony-I
• Event Propagation in Slony-I
• Sample Replication RT/3
• Upcoming Slony-I Enhancements

Joe Conway:

• Terrabytes of Business Intelligence

Lance Obermeyer:

• Running PostgreSQL On Windows

Bruce Momjian:

• State of PostgreSQL 8.1
• Porting PostgreSQL To Windows

Aaron Thul:

• PostgreSQL Built Your Car

Robert Treat:

• Explaining Explain

We're currently experimenting with various optimization techniques for our databases using PostgreSQL's features, and we've become quite interested in the Table Partitioning in PostgreSQL 8.1. Bizgres has this feature now (amongst others that will likely benefit our app) in its 0.7 release, so I decided to give it a whirl.

Installing Bizgres 0.7 on Mac OS X is easy enough, but it loses a few points (no big deal, it's beta):

• The installer doesn't use Apple's installer, it uses some InstallAnywhere crap that I don't really trust
• The installer is probably one of the ugliest things I have ever seen on Mac OS X outside of perversiontracker
• It throws a readline library in a Fink-owned location if it's not already there (install_name_tool, anyone?!)
• It doesn't install any sort of preference pane, StartupItem, or GUI tools at all
• The demos are there, but they're sitting in tar files... wtf?

Installing on FreeBSD er.. loses even more points. It's not a supported platform yet, so that's to be expected. The build process depends on a suite of bash scripts to set some environment variables, and ant to do the build. Autoconf happens somewhere too, but that's not invoked directly. The problems here are:

• Bash isn't the default shell in FreeBSD, and isn't even normally installed (unless you did it yourself)

• The bash scripts assume /bin/bash (where it's /usr/local/bin/bash on FreeBSD)

• The Ant build script, common.xml:

  • Needs OS-specific incantations
  • Assumes make is GNU make
  • Assumes bash is at /bin/bash (even though it checks for its presence anywhere in $PATH!)

• Oh, and it needs Java for Ant:

  Java Is A Royal Pain In The Ass On FreeBSD (and installing it is beyond the scope of this post).

So anyway, I fixed those things and made a patch. Here's how you could build and install with it:

• Download the 0.7 source tarball

• Do this:

  cd bizgres
  curl -s http://redivi.com/~bob/bizgres-0_7_4-FreeBSD-1.patch | patch -u -p0
  su
  bash
  source source-before-build.sh
  ant -Ddebug=false -DinstallRoot=/usr/local/bizgres -DdevoBuild=true install
  exit
  exit
  

This will install bizgres to /usr/local/bizgres-0_7_4. It's not going to make any rc scripts for you, but they can be trivially adapted from the PostgreSQL port.

Note that:

• This is the first time I've ever attempted to port something to FreeBSD (though I do have extensive Mac OS X experience)
• Bizgres 0.7 is beta software
• This is largely untested
• FreeBSD is not a supported platform (by them, or me)

If this causes you to lose all of your data, first, make sure that you weren't using MySQL. If you were indeed using Bizgres on FreeBSD, then blame yourself for not listening to the above.

UPDATE:

Changed the URL of the patch to one that compiles with OpenSSL support by default. To add OpenSSL support to an already patched source (or to JUST add OpenSSL support to unpatched source), use the following:

http://redivi.com/~bob/bizgres-0_7_4-OpenSSL.patch

/usr/ports/databases/postgresql-contrib comes with the very useful (until 8.1, anyway) pg_autovacuum daemon, but no rc script. Here's one I whipped up:

/usr/local/etc/rc.d/019.pg_autovacuum.sh

#!/bin/sh
#
# PROVIDE: pg_autovacuum
# REQUIRE: postgresql
# KEYWORD: FreeBSD shutdown

# Add the following line to /etc/rc.conf to enable `pg_autovacuum':
#
# pg_autovacuum_enable="YES"
#
#

. /etc/rc.subr


pg_autovacuum_enable=${pg_autovacuum_enable:-"NO"}
pg_autovacuum_flags=${pg_autovacuum_flags:-"-v 300 -V 0.5"}

name=pg_autovacuum
rcvar=`set_rcvar`
command=/usr/local/bin/pg_autovacuum
load_rc_config ${name}
command_args="-D ${pg_autovacuum_flags}"
pg_autovacuum_user=pgsql

run_rc_command "$1"

I did submit a PR with this by way of send-pr, but it doesn't seem to have ended up in the PR repository. I'm not sure what to do about that, as it's the recommended way to submit PRs!

We've been using MySQL for MochiBot for a while. It worked OK, until this week.

MySQL replication is unreliable. We had two MySQL servers running, one as a master, and one as a slave; the simplest of all replication scenearios. The slave was used only for redundancy and load balancing. It receives only SELECT queries and never mutates data (none of the connections connecting to it even have permission to mutate!). What happened? Replication stopped -- but only on one table, with no errors. The binlogs are all synchronized, it just decided not to update that table anymore. The workaround for this was easy, we stopped using replication. Granted, I consulted a few people who are running really high profile and high traffic sites on MySQL and they've never seen this before, but I can no longer trust MySQL to replicate our data.

The final blow was MySQL's MyISAM tables are fast, but dangerous. What happens when you try and put a lot of data in them? They explode. There's an arcane MAX_TABLE_ROWS limit to a MyISAM table. If you hit this limit, it won't let you insert any more rows. It doesn't grow for you. Why not? Beats me. I should've known this, but frankly, I've never trusted MySQL to store an 8GB table before, so I never ran up against it. Never again!

We're through, and are in the process of migrating to PostgreSQL ASAP. The bonus is that we can use all the nice features of SQL that you lose when making the MySQL choice: subselects, views, triggers, stored procedures, etc. I really should've listened to myself and avoided MySQL in the first place.