Sooner or later all of us have to change a parameter on the database. But how is this put into execution when using a Patroni cluster? Of course there are some specifics you have to consider.
\nThis post will give you a short introduction into this topic.
\n<\/p>\n
When you want to change a parameter on a Patroni<\/a> cluster you have several possibilities: If you want to change a parameter (or more) for the whole cluster, you should use patronictl. If you want to change the initial configuration as well, you should also adjust patroni.yml.<\/p>\n All parameters already set are shown and can be changed like in any other file using the vi commands:<\/p>\n Once saved, you get the following:<\/p>\n When connecting to the database you will see, that the parameter is changed now. It is also changed on all the other nodes.<\/p>\n How can parameters be changed that need a restart? Especially as we want to have a minimal downtime of the cluster. Afterwards there are two possibilites.<\/p>\n If you do not want to restart the whole cluster, you have the possibility to restart each node separatly. Keep in mind, that you have to restart the Leader Node first, otherwise the change does not take effect. It is also possible to schedule the restart of a node.<\/p>\n In case you don’t want to restart node by node and you have the possibility of a downtime, it is also possible to restart the whole cluster (scheduled or immediately)<\/p>\n Of course you can change a parameter only on one node using “alter system”, too.<\/p>\n For sure the parameter change is not automatically applied to the replicas. The parameter is only changed on that node. All the other nodes will keep the value from the DCS. So you can change the parameter using “patronictl edit-config” or with an “alter system” command on each node. But: you also have to keep in mind the order in which the parameters are applied. The “alter system” change will persist the “patronictl edit-config” command.<\/p>\n So if you consider that there are some specialities when changing parameters in a Patroni cluster, it is quite easy to change a parameter. There are some parameters that need the same value on all nodes, e.g. max_connections, max_worker_processes, wal_level. And there are as well some parameters controlled by patroni, e.g listen_addresses and port. For a more details check the Patroni documentation <\/a>. And last but not least: If you change the configuration with patronictl and one node still has another configuration. Look for a postgresql.auto.conf in the PGDATA directory. Maybe there you can find the reason for different parameters on your nodes. Sooner or later all of us have to change a parameter on the database. But how is this put into execution when using a Patroni cluster? Of course there are some specifics you have to consider. This post will give you a short introduction into this topic.<\/p>\n","protected":false},"author":28,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229,198,83],"tags":[84,1710,1543,77],"type_dbi":[],"class_list":["post-12868","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","category-database-management","category-postgresql","tag-high-availability","tag-parameter-change","tag-patroni","tag-postgresql"],"acf":[],"yoast_head":"\n
\n– Dynamic configuration in DCS. These changes are applied asynchronously to every node.
\n– Local configuration in patroni.yml. This will take precedence over the dynamic configuration.
\n– Cluster configuration using “alter system”.
\n– Environment configuration using local environment variables.<\/p>\n Change PostgreSQL parameters using patronictl <\/h3>\n
1. Change parameters, that do not need a restart<\/h4>\n
\npostgres@patroni1:\/u01\/app\/postgres\/local\/dmk\/etc\/ [PG1] patronictl edit-config PG1\n<\/pre>\n
postgres@patroni1:\/u01\/app\/postgres\/local\/dmk\/etc\/ [PG1] patronictl edit-config PG1\n\nloop_wait: 10\nmaximum_lag_on_failover: 1048576\npostgresql:\n parameters:\n archive_command: \/bin\/true\n archive_mode: 'on'\n autovacuum_max_workers: '6'\n autovacuum_vacuum_scale_factor: '0.1'\n autovacuum_vacuum_threshold: '50'\n client_min_messages: WARNING\n effective_cache_size: 512MB\n hot_standby: 'on'\n hot_standby_feedback: 'on'\n listen_addresses: '*'\n log_autovacuum_min_duration: 60s\n log_checkpoints: 'on'\n log_connections: 'on'\n log_directory: pg_log\n log_disconnections: 'on'\n log_duration: 'on'\n log_filename: postgresql-%a.log\n log_line_prefix: '%m - %l - %p - %h - %u@%d - %x'\n log_lock_waits: 'on'\n log_min_duration_statement: 30s\n log_min_error_statement: NOTICE\n log_min_messages: WARNING\n log_rotation_age: '1440'\n log_statement: ddl\n log_temp_files: '0'\n log_timezone: Europe\/Zurich\n log_truncate_on_rotation: 'on'\n logging_collector: 'on'\n maintenance_work_mem: 64MB\n max_replication_slots: 10\n max_wal_senders: '20'\n port: 5432\n shared_buffers: 128MB\n shared_preload_libraries: pg_stat_statements\n wal_compression: 'off'\n wal_keep_segments: 8\n wal_level: replica\n wal_log_hints: 'on'\n work_mem: 8MB\n use_pg_rewind: true\n use_slots: true\nretry_timeout: 10\nttl: 30\n<\/pre>\n
\n---\n+++\n@@ -2,7 +2,8 @@\n maximum_lag_on_failover: 1048576\n postgresql:\n parameters:\n- archive_command: \/bin\/true\n+ archive_command: 'test ! -f \/u99\/pgdata\/PG1\/archived_wal\/%f && cp %p \/u99\/pgdata\/PG1\/archived_wal\/%f'\n archive_mode: 'on'\n autovacuum_max_workers: '6'\n autovacuum_vacuum_scale_factor: '0.1'\n\nApply these changes? [y\/N]: y\nConfiguration changed\n<\/pre>\n
postgres@patroni1:\/u01\/app\/postgres\/local\/dmk\/etc\/ [PG1] sq\npsql (11.5)\nType \"help\" for help.\n\npostgres=# show archive_command;\n archive_command\n------------------------------------------------------------------------------------\n test ! -f \/u99\/pgdata\/PG1\/archived_wal\/%f && cp %p \/u99\/pgdata\/PG1\/archived_wal\/%f\n(1 row)\n<\/pre>\n
2. Change parameters, that need a restart<\/h4>\n
\nFirst of all the parameter can be changed the same way as the parameters that do not need a restart using patronictl edit-config. Once the parameter is changed the status overview of the cluster gets a new column showing which node needs a restart.<\/p>\n\npostgres@patroni1:\/u01\/app\/postgres\/local\/dmk\/etc\/ [PG1] patronictl list\n+---------+----------+----------------+--------+---------+----+-----------+-----------------+\n| Cluster | Member | Host | Role | State | TL | Lag in MB | Pending restart |\n+---------+----------+----------------+--------+---------+----+-----------+-----------------+\n| PG1 | patroni1 | 192.168.22.111 | Leader | running | 4 | 0.0 | * |\n| PG1 | patroni2 | 192.168.22.112 | | running | 4 | 0.0 | * |\n| PG1 | patroni3 | 192.168.22.113 | | running | 4 | 0.0 | * |\n+---------+----------+----------------+--------+---------+----+-----------+-----------------+\n<\/pre>\n
2.1 Restart node by node<\/h5>\n
\npostgres@patroni1:\/u01\/app\/postgres\/local\/dmk\/etc\/ [PG1] patronictl restart PG1 patroni1\nWhen should the restart take place (e.g. 2019-10-08T15:33) [now]:\n+---------+----------+----------------+--------+---------+----+-----------+-----------------+\n| Cluster | Member | Host | Role | State | TL | Lag in MB | Pending restart |\n+---------+----------+----------------+--------+---------+----+-----------+-----------------+\n| PG1 | patroni1 | 192.168.22.111 | Leader | running | 4 | 0.0 | * |\n| PG1 | patroni2 | 192.168.22.112 | | running | 4 | 0.0 | * |\n| PG1 | patroni3 | 192.168.22.113 | | running | 4 | 0.0 | * |\n+---------+----------+----------------+--------+---------+----+-----------+-----------------+\nAre you sure you want to restart members patroni1? [y\/N]: y\nRestart if the PostgreSQL version is less than provided (e.g. 9.5.2) []:\nSuccess: restart on member patroni1\npostgres@patroni1:\/u01\/app\/postgres\/local\/dmk\/etc\/ [PG1] patronictl restart PG1 patroni2\nWhen should the restart take place (e.g. 2019-10-08T15:34) [now]:\n+---------+----------+----------------+--------+---------+----+-----------+-----------------+\n| Cluster | Member | Host | Role | State | TL | Lag in MB | Pending restart |\n+---------+----------+----------------+--------+---------+----+-----------+-----------------+\n| PG1 | patroni1 | 192.168.22.111 | Leader | running | 4 | 0.0 | |\n| PG1 | patroni2 | 192.168.22.112 | | running | 4 | 0.0 | * |\n| PG1 | patroni3 | 192.168.22.113 | | running | 4 | 0.0 | * |\n+---------+----------+----------------+--------+---------+----+-----------+-----------------+\nAre you sure you want to restart members patroni2? [y\/N]: y\nRestart if the PostgreSQL version is less than provided (e.g. 9.5.2) []:\nSuccess: restart on member patroni2\npostgres@patroni1:\/u01\/app\/postgres\/local\/dmk\/etc\/ [PG1] patronictl restart PG1 patroni3\nWhen should the restart take place (e.g. 2019-10-08T15:34) [now]:\n+---------+----------+----------------+--------+---------+----+-----------+-----------------+\n| Cluster | Member | Host | Role | State | TL | Lag in MB | Pending restart |\n+---------+----------+----------------+--------+---------+----+-----------+-----------------+\n| PG1 | patroni1 | 192.168.22.111 | Leader | running | 4 | 0.0 | |\n| PG1 | patroni2 | 192.168.22.112 | | running | 4 | 0.0 | |\n| PG1 | patroni3 | 192.168.22.113 | | running | 4 | 0.0 | * |\n+---------+----------+----------------+--------+---------+----+-----------+-----------------+\nAre you sure you want to restart members patroni3? [y\/N]: y\nRestart if the PostgreSQL version is less than provided (e.g. 9.5.2) []:\nSuccess: restart on member patroni3\npostgres@patroni1:\/u01\/app\/postgres\/local\/dmk\/etc\/ [PG1] patronictl list\n+---------+----------+----------------+--------+---------+----+-----------+\n| Cluster | Member | Host | Role | State | TL | Lag in MB |\n+---------+----------+----------------+--------+---------+----+-----------+\n| PG1 | patroni1 | 192.168.22.111 | Leader | running | 4 | 0.0 |\n| PG1 | patroni2 | 192.168.22.112 | | running | 4 | 0.0 |\n| PG1 | patroni3 | 192.168.22.113 | | running | 4 | 0.0 |\n+---------+----------+----------------+--------+---------+----+-----------+\n<\/pre>\n
2.2 Restart the whole cluster<\/h5>\n
\npostgres@patroni1:\/u01\/app\/postgres\/local\/dmk\/etc\/ [PG1] patronictl restart PG1\nWhen should the restart take place (e.g. 2019-10-08T15:37) [now]:\n+---------+----------+----------------+--------+---------+----+-----------+-----------------+\n| Cluster | Member | Host | Role | State | TL | Lag in MB | Pending restart |\n+---------+----------+----------------+--------+---------+----+-----------+-----------------+\n| PG1 | patroni1 | 192.168.22.111 | Leader | running | 4 | 0.0 | * |\n| PG1 | patroni2 | 192.168.22.112 | | running | 4 | 0.0 | * |\n| PG1 | patroni3 | 192.168.22.113 | | running | 4 | 0.0 | * |\n+---------+----------+----------------+--------+---------+----+-----------+-----------------+\nAre you sure you want to restart members patroni1, patroni2, patroni3? [y\/N]: y\nRestart if the PostgreSQL version is less than provided (e.g. 9.5.2) []:\nSuccess: restart on member patroni1\nSuccess: restart on member patroni2\nSuccess: restart on member patroni3\npostgres@patroni1:\/u01\/app\/postgres\/local\/dmk\/etc\/ [PG1] patronictl list\n+---------+----------+----------------+--------+---------+----+-----------+\n| Cluster | Member | Host | Role | State | TL | Lag in MB |\n+---------+----------+----------------+--------+---------+----+-----------+\n| PG1 | patroni1 | 192.168.22.111 | Leader | running | 4 | 0.0 |\n| PG1 | patroni2 | 192.168.22.112 | | running | 4 | 0.0 |\n| PG1 | patroni3 | 192.168.22.113 | | running | 4 | 0.0 |\n+---------+----------+----------------+--------+---------+----+-----------+\n<\/pre>\n
Change PostgreSQL parameters using “alter system”<\/h3>\n
\n postgres@patroni1:\/home\/postgres\/ [PG1] sq\npsql (11.5)\nType \"help\" for help.\n\npostgres=# show archive_Command;\n archive_command\n-----------------\n \/bin\/false\n(1 row)\n\npostgres=# alter system set archive_command='\/bin\/true';\nALTER SYSTEM\n\npostgres=# select pg_reload_conf();\n pg_reload_conf\n----------------\n t\n(1 row)\n\npostgres=# show archive_command;\n archive_command\n-----------------\n \/bin\/true\n(1 row)\n<\/pre>\n
Conclusion <\/h3>\n
\nIf you are interested in more “Patroni Operations” blogs, check also this one Patroni operations: Switchover and Failover<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"