How to install postgres on SME server

The command below will install postgres and update postgres-libs. For dependency resolution it will also install tcl and mx from the base repository.

If you see other packages, such as perl or php, abort the installation. This might occur because you have installed perl-CGI-Session or perl-DBD-Pg. These two packages might be on your system because you have installed otrs or another aplication. You can check if they are still needed:

rpm -q --whatrequires perl-CGI-Session perl-DBD-Pg

If that answers no packages need ... you can safely remove these packages

yum remove perl-CGI-Session perl-DBD-Pg


Enable centosplus repository and install postgres (at the time of writing this is version 8.1.9)

yum --enablerepo=centosplus install postgresql postgresql-contrib postgresql-devel postgresql-docs postgresql-pl postgresql-python postgresql-test

After install, ignore the post-upgrade and restart message

Start postgres

/etc/init.d/postgresql start

Then make the necessary custom templates:

mkdir -p /etc/e-smith/templates/var/lib/pgsql/data
mkdir -p /etc/e-smith/templates-custom/var/lib/pgsql/data
cp /var/lib/pgsql/data/postgresql.conf /etc/e-smith/templates/var/lib/pgsql/data
cp /var/lib/pgsql/data/pg_hba.conf /etc/e-smith/templates/var/lib/pgsql/data
cp /var/lib/pgsql/data/postgresql.conf /etc/e-smith/templates-custom/var/lib/pgsql/data
cp /var/lib/pgsql/data/pg_hba.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.

psql -U postgres -d template1
alter user postgres with encrypted password 'add_your_pass_here';
\q
exit

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

superuser_reserved_connections=2
ssl = on
password_encryption = on
listen_addresses = 'localhost'

Then copy your server SSL keys:

cd /var/lib/pgsql/data
cp /etc/httpd/conf/ssl.crt/server.crt .
cp /etc/httpd/conf/ssl.key/server.key .
chown postgres:postgres server.*

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
ln -s /etc/init.d/postgresql /etc/rc.d/rc6.d/K03postgresql

To ensure everything is all right:

signal-event post-upgrade
signal-event reboot


To test if you have set postgres password correctly:

su postgres
psql -U postgres -d template1

and enter the password you have set above. If all is correct, you should enter the postgres console. Then exit:

\q

and exit from postgres user:

exit