This article documents how to install PostgreSQL on Fedora Linux 12. It may be useful for later versions as well.

Install PostgreSQL Server

# yum install postgresql-server

The client package will be installed as a dependency.

Initialize Database Cluster

# service postgresql initdb

Start Server

# service postgresql start

Connect as Initial User

Installing the postgresql-server package creates a user account called postgres, and a database login role of the same name. The default client authentication configuration only permits a connection that meets the following criteria.

1. The type is local (i.e. Unix domain socket) or the source host is either 127.0.0.1 (IPv4) or ::1 (IPv6)
   
2. The login role matches the user account

At this point, the only login role is postgres. In order to create databases and additional login roles, you will need to begin a session with the postgres account.

# su -l postgres

Create Login

$ createuser --no-superuser --no-createdb --no-createrole --pwprompt jbpm3

When prompted, type and confirm the password of your choice.

Create Database

$ createdb --owner jbpm3 jbpm3

Allow Local Connections

$ vi /var/lib/pgsql/data/pg_hba.conf

The following lines allow the jbpm3 user to connect locally to the jbpm3 and postgres databases with password authentication.

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
local   jbpm3       jbpm3                             md5
host    jbpm3       jbpm3       127.0.0.1/32          md5
host    jbpm3       jbpm3       127.0.0.1/32          md5

Reload the service for the changes to take effect.

# service postgresql reload

You may end the postgres session now.

Create Tables

$ psql --username jbpm3 --file jbpm.jpdl.postgresql.sql

Install pgAdmin

# yum install pgadmin3

Install Server Instrumentation

When you connect to the server through pgAdmin, you will be prompted to install the server instrumentation functions. If you want to do so, install the contrib package first.

# yum install postgresql-contrib

Afterwards, run the adminpack.sql script.

# su -l postgres$ psql --file /usr/share/pgsql/contrib/adminpack.sql

Schedule Maintenance Tasks

Put the following script in /etc/cron.daily/pgsqlmntn

#!/bin/sh
su -c 'vacuumdb --all --full --analyze' postgres
su -c 'reindexdb --all' postgres

Do not forget to make it executable

# chmod a+x pgsqlmntn

Increase Prepared Transactions Limit

In the most demanding exercises with the XA data source you may encounter this hideous creature.

org.postgresql.util.PSQLException: ERROR: maximum number of prepared transactions reached

To face it, refer to the book of sorcery. Look for parameter max_prepared_transactions. Parameters can be set in the configuration file: postgresql.conf.

# su -l postgres
$ cd /var/lib/pgsql/data/
$ vi postgresql.conf

Set max_prepared_transactions to be at least as large as max_connections, as recommended in the manual.

max_prepared_transactions = 100         # can be 0 or more
                                        # (change requires restart)

Reload the configuration for the changes to take effect.

# service postgresql reload

Good luck!