Custom PostgreSQL database location is ignored

[GuillaumeHold]

Configuring a custom database location with postgresql_data_dir is ignored since the value is hardcoded in the service file /usr/lib/systemd/system/postgresql-12.service with Environment=PGDATA=/var/lib/pgsql/12/data/.

The service file should be templated.

[kpgtek]

OK, I will check a solution to modify the value of the data directory hardcoded in the service file. The service file is provided by the RPM package and its content could depend on the version of the RDBMS.

[rpignolet]

I think we should avoid modify /usr/lib/systemd/system/postgresql-12.service which is provided by PostgreSQL RPM. Maybe it is possible to override only PGDATA variable using a file in /etc or something else.

[kpgtek]

Yes I think it is better not to modify /usr/lib/systemd/system/postgresql-{{ version }} containing default systemd configuration for PostgreSQL, it could be used for templating to create new systemd file with specific configuration (for example PGDATA modified).

Generally the default PGDATA path is /var/lib/pgsql/{{ version }}/data/, this allows to avoid to reconfigure SELINUX context if status is enforcing, and to manager major version migration with mimimum downtime.

[kpgtek]

My recommandation is to keep the default values for use:

postgresql_service: "postgresql-{{ postgresql_version }}"
postgresql_common_root_dir: /var/lib/pgsql
postgresql_root_dir: "{{ postgresql_common_root_dir }}/{{ postgresql_version }}"
postgresql_data_dir: "{{ postgresql_root_dir }}/data"

``postgresql-{{ postgresql_version }}systemd file and/var/lib/pgsql/{{ postgresql_version }}/data``` are provided by PostgreSQL RPM packages.

Otherwise if we would like to custom {{ postgresql_data_dir }}, we would need to create a new customed {{ postgresql_service }} systemd file.

[GuillaumeHold]

From the documentation:

"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 ."

So the data folder can be configured with postgresql.conf. However, if we don't give to the command the parameters config_file, hba_file and/or ident_file to the custom location, it will look in PGDATA .

[kpgtek]

When you stop or start an PostgreSQL instance, you need to know the data_directory or PGDATA path to identify which instance to stop or start. That's why it is put in the systemd /usr/lib/systemd/system/postgresql-{{ version }}.service file. If you don't need to start/stop the instance on boot/shutdown, the systemd file can be disable and not used, specially in high availability configuration, the instances should be started/stopped manually.