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

2 comments:

  1. Ran into some shared memory problems, so bumped the segment size up in Linux to 128MB max. To set these here is an explaination:

    =====================
    The default settings are only suitable for small installations (the default max segment size is 32 MB). However the remaining defaults are quite generously sized, and usually do not require changes. The max segment size can be changed via the sysctl interface. For example, to allow 128 MB, and explicitly set the maximum total shared memory size to 2097152 pages (the default):

    $ sysctl -w kernel.shmmax=134217728
    $ sysctl -w kernel.shmall=2097152

    In addition these settings can be saved between reboots in /etc/sysctl.conf.

    Older distributions may not have the sysctl program, but equivalent changes can be made by manipulating the /proc file system:

    $ echo 134217728 >/proc/sys/kernel/shmmax
    $ echo 2097152 >/proc/sys/kernel/shmall
    ===============

    After this, I could set my buffers to 80MB without problems.

    ReplyDelete
  2. Thanks, never would have know one of the first entries in Google I found would have been by you. Maybe Google does some 'correlation' things ;-)

    ReplyDelete