Set Up PostgreSQL

Set Up External Hyperic Database on PostgreSQL

This section provides instructions for setting up PostgreSQL as your external Hyperic database.

This task corresponds to Step 2 – Set Up Hyperic Database of Hyperic Installation and Startup Process.

It is assumed that the reader is or has the support of a database administrator.

Evalating Hyperic?

If you are installing Hyperic for evaluation, you can use Hyperic's built-in PostgreSQL database, rather than set up an external database.

These instructions assume that you are performing a new installation of an RPM package of PostgreSQL, using Yum, an automatic RPM package installer.

If you do not have an RPM package, you can obtain the source from the http://www.postgresql.org/

Hyperic requires utf-8 character encoding

Create the Hyperic database with utf-8 character encoding.

Example Configuration

The instructions that follow show commands for setting up PostgreSQL in an environment with these characteristics:

Operating system

Red Hat Enterprise Linux 4

Database

PostgreSQL 8.3

Database IP address

192.168.1.4

Hyperic Server IP address

192.168.1.6

Database user name

admin

Database password

hqadmin

Database location

/var/lib/pgsql/data/

PostgreSQL configuration file

/var/lib/pgsql/data/postgresql.conf

PostgreSQL authorization file

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

Install and Initialize PostgreSQL

Log in as root to the target Linux machine, and enter this command:

yum install postgresql postgresql-server

PostgreSQL will be installed in:

/etc/init.d/
/usr/bin/
/usr/share/doc/
/var/lib/pgsql/

Create PostgreSQL User

  1. Change user to PostgreSQL and connect to the database locally.

    # su postgres
    • The psql prompt is displayed.

  2. Create a user named admin with login and createdb privileges.

    create role admin with login createdb password 'hqadmin';
  3. Create a default database for Hyperic. Place quotes around the string HQ so that the database name will be uppercase.

    CREATE DATABASE "HQ" OWNER admin;

Configure PostgreSQL Server Options

In this step, you configure PostgreSQL Server options in the PostgreSQL server configuration file, postgresql.conf.

  1. Open postgresql.conf.

  2. The default database permissions allow local connections only. To configure PostgreSQL to listen on all network interfaces, uncomment the listen address entry and change its value as shown below.

    listen_addresses = '*'
  3. Add the following settings to optimize Hyperic performance:

    ##performance changes for HQ
    shared_buffers=10000
    work_mem=2048
    statement_timeout=30000

In PostgreSQL 8.3, the PostgreSQL parameter that enables Hyperic to monitor the database — track_counts — is enabled, because the PostgreSQL autovacuum daemon needs the collected information. Only superusers can change this setting.

PostgreSQL Tuning for Large Deployments (Optional)

These changes and additions to postgresql.conf can improve Hyperic performance in large environments, if you have at least 2GB RAM available for the database.

shared_buffers = 20000
commit_delay = 10000
checkpoint_segments = 15
work_mem = 8192
maintenance_work_mem = 32768
max_fsm_pages = 40000
effective_cache_size = 5000

In particular, increasing effective_cache_size is beneficial, given sufficient RAM.

You may need to refine your database configuration based on the performance you experience. Review of the database log by a database administrator should indicate whether further adjustments to checkpoint_segments or max_fsm_pages are appropriate.

Configure Client Authentication

In this step, you configure PostgreSQL to allow connections from other users and from the Hyperic Server.

PostgreSQL client authentication is defined in the pg_hba.conf file, which contains lines, referred to as records, that specify allowed connection types, users, client IP addresses, and authentication method. Locate this line in the file:

# TYPE  DATABASE USER  CIDR-ADDRESS AUTH-METHOD

and add these lines below it:

local   all      all   ident           sameuser
host    all      all *192.168.1.6/32* password

For more information about pg_hba.conf see http://www.postgresql.org/docs/8.2/interactive/auth-pg-hba-conf.html

Install PostgreSQL Client on the Hyperic Server Host

Install the PostgreSQL client so that you can verify connectivity between the server and the database. To install the client, enter these commands:

yum -y install postgresql
psql -d postgres -h 192.168.1.4 -U admin -W

After you verify the connection, you can remove the PostgreSQL client with this command:

yum -y remove postgresql

Install the Hyperic Server

Install the Hyperic Server. For instructions, see Step 3 - Set Up Hyperic Server of the Hyperic Installation and Startup Process.

Don't start up the server yet

Do not start the Hyperic Server until after completing the steps in the following section.

Start Hyperic Server

Start the Hyperic Server. For instructions, see the "Start the Hyperic Server" step of the Hyperic Installation and Startup Process.

If the server fails to start up, there may be problems with your PostgreSQL configuration. Check the PostgreSQL logs for connection failures or errors.

Troubleshoot PostgreSQL Connection Problems

If network connections to the database fail, you can troubleshoot the issue in PostgreSQL log files, using the UNIX® tail command with the -f parameter

tail -f displays the lines at the end of a file, and displays additional log messages that follow to the terminal. This is useful for watching log files, or any other file which may be appended over time. Failed connection messages are written to the following files:.

  • /var/lib/pgsql/data/pg_log/postgresql-day.log

  • /var/lib/pgsql/pgstartup.log

Useful PostgreSQL Commands

\h — help with SQL commands
? — help with psql commands
\du — list roles/users
\l — list databases
\c — to choose a database
\d — to list tables once in a database
\q — quit