Postgresql – Setting PostgreSQL data_directory in postgresql.conf not working

centospostgresqlpostgresql-9.3

I'm attempting to configure PostgreSQL 9.3 on a Centos 6.6 box to keep the configuration files (postgresql.conf, pg_hba.conf, pg_ident.conf) in a different directory than the data directory. According to the documentation, I should be able to set the PGDATA variable to the directory that contains postgresql.conf, and then specify the data_directory from within postgresql.conf to point to the directory that contains my data.

I installed PostgreSQL 9.3 via these instructions.

Steps I followed to configure Postgres (/data is a different partition from the OS):

  1. mkdir -p /data/pgsql/data && mkdir /data/pgsql/config && sudo chown -R postgres:postgres /data/pgsql
  2. sudo su postgres -c '/usr/pgsql-9.3/bin/initdb -D /data/pgsql/data'
  3. sudo mv /data/pgsql/data/postgresql.conf /data/pgsql/config/ && sudo mv /data/pgsql/data/pg_hba.conf /data/pgsql/config/ && sudo mv /data/pgsql/data/pg_ident.conf /data/pgsql/config/
  4. Edit /data/pgsql/config/postgresql.conf, making sure these variables are set as so: 
    hba_file = '/data/pgsql/config/pg_hba.conf'`
ident_file = '/data/pgsql/config/pg_ident.conf'
data_directory = '/data/pgsql/data'
  5. sudo sh -c 'echo "export PGDATA=/data/pgsql/config" > /etc/sysconfig/pgsql/postgresql-9.3'
  6. sudo service postgresql-9.3 start

This results in the error message:

/data/pgsql/config is missing. Use "service postgresql-9.3 initdb" to initialize the cluster first.

I looked at the init script (/etc/init.d/postgresql-9.3) and noticed this:

# Check for the PGDATA structure
if [ -f "$PGDATA/PG_VERSION" ] && [ -d "$PGDATA/base" ]
then
    ...
else
    # No existing PGDATA! Warn the user to initdb it.

    echo
    echo "$PGDATA is missing. Use \"service $NAME initdb\" to initialize the cluster first."
    echo_failure
    echo
    exit 1
fi

I added an echo and PGDATA is set to /data/pgsql/config, which I would expect, but I'm not sure how setting PGDATA and data_directory could even work, because the init script isn't checking postgresql.conf to get the data_directory like the documentation says it should. Am I doing something wrong? Is the documentation wrong?

Best Answer

You are not doing anything wrong per se, and the documentation is not wrong. But the initscript from CentOS doesn't support you doing things this way, so you have to change the initscripts to not check for these files, since PostgreSQL will check for it itself and just try to start the database even if these files are not there.

So to test it out, comment out the if, then and the whole else block and just let it run the ... part of the script. PostgreSQL should read the settings correctly and start itself without a problem. And you might of course inform the people responsible for the CentOS package that they should do it differently. If it doesn't run, you should get a reasonable error message from PostgreSQL itself.

Related Question