search

vitabaks / postgresql_cluster

PostgreSQL High-Availability Cluster (based on "Patroni" and DCS "etcd" or "consul"). Automating with Ansible.
MIT License
1.29k stars 352 forks source link

Unable to reflect postgresql parameter changes after executing playbook #550

Open kumarashish071092 opened 4 months ago

kumarashish071092 commented 4 months ago

I have added below entries in vars/main.yml file : 

log_checkpoints = on
log_connections = on
log_disconnections =on
log_lock_waits=0
log_temp_files=0
log_autovacuum_min_duration=0
log_error_verbosity=default
log_statement=off
log_min_duration_statement=10
log_line_prefix = '%t [%p]: db=%d,user=%u,app=%a,client=%h '

After executing the playing the changes were not reflected in the database.

I have also added "pending_restart: false" and "pending_restart: true" and made modifications in the config_pgcluster.yml as well .

the query "select * from pg_settings where pending_restart is true;" is also giving null data.

what am I missing here?

vitabaks commented 4 months ago

@kumarashish071092 please attach

  1. the variables file main.yml
  2. the ansible playbook execution log
  3. the result of the patronictl show-config command
kumarashish071092 commented 4 months ago

From: Vitaliy Kukharik @.> Sent: Thursday, January 11, 2024 4:25 PM To: vitabaks/postgresql_cluster @.> Cc: Kumar Ashish @.>; Mention @.> Subject: Re: [vitabaks/postgresql_cluster] Unable to reflect postgresql parameter changes after executing playbook (Issue #550)

[Email from a non-Nagarro source: please exercise caution with links and attachments]

@kumarashish071092https://github.com/kumarashish071092 please attach the variables file main.yml, the ansible playbook execution log, and the result of the command

patronictl show-config

— Reply to this email directly, view it on GitHubhttps://github.com/vitabaks/postgresql_cluster/issues/550#issuecomment-1886865081, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AVXUU3T3X6JDYYQ5PWEWMFTYN7AJ3AVCNFSM6AAAAABBWHQKIOVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTQOBWHA3DKMBYGE. You are receiving this because you were mentioned.Message ID: @.***>

loop_wait: 10 master_start_timeout: 900 maximum_lag_on_failover: 1048576 postgresql: parameters: archive_command: pgbackrest --stanza=supplier-connect-uat archive-push %p archive_mode: true archive_timeout: 1800s auto_explain.log_analyze: true auto_explain.log_buffers: true auto_explain.log_min_duration: 10s auto_explain.log_nested_statements: true auto_explain.log_timing: false auto_explain.log_triggers: true auto_explain.log_verbose: true auto_explain.sample_rate: 0.01 autovacuum: true autovacuum_analyze_scale_factor: 0.01 autovacuum_max_workers: 5 autovacuum_naptime: 1s autovacuum_vacuum_cost_delay: 2 autovacuum_vacuum_cost_limit: 500 autovacuum_vacuum_scale_factor: 0.02 checkpoint_completion_target: 0.9 checkpoint_timeout: 15min default_statistics_target: 1000 effective_cache_size: 5926MB effective_io_concurrency: 2 hot_standby: true hot_standby_feedback: true huge_pages: try idle_in_transaction_session_timeout: 10min jit: false listen_addresses: 0.0.0.0 log_checkpoints: true log_directory: /mnt/Postgres/postgresql/15/supplier-connect-uat/log log_filename: postgresql-%a.log log_line_prefix: '%t [%p-%l] %r %q%u@%d ' log_lock_waits: true log_rotation_age: 1d log_rotation_size: 0 log_temp_files: 0 log_truncate_on_rotation: true logging_collector: true maintenance_work_mem: 256MB max_connections: 500 max_files_per_process: 4096 max_locks_per_transaction: 512 max_parallel_maintenance_workers: 2 max_parallel_workers: 8 max_parallel_workers_per_gather: 2 max_prepared_transactions: 0 max_replication_slots: 10 max_standby_streaming_delay: 30s max_wal_senders: 10 max_wal_size: 2GB max_worker_processes: 24 min_wal_size: 1GB password_encryption: scram-sha-256 pg_stat_statements.max: 10000 pg_stat_statements.save: true pg_stat_statements.track: all pg_stat_statements.track_utility: false random_page_cost: 4 seq_page_cost: 1 shared_buffers: 1975MB shared_preload_libraries: pg_stat_statements,auto_explain superuser_reserved_connections: 5 synchronous_commit: true tcp_keepalives_count: 10 tcp_keepalives_idle: 300 tcp_keepalives_interval: 30 track_activities: true track_activity_query_size: 4096 track_counts: true track_functions: all track_io_timing: true wal_buffers: 32MB wal_compression: true wal_keep_size: 2GB wal_level: replica wal_log_hints: true wal_receiver_status_interval: 10s work_mem: 128MB use_pg_rewind: true use_slots: true retry_timeout: 10 synchronous_mode: false synchronous_mode_strict: false synchronous_node_count: 1 ttl: 30

kumarashish071092 commented 4 months ago

@vitabaks any update here? I am trying few options but still it is not reflecting.

vitabaks commented 4 months ago

Please provide all information for analysis:

  1. the variables file main.yml
  2. the ansible playbook execution log
  3. the result of the patronictl show-config command
kumarashish071092 commented 4 months ago

Re_ [vitabaks_postgresql_cluster] Unable to reflect postgresql parameter changes after executing playbook (Issue #550).zip

vitabaks commented 4 months ago

To change the Postgres configuration in a cluster using automation:

1) change the parameters in the postgresql_parameters variable. You can also specify the pending_restart: true variable so that Postgres is automatically restarted if a parameter change requires a restart. 2) run config_pgcluster.yml playbook to apply changes.

vitabaks commented 4 months ago

@kumarashish071092 Judging by ansible.log, you ran deploy_pgcluster.yml, but you need config_pgcluster.yml to manage the cluster parameters after deployment.

vitabaks commented 3 months ago

@kumarashish071092 Is the problem still relevant?

sapisuper commented 2 months ago

@vitabaks the problem still relevant, I also facing this problem.

sapisuper commented 2 months ago

please see the attachment, I will provided main.yml, ansible execution log and result from patronictl show-config

issue-postgresql_cluster.zip

vitabaks commented 2 months ago

@sapisuper Have you tried this on version 1.9.0 according to the instructions?

vitabaks commented 2 months ago

according to the ansible log, I see that the task "Update conf file" has been completed

TASK [../../roles/patroni/config : Update conf file "/etc/patroni/patroni.yml"] ***********************************************************************************************************************************************************
changed: [172.16.11.99]
changed: [172.16.11.25]
changed: [172.16.12.156]

but I do not see the task "Update postgresql parameters in DCS". Since no changes have been made to the DCS so you don't see the changes. Code here

Please attach an archive of the entire postgresql_cluster directory so that I can analyze all the changes you have made.

sapisuper commented 1 month ago

@vitabaks hi Vita, thanks for you feedback I will check later.