search

puppetlabs / puppetlabs-postgresql

Puppet module for managing PostgreSQL
Apache License 2.0
228 stars 614 forks source link

Fix instance reload #1588

Closed SimonHoenscheid closed 6 months ago

SimonHoenscheid commented 7 months ago

Summary

changes in additional instaces currently trigger a restart of the main/default instance

Checklist

SimonHoenscheid commented 6 months ago

I tested on a VM with:

[ins_test1@el8 ~]$ puppet facts os
{
  "os": {
    "architecture": "x86_64",
    "distro": {
      "description": "CentOS Stream release 8",
      "id": "CentOSStream",
      "release": {
        "full": "8",
        "major": "8"
      }
    },
    "family": "RedHat",
    "hardware": "x86_64",
    "name": "CentOS",
    "release": {
      "full": "8",
      "major": "8"
    },
    "selinux": {
      "config_mode": "permissive",
      "config_policy": "targeted",
      "current_mode": "permissive",
      "enabled": true,
      "enforced": false,
      "policy_version": "33"
    }
  }
}

The following cases are WORKING:

reload pg_hba.conf change:

[root@el8 puppet]# diff -u ../common.yaml data/common.yaml
--- ../common.yaml  2024-04-15 20:40:41.555885160 +0000
+++ data/common.yaml    2024-04-15 20:36:43.909352569 +0000
@@ -102,10 +102,11 @@
         auth_method: "peer"
         order: 3
       "local all READONLY user":
-        type: "local"
+        type: "host"
         database: "all"
         user: "rou_test1"
-        auth_method: "peer"
+        address: "192.168.22.131/32"
+        auth_method: "md5"
         order: 4
       "remote all INSTANCE user PGADMIN server":
         type: "host"

[root@el8 puppet]# puppet apply --hiera_config /root/puppet/hiera.yaml --modulepath=/root/puppet/modules/ /root/puppet/manifests/site.pp
Notice: Compiled catalog for el8 in environment production in 0.59 seconds
Notice: /Stage[main]/Profiles::Postgres/Postgresql::Server_instance[test1]/Postgresql::Server::Instance::Config[test1]/Concat[/opt/pgsql/data/13/test1/pg_hba.conf]/File[/opt/pgsql/data/13/test1/pg_hba.conf]/content: content changed '{sha256}e081221b6cfba3363105401616bfdd68bf4bc7d1b4fda9f19569c4a1c09d31dd' to '{sha256}a333d7a254b7befcc82db4d4c370ab72234817a60065dd72cf4f0c0c4494c2e0'
Notice: /Stage[main]/Profiles::Postgres/Postgresql::Server_instance[test1]/Postgresql::Server::Instance::Reload[test1]/Exec[postgresql_reload_test1]: Triggered 'refresh' from 1 event
Notice: Applied catalog in 3.82 seconds

[ins_test1@el8 ~]$ psql -p 5433 postgres
psql (13.7)
Type "help" for help.

postgres=# table pg_hba_file_rules ;
 line_number | type  |   database    |  user_name  |    address     |     netmask     | auth_method | options | error
-------------+-------+---------------+-------------+----------------+-----------------+-------------+---------+-------
           5 | local | {all}         | {ins_test1} |                |                 | peer        |         |
          10 | local | {all}         | {dba_test1} |                |                 | peer        |         |
          15 | local | {all}         | {app_test1} |                |                 | peer        |         |
          20 | host  | {all}         | {rou_test1} | 192.168.22.131 | 255.255.255.255 | md5         |         |
          25 | host  | {all}         | {ins_test1} | 192.168.22.131 | 255.255.255.255 | md5         |         |
          30 | local | {replication} | {ins_test1} |                |                 | peer        |         |
          35 | local | {replication} | {rep_test1} |                |                 | peer        |         |
(7 rows)

restart of postgresql.conf change:

[root@el8 puppet]# diff -u ../common.yaml data/common.yaml
--- ../common.yaml  2024-04-15 20:40:41.555885160 +0000
+++ data/common.yaml    2024-04-15 20:47:27.651070212 +0000
@@ -59,6 +59,8 @@
         value: "off"
       autovacuum_vacuum_scale_factor:
         value: 0.3
+      max_connections:
+        value: 700

[root@el8 puppet]# su - ins_test1
Last login: Mon Apr 15 20:38:30 UTC 2024 on pts/0
[ins_test1@el8 ~]$ psql -p 5433 postgres
psql (13.7)
Type "help" for help.

postgres=# show max_connections;
 max_connections
-----------------
 100
(1 row)

[root@el8 puppet]# puppet apply --hiera_config /root/puppet/hiera.yaml --modulepath=/root/puppet/modules/ /root/puppet/manifests/site.pp
Notice: Compiled catalog for el8 in environment production in 0.60 seconds
Notice: /Stage[main]/Profiles::Postgres/Postgresql::Server_instance[test1]/Postgresql::Server::Config_entry[max_connections_test1]/Postgresql_conf[max_connections_test1]/value: value changed 100 to 700
Notice: /Stage[main]/Profiles::Postgres/Postgresql::Server_instance[test1]/Postgresql::Server::Instance::Service[test1]/Anchor[postgresql::server::service::begin::test1]: Triggered 'refresh' from 1 event
Notice: /Stage[main]/Profiles::Postgres/Postgresql::Server_instance[test1]/Postgresql::Server::Instance::Service[test1]/Service[postgresqld_instance_test1]: Triggered 'refresh' from 1 event
Notice: /Stage[main]/Profiles::Postgres/Postgresql::Server_instance[test1]/Postgresql::Server::Instance::Service[test1]/Anchor[postgresql::server::service::end::test1]: Triggered 'refresh' from 1 event
Notice: Applied catalog in 3.96 seconds

[root@el8 puppet]# su - ins_test1
Last login: Mon Apr 15 20:48:54 UTC 2024 on pts/0
[ins_test1@el8 ~]$ psql -p 5433 postgres
psql (13.7)
Type "help" for help.

postgres=# show max_connections;
 max_connections
-----------------
 700
(1 row)

reload of postgresql.conf change:

[root@el8 puppet]# diff -u ../common.yaml data/common.yaml
--- ../common.yaml  2024-04-15 20:40:41.555885160 +0000
+++ data/common.yaml    2024-04-15 21:00:10.859744089 +0000
@@ -59,6 +59,10 @@
         value: "off"
       autovacuum_vacuum_scale_factor:
         value: 0.3
+      work_mem:
+        value: '16MB'

[ins_test1@el8 ~]$ psql -p 5433 postgres
psql (13.7)
Type "help" for help.

postgres=# show work_mem;
 work_mem
----------
 4MB
(1 row)

[root@el8 puppet]# puppet apply --hiera_config /root/puppet/hiera.yaml --modulepath=/root/puppet/modules/ /root/puppet/manifests/site.pp
Notice: Compiled catalog for el8 in environment production in 0.78 seconds
Notice: /Stage[main]/Profiles::Postgres/Postgresql::Server_instance[test1]/Postgresql::Server::Config_entry[work_mem_test1]/Postgresql_conf[work_mem_test1]/ensure: created
Notice: /Stage[main]/Profiles::Postgres/Postgresql::Server_instance[test1]/Postgresql::Server::Instance::Reload[test1]/Exec[postgresql_reload_test1]: Triggered 'refresh' from 1 event
Notice: Applied catalog in 4.19 seconds

[ins_test1@el8 ~]$ psql -p 5433 postgres
psql (13.7)
Type "help" for help.

postgres=# show work_mem;
 work_mem
----------
 16MB
(1 row)
SimonHoenscheid commented 6 months ago

Starting a stopped instance shows a weird behaviour:

[root@el8 puppet]# systemctl stop postgresql@13-test1

[root@el8 puppet]# puppet apply --hiera_config /root/puppet/hiera.yaml --modulepath=/root/puppet/modules/ /root/puppet/manifests/site.pp
Notice: Compiled catalog for el8 in environment production in 0.62 seconds
Notice: /Stage[main]/Profiles::Postgres/Postgresql::Server_instance[test1]/Postgresql::Server::Database[testdb1]/Postgresql_psql[CREATE DATABASE "testdb1"]/command: command changed 'notrun' to 'CREATE DATABASE "testdb1" WITH TEMPLATE = "template0" ENCODING = \'UTF8\' LC_COLLATE = \'en_US.UTF8\' LC_CTYPE = \'en_US.UTF8\' '
Notice: /Stage[main]/Profiles::Postgres/Postgresql::Server_instance[test1]/Postgresql::Server::Database[testdb1]/Postgresql_psql[REVOKE CONNECT ON DATABASE "testdb1" FROM public]: Triggered 'refresh' from 1 event
Notice: /Stage[main]/Profiles::Postgres/Postgresql::Server_instance[test1]/Postgresql::Server::Database[testdb1]/Postgresql_psql[UPDATE pg_database SET datistemplate = false WHERE datname = 'testdb1']/command: command changed 'notrun' to 'UPDATE pg_database SET datistemplate = false WHERE datname = \'testdb1\''
Notice: /Stage[main]/Profiles::Postgres/Postgresql::Server_instance[test1]/Postgresql::Server::Database[testdb2]/Postgresql_psql[CREATE DATABASE "testdb2"]/command: command changed 'notrun' to 'CREATE DATABASE "testdb2" WITH TEMPLATE = "template0" ENCODING = \'UTF8\' LC_COLLATE = \'en_US.UTF8\' LC_CTYPE = \'en_US.UTF8\' '
Notice: /Stage[main]/Profiles::Postgres/Postgresql::Server_instance[test1]/Postgresql::Server::Database[testdb2]/Postgresql_psql[REVOKE CONNECT ON DATABASE "testdb2" FROM public]: Triggered 'refresh' from 1 event
Notice: /Stage[main]/Profiles::Postgres/Postgresql::Server_instance[test1]/Postgresql::Server::Database[testdb2]/Postgresql_psql[UPDATE pg_database SET datistemplate = false WHERE datname = 'testdb2']/command: command changed 'notrun' to 'UPDATE pg_database SET datistemplate = false WHERE datname = \'testdb2\''
Notice: /Stage[main]/Profiles::Postgres/Postgresql::Server_instance[test1]/Postgresql::Server::Instance::Service[test1]/Service[postgresqld_instance_test1]/ensure: ensure changed 'stopped' to 'running'
Notice: Applied catalog in 4.53 seconds

[root@el8 puppet]# puppet apply --hiera_config /root/puppet/hiera.yaml --modulepath=/root/puppet/modules/ /root/puppet/manifests/site.pp
Notice: Compiled catalog for el8 in environment production in 0.67 seconds
Notice: Applied catalog in 4.07 seconds

Should look like: 

Notice: /Stage[main]/Postgresql::Server::Service/Postgresql::Server::Instance::Service[main]/Service[postgresqld_instance_main]/ensure: ensure changed 'stopped' to 'running' (corrective)
Info: /Stage[main]/Postgresql::Server::Service/Postgresql::Server::Instance::Service[main]/Service[postgresqld_instance_main]: Unscheduling refresh on Service[postgresqld_instance_main]
SimonHoenscheid commented 6 months ago

This looks good to me, the nightlies are also broken in main