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

Debian/Ubuntu users please see this tutorial. To use Directadmin PostgreSQL plugin you will first need to ensure PostgreSQL server is installed as well as PostgreSQL support in PHP exists. See how you can install PostgreSQL 12 and build related PHP module. EPEL is another 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. Pure Red Hat users need to download and install centos-release-scl-rh RPM manually. Cloudlinux users may use cloudlinux-scl-release to get libpqxx-devel.

[ -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.

You can then proceed to plugin installation. Note the plugin requires your DirectAdmin server to be acessible over https.

AlmaLimux 8.5 + PosgtreSQL 14

In AlmaLimux 8.5 + PosgtreSQL 14 you can get:

# yum -y install postgresql14-server postgresql14-contrib
Last metadata expiration check: 0:20:39 ago on Mon 17 Jan 2022 12:34:19 PM UTC.
All matches were filtered out by modular filtering for argument: postgresql14-server
All matches were filtered out by modular filtering for argument: postgresql14-contrib
Error: Unable to find a match: postgresql14-server postgresql14-contrib

Then run

# dnf -y module disable postgresql
# dnf -y libpq-devel # this module may be not existing or already disabled