Bring various postgres settings in line with modern defaults

diff --git a/cookbooks/postgresql/attributes/default.rb b/cookbooks/postgresql/attributes/default.rb
index 419b44de8a5af1807025fe3314fee6d789b0ecc1..fd1afcea1f66fb82f9c53df6c02b7f0e543a1c74 100644 (file)
--- a/cookbooks/postgresql/attributes/default.rb
+++ b/cookbooks/postgresql/attributes/default.rb
@@ -4,10 +4,10 @@ default[:postgresql][:monitor_tables] = true
 default[:postgresql][:settings][:defaults][:port] = "5432"
 default[:postgresql][:settings][:defaults][:max_connections] = "100"
 default[:postgresql][:settings][:defaults][:ssl] = "true"
-default[:postgresql][:settings][:defaults][:shared_buffers] = "32MB"
+default[:postgresql][:settings][:defaults][:shared_buffers] = "128MB"
 default[:postgresql][:settings][:defaults][:temp_buffers] = "8MB"
-default[:postgresql][:settings][:defaults][:work_mem] = "1MB"
-default[:postgresql][:settings][:defaults][:maintenance_work_mem] = "16MB"
+default[:postgresql][:settings][:defaults][:work_mem] = "4MB"
+default[:postgresql][:settings][:defaults][:maintenance_work_mem] = "64MB"
 default[:postgresql][:settings][:defaults][:max_stack_depth] = "2MB"
 default[:postgresql][:settings][:defaults][:effective_io_concurrency] = "1"
 default[:postgresql][:settings][:defaults][:max_worker_processes] = "8"
@@ -20,22 +20,22 @@ default[:postgresql][:settings][:defaults][:wal_buffers] = "-1"
 default[:postgresql][:settings][:defaults][:wal_writer_delay] = "200ms"
 default[:postgresql][:settings][:defaults][:commit_delay] = "0"
 default[:postgresql][:settings][:defaults][:checkpoint_timeout] = "5min"
+default[:postgresql][:settings][:defaults][:checkpoint_completion_target] = "0.9"
 default[:postgresql][:settings][:defaults][:max_wal_size] = "1GB"
 default[:postgresql][:settings][:defaults][:min_wal_size] = "80MB"
-default[:postgresql][:settings][:defaults][:checkpoint_completion_target] = "0.5"
 default[:postgresql][:settings][:defaults][:archive_mode] = "off"
-default[:postgresql][:settings][:defaults][:max_wal_senders] = "0"
-default[:postgresql][:settings][:defaults][:max_replication_slots] = "0"
-default[:postgresql][:settings][:defaults][:hot_standby] = "off"
+default[:postgresql][:settings][:defaults][:max_wal_senders] = "10"
+default[:postgresql][:settings][:defaults][:max_replication_slots] = "10"
+default[:postgresql][:settings][:defaults][:hot_standby] = "on"
 default[:postgresql][:settings][:defaults][:hot_standby_feedback] = "off"
 default[:postgresql][:settings][:defaults][:random_page_cost] = "4.0"
 default[:postgresql][:settings][:defaults][:cpu_tuple_cost] = "0.01"
-default[:postgresql][:settings][:defaults][:effective_cache_size] = "128MB"
+default[:postgresql][:settings][:defaults][:effective_cache_size] = "4GB"
 default[:postgresql][:settings][:defaults][:default_statistics_target] = "100"
 default[:postgresql][:settings][:defaults][:jit] = "on"
 default[:postgresql][:settings][:defaults][:log_min_duration_statement] = "-1"
-default[:postgresql][:settings][:defaults][:track_activity_query_size] = "1024"
 default[:postgresql][:settings][:defaults][:log_autovacuum_min_duration] = "-1"
+default[:postgresql][:settings][:defaults][:track_activity_query_size] = "1024"
 default[:postgresql][:settings][:defaults][:autovacuum_max_workers] = "3"
 default[:postgresql][:settings][:defaults][:autovacuum_naptime] = "1min"
 default[:postgresql][:settings][:defaults][:autovacuum_vacuum_scale_factor] = "0.2"

diff --git a/cookbooks/postgresql/templates/default/postgresql.conf.erb b/cookbooks/postgresql/templates/default/postgresql.conf.erb
index 855e8dc6c35701d69e30bb825d1e045885ffbfad..a8bc83c05fd1805b9515aa13d3b2650b4e8461b9 100644 (file)
--- a/cookbooks/postgresql/templates/default/postgresql.conf.erb
+++ b/cookbooks/postgresql/templates/default/postgresql.conf.erb
@@ -62,9 +62,9 @@ commit_delay = <%= @settings[:commit_delay] || @defaults[:commit_delay] %>
 # - Checkpoints -
 
 checkpoint_timeout = <%= @settings[:checkpoint_timeout] || @defaults[:checkpoint_timeout] %>
+checkpoint_completion_target = <%= @settings[:checkpoint_completion_target] || @defaults[:checkpoint_completion_target] %>
 max_wal_size = <%= @settings[:max_wal_size] || @defaults[:max_wal_size] %>
 min_wal_size = <%= @settings[:min_wal_size] || @defaults[:min_wal_size] %>
-checkpoint_completion_target = <%= @settings[:checkpoint_completion_target] || @defaults[:checkpoint_completion_target] %>
 
 # - Archiving -
 
@@ -123,6 +123,7 @@ log_min_duration_statement = <%= @settings[:log_min_duration_statement] || @defa
 
 # - What to Log -
 
+log_autovacuum_min_duration = <%= @settings[:log_autovacuum_min_duration] || @defaults[:log_autovacuum_min_duration] %>
 log_line_prefix = '%t '
 
 #------------------------------------------------------------------------------
@@ -140,7 +141,6 @@ stats_temp_directory = '/run/postgresql/<%= @version %>-main.pg_stat_tmp'
 # AUTOVACUUM PARAMETERS
 #------------------------------------------------------------------------------
 
-log_autovacuum_min_duration = <%= @settings[:log_autovacuum_min_duration] || @defaults[:log_autovacuum_min_duration] %>
 autovacuum_max_workers = <%= @settings[:autovacuum_max_workers] || @defaults[:autovacuum_max_workers] %>
 autovacuum_naptime = <%= @settings[:autovacuum_naptime] || @defaults[:autovacuum_naptime] %>
 autovacuum_vacuum_scale_factor = <%= @settings[:autovacuum_vacuum_scale_factor] || @defaults[:autovacuum_vacuum_scale_factor] %>

diff --git a/roles/db-master.rb b/roles/db-master.rb
index 0bb92773968997b11b2b771a6e3b284bf484e145..3ebd79e826800fa1c422bb921938ebbb7eff91ab 100644 (file)
--- a/roles/db-master.rb
+++ b/roles/db-master.rb
@@ -6,8 +6,7 @@ default_attributes(
     :settings => {
       :defaults => {
         :archive_mode => "on",
-        :archive_command => "/usr/local/bin/openstreetmap-wal-g wal-push %p --walg-prevent-wal-overwrite=true",
-        :max_replication_slots => "1"
+        :archive_command => "/usr/local/bin/openstreetmap-wal-g wal-push %p --walg-prevent-wal-overwrite=true"
       }
     }
   }

diff --git a/roles/db.rb b/roles/db.rb
index f0cbe0f97d6d5eec634ad56ca60f90a4e55b2695..05cd66aa946ac426fddf39299028976b8ce77b07 100644 (file)
--- a/roles/db.rb
+++ b/roles/db.rb
@@ -44,7 +44,6 @@ default_attributes(
         :wal_level => "logical",
         :max_wal_size => "1536MB",
         :checkpoint_completion_target => "0.8",
-        :max_wal_senders => "10",
         :cpu_tuple_cost => "0.1",
         :jit => "off",
         :log_min_duration_statement => "1000",

diff --git a/roles/dev.rb b/roles/dev.rb
index c41dda8dc35a51c50102a51f3f9f51fbf5af4c4d..6fc9c61634a5475a0a74e8770ecbe794f61ddc8b 100644 (file)
--- a/roles/dev.rb
+++ b/roles/dev.rb
@@ -139,8 +139,7 @@ default_attributes(
       },
       "15" => {
         :port => "5432",
-        :wal_level => "logical",
-        :max_replication_slots => "1"
+        :wal_level => "logical"
       }
     }
   },