Postgres 9.0.1 compile and install on Ubuntu 10.04 64 bit server

358

Postgres 9.0.1 compile and install on Ubuntu 10.04 64 bit server

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?

  1. Install RPM based distro since RPM’s are available for download – no, I do not think so.

  2. 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.

  3. 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.

Environment

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 reboot

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:

./configure

make

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

exit

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:

PATH=$PATH:/usr/local/pgsql/bin

export PATH

We are ready to test:

psql postgres

and there we do our SQL statements

postgres=# create database mydb1;

CREATE DATABASE

postgres=# drop database mydb1;

DROP DATABASE

postgres=# q

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.