postgres: is not a valid data directory

Question

I am trying to bring up a postgres server where the configuration files and the data are in separate places/partitions

postgresql-10 has been installed using the official repository using YUM in Centos 7

And it has been initialised using

/usr/pgsql-10/bin/pg_ctl initdb --pgdata=/var/lib/pgsql/10/data

as user app

What is referenced as PGDATA (configuration) directory is the default one:

# ls -salt /var/lib/pgsql/10/data
total 120
 4 drwxrwxr-x. 19 app app 4096 Nov 10 17:29 .

I would like to use the following directory for data storage:

# ls -salt /usr/ip-spotlight/postgresql/
total 12
4 drwxrwxr-x. 3 postgres postgres 4096 Nov 10 17:43 .
4 drwx------. 2 postgres postgres 4096 Nov 10 17:43 data

And it complains about:

2017年11月10日 17:51:44.495 CET [81743] FATAL: "/usr/ip-spotlight/postgresql/data" is not a valid data directory
2017年11月10日 17:51:44.495 CET [81743] DETAIL: File "/usr/ip-spotlight/postgresql/data/PG_VERSION" is missing.

On the other hand, in https://www.postgresql.org/docs/current/static/runtime-config-file-locations.html it says:

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.

My /var/lib/pgsql/10/data/postgresql.conf is configured as:

data_directory = '/usr/ip-spotlight/postgresql/data'
hba_file = '/var/lib/pgsql/10/data/pg_hba.conf'
ident_file = '/var/lib/pgsql/10/data/pg_ident.conf'

Could you please advise how to separate the config files of postgresql and the actual data storage ?

Answer 1

OK, i figured it out:

1. initialise the db as user postgres

   name: "Create a new PostgreSQL database cluster"
   become: postgres
   command: "/usr/pgsql-{{ postgres_version.major }}/bin/pg_ctl initdb --pgdata={{ postgres_data }}"
   ignore_errors: yes
   

2. override PGDATA env variable through systemd

   name: "Override PGDATA pathname (1/3)"
   become: yes
   file: path="/etc/systemd/system/postgresql-{{ postgres_version.major }}.service" state=touch owner=root group=root mode=0644
   when: dev.platform == "baremetal"
   

   name: "Override PGDATA pathname (2/3)"
   become: yes
   lineinfile: path="/etc/systemd/system/postgresql-{{ postgres_version.major }}.service" line=".include /lib/systemd/system/postgresql-{{ postgres_version.major }}.service" state=present
   when: dev.platform == "baremetal"
   

   name: "Override PGDATA pathname (3/3)"
   become: yes
   blockinfile:
    path: "/etc/systemd/system/postgresql-{{ postgres_version.major }}.service"
    state: present
    block: |
    [Service]
    Environment=PGDATA={{ postgres_data }}
    when: dev.platform == "baremetal"
    notify:
    reload-systemd