how to update postgres.conf path after relocating it from data directory

Question

I'm installing a Postgresql-9.4 HA environment in RHEL 7, when I initialize postgres db - it creates a data directory and within the data directory goes postgresql.conf & pg_hba.conf files. Here, I anticipate the creation of config files in data directory creates a collision, as it gets overridden during the master replication.

So, I'm trying to relocate the postgres.conf and pg_hba.conf files from the data directory and make postgres service to use the relocated config files. Where do I updated the path of the config files?

Update: I've updated the PGDATA in /usr/lib/systemd/system/postgresql-9.4.service to point the relocated conf files. In the new postgres.conf file, updated data directory and hba_file paths. And I tried to restart the service, then it returns an error

Job for postgresql-9.4.service failed. See 'systemctl status postgresql-9.4.service' and 'journalctl -xn' for details.

Answer 1

Recently, I faced up the same problem. And the true problem is how to tell the postmaster its real configuration file location. All we have to do is to add -c config_file <configuration/filename/location> as commandline option to postgres process. And issue is: how to do that...?

Long time ago, in a galaxy far, far away, we had System V initscripts and we could configure command line options within certain file located somewhere in; /etc/sysconfig dir. But now, we have systemd - robust, advanced and... hard configurable machinery to do all and even more. So, the only way to split configuration files and data files is to configure postgresql systemd init system. How to achieve this? This is what I have done:

1. First of all I moved configuration files:postgresql.conf, pg_hba.conf ang pg_ident.conf into external directory, let's say: /var/lib/pgsql/config

2. I configured new location of above files in postgresql.conf:

    hba_file = '/var/lib/pgsql/config/pg_hba.conf'
    ident_file = '/var/lib/pgsql/config/pg_ident.conf'
   

3. I edited systemd postgresql.service by overriding existing (system) configuration:

   systemctl edit postgresql.service
   

   This causes creation of new, empty systemd service file: /etc/systemd/system/postgresql.service.d/override.conf and opens it in $EDITOR editor.

4. I put there:

   [Service]
   ExecStart=
   ExecStart=/usr/bin/pg_ctl start -D ${PGDATA} -s -o "-p ${PGPORT} -c config_file=/var/lib/pgsql/config/postgresql.conf" -w -t 300
   

   As we see - I added: "-c config_file /var/lib/pgsql/config/postgresql.conf" string into -o argument. Another interesting point is empty: "ExecStart" directive. Though it's called: "overriding", we have to first of all zeroed this directive, otherwise, we will have error: "postgresql.service has more than one ExecStart= setting...".

5. Restarted service: systemctl restart postgresql

   And I enjoyed the Postgresql with data files in $PGDATA and configuration files, everywhere else:

   /usr/bin/postgres -D /var/lib/pgsql/data -p 5432 -c config_file=/var/lib/pgsql/config/postgresql.conf
   

I have made this with CentOS 7 and RHEL7, but hope it should be working in any systemd based OS.

Hope, this story will be helpful for someone.

Regards.

Answer 2

I've found this page in Postgresql official website, in detail :

If you wish to keep the configuration files elsewhere than the data directory, the postgres -D command-line option or PGDATA environment variable must point to the directory containing the configuration files, and the data_directory parameter must be set in postgresql.conf (or on the command line) to show where the data directory is actually located. Notice that data_directory overrides -D and PGDATA for the location of the data directory, but not for the location of the configuration files.

If you wish, you can specify the configuration file names and locations individually using the parameters config_file, hba_file and/or ident_file. config_file can only be specified on the postgres command line, but the others can be set within the main configuration file. If all three parameters plus data_directory are explicitly set, then it is not necessary to specify -D or PGDATA.

When setting any of these parameters, a relative path will be interpreted with respect to the directory in which postgres is started.

What's the output of echo $PGDATA on your host?