Installing and PostgreSQL on Linux
Contents |
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:
| postgresql | basics for postgres |
| postgresql-server | server components |
| 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
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.