Update to PostgreSQL 8.4

PostgreSQL 8.4.1 packages are available here: https://launchpad.net/~pitti/+archive/postgresql\

Bug #431157 is requesting an official backport, but that isn't necessary.

Slony 1.2.17 is needed for PostgreSQL 8.4 support. It is currently in RC status. We need this released and packaged before we can use PostgreSQL 8.4 on staging or production.

Here is the result of the test suite running against a PostgreSQL 8.4 database.

Looks like we are triggering a PostgreSQL bug.

http://archives.postgresql.org/pgsql-general/2009-10/msg00198.php

Working on getting this fixed upstream. I suspect we will be blocked on a PostgreSQL 8.4.2 release (which isn't bad, as we are already blocked on a Slony-I 1.2.17 release as far as staging and production are concerned).

A fix is available for the 'invisible tuple' error. Hopefully it will be landed upstream soon.

The testsuite rapidly creates and drops databases. With PG 8.4.1, this chews up disk space at an alarming rate - it seems the autovacuum process never runs to completion on the system tables because it gets cancelled by CREATE DATABASE. We may need to manually vacuum the system tables occasionally.

The patch to solve the 'invisible tuple' error has been landed upstream: http://bazaar.launchpad.net/~vcs-imports/postgresql/git-trunk/revision/28894

Sony-I 1.2.17 has been released.

PostgreSQL 8.4.2 has been released.

Slony 1.2.20 has been released (.17 - .19 were quick due to bugs).

We seem to be down to just a few issues:

Tests with failures:
   lib/lp/soyuz/tests/../doc/package-cache.txt
   lib/lp/soyuz/tests/../doc/publishing.txt
   lib/lp/registry/tests/../doc/person-karma.txt
   lib/lp/soyuz/tests/../doc/archive.txt
   lib/lp/soyuz/tests/../doc/distroseriesqueue.txt
   lib/lp/registry/tests/../doc/mentoringoffer.txt
   lib/lp/registry/tests/../stories/webservice/xx-project-registry.txt
Total: 25465 tests, 7 failures, 0 errors in 238 minutes 45.204 seconds.

Tests all passing now

I encountered a similar error in 10.04 because postgresql was 8.4 instead of 8.3, and some browsing showed postgresql-8.3 stopped in 9.10. Why doesn't Launchpad warn that it isn't compatible with 10.04? dev.launchpad.net simply "recommends" 9.10.

It does work with 10.04; all Canonical devs use that and we will be deploying it on Lucid soon.

It doesn't work with 8.4 yet, no. That's also coming.

How can I downgrade to 8.3? I can't find 8.3 in the repositories for 10.04.

On Wed, Jul 7, 2010 at 3:19 AM, Alexander Lancey <email address hidden> wrote:
> How can I downgrade to 8.3? I can't find 8.3 in the repositories for
> 10.04.

I think there are packages in the Launchpad PPA (ppa:launchpad/ppa).
I'd recommend sticking with 8.4 though. What problem did you
encounter? As far as I know everything is ready to go with 8.4, and
the migration blocked on non-Launchpad stuff.

--
Stuart Bishop <email address hidden>
http://www.stuartbishop.net/

Stuart also says: "If you have two versions installed..., then you need to confirm 8.4 is listening on port 5432 and 8.3 is not. This is /etc/postgresql/8.XXXX/main/postgresql.conf"

Not sure if that helps you, Alexander, but FWIW.

Building failed at the step of "make schema". Here's the last few lines of output:

make[1]: Entering directory `/home/alex/launchpad/lp-branches/devel/database/schema'
* If this fails you need to run as the postgresql superuser
* eg. sudo -u postgres make create
* Creating database "launchpad_empty".
* Installing PL/PythonU
createlang: language installation failed: ERROR: could not access file "$libdir/plpython": No such file or directory
make[1]: *** [create] Error 1
make[1]: Leaving directory `/home/alex/launchpad/lp-branches/devel/database/schema'
make: *** [schema] Error 2

I checked in /etc/postgresql/*, 8.3 and 8.4 are installed, both listening on the right ports. I'm doing this is an updated fresh install of 10.04 server (in a VM, of course). Searching for the error showed messages about the postgresql version.

http://www.google.ca/search?q=plpython+launchpad+error

On Thu, Jul 8, 2010 at 7:30 AM, Alexander Lancey <email address hidden> wrote:
> Building failed at the step of "make schema". Here's the last few lines
> of output:
>
> make[1]: Entering directory `/home/alex/launchpad/lp-branches/devel/database/schema'
> * If this fails you need to run as the postgresql superuser
> * eg. sudo -u postgres make create
> * Creating database "launchpad_empty".
> * Installing PL/PythonU
> createlang: language installation failed: ERROR:  could not access file "$libdir/plpython": No such file or directory

You don't have the plpython package installed for PostgreSQL 8.4

--
Stuart Bishop <email address hidden>
http://www.stuartbishop.net/

Thanks, I got it running.

I think the setup ought to install postgresql-plpython8.4 since it's not a default install and isn't depended on by postgresql-*.

Stuart, is this fixed? Are we waiting for the ec2 instances to be using PG 8.4?