Installing and PostgreSQL on Linux
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.
Installing the RPM packages
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
If you do have permission then add the postgresql bundles you require. There are several:
|postgresql||basics for postgres|
|postgres||An 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
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.
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
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
Now run the command
This creates the database setting the superuser to your user-id.
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 ~$
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
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:
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.