Knowledge Base

Can’t initialize PostgreSQL in Plesk: start service postgresql failed psql: could not connect to server:

Symptoms

Unable to initialize PostgreSQL database in Plesk:

Job for postgresql.service failed because the control process exited with error code. See "systemctl status postgresql.service" and "journalctl -xe" for details. ***** problem report ***** Warning: start service postgresql failed psql: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

Additional PostgreSQL errors

Checking PostgreSQL status

# systemctl status postgresql.service
● postgresql.service - PostgreSQL database server
Loaded: loaded (/usr/lib/systemd/system/postgresql.service; disabled; vendor preset: disabled)
Active: failed (Result: exit-code) since Fri 2020-03-27 22:56:17 WIB; 26s ago
Process: 9181 ExecStartPre=/usr/bin/postgresql-check-db-dir ${PGDATA} (code=exited, status=1/FAILURE)
Errors in /var/log/messages

systemd: Starting PostgreSQL database server...
postgresql-check-db-dir: "/var/lib/pgsql/data" is missing or empty.
postgresql-check-db-dir: Use "postgresql-setup initdb" to initialize the database cluster.
Manual initialization fails

# service postgresql initdb
Hint: the preferred way to do this is now "postgresql-setup initdb"
Initializing database ... failed, see /var/lib/pgsql/initdb.log
Message in /var/lib/pgsql/initdb.log

The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "C".
The default database encoding has accordingly been set to "SQL_ASCII".
The default text search configuration will be set to "english".
initdb: directory "/var/lib/pgsql/data" exists but is not empty
If you want to create a new database system, either remove or empty
the directory "/var/lib/pgsql/data" or run initdb
with an argument other than "/var/lib/pgsql/data".

Cause

Product issue PPPM-12145.

Resolution

Empty the Postgres data directory and initialize it manually.

Connect to the server over SSH
Empty /var/lib/pgsql/data

# rm -rf /var/lib/pgsql/data/*
Initialize PostgreSQL manually

# postgresql-setup initdb
Change the PostgreSQL password

# export PSA_PASSWORD=PASSWORD_HERE
# /usr/local/psa/admin/bin/pg_manage --create-admin-login admin
Log in to Plesk
Set the PostgreSQL password at Tools & Settings > Database Servers > PostgreSQL > localhost