Re: Which version of PostgreSQL should I use.

> We're currently using psql 8.1 and are on the way to upgrading to 8.4.

The tools "pg_dump" & "pg_restore" are used to extract and re-create databases. Can be used for chores such as deploying from development to production, backing up, and upgrading.
http://www.postgresql.org/docs/current/static/app-pgdump.html
http://www.postgresql.org/docs/9.0/static/app-pgrestore.html

The tool "pg_upgrade" is intended to help you upgrade from one version to another. Think of it as a wrapper around pg_dump & pg_restore. Has a bonus feature where you can upgrade a database in place rather than re-create it if you have a huge amount of data and too little time at the moment of officially upgrading to afford re-creating the data.

http://www.postgresql.org/docs/current/static/pgupgrade.html

You may want to go to version 9 rather than 8.4.

a) 9.0 is not a major upgrade in terms of compatibility. Original plans labeled it as 8.5 for that reason. "9" was eventually chosen as a label only because of dramatic new features added.

b) Some hooks were added to 9 to allow enhancements to the 'pg_upgrade' tool. It may be actually be easier to upgrade to 9 than 8.4 for that reason.

> I have now been asked to start replicating our databases between servers - as a hot-copy / redundancy improvement.

A relatively simple and built-in replication system was one of the major features added to version 9.0.

http://www.postgresql.org/docs/current/static/high-availability.html

> Is there are a particular version of PostgreSQL that we should be "aiming" to upgrade to that provides for synching of databases.
> My initial thought is;
> We should upgrade to the latest stable version - whatever that is;

Version 9.0.4 is the current stable version. Version 9.1 is due out soon.

> pint me in the correct direction for some information about what's in what version and any upgrade requirements to get to XXX from 8.1

http://www.postgresql.org/docs/current/static/release.html

--Basil Bourque