Computer Laboratory

Installing and PostgreSQL on Linux

Introduction

This page describes how to install and run PostgreSQL database servers on Lab managed Linux machines.

This page is not meant to be comprehensive - it gives just the basics to get a server running. To do anything more than that you will need to read the system manual pages, installed with the packages. The relevant ones are

initdb Initialising the database directory.
pg_ctl Controlling the database server (mainly starting and stopping.)
postmaster The database server itself.
pg_hba.conf The host-based authentication configration.
psql The SQL command line client.

There are three stages detailed below: installing the software, initialising the database directory and creating the database. The final sections give some simple examples to allow you to test the setup using the SQL command line interpreter and offer some hints on backing up databases.

The commands in pink boxes are designed to be copied and pasted from this web page, in order. After running each command, carefully check the output. If something goes wrong, stop and try to understand what and why. Only continue when you are sure you know what you are doing, otherwise you may get into deeper difficulties. Mail unix-admin@cl.cam.ac.uk for help if you cannot easily understand a problem. When you mail, explain what you are trying to do, on which machine, and what the problem is. Copy output from commands that fail. The better the information you supply the better we are able to help.

Installing the RPM packages

Choosing bundles

To install the RPMs you will need to be the main user of the machine and registered as such. Then you will have permission to edit the file /etc/user-config/bundles. To test this type the command

touch /etc/user-config/bundles

If you do not get any error message then you can edit the file. If you do not have appropriate permissions then e-mail unix-admin@cl.cam.ac.uk or, if it is not your machine, you will need to ask the registered user to install the software for you.

If you do have permission then add the postgresql bundles you require. There are several:

postgresqlbasics for postgres
postgresql-serverserver components
postgresAn alternative for the above two RPMs. The database server and command-line and tcl clients (psql and pgtclsh respectively.)
postgres-devel The C-language header files and client libraries for using the database from C programs
postgres-python The Python bindings for the database client API
postgres-perl The Perl language bindings for the database client API

For each bundle you require add them using a command like

echo postgresql        >>/etc/user-config/bundles
echo postgresql-server >>/etc/user-config/bundles

Or if you want to add several you could use

for b in postgres postgres-devel postgres-perl
   do echo $b >>/etc/user-config/bundles
done

Triggering the install process

The next step is to run the system command to update the installed packages. This is just

start-system-update --fixpkg

Check the resulting output. If there are errors then e-mail unix-admin@cl.cam.ac.uk. Tell them what you did and include a transcript of what occurred.

Creating and Initialising the Database Directory

Databases work best on local disk, which means scratch space on Lab hosts. Remember SCRATCH SPACE IS NOT BACKED UP and you risk losing the data unless you regularly make backups yourself.

If you have only a small amount of data you can use tar to copy it to your home directory (after stopping the server.)

Creating the directory

assuming you are going to use scratch space, if you have not already created one, create a scratch directory using the command

sudo cl-mkscratchdir

Next, set an environment variable to point to where you want to keep the data, and create the directory

export PGDATA=/local/scratch/$USER/pg-data
mkdir $PGDATA

Here $USER is your user-id. You should not need to set this variable - it is set by default.

Initialising the database

Checking security levels

If you are using a machine running Fedora Core 3 then it may have selinux security settings which prevent the database service from running under user accounts. Check the selinux configuration using the following command

getenforce

This should output Permissive. If it doesn't then e-mail unix-admin@cl.cam.ac.uk. Tell them which machine you are using, that you want to run a postgres database and that you need selinux set to permissive mode to do so.

Running initdb

Now run the command

/usr/bin/initdb

This creates the database setting the superuser to your user-id.

Creating databases

Starting the server

The server needs to be started before databases can be created:

/usr/bin/pg_ctl -D /local/scratch/$USER/pg-data \
                -l /local/scratch/$USER/postgres_log start

Use this command every time you want to start the server. It does not require the PGDATA environment variable to be set and it instructs the server to store the logs in /local/scratch/$USER/postgres_log.

Running the CREATE DATABASE command

To create a database for yourself run this command:

psql -c "CREATE DATABASE $USER;" template1

This creates your default database, which is what will be assumed if you do not specify any database explicitly when you start e.g. psql.

Stopping the server

To stop the server gracefully (which means waiting for client sessions to close before shutting down) use

/usr/bin/pg_ctl -D /local/scratch/$USER/pg-data stop

Running the psql client to check the install

With the server running you can start psql to use your database. Here is an example

~$ psql
Welcome to psql, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

ig206=# CREATE TABLE foo (foo integer not null default 0, bar text);
CREATE TABLE
ig206=# \d foo
               Table "foo"
 Attribute |  Type   |      Modifier      
-----------+---------+--------------------
 foo       | integer | not null default 0
 bar       | text    | 

ig206=# INSERT INTO foo VALUES (1, 'Some text');
INSERT 17149 1
ig206=# SELECT * FROM foo;
 foo |    bar    
-----+-----------
   1 | Some text
(1 row)

ig206=#\q
~$ 

Backing-up Databases

To make a backup of the database, first stop the server, as described above then use tar to copy create a compressed archive in your home directory:

/usr/bin/pg_ctl -D /local/scratch/$USER/pg-data stop
cd /local/scratch/$USER
tar cpzf ~/pg-data.tar.gz pg-data

Handling database server upgrades

From time to time there are PostgreSQL server upgrades which are not binary compatible with the database files of previous versions. These usually occur between major releases, e.g. from PostgreSQL 7.xx to PostgreSQL 8.xx. If your machine is upgraded the system administrators should warn you of this and give you a chance to make an ASCII dump of your databases so that you can recreate the databases under the new version when it is installed. To do this use the following command

SCRATCH=/local/scratch/$USER
/usr/bin/pg_dumpall | bzip2 -c >$SCRATCH/pg-data.ascii.bz2

You can examine the file with something like:

bzcat $SCRATCH/pg-data.ascii.bz2 | less

When the new database version is installed, after copying or renaming the database directory and creating the directories afresh with the new version of initdb use a command like this to recreate the database:

bzcat $SCRATCH/pg-data.ascii.bz2 | /usr/bin/psql template1

Problems

Running more than one server

If you want to run more than server on a given machine (say someone else is using PostgreSQL databases in a different directory) then you will need to choose a different port, 5433 say. Set the PGPORT environment variable before starting the server or running clients like psql or pg_dumpall:

export PGPORT=5433

Alternatively add the option -o "-p 5433" to the /usr/bin/pg_ctl commands and add the option -p 5433 to the psql and pg_dumpall commands.