PostgreSQL Installation And Configuration

Step-By-Step Installation

Step 1. PostgreSQL Installation Checkup.

First, check if you have PostgreSQL database server already installed.
You may do this by entering the following command into your command prompt:

which psql

If you get the following (or similar):
/usr/local/pgsql/bin/psql,
this means you already have got PostgreSQL database software installed.

Using the rpm -q command (on RedHat servers) is an alternative way to check if PostgreSQL is installed. Type the following command in your command prompt:

rpm -qa | grep postgresql

If you get
postgresql-7.3.4-x
or something alike when the command is executed, it will mean that you already have PostgreSQL database software installed.

Now you may use the existing one or install a later version of PostgreSQL.

Step 2. Downloading PostgreSQL

(skip this step if PostgreSQL is already installed)

If you don't have PostgreSQL installed, you will need to download PostgreSQL from binary RPM distribution from www.postgresql.org or its mirror sites.

Find RPM file which is usually stored in the software/download directory and download it.

Step 3. PostgreSQL Installation.

Install the PostgreSQL database software. Do this by following command:

On RedHat servers:
rpm -i postgresql_rpm_file_name
where postgresql_rpm_file_name is PostgreSQL binary RPM distribution.

On FreeBSD servers:
pkg_add postgresql_pkg_file_name
where postgresql_pkg_file_name is PostgreSQL package for FreeBSD.

Step 4. PostgreSQL Configuration.

1. Prior to configuration, you need to start PostgreSQL for the first time to initialize the PostgreSQL service database and to create the necessary files and directories.

On RedHat servers, PostgreSQL service is initialized automatically on the first PostgreSQL start:
/etc/rc.d/init.d/postgresql start
On FreeBSD servers, you need to initialize it manually before you start PostgreSQL:
su - pgsql -c initdb
/usr/local/etc/rc.d/010.pgsql.sh start

2. To configure the access to PostgreSQL DBs, go to PostgreSQL home directory. It is usually /usr/local/pgsql. To find out what is the path to PostgreSQL home directory, login as postgres user under root and type pwd:
# su - postgres
# pwd
or, finger postgres to get info about the postgres user:
# finger postgres

In this directory, find the data/pg_hba.conf file. Open it and find the records similar to the ones below:

If the 'AUTHTYPE' parameter is set to trust, you must change the authentication option to password.
* For more detailed configuration, see pg_hba.conf file.

See also: Setting password for the PostgreSQL user (postgres on RedHat, pgsql on FreeBSD).

PostgreSQL Log File

/var/log/pgsql is the PostgreSQL log file.

Backing Up PostgreSQL Database backup

Back up the PostgreSQL home directory or make the database export by the means of PostgreSQL. Type 'man psql' or see Postgres documentation for details.

VACUUM Utility

The Postgres VACUUM command enables to clean up the server transactions.

Enter the psql server:

# psql database_name [user_name]

In the psql command line, type the 'vacuum full' command:

vacuum full;

Or, write a shell script performing this procedure and add it to cron jobs on the PostgreSQL server to be launched regularily.

Note: vacuum is a time-consuming procedure; it may take up to several hours to complete!

H-Sphere PostgreSQL Scripts

On the PostgreSQL database box in the /hsphere/shared/scripts directory, the following scripts must be installed:

pgsql-change-user-password - changes user password
pgsql-change-user-password.sh - changes user password
pgsql-create-db - creates PostgreSQL database
pgsql-create-db.sh - creates PostgreSQL database
pgsql-create-user - creates PostgreSQL user
pgsql-create-user.sh - creates PostgreSQL user
pgsql-db-size - calculates database size
pgsql-db-size.pl - calculates database size
pgsql-delete-user - deletes PostgreSQL user
pgsql-delete-user.sh - deletes PostgreSQL user
pgsql-drop-database - drops PostgreSQL database
pgsql-drop-database.sh - drops PostgreSQL database
pgsql-get-login - gets PostgreSQL superuser login and password
pgsql-get-login.pl - gets PostgreSQL superuser login and password
pgsql-resume-user - resumes the suspended user
pgsql-resume-user.sh - resumes the suspended user
pgsql-setenv - sets PostgreSQL environment variables
pgsql-suspend-user - suspends PostgreSQL user
pgsql-suspend-user.sh - suspends PostgreSQL user

All scripts accept some command line parameters. All scripts consist of two parts. The first part, typically without extension, sets necessary variables and then calls the second part of the script under sudo.

INFO: fix_perm.sh scripts sets needed owner and rights to Postgres scripts.

WARNING: Some of these scripts are different on FreeBSD systems, so copy corresponding versions of scripts from /hsphere/shared/scripts/FreeBSD.