BIRN Human Imaging Database Installation Checklist for PostgreSQL

offbeatlossData Management

Nov 22, 2012 (4 years and 9 months ago)

290 views

Page 1 of 6

BIRN Human Imaging Database
Installation Checklist for PostgreSQL

Overview:

This document provides instructions for completing a PostgreSQL installation of the
BIRN Human Imaging Database (HID) including:
a. Installing PostgreSQL 7.4.x.
b. Installing the HID schema, functions and triggers.

System Requirements

1. Unix-compatible server.
2. 400 MB available disk space (105 MB for PostgreSQL + 90 MB for regression
tests + approximately, 205 MB for the HID database. In general, databases take
about five times the amount of space that a flat text file with the same data would
take. So, depending on your usage of the HID database, plan accordingly with
disk space).
3. PostgreSQL source code (http://www.postgresql.org/download/).
4. HIDscripts
(/home/Projects/fBIRN_IT_Docs__0011/Docs/HID_Data/db_objects.tar.gz).

The installation instructions are specific to PostgreSQL version 7.4.x. If you are using
version 7.3.x or version 8.x, please refer to the corresponding online documentation
(http://www.PostgreSQLql.org/docs/
) for installation details.

Postgres Database 7.4.x Installation

PostgreSQL is a popular open source database. The database is freely available with
source code and documentation available from the PostgreSQL web site
(http://www.postgresql.org/
). Below is a quick start guide to install PostgreSQL 7.4.x on
your server. For more detailed instructions, refer to the official documentation at
http://www.PostgreSQLql.org/docs/7.4/interactive
.

1. If you already have PostgreSQL 7.4.x installed, skip this section. Otherwise, if you are
starting completely fresh and don’t have PostgreSQL installed, first download the source
code for PostgreSQL version 7.4.9 from
ftp://ftp.postgresql.org/pub/source/v7.4.9/postgresql-7.4.9.tar.gz
.

2. Login as the root user to the linux server on which you wish to install postgres.

3. Ensure that you have sufficient disk space on your server for the installation. You will
need 105 MB of space for the postgres installation and empty database cluster alone (65
Page 2 of 6
MB for source tree during compilation+15 MB for installation directory+25MB for
empty database cluster). You can check how much disk space is available on the
partitions on your server using the command:
root# df –k or
root# df –h (for a clearer and understandable output).

Note: The 105 MB disk space excludes the HID database installation and any data that is
put into the database thereafter. So, ensure to have more than 105 MB of disk space,
about 400MB in all (including HID database installation and HID data) would be a safe
bet.

4. After getting the postgresql-7.4.9.tar.gz file, login as the root and copy it to a directory
on your server and unpack it using the commands
root# gunzip postgresql-7.4.9.tar.gz
root# tar xf postgresql-7.4.9.tar

5. This will create a directory postgresql-7.4.9 under the current directory with the
PostgreSQL sources. Change directory (cd) to that directory.

6. For a default installation, enter
root# ./configure

The default configuration will build the server and utilities, as well as all client
applications and interfaces that require only a C compiler. All files will be installed under
/usr/local/pgsql by default. For more details on customized configuration, refer to the
online documentation.

7. To start build, type
root# gmake
This could take between 5-30 minutes depending on your hardware. The last line
displayed should be
All of PostgreSQL is successfully made. Ready to install.

8. Optional (but helpful) step: You can test the server before installation by typing
username$ gmake check
This will run the regression tests that will help verify that postgreSQL runs on the
machine in the way the developers expect it to. If everything is ok, you will get a
message such as
“All n tests passed”
Note: You can do this only as an unprivileged user and not as root. Also, the regression
tests need about 90 MB of disk space.

9. To install postgres, enter
root# gmake install

Page 3 of 6
Note: (a) If you want to save on disk space, execute gmake install-strip instead of gmake
install. This will strip the executable files and libraries as they are installed
(b) The standard installation provides only the header files needed for client application
development. If you plan to do any server-side program development (such as custom
functions or data types written in C), then you may want to install the entire PostgreSQL
include tree into your target include directory. For more information on client-only
installations versus installations for server-side development, refer to the online
documentation. (http://www.postgresql.org/docs/7.4/interactive/install-procedure.html
)

10. To make the system now be able to recognize the newly installed shared libraries, set
the environment variable
LD_LIBRARY_PATH
in your shell start-up file such as
/etc/profile. For Bourne shells (sh, ksh, bash, zsh) use:
LD_LIBRARY_PATH=/usr/local/pgsql/lib
export LD_LIBRARY_PATH

For csh or tcsh use:
setenv LD_LIBRARY_PATH /usr/local/pgsql/lib

Note: To see which shell you are using, execute
root# echo $SHELL

11. Add the /usr/local/pgsql/bin and the man pages (/usr/local/pgsql/man) to the PATH in
the /etc/profile file. For Bourne shells (sh, ksh, bash, zsh) add the following lines to the
/etc/profile file:

PATH=/usr/local/pgsql/bin:$PATH
export PATH
MANPATH=/usr/local/pgsql/man:$MANPATH
export MANPATH

For csh or tcsh add the below lines:
set path = ( /usr/local/pgsql/bin $path )
set manpath = ( /usr/local/pgsql/man $manpath )

Create the database cluster

A database cluster is a data directory that will hold all the data stored in the database. As
a normal practice, the data directory is created in /usr/local/pgsql/ but can be created
anywhere else also. To create a database template for the HID, follow the instructions
below:
1. Login as root and create the data directory.
root# mkdir /usr/local/pgsql/data

2. Create (unix) user postgres, if it does not already exist.

3. Change the ownership of the pgsql directory to postgres.
Page 4 of 6
root# chown –R postgres /usr/local/pgsql

4. (Unix) login as postgres user.

5. Initialize the data cluster.
postgres$ initdb -D /usr/local/pgsql/data

Step 5 creates a template database called template1. This database is only a template and
should not be used for actual work. If you have successfully initialized the data cluster,
you will get a message like
“Success. You can now start the database server using:
/usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data/
or
/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data/ -l logfile start”

5. Next step is to start the database server. It is important to start the server such that
tcp/ip connections are enabled. This is necessary to allow requests from external
sources/servers to the HID database. Execute the command below to start the postmaster:

postgres$ pg_ctl -D /usr/local/pgsql/data/ -l logfile -o -i start

The server’s stdout and stderr are stored in the named logfile above. The –i flag allows
tcp/ip requests.

For more details on administering the postgres database, refer to
http://www.postgresql.org/docs/7.4/interactive


The rest of the document assumes that you have installed postgres 7.4.x on your server in
the directory /usr/local/pgsql, created the database cluster and started the database server.

Create the BIRNWEB user

Create the database user BIRNWEB.
postgres$ createuser birnweb –P
Password:

You will be prompted to enter the password twice. You will also be asked if the user can
create databases and users. Enter ‘n’ for both cases.

Create the HID database

Create the HID database (fbirn_hid, for example) using the command:
postgres$ createdb fbirn_hid –O birnweb.
This will create the hid database named fbirn_hid whose owner is birnweb.
postgres$ createlang -d fbirn_hid plpgsql
This creates the plpgsql language in the HID database.
Page 5 of 6

Add relevant entries in pg_hba.conf file

1. Comment all the lines (prepend a # before the lines) in this file and add the below
lines.
# TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD
local all postgres trust
local fbirn_hid birnweb md5
host fbirn_hid birnweb 0.0.0.0 0.0.0.0 md5

This will ensure that you can connect as user BIRNWEB to the HID database fbirn_hid
either locally (from the server) or remotely(from external sources). The 0.0.0.0 for IP-
ADDRESS and the IP-MASK enables external connections from *any* IP address. You
can use a specific IP address or subnet to restrict this use. The “md5” method is used for
encrypted passwords. The type “trust” is used for users who can connect to the
database(s) without a password and hence, this should be used restrictively.

2. Whenever pg_hba.conf file is modified, the postmaster needs to be sent a SIGHUP
signal. If changes are made to a running postmaster, the signal can be sent using the
command below:
postgres$ pg_ctl –D /usr/local/pgsql/data reload

If the postmaster is not running when you make changes to the pg_hba.conf file, you
don’t have to run the above command because the pg_hba.conf file is read on server
startup.

Note: For every database user that you create using ‘createuser’, you will need to make
an entry for that user in the pg_hba.conf file and signal the postmaster, if it is running.

Execute the scripts to create functions, tables and triggers in
the HID database

1. Login as user postgres.

2. Change directory to /usr/local/pgsql.

3. Get the database schema creation scripts for postgres from SRB location
/home/Projects/fBIRN_IT_Docs__0011/Docs/HID_Data/db_objects.tar.gz. Copy the
tarred and zipped db_objects file to the /usr/local/pgsql directory.

4. Unpack the distribution using the commands
postgres$ gunzip db_objects.tar.gz
postgres$ tar xf db_objects.tar

5. This will create a directory called db_objects. Change directory (cd) to this directory.
Page 6 of 6

6. Read the README carefully. Make appropriate changes to the specified scripts as
mentioned in the README. In particular, you will need to update the main.sql and
static_data_project.sql in the db_objects directory to customize it for your site,
specifically, the scanner equipment used at your site, the task descriptions and the like.

7. Login to the database as user birnweb.
postgres$ psql –d fbirn_hid –U birnweb
Password:

8. Execute the database creation scripts.
fbirn_hid=>\i main.sql

To confirm that the HID database installation was successful, execute the command
below:
fbirn_hid=>\d

The above command will list all the tables in the database. The last couple of lines listed
should be:
public | uid_seq | sequence | birnweb
(81 rows)

Contact Information

Rashmi Chandrasekhar
Programmer Analyst II
Laboratory of Neuro Imaging, UCLA
Email: rchandra@loni.ucla.edu

Phone: 310-267-5122

References

1. Online postgreSQL version 7.4 documentation:
http://www.postgresql.org/docs/7.4/interactive

2. Quick start guide:
http://www.databasejournal.com/features/postgresql/article.php/3064861