An attempt to connect a PostgreSQL instance in Plesk produces an Internal Server Error: Configuration in /etc/psa/psa.conf is malformed

Daniel Yordanov

Updated November 11, 2024 17:20

kb: bug Plesk for Linux

Applicable to:

The server OS was updated by using dist-upgrade
During an attempt to connect the PostgreSQL instance via Tools & Settings > Database Servers with hostname localhost and any login and password, the process fails and Plesk produces an Internal Server error
PostgreSQL variables in the /etc/psa/psa.conf are malformed and are similar to the following:

PGSQL_DATA_D /var/lib/postgresql/11
16/main
main
PGSQL_CONF_D /etc/postgresql/11
16/main
main
PGSQL_BIN_D /usr/lib/postgresql/11
16/bin
The configuration file /etc/psa/psa.conf got malformed further every time /opt/psa/admin/bin/pg_manage --status ran
There are no running PostgreSQL instances:

# /usr/bin/pg_lsclusters -h
11 main 5432 down postgres /var/lib/postgresql/11/main /var/log/postgresql/postgresql-11-main.log
16 main 5432 online postgres /var/lib/postgresql/16/main /var/log/postgresql/postgresql-16-main.log

This behavior is tied to a known bug with ID PPPM-13771, which will be resolved in future versions of Plesk.

Until the bug is fixed permanently, you may apply the following workaround:

Workaround

Connect to the server via SSH
Check which PostgreSQL instance is down

# /usr/bin/pg_lsclusters -h
11 main 5432 down postgres /var/lib/postgresql/11/main /var/log/postgresql/postgresql-11-main.log
16 main 5432 online postgres /var/lib/postgresql/16/main /var/log/postgresql/postgresql-16-main.log

Note: In this case, the instance 11 is down.
Create a backup folder to store the instance that is down

# mkdir ~/pgsql_etc_bkp
Move the instance that is down to the backup folder

# mv /etc/postgresql/11 ~/pgsql_etc_bkp/
Correct the PostgreSQL configuration in /etc/psa/psa.conf by using the following example:

# PostgreSQL
PGSQL_DATA_D /var/lib/postgresql/16/main
PGSQL_CONF_D /etc/postgresql/16/main
PGSQL_BIN_D /usr/lib/postgresql/16/bin

Note: Where 16 is the online instance in this case.
Reread the PostgreSQL configuration by executing the following command:

# plesk sbin pg_manage --check-installed