Thursday, June 04, 2009

Install PostgreSQL 8.3 on SME Server 7.4

Download PostgreSQL PGDG RPMs for CentOS - Yum Repository Configuration
# wget
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 '$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';
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 md5
host all all ::1/128 md5
and add at the end:
host    all    all  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



matteo said...

i have some truble after the installation.

installing openbravo i need utf-8 bases

and i solved using this:

initdb --encoding=utf8 --locale=it_IT.utf8 --lc-ctype=UTF8

beacuse the initdb look in to locale setting of smebox that is not utf-8 compliant.

withouth this the db was initializated as LATIN1 and not as UTF8.

next problem is that i get error using UUID:

[sql] org.postgresql.util.PSQLException: ERROR: function get_uuid() does not exist

this is caused due to the lack of ossp-uuid module.
on that forum someone told that uuid is in the cotrib rpm. but i think that is not exact. but this was not help, after i found it: where is a link to dowlad the source of the desired library
so i like to know if exist another way to install uuid that install gcc and compile it.
i hope mi post can help thosa have my same problem

emnaz said...


yum localinstall uuid-1.5.1-4.rhel4.i386.rpm

emnaz said...

@matteo:postgresql package from PGDG did not support ossp-uuid. You can download source rpm from here and rebuild with rpmbuild -bb --with-ossp-uuid postgresql-8.3.8-1PGDG.rhel4.src.rpm

Quote from specs-file
* Thu Dec 06 2007 Devrim GUNDUZ <> 8.3beta4-2PGDG
- Set uuid default to 0. RHEL 4 does not have uuid package. RHEL 5 has in EPEL repository