How to install and enable PostgreSQL database server in Plesk

Question

How to install and enable PostgreSQL database server in Plesk?

Answer

Note: PostgreSQL management is only available in Web Pro and Web Host licenses. To manage PostgreSQL via Plesk with Web Admin license it is required to purchase Power Pack / Developer Pack add-on.

1. Install PostgreSQL database server using Plesk Installer according to this KB article.

2. Log in to Plesk.

3. Go to Tools & Settings > Database Servers and click localhost next to PostgreSQL.

   

4. Specify username and password which will be administrative for PostgreSQL and click OK to initialize PostgreSQL:

   

In case customer wants to replace the current PostgreSQL with a newer version (14 in the example below):

Note: - This configuration is not officially supported, all actions are done at your own risk.
- Stable work of such configuration is not guaranteed and we cannot predict if there will be any issues in the future.
- These instructions are applicable to RHEL7 and its forks (including CloudLinux 7) only.
- Before moving forward I strongly recommend you to create a full server snapshot.

In snippets below, # symbol means that the command should be executed under "root" user and $ symbol means that it should be executed under "postgres" user.

First it is needed to install another instance of PostgreSQL:

1. Install the repository RPM:

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

2. Install PostgreSQL:

# yum install -y postgresql14-server

3. (Optionally) Install contrib package if corresponding extensions are used:

# yum install -y postgresql14-contrib

Next initialize and migrate databases:

1. Stop PostgreSQL 9:

# systemctl stop postgresql.service

2. Set locale to avoid possible locale issue:

# export LC_ALL="en_US.UTF-8"

3. Initialise PostgreSQL 14:

# su postgres
$ cd ~/
$ /usr/pgsql-14/bin/initdb -D /var/lib/pgsql/14/data
$ exit

4. Allow socket connections without password authentication. Edit /var/lib/pgsql/data/pg_hba.conf, replacing password to trust on the first line and ident and peer to trust at the bottom of the file as follows:

CONFIG_TEXT: local samegroup all trust #Added by Plesk
...
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 trust

5. Workaround Fedora bug by modifying pg_ctl binary:

# mv /usr/bin/pg_ctl{,-orig}

# echo '#!/bin/bash' > /usr/bin/pg_ctl

# echo '"$0"-orig "${@/unix_socket_directory/unix_socket_directories}"' >> \
/usr/bin/pg_ctl

# chmod +x /usr/bin/pg_ctl

6. Migrate databases:

# su postgres
$ cd ~/
$ /usr/pgsql-14/bin/pg_upgrade --old-datadir /var/lib/pgsql/data/ --new-datadir /var/lib/pgsql/14/data/ --old-bindir /usr/bin/ --new-bindir /usr/pgsql-14/bin/
$ exit

7. (Optional). In case of any configs customizations modify new server configs.
Old: /var/lib/pgsql/data/pg_hba.conf New: /var/lib/pgsql/14/data/pg_hba.conf
Old: /var/lib/pgsql/data/postgresql.conf New: /var/lib/pgsql/14/data/postgresql.conf

8. Start PostgreSQL 14 server:

# systemctl start postgresql-14.service

9. Analyse and optimise new cluster:

# su postgres
$ cd ~/
$ /usr/pgsql-14/bin/vacuumdb --all --analyze-in-stages

10. (Optional). Remove old cluster:

$ ./delete_old_cluster.sh
$ exit

11. Disable old service and enable autostart for a new one:

# systemctl disable postgresql.service

# systemctl enable postgresql-14.service

12. Make new service manageable by Plesk:

# rm /usr/lib/systemd/system/postgresql.service

# ln -s /usr/lib/systemd/system/postgresql-14.service /usr/lib/systemd/system/postgresql.service

13. Refresh data in Plesk:

Under Plesk > Tools & Settings > Database Servers click "Refresh" button

These steps successfully worked on vanilla Plesk Obsidian 18.0.52.