Wednesday, 20 October 2010

Extensions in 9.1+

One of the most exciting developments in 9.1 will be the new CREATE EXTENSION feature that Dimitri Fontaine has been designing. Dimitri has been working on them for some time now, proposing them in detail at the 2010 Developer Meeting. Recently he's been coding them and the first patch arrived a few days ago. Reviews should happen in November, more than enough time to happen for the next release.

What do extensions do? Make it easier to add and remove plugins, datatypes, functions etc with minimum fuss and without needing to compile things yourself. It turns out that there's a great many aspects to this and much more complex than I've made it sound.

Dimitri has himself designed a number of very cool add-ons for PostgreSQL and its from those that he's gained insight into what's required. I've been trying to follow it myself, but I'm a bit lost on some aspects of it just yet.

Anyway, looks like there will be a few websites and repositories from which you can download extensions, so the hope is that it will be much easier to both obtain and install add-in components in future versions of PostgreSQL.

We're especially lucky that funding has been made available from the 4CaaST project, a Cloud Computing project funded by the European Union (under FP7). More on that another time.

Tuesday, 5 October 2010

Cleaning your archive

Whether you're thinking of using PostgreSQL 9.0 yet or not, you maybe interested in a small new utility that is designed to help you clean out old WAL archive files.

pg_archivecleanup is a standalone program which simply removes all WAL files older than a certain filename. You could use it like this:

pg_archivecleanup /my/archive 000000010000DEAD0000BEEF

The good thing here is that the tool works just fine for any release of PostgreSQL, not just 9.0. Mostly useful for bash or Windows scripts, since people will no doubt confirm it's trivial to do this in Python or perl etc..

The utility was designed as a dual purpose tool, since with 9.0 pg_standby is no longer required, yet you still need some way to clear down an archive. So you can also include it in the archive_cleanup_command, like so

archive_cleanup_command = 'pg_archivecleanup /my/archive %r'

which gets called regularly with changing values of %r to clean out your archive directories.

Not big, but its useful.

Friday, 1 October 2010

PostgreSQL Admin Book: Last Edit

I've been working on a PostgreSQL Administration book for some time now and am very happy to report that I'm done with the last edit on the last chapter. Hannu finished his a few days earlier.


The full title of the book is "PostgreSQL 9 Administration Cookbook" and has been written by Hannu Krosing and myself. We've written it together over the course of 9 months or so. I guess it's no surprise to some, but these things always take more time than you expect.

The book is in the form of short, punchy recipes that tell you what you need to know on a topic quickly with examples, then we describe how it works later. Some of the recipes are really basic to make sure we address the frequent questions, but around half are advanced topics. It's certainly taken some time to express both the basic and the advanced topics succinctly.

An advanced database system needs advanced books to show it off in the best light. I very much hope it is up to the high standards of the PostgreSQL community and moves us all forwards.

I'll write some more about it later. All I can really express now is relief!

You can pre-order the book here: