PostgreSQL 11.9 Unable to restart the server with error -- pg_ctl: directory "/var/lib/postgresql/11/main" is not a database cluster directory

Question

We have been trying hard to restart the DB cluster server which was shutdown after power failure. We have tried systemctl , pg_ctl and pg_ctlcluster commands but all the time we are getting same error.

directory "/var/lib/postgresql/11/main" is not a database cluster directory

While in our configuration file (postgresql.conf) same path has been mentioned:

data_directory = '/var/lib/postgresql/11/main'

I was not able to understand why we are getting this error even the server was running with same configuration before power shutdown.

This is our production server.

The command line I am feeding to those commands is:

• pg_ctl : /usr/lib/postgresql/11/bin/pg_ctl start -D /var/lib/postgresql/11/main
• pg_ctlcluster : /usr/bin/pg_ctlcluster --skip-systemctl-redirect 11-main start (this is the same command mentioned under the ExecStart parameter of [email protected])
• systemctl : systemctl status [email protected]

Answer 1

directory "/var/lib/postgresql/11/main" is not a database cluster directory

It means that this directory does not contain a PG_VERSION file, or it's somehow unreadable.

This file contains one line of text with the major version of PostgreSQL that corresponds to this data directory (11 in your case).

This might imply that the data directory has been damaged somehow, following the power failure, or is empty, because it's a dedicated partition that is not mounted. In the best case, it can be fixed manually and the service restarted with the normal procedure. In the worst case, you need to restore from a backup. See also https://wiki.postgresql.org/wiki/Corruption for some generic advice.

Answer 2

Since your postgresql.conf has the parameter data_directory set, it seems likely that postgresql.conf is in a directory different from the data directory.

When starting the database, the argument to the -D option is not the data directory, but the directory that contains postgresql.conf.

So, assuming that you are following the normal Debian/Ubuntu layout, it might be

pg_ctl -D /etc/postgresql/11/main start

• AKBAR ALI: Thanks for your response /etc/postgresql/11/main is mentioned as the config directory where postgresql.conf , pg_hba.conf and other conf files has been placed
• Anatoly Alekseev: Wery weird. Docs postgresql.org/docs/current/app-pg-ctl.html name D parmeter as datadir, and to stop the instance datadir works well: /usr/lib/postgresql/15/bin/pg_ctl stop -D /var/lib/postgresql/15/main. But to START the instance, another directory must be used, indeed, as you say: /usr/lib/postgresql/15/bin/pg_ctl start -D /etc/postgresql/15/main/. Otherwise it's an error.

Answer 3

I found this answer on -> Ask Ubuntu

Here it is: And it worked for me.

Begin:

In my case this was related to incorrectly configured locales.

I've found the solution in this dba.stackexchange.com answer:

1. Use sudo dpkg-reconfigure locales to generate the necessary locales
2. Drop the existing database cluster via sudo pg_dropcluster 9.5 main (this will erase all the data in the cluster!)
3. Re-create the cluster via sudo pg_createcluster 9.5 main --start
4. Restart PostgreSQL via sudo service postgresql restart

This solution worked for me. I have postgresql 11-main so adjust to your version.