PostgreSQL Configuration

This article deals with configuring PostgreSQL in a RedHat system. The test system was running RedHat7.2.

PostgreSQL (pronounced `postgres-QL') is a open source free relational database. When compared to MySQL, this offers more features: sub-queries, procedural language support, commit/rollback, etc. The one feature where MySQL beats PostgreSQL is speed.

Running Postmaster

It is assumed that PostgreSQL is installed in your system. If not install it. Installation instruction is available with the download. Download PostgreSQL from http://www.postgres.org/. Postmaster is the PostgreSQL daemon process. This process should be running for use of Postgres database. Issue the following command to check if it is running:

# /usr/sbin/ntsysv

See if there is a ``*'' near ``postgresql''. Else press spacebar to select it. Restart the system.

Steps in Creating Postgres User and Database

For creating a Postgres user, you need to have a corresponding system user. You also should note that a Postgres user can be created only by logging in the system as user ``postgres''. Not even ``root'' has this permission!

1. Log in to the system as root, change the password of the user ``postgres'':

   # passwd postgres

2. Now we will create a system user ``wiz'' & assign password:

   # useradd wiz
   # passwd wiz

3. Now either ``su'' as user ``postgres'' or login as ``postgres'' (use the password given in step 1). Issue the following command to create Postgres user ``wiz'':

   $ createuser wiz

   This command will ask two questions:

   1. Shall the new user be allowed to create databases?
   2. Shall the new user be allowed to create more new users?

   I usually give 'y' to the first question and 'n' to the second. If you do not give 'y' to the first question, you cannot proceed to the next step!

4. Now login as user ``wiz''. Issue the following command:

   $ createdb wizdb

   The database ``wizdb'' for the user ``wiz'' has been created!

psql

``psql'' is an interactive client for connecting to the Postgres database. Taking the example from the previous step, login as ``wiz'' and issue the following:

$ psql -d wizdb

This will give you a prompt ``wizdb=>''. This means you are now connected to the Postgres database ``wizdb''. In this prompt you can issue all types of SQL queries supported by Postgres. Another important parameter to the command ``psql'' is ``-f''. If you want to execute SQL queries from a file, say ``create.sql'', you have to give the following:

$ psql -d wizdb -f create.sql

All the SQL statements in the text file ``create.sql'' will be executed.

To know more about the ``psql'' commands use ``\?'' in the psql prompt. Press ``^d'' or type ``\q'' to quit psql.

-S.Subhash
July '02