Version 16

    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 (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.

    local   jbpm3       jbpm3                             md5
    host    jbpm3       jbpm3          md5
    host    jbpm3       jbpm3          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

    To install the pgAdmin graphical client:
    # 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

    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!