Archive for December, 2010

15 December, 2010

PostgreSQL 9 Admin Cookbook

by gorthx

The PostgreSQL community has been waiting for a “cookbook”, and I’m really excited that we finally have one. The PostgreSQL 9 Admin Cookbook contains many tips & techniques I’m going to put to immediate use. Like, pgloader and the ON_ERROR_STOP option to psql. This book also reminded me that there’s still a lot I don’t know about Postgres: for example, I’ve never worked with pg_controldata or the quote_ident() function. If you’re a Pg admin (or wannabe), you should give this book at least a run-through, even if you think you already know everything.

Most of the “recipes” in this cookbook will stand on their own; some require material from previous or other referenced sections to make sense. I am a sucker for conversational style, and while the book gets off to a rough start, it does even out after a bit. Big blocks of SQL are formatted consistently in a style that, while it’s not one I use myself, is easy to read. (This is very important.)

The authors give a great explanation of why they prefer their filesystem set up a specific way, and how to do it (of course, it probably helps that I agree with them on this point); and good advice about schema & relation names. Specific problem-solving tools I found useful are: the list of steps to troubleshoot failed connections; specific things to do if a backend is hung, or a query is taking too long; and generating test data and taking random samples of real-world data. They also give warnings where something you do might cause application downtime.

Sadly, this book suffers from inadequate editing. I often struggled against the grammar and organization to understand what the authors were trying to express. This frustrated me and slowed me down; a detriment in a technical manual. I also found that I couldn’t rely on the (seemingly auto-generated) index to find what I need, although the TOC and chapter headings helped out a lot there.

Bottom line: would I buy a hard copy? At the listed pricepoint (US$45 at this time), probably not. However, it’s a great first effort and I eagerly await the second edition.


Disclaimer: I received a free review copy of this book from the publisher, in pdf format.

10 December, 2010

Installing PostgreSQL from source on Ubuntu 10.10 (Maverick Meerkat)

by gorthx

About a month ago, I rebuilt my laptop with Ubuntu 10.10. This week, while I was waiting around to be selected for jury duty, I decided I should be constructive with my time instead of just getting annoyed with the really loud people sitting next to me. So I slurped up some of that delicious courthouse wireless & downloaded & installed Postgres 9.0.1. (You can get 8.4.5 as a package for Ubuntu, but I like using a more recent version.)

During my install, I remembered I’d had some problems the first time I installed Pg from source on Ubuntu, and had never written about it. I had the same problems this time, but they are all easily solvable by installing some extra packages.

Here’s my configuration command:
./configure
–with-openssl
–with-perl
–with-libxml

When I tried this, I got:
configure: error: readline library not found
…even though I already had readline installed. The solution was to install libreadline6-dev*. The -dev libraries include the header files needed to compile Pg.

For reference (and search engine) purposes, here are the other errors I got:
configure: error: library ‘crypto’ is required for OpenSSL
…fixed by installing libssl-dev.
configure: error: library ‘xml2’ (version >= 2.6.23) is required for XML support
…fixed by installing libxml2-dev.
/usr/bin/ld: cannot find -lperl #got this one during make
…fixed by installing libperl-dev.

‘make clean’, ‘make’, and ‘sudo make install’, and then I continued with the normal installation steps. Ta-da.


*libreadline5-dev may work; I didn’t try that.

eta I’ve since been informed of this repo: https://launchpad.net/~pitti/+archive/postgresql (I’ll continue to install from source, myself, because I like having control over all the little details.)