Install PostgreSQL 8.3 on SME Server 7.4

Download PostgreSQL PGDG RPMs for CentOS - Yum Repository Configuration

# wget http://yum.pgsqlrpms.org/reporpms/8.3/pgdg-centos-8.3-6.noarch.rpm

Install it

# yum localinstall pgdg-centos-8.3-6.noarch.rpm

Import RPM GPG key

# rpm --import /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG

Modify db yum_repositories

# /sbin/e-smith/db yum_repositories set pgdg83 repository \
Name 'PostgreSQL 8.3 $releasever - $basearch' \
BaseURL 'http://yum.pgsqlrpms.org/8.3/redhat/rhel-$releasever-$basearch' \
EnableGroups yes \
GPGCheck yes \
Visible yes \
status enabled

Exclude postgres from base

# db yum_repositories setprop updates Exclude 'kernel, kernel-smp, kernel-xenU, mkinitrd, mdadm, initscripts, postgresql*'

Exclude postgres from update

# db yum_repositories setprop base Exclude 'kernel,kernel-smp, kernel-xenU,mkinitrd, mdadm, initscripts, postgresql*'

Update yum config

# signal-event yum-modify

Install PostgreSQL

# yum install postgresql postgresql-server postgresql-contrib

Initialize postgres database

# /etc/init.d/postgresql initdb

Start postgresql

# /etc/init.d/postgresql start

Create template directories

# mkdir -p /etc/e-smith/templates/var/lib/pgsql/data
# mkdir -p /etc/e-smith/templates-custom/var/lib/pgsql/data

Copy config files to template directories

# cp /var/lib/pgsql/data/*.conf /etc/e-smith/templates/var/lib/pgsql/data
# cp /var/lib/pgsql/data/*.conf /etc/e-smith/templates-custom/var/lib/pgsql/data

Now login as postgres user:

# su postgres

Then access to superuser database, set a password and exit.

bash-3.00$ psql -U postgres -d template1
Welcome to psql 8.3.7, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
template1=# alter role postgres with password 'postgres';
ALTER ROLE
template1=# \q
bash-3.00$ exit

Edit the file /etc/e-smith/templates-custom/var/lib/pgsql/data/postgresql.conf remove # and change the following settings:

password_encryption = on
listen_addresses = 'localhost'

Edit the file /etc/e-smith/templates-custom/var/lib/pgsql/data/pg_hba.conf and change the following settings:

local    all   all                    md5
host     all   all    127.0.0.1/32    md5
host     all   all    ::1/128         md5

and add at the end:

host    all    all    0.0.0.0 255.255.255.255  reject

Be sure to add an extra white line at the end.

Expand template:

# expand-template /var/lib/pgsql/data/pg_hba.conf
# expand-template /var/lib/pgsql/data/postgresql.conf

Restart with new settings:

# /etc/init.d/postgresql stop
# /etc/init.d/postgresql start

and add the symlinks to start automatically:

# ln -s /etc/init.d/postgresql /etc/rc.d/rc7.d/S56postgresql

Add postgresql to configuration database

# config set postgresql service status enabled

Reference:

http://wiki.contribs.org/Postgres