Creating an Ubuntu Postgres server

Follow these steps to create an Ubuntu Linux "server" running a Postgres database which allows non-local connections. You will need a machine with at least 10GB HD, bootable from CD-ROM, network card, and preferably more than 200MB RAM. Other than that, it can even be a Mac G4 if you like (just download the correct Ubuntu build).

  1. Download the [ISO image for Ubuntu 6.06.1 (Dapper Drake)](http://se.releases.ubuntu.com/6.06/). I chose the desktop version to have the graphic user interface to fall back on, I'm not a command-line typing machine.
  2. Burn the ISO image to a CD-ROM, and startup your soon-to-be-database-server with it. To make the installation go smoothly, hook it up to a network with a DHCP server. You can configure the DHCP server to always deliver the same IP to your database server, which is often much easier than configuring all those stupid Linux config files all over the place.
  3. Choose install values as if it were going to be your desktop. Follow the installer, don't try to "outsmart" it.
  4. When it boots for the first time, log on and find the software updates on the top left of the screen. Install the updates. This may take a while but you don't have to babysit it. Wait for it to finish and reboot when it asks you to. Please note that your root password will be the same password as you used for creating your first user.
  5. [optional] If you would like to be able to remote-connect to this machine using SSH, install the ssh server by typing in the following command on in a terminal window:


sudo apt-get install ssh openssh-server

You should now be able to connect to your server remotely. If you installed SSH, you can now move the machine to it's final physical destination (a nice broomcloset or something) 6. Install postgres 8.1 (plus client) with the following command:

sudo apt-get install postgresql-8.1 postgresql-client-8.1



  7. Change the password of the postgres user with the following commands (typing in red)


<font color="red">sudo su postgres -c psql template1</font>
template1=# <font color="red">ALTER USER postgres WITH PASSWORD 'mySecret123Password';</font>
template1=# <font color="red">\q</font>



  8. Make postgres listen on all interfaces by editting the postgres.conf file. Edit the file with:


sudo pico /etc/postgresql/8.1/main/postgresql.conf

Change the line

#listen_addresses = 'localhost'

to

listen_addresses = '*'

In the same postgres.conf file, make sure the password encryption is turned on by changing the line

#password_encryption = on

to

password_encryption = on

and close the file. 9. Tell postgres which machines are allowed to connect. Edit the pg_hba.conf file (nice descriptive name, huh?) with the following command:

sudo pico /etc/postgresql/8.1/main/pg_hba.conf

and add the following lines to the end of the file:

#Allow all hosts on the same subnet to connect to this database server
host all all 192.168.0.0 255.255.255.0 md5

Please note that my home network is a 192.168.0.* network. If yours is a 10.0.0.*, you know what to replace in the line above, right? 10. Make the postgres server restart and load the changes you've just made by using the command

sudo /etc/init.d/postgresql-8.1 restart



  11. Check that it actually works by using the following telnet command from a different machine in your network:


telnet [postgresmachine] 5432

If telnet responds with "Connection refused", you have a problem (which I'm not going to cover here, sorry, just retry). If all went well, telnet will tell you that it's "Connected to [postgresmachine]". You can start using it.

Creating a working postgres server from a bare metal machine should take not much more than 1 hour or so. For me, it took 2 complete evenings because so few people are posting good step-by-step simple guides. 80% of the time you get redirected to a long, complete, cryptic information page on the postgres site, which by the time you've read completely through it, only solved half your problem. The other 20% of the time people are steering you in the wrong direction by making you edit the "inner guts" of your server, like the rc.d files and whatnot.

Have fun with your new database machine!

Update: Replaced gedit with pico, this will work on an ssh session as well as a local terminal session. gedit is a graphical editor.