Wednesday, July 8, 2009

Quick start setup of postgresql on Fedora

Today I needed to have postgresql running on a new Fedora installation so I could setup a demo for JBoss Operation Network.

This is what I did:

# First install server as root.
#
$ yum install posgresql-server

# init the database (note SELinux might complain
# if not in permissive mode) which is found in
# /var/lib/pgsql/data/*.
#
$ service postgresql initdb

# start the database service.
#
$ service postgresql start

# to setup users and databases we need to use
# the default 'postgres' user account, so login
# to this session.
#
$ su -l postgres

# create a new user for the jboss demo user 
# and database in my case.
#
$ createuser --no-superuser --no-createdb --no-createrole jbossdemo

$ createdb --owner jbossdemo jbossdemo

# leave postgres account back to root.
#
$ exit

# allow local connections to this account by adding the
# following lines to the pg_hba.conf:
#
#  TYPE  DATABASE  USER      CIDR-ADDRESS   METHOD
#  local jbossdemo jbossdemo                trust
#  host  jbossdemo jbossdemo 127.0.0.1/32   trust
#  host  postgres  jbossdemo 127.0.0.1/32   trust
#
$ vim /var/lib/pgsql/data/pg_hba.conf

# reload service to acquire changes.
#
$ service postgresql reload

# now install admin console for normal db 
# administration. Search internet for repos
# should your install not have this package.
#
$ yum install pgadmin3

Now I can install JBoss ON and use the jbossdemo account and database for my installation demo.

# some handy administration tips for your installation.
#
$ yum install postgresql-contrib

# install server instrumentation functions as 'postgres' user.
#
$ su -l postgres

$ psql --file /usr/share/pgsql/contrib/adminpack.sql

# schedule maintenance in daily cron job at
# /etc/cron.daily/pgsqlmaint. Put this in the 
# file:
#
#  #!/bin/sh
#  su -c 'vacuumdb --all --full --analyze' postgres
#  su -c 'reindexdb --all' postgres
#
$ vim /etc/cron.daily/pgsqlmaint

# back to root.
#
$ exit

# make it executable.
#
$ chmod a+x /etc/cron.daily/pgsqlmaint

# often going to need more transactions than the default
# allows so adjusts this as follows.
$ su -l postgres

# change this line to look as folows:
#
#  max_prepared_transactions = 100
#
$ vim /var/lib/pgsql/data/postgresql.conf

# back to root.
#
$ exit

# reload service.
#
$ service postgresql reload