Deploy/SpaceWalk/PostgresqlSetup

Setup of the PostgreSQL database

You should have PostgreSQL server running somewhere. Let's assume you will run the server on the same machine as Spacewalk itself:

yum install -y 'postgresql-server > 8.4'
# we do this to get postgresql84-server on RHEL 5
chkconfig postgresql on
# on Fedoras (16, 17), run:
postgresql-setup initdb
# everywhere else run:
service postgresql initdb

service postgresql start

Create database, user, and plpgsql language there:

su - postgres -c 'PGPASSWORD=spacepw; createdb -E UTF8 spaceschema ; createlang plpgsql spaceschema ; yes $PGPASSWORD | createuser -P -sDR spaceuser'

Configure the user to use md5 password to connect to that database. Put the lines like following to

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

. Avoid the common pitfall: Make sure you put them *before* those existing lines that are for all.

local spaceschema spaceuser md5
host  spaceschema spaceuser 127.0.0.1/8 md5
host  spaceschema spaceuser ::1/128 md5
local spaceschema postgres  ident

Then reload PostgreSQL:

service postgresql reload

and test the connection:

PGPASSWORD=spacepw psql -a -U spaceuser spaceschema
PGPASSWORD=spacepw psql -h localhost -a -U spaceuser spaceschema

Please note that you will want the TCP connection allowed as well because the JDBC driver cannot use the Unix domain socket. So even if the Python and Perl stack will use the Unix domain socket if you do not specify the hostname, JDBC will still go to localhost via TCP.

If you want to have the database on a separate box you will likely need to change this line in pg_hba.conf from 127.0.0.1/8 to something else:

host  spaceschema spaceuser 127.0.0.1/8 md5

and also to add line to /var/lib/pgsql/data/postgresql.conf

listen_addresses = '*'

The contrib package

If your PostgreSQL server is installed on different machine than where you setup your Spacewalk server, please make sure the <package>postgresql-contrib >= 8.4</package> (or postgresql84-contrib on RHEL 5) is installed on the PostgreSQL server.

Tune up PostgreSQL

Tune up PostgreSQL's performance by running pgtune:

yum install pgtune
pgtune --type=web -c 600 -i /var/lib/pgsql/data/postgresql.conf >/tmp/pgtune.conf
# Review the changes by
diff -u /var/lib/pgsql/data/postgresql.conf /tmp/pgtune.conf
cp /var/lib/pgsql/data/postgresql.conf /var/lib/pgsql/data/postgresql.conf.bak
cp /tmp/pgtune.conf /var/lib/pgsql/data/postgresql.conf
service postgresql restart

or at least increase maximal number of connections to 600:

echo max_connections = 600 >>/var/lib/pgsql/data/postgresql.conf