Monday, December 28, 2009

Testing PostgreSQL 8.5-alpha3 with peg

PostgreSQL 8.5-alpha3 was announced last week. The biggest single feature introduced in it is Hot Standby, which allows you to run queries against a server that's being used as a Warm Standby replica. Since you can make any number of such replicas from a single master database, this introduces a whole new way to scale up PostgreSQL server farms in situations where you can live with queries that won't necessarily have the very latest data in them, due to replication lag. For example, it's a great way to run large batch reports like daily business summaries against the standby, rather than beating the master server with that load. Just wait a little bit after the end of the day for the transactions to copy over, then kick the query off against the hot standby system.

Actually getting two servers up and running so you can test this feature can be a drag though, since the alpha releases aren't necessarily going to be packaged up for you to install easily. In order to test a new version of PostgreSQL built from source, there are a fair number of steps involved: checkout the source, build, create a database cluster, start the server, and then you can finally run a client. Each one of these has its own bits you likely need to customize, from needing a directory tree to keep all these pieces (source, binaries, database) organized to source configuration time options.

After a few years of building increasingly complicated scripts to handle pieces of this job, recently I assembled them Voltron-style into one giant script that takes care of everything: peg, short for "PostgreSQL environment generator". The basic idea is that you give peg a directory to store everything in, point it toward the source you want to use, and it takes care of all the dirty work.

The best way to show how peg can help speed up development and testing is to see how easy it makes testing the latest PostgreSQL 8.5 alpha3:
$ git clone git://
$ sudo ln -s /home/gsmith/peg/peg /usr/local/bin/peg
$ mkdir -p pgwork/repo/tgz
$ pushd pgwork/repo/tgz/
$ wget
$ popd
$ peg init alpha3
Using discovered PGWORK=/home/gsmith/pgwork
Using discovered PGVCS=tgz
Using tgz repo source /home/gsmith/pgwork/repo/tgz/postgresql-8.5alpha3.tar.gz
Extracting repo to /home/gsmith/pgwork/src/alpha3
tar: Read 6656 bytes from -
Extracted tgz root directory is named postgresql-8.5alpha3
No checkout step needed for PGVCS=tgz
$ . peg build
$ psql
psql (8.5alpha3)
Type "help" for help.

gsmith=# \q
That's 9 lines of commands to go from blank development system to working psql client, and half of that was installing peg. The above downloads and installs peg (in /usr/local/bin, you may want to use a private binary location instead), grabs the alpha3 source (from the US, not necessarily best for you), and build a server using the standard options for testing--including debugging and assertion checks. If you want to do performance tests instead, you can do that with peg by setting the PGDEBUG variable to something else. That's covered in the documentation.

Warning: if you try to do this on a RedHat/CentOS system, you will probably discover it won't work. PostgreSQL 8.5 requires a newer version of the Flex tool in order to build from an early source code build now than is available in RHEL5 and its derivatives. I've got some notes on Upgrading Flex from source RPM to compile PostgreSQL from CVS you can read. There were official RPM packages of alpha2 released that bypass this problem, you may be able to get an alpha3 set from there in the near future too.

My goal was to make learning how to use peg pay for itself in time savings the first time you use it for testing a PostgreSQL development snapshot. The script still has some rough edges, but I've been using it regularly for over a month now with minimal functional issues. The documentation is almost complete, even showing examples of how to use peg to create two PostgreSQL installs on the same host--which allows testing hot standby even if you've got only one system.

peg is hosted on github, I've heavily noted the things that need improvement with TODO tags in the source and documentation, and patches are welcome. Hope it helps you out, and that you'll be joining the 8.5-alpha3 testers.