Install PostgreSQL on Fedora

    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

    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

    #!/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!