Thursday, June 04, 2009

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

3 comments:

matteo said...

hi!
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.
http://forge.openbravo.com/plugins/espforum/view.php?group_id=100&forumid=808030&topicid=6229490
on that forum someone told that uuid is in the cotrib rpm. but i think that is not exact.
http://wiki.openbravo.com/wiki/Projects/UUIDs_Usage/Technical_Documentation#PostgreSQL but this was not help, after i found it:
http://www.postgresql.org/docs/8.3/interactive/uuid-ossp.html where is a link to dowlad the source of the desired library http://www.ossp.org/pkg/lib/uuid.
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...

wget http://yum.pgsqlrpms.org/8.3/redhat/rhel-4-i386/uuid-1.5.1-4.rhel4.i386.rpm

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 <devrim@commandprompt.com> 8.3beta4-2PGDG
- Set uuid default to 0. RHEL 4 does not have uuid package. RHEL 5 has in EPEL repository