Table of Contents

Installing PostgreSQL 9.0 when a package is not available from Debian or Ubuntu

Using backports

Most distributions do not yet offer packages for PostgreSQL 9. However, you may be the adventurous sort and wish to give PostgreSQL 9.0 a whirl.

For Debian Squeeze and Ubuntu Lucid :

  1. You can add the following line to /etc/apt/sources.list.
    1. for Debian Squeeze:
      deb http://backports.debian.org/debian-backports squeeze-backports main contrib
    2. for Ubuntu Lucid:
      deb http://archive.ubuntu.com/ubuntu lucid-backports main universe multiverse restricted
  2. As the root user, retrieve the updated package list from the newly added repository by issuing the following command:
    aptitude update
  3. Then return to the Evergreen install instructions and run the PostgresSQL Makefile.install command.

If you are using Ubuntu, you can use a PPA (personal package archive). These are handy apt-sources on Launchpad where folks can host unofficial packages. These packages work reasonably well and are in fact maintained by Martin Pitt who also maintains the official Postgres packages for Ubuntu.

https://launchpad.net/~pitti/+archive/postgresql

To install them (assuming you're running Ubuntu Lucid):

sudo apt-get install python-software-properties
sudo add-apt-repository ppa:pitti/postgresql
sudo apt-get update

Installing from source

It hasn't been extensively tested at press time (Nov 25, 2010), however I, James from SITKA can report it is reasonably stable out of the box running Evergreen 1.6.2 – it should be equally fine with Evergreen 2.0. Following is a rough set of instructions for installing PostgreSQL 9 from source on a Debian or Ubuntu system as a stow-managed application. These instructions were modified from the ones here debian

  1. If you have not yet installed stow on your system, install it. Issue the following command as the root user:
    aptitude install stow
  2. You may also need to install some other packages
    apt-get install bison libxslt1-dev libxml2-dev
  3. Download, compile, and install the latest bugfix release for PostgreSQL 9.0.1
    wget http://wwwmaster.postgresql.org/redir/333/h/source/v9.0.1/postgresql-9.0.1.tar.bz2
    tar xjf postgresql-9.0.1.tar.bz2
    cd postgresql-9.0.1
    ./configure --with-perl --enable-integer-datetimes --with-openssl --with-libxml --with-libxslt --prefix=/usr/local/stow/pgsql && make
    make install
    cd contrib && make
    make install
    cd xml2 && make
    make install
    cd /usr/local/stow
    stow pgsql
    # if there was a conflict, you can move the offending directory out of the way:
    mv /usr/local/man /usr/local/man.bak
    # then try again
    stow pgsql
  4. Create a new user, postgres, to run the PostgreSQL processes:
    adduser postgres
  5. Initialize the database directory and start up PostgreSQL:
    mkdir -p /usr/local/pgsql/data
    chown postgres /usr/local/pgsql/data
    su - postgres
    initdb -D /usr/local/pgsql/data -E UNICODE --locale=C
    pg_ctl -D /usr/local/pgsql/data -l /home/postgres/logfile start

Config stuff to change

FIXME

echo 7000000000 > /proc/sys/kernel/shmmax

default stats target (from 10 to 100)

work mem (from 4m to something higher… 128M?)

Shared buffers (to 512M for a 2G machine)

Effective cache size

(11:41:49 AM) dmcmorris_esi: miker_: stats target , workmem, shared buffers for 32GB Mem (11:42:34 AM) miker_: stats target = 100 (always), work mem = 128M, shared buffers = 4G (11:42:51 AM) miker_: effective cache size = 24G

Vaccuum full analyze verbose