That which we expected to be Postgres 8.5 is out and it is called Postgres 9.0. The odds are that it will be frequently used on Ubuntu 10.04 but there is no package in the repository. So, what would one bored programmer who is using Ubuntu at home do about it?
Install RPM based distro since RPM's are available for download – no, I do not think so.
Go to Martin Pitt's PPA (https://launchpad.net/~pitti/+archive/postgresql) and install his backports? Well, kind of OK for a lazy or not very ambitious Linux user.
Point the browser to http://www.postgresql.org/ftp/source/v9.0.1/, download source and build your own Postgres 9.0.1. That sounds like fun.
Since we do not want to make Postgres 9.0.1 on our desktop which already has installed Postgres 8.4 with tons of important work in it, we will pull out one clean VM. I am using libvirt and virt-manager (what one can install from repository) and it allows me painless VM management.
All machine settings are default. During install we use defaults and select to install OpenSSH server. After fresh install we do the common stuff:
sudo apt-get update
sudo apt-get upgrade
sudo apt-get install landscape-common linux-headers-server linux-image-server linux-server
sudo apt-get autoremove
Last instruction was to remove old boot images. Now we need a compiler and build environment:
sudo apt-get install build-essential libreadline6-dev zlib1g-dev
Build and test
Thanks to SSH we can connect to the remote server and simply copy and paste source to home folder. Since I downloaded postgresql-9.0.1.tar.bz2 I used:
tar xjvf postgresql-9.0.1.tar.bz2
Those who downloaded postgresql-9.0beta1.tar.gz will use different switches:
tar zxvf postgresql-9.0beta1.tar.gz
Now is a good time to cd into source folder and check what README and INSTALL say.
So, following instructions from INSTALL we cd to source folder, this time from terminal, and do the following:
sudo make install
This will take some time. I didn't encounter any problems during build and I do not think that anybody else will. Again, following suggestions from INSTALL we create postgres user and data directory:
sudo adduser postgres
sudo mkdir /usr/local/pgsql/data
sudo chown postgres /usr/local/pgsql/data
All important Postgres configuration files, like postgresql.conf and pg_hba.conf will be in data folder. That is different from Postgres 8.4 where /etc/postgresql/8.4/main is the place where we find postgresql.conf.
Now we can impersonate postgres, create those config files and start the server:
sudo su postgres
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
/usr/local/pgsql/bin/pg_ctl start -D /usr/local/pgsql/data
Last line could be improved:
/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data >logfile 2>&1 &
Back into postgres account, now we may add Postgres binaries to PATH variable:
We are ready to test:
and there we do our SQL statements
postgres=# create database mydb1;
postgres=# drop database mydb1;
to stop database we do:
/usr/local/pgsql/bin/pg_ctl stop -D /usr/local/pgsql/data
Still impersonating postgres, which concludes our test.
If you want to connect remotely to database, config files are in /usr/local/pgsql/data. In postgres.conf locate the following line:
#listen_addresses = 'localhost'# what IP address(es) to listen on;
and add what IP it should listen to, * is for all:
listen_addresses = '*'# what IP address(es) to listen on;
Do not forget to uncomment. Similarly, in pga_hb.conf append something like:
host all all 192.168.122.0 255.255.255.0 md5
Start postgres and you can connect using pgAdmin. The one that comes with Postgres 8.4 will complain that version 9 is not supported and report an error:
ERROR: column "datconfig" does not exist at character 76
Finally we are connected, but with limited usability.
So to do something useful with pgAdmin we need to build one or use one from Martin's PPA.
Instead of conclusion
This was a simple and fulfilling task which didn't require some exceptional effort. Now, when we have Postgres 9.0.1 we may check pgpool II. Naturally, if and when I find time for it. Ishii-san published a nice tutorial here http://pgpool.projects.postgresql.org/contrib_docs/simple_sr_setting/index.html you may wish to take a look in the meantime.