wiki:sysadminPostgreSQLConfiguration

Version 4 (modified by risard, 13 years ago) (diff)

--

Installing Postgres


Overview

PostgreSQL is a powerful object-relational open source database server. Evergreen uses Postgres as its backend datastore. Earlier versions of Evergreen used version 8.2, currently (post v1.4) Evergreen uses Postgresql 8.3. The instructions below are for installing 8.3. See the references below for instructions on installing version 8.2 if you're using an older version of Evergreen.

The assumption being made in this documentation, is that the database server is separate from the open-ils server.


Installing the Software

Postgres is a common default install on most distros and might already be installed. As root (using either sudo or su), run apt to see:

    sudo apt-get update
    sudo apt-get install postgresql-8.3

If postgres is already installed, apt will say so and make no changes, otherwise it will be installed with default settings.

During installation,a postgres user will be added to the local system (ie the computer). This is a standard linux user account and it's permissions are created during setup. Do not change the permissions of this user! This user runs the postgres processes and should have only the permissions necessary to do that. Also created during installation is a db role called postgres. This is internal to postgres and has nothing to do with the operating system. The postgres role provides complete access to all databases created on that db server. It's important to change the passwords for both the postgres account and postgres role for security purposes.

    $sudo passwd postgres

You will be prompted to change postgres's password. After doing so, you can check that it worked by trying:

    $su postgres

You should gain access to the postgres account with the password you entered. If not, you likely made a mistake and can try again with the passwd command.

You change the db role via psql. Switch users to postgres:

    $su postgres

and log into psql:

    $psql

Run the following:

    ALTER ROLE postgres WITH ENCRYPTED PASSWORD 'mypassword';  #include the quotes around mypassword

Initializing The Database Server

Postgres uses the term "database cluster" to describe the directory it uses to store data. During the apt install, a default directory is created, usually at: /var/lib/postgresql/8.3/main.

NOTE: our servers have been partitioned to best serve the needs of Evergreen. The default location is not usually on a partition that has been sized appropriately for the Evergreen db. Generally, you will not be using the default install directory for your database cluster.

You will need to determine the appropriate partition to install the cluster on. Usually, this is /data. To be sure, you can use the disk free command to see:

    df -h

should result in something like:

    Filesystem        Size  Used Avail Use% Mounted on

    /dev/mapper/LogVol00-VolGroup03
                      4.7G  145M  4.4G   4% /usr/local
    /dev/mapper/LogVol00-VolGroup07
                      1.9G  1.1G  797M  57% /var
    /dev/mapper/LogVol00-VolGroup08
                      1.9G   53M  1.8G   3% /var/log
    /dev/mapper/LogVol00-VolGroup09
                       84G   41G   39G  52% /data

Looking at the above, clearly the /data patition is the largest (84G) compared to the others.

NOTE:

Having located the largest partition, you will need to create directory for the data cluster, and give the postgres user full access to it. In the spirit of keeping things standardized, we have been using the /postgresql/<version>/main format.

    sudo mkdir /data/postgresql/8.3/main
    sudo chown postgres:postgres /data/postgresql/8.3/main

Now you need to initialize the data cluster. Remember to include the -D switch to point it at the new directory you created above and not the default one!