Published on Jul 2 2020 in Control Panels Databases Non-Java

To use Directadmin PostgreSQL plugin you will first need to ensure PostgreSQL server is installed as well as PostrgreSQL support in PHP exists. See how you can install PostgreSQL 12 and build related PHP module. EPEL is prerequisite so start with:

yum -y install epel-release yum-utils

Ensure you have latest pgdg repo to avoid broken dependencies:

yum remove pgdg-redhat-repo
yum -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

Default used to be all repos enabled including pgdg-common so let’s disable uneeded ones:

yum-config-manager --disable pgdg11 pgdg10 pgdg96 pgdg95
yum -y install postgresql12-server postgresql12-contrib
#dnf module enable postgresql:12 # centos8 
#dnf install postgresql-server postgresql12-contrib libpq-devel # centos8 

You may also need to install database extensions if any of the databases to be restored used them. For example to install postgis you would run yum install postgis30_12. Let’s now find where psql resides, init DB server and start it.

readlink -f `which psql` 
/usr/pgsql-12/bin/postgresql-12-setup initdb
#postgresql-setup initdb # centos8
systemctl enable --now postgresql-12
#systemctl enable --now postgresql # centos8
su - postgres -c 'psql -c "SELECT version()"'
su - postgres -c 'psql -tA -c "SHOW data_directory;"'

Replace ident with md5 in pg_hba.conf

sed -i 's/ident$/md5/' /var/lib/pgsql/12/data/pg_hba.conf
systemctl reload postgresql-12
#sed -i 's/ident$/md5/' /var/lib/pgsql/data/pg_hba.conf # centos8
#systemctl reload postgresql # centos8

You may also use su - postgres -c '/usr/pgsql-12/bin/pg_ctl reload' to reload DB server.

Enabling PostgreSQL support in default PHP

This is not needed for the plugin itself but for users who may need to access their PostgreSQL databases from PHP. Below yum command section is not needed for CentOS 8 as we already have libpq-devel installed.

[ -f `/usr/pgsql-12/bin/pg_config --includedir`/libpq-fe.h ] && echo "devel installed" || echo "devel missing"
# scl-rh for llvm-toolset-7-clang 4.0.1
yum -y install centos-release-scl-rh 
yum-config-manager --disable centos-sclo-rh
yum -y install postgresql12-devel --enablerepo=centos-sclo-rh

Now check default PHP version and copy configure.phpXX of the version to custom directory. If you already have it there please add this line --with-pgsql=/usr/pgsql-12 --with-pdo-pgsql=/usr/pgsql-12 to the file. In our case default PHP uses configure.php73 and the customized file is not existing so we copy it.

cd /usr/local/directadmin/custombuild
./build used_configs | grep 'PHP (default) configuration' | awk '{print $NF}'
/usr/local/directadmin/custombuild/configure/php/configure.php73
mkdir -p /usr/local/directadmin/custombuild/custom/php
\cp -fp /usr/local/directadmin/custombuild/configure/php/configure.php73 /usr/local/directadmin/custombuild/custom/php

Add your --with-pgsql= --with-pdo-pgsql= line to custom/php/configure.php73. In this case ‘include’ directory is in /usr/pgsql-12 so we use this value. To know where is include run dirname $(/usr/pgsql-12/bin/pg_config --includedir).

perl -0777 -i.bak -pe 's|^(\./configure \\)$(?!\s*--with-pgsql)|$1\n\t--with-pgsql=/usr/pgsql-12 --with-pdo-pgsql=/usr/pgsql-12 \\|ms' /usr/local/directadmin/custombuild/custom/php/configure.php73
cd /usr/local/directadmin/custombuild
./build php d

For CentOS 8 the line in configure.php73 should be --with-pgsql=/usr --with-pdo-pgsql=/usr.

Similarly you can add PostgreSQL support to other PHP versions.

Facilitate your work with PostgreSQL

As a server admin you may want to have faster access to PostgreSQL client. While we need postgres user peer authentication to work for the DirectAdmin PostgreSQL plugin you may prefer to manage PostgreSQL as root.

su - postgres -c 'createuser -d -s -r root; createdb -O root root'

Now we can simply use psql from root account.