Puppet Class: pe_databases::postgresql_settings

Defined in:
manifests/postgresql_settings.pp

Summary

Tune PostgreSQL settings

Overview

Tune PostgreSQL

Parameters:

  • maintenance_work_mem (String) (defaults to: $all_in_one_pe_install ? { false => "${facts['memory']['system']['total_bytes'] / 1024 / 1024 / 3}MB", true => "${facts['memory']['system']['total_bytes'] / 1024 / 1024 / 8}MB") —

    Increase to improve speed of speed of vacuuming and reindexing (Example “1GB”)

  • work_mem (String) (defaults to: '8MB') —

    Allows PostgreSQL to do larger in-memory sorts (Default: “4MB”)

  • autovacumn_work_mem (String) —

    Similar to but for maintenance_work_mem autovacuum processes only (Example “256MB”)

  • autovacuum_max_workers (Integer) (defaults to: max(3, min(8, $facts['processors']['count'] / 3))) —

    Maximum number of autovacuum processes to run concurrently (Default: 3)

  • autovacuum_vacuum_scale_factor (Float[0,1]) (defaults to: 0.08)
  • autovacuum_analyze_scale_factor (Float[0,1]) (defaults to: 0.04)
  • log_autovacuum_min_duration (Integer) (defaults to: -1)
  • log_temp_files (Integer) (defaults to: -1)
  • max_connections (Integer) (defaults to: 1000)
  • arbitrary_postgresql_conf_settings (Hash) (defaults to: {})
  • checkpoint_completion_target (Float[0,1]) (defaults to: 0.9)
  • checkpoint_segments (Integer) (defaults to: 128)
  • manage_postgresql_service (Boolean) (defaults to: true)
  • all_in_one_pe_install (Boolean) (defaults to: true)
  • manage_reports_autovacuum_cost_delay (Boolean) (defaults to: true)
  • factsets_autovacuum_vacuum_scale_factor (Optional[Float[0,1]]) (defaults to: 0.80)
  • reports_autovacuum_vacuum_scale_factor (Optional[Float[0,1]]) (defaults to: 0.01)
  • autovacuum_work_mem (String) (defaults to: $all_in_one_pe_install ? { false => "${facts['memory']['system']['total_bytes'] / 1024 / 1024 / 3 / $autovacuum_max_workers}MB", true => "${facts['memory']['system']['total_bytes'] / 1024 / 1024 / 8 / $autovacuum_max_workers}MB")
  • psql_version (String) (defaults to: $pe_databases::psql_version) 


11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
 
# File 'manifests/postgresql_settings.pp', line 11

class pe_databases::postgresql_settings (
  # lint:ignore:140chars
  Float[0,1] $autovacuum_vacuum_scale_factor                    = 0.08,
  Float[0,1] $autovacuum_analyze_scale_factor                   = 0.04,
  Integer    $autovacuum_max_workers                            = max(3, min(8, $facts['processors']['count'] / 3)),
  Integer    $log_autovacuum_min_duration                       = -1,
  Integer    $log_temp_files                                    = -1,
  String     $work_mem                                          = '8MB',
  Integer    $max_connections                                   = 1000,
  Hash       $arbitrary_postgresql_conf_settings                = {},
  Float[0,1] $checkpoint_completion_target                      = 0.9,
  Integer    $checkpoint_segments                               = 128,
  Boolean    $manage_postgresql_service                         = true,
  Boolean    $all_in_one_pe_install                             = true,
  Boolean    $manage_reports_autovacuum_cost_delay              = true,
  Optional[Float[0,1]] $factsets_autovacuum_vacuum_scale_factor = 0.80,
  Optional[Float[0,1]] $reports_autovacuum_vacuum_scale_factor  = 0.01,
  String     $maintenance_work_mem                              = $all_in_one_pe_install ? {
                                                                    false => "${facts['memory']['system']['total_bytes'] / 1024 / 1024 / 3}MB",
                                                                    true  => "${facts['memory']['system']['total_bytes'] / 1024 / 1024 / 8}MB",
                                                                  },
  String     $autovacuum_work_mem                               = $all_in_one_pe_install ? {
                                                                    false => "${facts['memory']['system']['total_bytes'] / 1024 / 1024 / 3 / $autovacuum_max_workers}MB",
                                                                    true  => "${facts['memory']['system']['total_bytes'] / 1024 / 1024 / 8 / $autovacuum_max_workers}MB",
                                                                  },
  String     $psql_version                                      = $pe_databases::psql_version,
  # lint:endignore
) {

  $postgresql_service_resource_name = 'postgresqld'
  $postgresql_service_name          = 'pe-postgresql'
  $notify_postgresql_service        = $manage_postgresql_service ? {
    true    => Service[$postgresql_service_resource_name],
    default => undef,
  }
  $notify_console_services          = $all_in_one_pe_install ? {
    true    => Service['pe-console-services'],
    default => undef,
  }

  if $manage_postgresql_service {
    service { $postgresql_service_resource_name :
      ensure => running,
      name   => $postgresql_service_name,
      enable => true,
      notify => $notify_console_services,
    }
  }

  # The value attribute of postgresql_conf requires a string despite validating a float above.
  # https://tickets.puppetlabs.com/browse/MODULES-2960
  # http://www.postgresql.org/docs/9.4/static/runtime-config-autovacuum.html

  Pe_postgresql_conf {
    ensure => present,
    target => "/opt/puppetlabs/server/data/postgresql/${psql_version}/data/postgresql.conf",
    notify => $notify_postgresql_service,
  }

  pe_postgresql_conf { 'autovacuum_vacuum_scale_factor' :
    value => sprintf('%#.2f', $autovacuum_vacuum_scale_factor),
  }

  pe_postgresql_conf { 'autovacuum_analyze_scale_factor' :
    value => sprintf('%#.2f', $autovacuum_analyze_scale_factor),
  }

  pe_postgresql_conf { 'autovacuum_max_workers' :
    value => String($autovacuum_max_workers),
  }

  pe_postgresql_conf { 'autovacuum_work_mem' :
    value => String($autovacuum_work_mem),
  }

  pe_postgresql_conf { 'log_autovacuum_min_duration' :
    value => String($log_autovacuum_min_duration),
  }

  pe_postgresql_conf { 'log_temp_files' :
    value => String($log_temp_files),
  }

  pe_postgresql_conf { 'maintenance_work_mem' :
    value => String($maintenance_work_mem),
  }

  pe_postgresql_conf { 'work_mem' :
    value => String($work_mem),
  }

  pe_postgresql_conf { 'max_connections' :
    value => String($max_connections),
  }

  pe_postgresql_conf { 'checkpoint_completion_target' :
    value => sprintf('%#.2f', $checkpoint_completion_target),
  }

  $checkpoint_segments_ensure = $psql_version ? {
    '9.4'   => 'present',
    default => 'absent',
  }

  pe_postgresql_conf { 'checkpoint_segments' :
    ensure => $checkpoint_segments_ensure,
    value  => String($checkpoint_segments),
  }

  if !empty($arbitrary_postgresql_conf_settings) {
    $arbitrary_postgresql_conf_settings.each | $key, $value | {
      pe_postgresql_conf { $key :
        value => String($value),
      }
    }
  }
}