PostgreSQL

PostgreSQL version RHQ currently supports versions 8.2.4 or later of PostgreSQL. Earlier 8.2 versions are not recommended. Postgres 8.1 is not supported.

PostgreSQL Quick Start Installation

If you already have a PostgreSQL server installed, you only need to perform steps 4 and 5 to create the RHQ database and user. You can then go to the PostgreSQL Preparation section to prepare your database.

1. Download the latest release of PostgreSQL .
   • For UNIX: Download the source from http://www.postgresql.org/ftp/source/.
   • For Windows: Download the installer from http://www.postgresql.org/ftp/binary/.
2. Install it.
   • For UNIX: Build and install it as described here: http://www.postgresql.org/docs/8.3/static/install-short.html
     • Another way to install Postgres is via a package installer such as yum on Fedora. Depending on how you installed PostgreSQL, you may have to configure its authentication mechanisms for the rest of the following commands to work. One quick way to get PostgreSQL up and running is to edit its data/pg_hba.conf file so that it trusts all users on the computer in which it is installed. You can do this by adding the following lines in the data/pg_hba.conf configuration file:

       local all all trust
       host all all 127.0.0.1/32 trust

       You may also want to change the postgres user's password; as root execute:

       passwd postgres

   • For Windows: Install it as described here: http://pginstaller.projects.postgresql.org/,
3. Start Postgres:
   • For UNIX:

     service postgresql start

     You may find that you have to initialize the database if this is the very first time you are starting a newly installed PostgreSQL server. If the above start command fails, try the following first and then execute the start command afterwards:

     service postgresql initdb

   • For Windows:
     If installed as a service then postgres may already be running. Otherwise:

     net start pgsql-8.3

4. Create a PostgreSQL role named rhqadmin with password rhqadmin. Do this via pgAdmin or as follows from the command line:

   cd <postgres-install-dir>
   createuser -h 127.0.0.1 -p 5432 -U postgres -S -D -R -P rhqadmin

5. Create a PostgreSQL database named rhq, specifying the rhqadmin role as the owner. Do this via pgAdmin or as follows from the command line:

   cd <postgres-install-dir>
   createdb -h 127.0.0.1 -p 5432 -U postgres -O rhqadmin rhq

6. If you are going to use this PostgreSQL installation for more than just demonstration or testing purposes, continue on to PostgreSQL Preparation for more advanced settings you'll need to make on your PostgreSQL server for production use with RHQ.

PostgreSQL Preparation

If you do not have PostgreSQL installed, refer to PostgreSQL Quick Start Installation for steps on how to install PostgreSQL.

postgresql.conf

PostgreSQL requires minor changes to the database configuration. Make the following changes to the postgresql.conf file:

## not necessary  if the database is started with the -i flag
listen_addresses = '*'

## performance changes for RHQ
shared_buffers = 80MB      #  default is 32MB
work_mem = 2048            #  default is 1MB
statement_timeout = 30s    #  default is 0s
checkpoint_segments = 10   #  default is 3
# NOTE: If you are running Postgres 8.4 or later, comment out the below line, as the
# max_fsm_pages parameter is no longer supported by 8.4 or later.
max_fsm_pages = 100000     #  default is 204800

RHQ can use up to 55 database connections for the server. PostgreSQL also allows for connections reserved for administrators. These connections are counted in the pool of max_connections and therefore need to be added to the total number of max_connections. Assuming we have 5 connections reserved for the administrator, edit the postgresql.conf file as follows:

max_connections = 60     #  default is 100
superuser_reserved_connections = 5 #  default is 3
max_prepared_transactions = 60     #  default is 5 (in v8.3) or 0 (in v8.4)

Note that max_prepared_transactions is set to the same value as max_connections as per http://www.postgresql.org/docs/8.2/interactive/runtime-config-resource.html where it says: "If you are using prepared transactions, you will probably want max_prepared_transactions to be at least as large as max_connections, to avoid unwanted failures at the prepare step."

If you are using the Postgres plugin to monitor this database instance, add one more connection per (logical) database you have setup in PostgreSQL. For further information about this plugin refer to the Postgres Server section of the Managed Resources Guide.

Kernel parameters

Depending on the OS you are using, you may need to adjust some kernel parameters. Refer to http://www.postgresql.org/docs/8.2/interactive/kernel-resources.html for more information.

pg_hba.conf

Update the pg_hba.conf file to allow the newly created role to connect from the machine the RHQ Server is installed on, (for example localhost). For details on how to do this refer to http://www.postgresql.org/docs/8.2/interactive/client-authentication.html. After completing the above step, restart PostgreSQL for the changes to take effect. If no errors are displayed, the database is now ready to support a RHQ installation. For more information on tuning PostgreSQL, see http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server.

Fixes for "Relation RHQ_Principal does not exist" error

Sometimes the database connection is marked as valid but the install still fails with the "Relation RHQ_Principal does not exist" error. This occurs when a new database is created by running initdb in a non-C locale. To fix this error:

1. Using a database explorer, create an empty table called RHQ_PRINCIPAL in the database used for RHQ.
2. Go through the installer webapp - the installer displays a warning about an existing schema. Overwrite the existing schema as it only consists of one empty table.

Another option is to specify the encoding of the created database as SQL-ASCII at creation time. For example:

initdb -D /my/test/data -E SQL_ASCII --locale en_US.UTF-8

Replace the directory after -D with your target directory for the database.