psql gives Invalid data directory error

Question

I have VM with ubuntu 18.04 server installed. I have installed postgres 10 from the repo.

Default installation. I made some connection count related changes in postgres.conf and connection security related changes in pg_hba.conf.

The install directories are all the default, including the data directory.

Server is running. I was able to create databases and access the same from java client.

But when I start psql from the same VM, it throws the following error.

Error: Invalid data directory

Just psql without any parameters also gives this error.

People have faced this error when they have tried to change the data directory which is not the case for me. Data directory is the default and it is owned by the postgres user.

ubuntu@ubuntu:~$ ls -l /var/lib/postgresql/10/
total 4
drwx------ 20 postgres postgres 4096 Jun 12 02:54 main

Any idea what could be the cause or where should I look for detailed error for psql client?

Answer 1

Error: Invalid data directory

This happens when pg_wrapper is unable to figure out the data directory from the configuration. pg_wrapper is the layer on top of postgres that can juggle between several live PostgreSQL instances on debian-based systems. As installed by the postgresql-client package, psql is a link to pg_wrapper on such systems:

/usr/bin/psql -> ../share/postgresql-common/pg_wrapper

Since you mention slightly modifying postgresql.conf, make sure you didn't change the permissions inside /etc/postgresql/, including postgresql.conf. That looks like the most plausible change that would immediately lead to the mentioned error. The next one would be that your changes are syntactically wrong, in a way that pg_wrapper would no longer be able to parse the file.

Answer 2

This happened to me when i logged in as root and used sed to change postgresql.conf, which resulted in that new conf file being owned by root instead of postgres. postgresql.conf and everything else in the director should have the following permissions:

total 92
drwxr-xr-x 3 postgres postgres  4096 Apr  5 18:56 .
drwxr-xr-x 3 postgres postgres  4096 Apr  5 18:11 ..
drwxr-xr-x 2 postgres postgres  4096 Apr  5 18:11 conf.d
-rw-r--r-- 1 postgres postgres   315 Apr  5 18:11 environment
-rw-r--r-- 1 postgres postgres   143 Apr  5 18:11 pg_ctl.conf
-rw-r----- 1 postgres postgres  4933 Apr  5 18:11 pg_hba.conf
-rw-r----- 1 postgres postgres  1636 Apr  5 18:11 pg_ident.conf
-rw-r----- 1 postgres postgres 26889 Apr  5 18:55 postgresql.conf
-rw-r--r-- 1 postgres postgres   317 Apr  5 18:11 start.conf

Answer 3

I found the source of the issue thanks to @x-yuri comment above to the source code.

It is all to do with this block of code:

# Read a 'var = value' style configuration file from a cluster configuration
# directory (with /etc/postgresql-common/<file name> as fallback) and return a
# hash with the values. Error out if the file cannot be read.
# Arguments: <version> <cluster> <config file name>
# Returns: hash (empty if the file does not exist)
sub read_cluster_conf_file {
    my $fname = "$confroot/$_[0]/$_[1]/$_[2]";
    -e $fname or $fname = "$common_confdir/$_[2]";
    my %conf = read_conf_file $fname;

    if ($_[0] >= 9.4 and $_[2] eq 'postgresql.conf') { # merge settings changed by ALTER SYSTEM
        # data_directory cannot be changed by ALTER SYSTEM
        *my $data_directory = $conf{data_directory} || "/var/lib/postgresql/$_[0]/$_[1]";*
        my %auto_conf = read_conf_file "$data_directory/postgresql.auto.conf";
        foreach my $guc (keys %auto_conf) {
            $conf{$guc} = $auto_conf{$guc};
        }
    }

    return %conf;
}

If your configuration file directory has this format: /etc/postgresql/15/main/postgresql.conf

It will use the subdirectories to construct a non-existent data directory!

If you add your real data directory to your postgresql.conf, the error should disappear! (it did for me)

Then when I changed my configuration file direction to this format: /etc/postgresql/postgresql.conf The error also disappeared!

Some context- I am working with postgres:15 as a docker image and was facing Error: Invalid data directory for cluster 15 main when I manually updated my config file location to above.

Many thanks for your help x-yuri!