Initial postgresql.conf file not managed

[jschaeff]

Hi,

I try to use this nice formula to deploy a postgres server, but at a non default directory. I put data_dir and conf_dir at "/data/postgres".
The formula creates this directory but then it ignores it for the rest of the configuration, resulting in :

• systemd file not pointing to the right PGDATA
• postgresql.conf not present in the conf_dir

My postgresql server is on CentOS7. I made a local workaround but it's not playing nice with the whole formula.
The documentation lacks some explanations as how to use conf_dir and data_dir effectively.

Thanks for your help !

[myii]

@jschaeff Thanks for your bug report. I've tested on a docker-based CentOS 7 minion and can reproduce at least part of what you've mentioned.

So using a basic pillar containing:

postgres:
  use_upstream_repo: False
  conf_dir: /data/postgres
  data_dir: /data/postgres

I can reproduce the systemd problem in /usr/lib/systemd/system/postgresql.service:

...
# Location of database directory
Environment=PGDATA=/var/lib/pgsql/data
...

However, I cannot reproduce the postgresql.conf problem:

# ll /data/postgres/
total 133
-rw------- 1 postgres root         4 Apr 19 23:20 PG_VERSION
drwx------ 5 postgres root         5 Apr 19 23:20 base
drwx------ 2 postgres root        42 Apr 19 23:20 global
drwx------ 2 postgres root         3 Apr 19 23:20 pg_clog
-rw------- 1 postgres postgres  1431 Apr 19 23:20 pg_hba.conf
-rw------- 1 postgres root      4476 Apr 19 23:20 pg_hba.conf.bak
-rw------- 1 postgres postgres  1636 Apr 19 23:20 pg_ident.conf
drwx------ 4 postgres root         4 Apr 19 23:20 pg_multixact
drwx------ 2 postgres root         3 Apr 19 23:20 pg_notify
drwx------ 2 postgres root         2 Apr 19 23:20 pg_serial
drwx------ 2 postgres root         2 Apr 19 23:20 pg_snapshots
drwx------ 2 postgres root         2 Apr 19 23:20 pg_stat_tmp
drwx------ 2 postgres root         3 Apr 19 23:20 pg_subtrans
drwx------ 2 postgres root         2 Apr 19 23:20 pg_tblspc
drwx------ 2 postgres root         2 Apr 19 23:20 pg_twophase
drwx------ 3 postgres root         4 Apr 19 23:20 pg_xlog
-rw------- 1 postgres root     19805 Apr 19 23:20 postgresql.conf

@jschaeff Could you share your specifics, such as the pillar file you are using? Please remember to remove any sensitive data first.

---

@vutny In terms of the fixing the systemd job, we could do one of the following:

1. Add an override file under /etc/systemd/system/postgresql.service.
2. Use a "nice" file.replace hack directly on /usr/lib/systemd/system/postgresql.service.

What do you think?

[myii]

Unfortunately, things get uglier with an Ubuntu minion when trying to supply custom values for conf_dir and data_dir:

          ID: postgresql-cluster-prepared
    Function: cmd.run
        Name: pg_createcluster 10 main -d /data/postgres
      Result: False
     ...
     Changes:   
              ----------
              pid:
                  2736
              retcode:
                  1
              stderr:
                  Error: cluster configuration already exists
              stdout:

Since the default cluster location is used as part of the package installation process:

# pg_lsclusters
Ver Cluster Port Status Owner    Data directory              Log file
10  main    5432 down   postgres /var/lib/postgresql/10/main /var/log/postgresql/postgresql-10-main.log

We'd need to find a way of dealing with this cluster first.

[vutny]

@myii Regarding the suitable fix for SystemD unit on RedHats, I prefer putting only the changed string into
/usr/lib/systemd/system/postgresql.service.d/10-pgdata.conf.

[myii]

@vutny This is an excerpt of what I find at the top of /usr/lib/systemd/system/postgresql.service:

# It's not recommended to modify this file in-place, because it will be
# overwritten during package upgrades.  If you want to customize, the
# best way is to create a file "/etc/systemd/system/postgresql.service",
# containing
#       .include /lib/systemd/system/postgresql.service
#       ...make your changes here...
# ...

# For example, if you want to change the server's port number to 5433,
# create a file named "/etc/systemd/system/postgresql.service" containing:
#       .include /lib/systemd/system/postgresql.service
#       [Service]
#       Environment=PGPORT=5433
# This will override the setting appearing below.

# Note: changing PGPORT or PGDATA will typically require adjusting SELinux
# configuration as well; see /usr/share/doc/postgresql-*/README.rpm-dist.

• States the override file should be /etc/systemd/system/postgresql.service.
• Also need to adjust the SELinux configuration -- does the formula already handle this?

[vutny]

That is just another way of how you can do it in Systemd. It's fine if PostgreSQL package recommends such approach. Just need to manage it from a single place and run systemctl daemon-reload on change.

Regarding SELinux, we have some code covering policy updates for tablespaces. I guess that need to be abstracted to cover PGDATA as well.

[myii]

@vutny Looks like we've got deeper problems with CentOS 7, even without these customisations. Running postgres.server ends in failure:

          ID: postgresql-running
    Function: service.running
        Name: postgresql
      Result: False
     Comment: State 'service.running' was not found in SLS 'postgres.server'
              Reason: 'service' __virtual__ returned False: No service execution module loaded: check support for service management on CentOS Linux-7

[vutny]

@myii Are you testing in Docker? Since there is no systemd running as init, this is legitimate.

[myii]

@vutny You got me there. The question now is how is this going to work with the new CI testing matrix? Simply disable for centos-7?

[vutny]

😸 There is a special Pillar setting called bake_image.
I have specifically implemented it to be able to create custom container/VM images. You may try that, but it wasn't tested for a long time.

[myii]

@vutny Thanks for that. I'm speaking with @javierbertoli in our Slack/IRC/Matrix room right now and he's got some suggestions. Do join us when you get a chance (even once in a while), it really helps work things out collaboratively.

[vutny]

I got used to https://gitter.im/. It is so easy to create a room per repo or an organization and you don't need anything else than GitHub OAuth. I've invited you there. Probably the community would consider it as an alternative channel for conversations around specific repositories.

[vutny]

JFYI folks: https://gitter.im/postgres-formula/community