Postgresql

There is now a smeserver-postgresql contrib in the contribs repo. It requires postgresql 13 from the postgresql repo.

Version

Contrib 10:

0.2.0-4

smeserver-postgresql

The latest version of smeserver-postgresql is available in the SME repository, click on the version number(s) for more information.

How to install Postgresql 13

db yum_repositories set postgresql13 repository \
  BaseURL https://download.postgresql.org/pub/repos/yum/13/redhat/rhel-7Server-\$basearch/ \
  EnableGroups no \
  GPGCheck yes \
  Name "Postgresql-13" \
  GPGKey https://download.postgresql.org/pub/repos/yum/RPM-GPG-KEY-PGDG-13 \
  Visible no \
  status disabled

Update yum

signal-event yum-modify

Install

yum --enablerepo=smecontribs,postgresql13 install smeserver-postgresql 
signal-event post-upgrade;signal-event reboot

How to install Postgres 8

	Warning:
	Tis information is now outdated/deprecated as we now use systemd on Koozali SME Server v10

	Note:
	This howto is created with information extracted from forums. Proceed at your own risk.

Warning:

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.That why you got an extra line on above box!

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

Testing your environment

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

References:

1. RE: How do I install postgreSQL 8+ by hedererjs
2. Resetting the postgres superuser password

How to Remove

rpm -e postgresql.i386 postgresql-contrib postgresql-devel postgresql-docs postgresql-pl postgresql-test  postgresql-server postgresqlclient7  postgresql-python

How to install Postgres 12 - old method

This is an old method and the systemd method below should be used

	Note:
	This howto is intended for use with SME Server 10, although it may work for other versions.

Postgres 12 is one of the latest versions available. The directions here may apply to other versions as well. The installation of version 12 uses RPMs directly from the Postgres support site. The links below come from https://yum.postgresql.org/12/redhat/rhel-7-x86_64/repoview/postgresqldbserver12.group.html but you can adjust the 12 in this link to a different version and get a different version's installation.

First, it is necessary to install the dependencies as well as the contrib RPM.

yum install https://yum.postgresql.org/12/redhat/rhel-7-x86_64/postgresql12-libs-12.5-1PGDG.rhel7.x86_64.rpm
yum install https://yum.postgresql.org/12/redhat/rhel-7-x86_64/postgresql12-12.5-1PGDG.rhel7.x86_64.rpm
yum install https://yum.postgresql.org/12/redhat/rhel-7-x86_64/postgresql12-contrib-12.5-1PGDG.rhel7.x86_64.rpm

Next, install the server portion.

yum install https://yum.postgresql.org/12/redhat/rhel-7-x86_64/postgresql12-server-12.5-1PGDG.rhel7.x86_64.rpm

Initialize the database.

/usr/pgsql-12/bin/postgresql-12-setup initdb

If you have a previously installed version of Postgres, you will need to stop the service for it before starting the service for the new version. Note that the new service name incorporates the version number. It can be started and enabled like this:

systemctl start postgresql-12
systemctl enable postgresql-12

Koozali SME v10 and systemd

	Note:
	There is now a contrib for Koozali SMEServer v10

db yum_repositories set postgresql12 repository \
  BaseURL https://download.postgresql.org/pub/repos/yum/13/redhat/rhel-7Server-\$basearch/ \
  EnableGroups no \
  GPGCheck yes \
  Name "Postgresql-12" \
  GPGKey https://download.postgresql.org/pub/repos/yum/RPM-GPG-KEY-PGDG-12 \
  Visible no \
  status disabled

Update yum

signal-event yum-modify

Install the rpms

yum --enablerepo=postgresql12 install postgresql12-server postgresql12-contrib

Set a config key for SME to recognise the service:

config set postgresql-12 service status enabled access private

We will need a file:

mkdir -p /usr/lib/systemd/system/postgresql-12.service.d
nano /usr/lib/systemd/system/postgresql-12.service.d/50koozali.conf

Add something like this:

[Unit]
After=network.target network.service wan.service
[Install]
WantedBy=sme-server.target

Reconfigure and reboot:

signal-event post-upgrade;signal-event reboot

The service will show as failed at this point.

systemctl status postgresql-12

So we need to init the DB:

/usr/pgsql-12/bin/postgresql-12-setup initdb

Then start it:

systemctl start postgresql-12

And now we can see it running.

We can check some of the defaults:

grep '^blank:*[^[:blank:]#;]' /var/lib/pgsql/12/data/pg_hba.conf
grep '^blank:*[^[:blank:]#;]' /var/lib/pgsql/12/data/postgresql.conf

---