Plesk

Unable to initialize PostgreSQL database in Plesk: start service postgresql failed psql: could not connect to server:

Symptoms

Errors below can be found when checking PostgreSQL status:

  • Checking PostgreSQL status shows the following:

    # 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)

  • There are the following records in /var/log/messages file:

    Mar 27 22:56:17 localhost systemd: Starting PostgreSQL database server...
    Mar 27 22:56:17 localhost postgresql-check-db-dir: "/var/lib/pgsql/data" is missing or empty.
    Mar 27 22:56:17 localhost postgresql-check-db-dir: Use "postgresql-setup initdb" to initialize the database cluster.

  • Manual initialization fails with:

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

  • In /var/lib/pgsql/initdb.log file:

    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

Plesk Bug ID #PPPM-12145.

Resolution

As a workaround:

  1. Connect to the server via SSH

  2. Remove files from the /var/lib/pgsql/data folder:

    # rm -fr /var/lib/pgsql/data/*

  3. Initialize PostgreSQL manually via the command below:

    # postgresql-setup initdb

  4. Change the Password for PostgreSQL:

    # export PSA_PASSWORD=PASSWORD_HERE
    # /usr/local/psa/admin/bin/pg_manage --create-admin-login admin

  5. Log in to Plesk.

  6. Set the password for PostgreSQL under Tools & Settings > Database Servers > PostgreSQL > localhost.