class Postgres

Public Instance Methods

deploy() click to toggle source
# File lib/dust/recipes/postgres.rb, line 3
def deploy
  # version: 9.1
  # package:  postgresql-9.1
  # profile: [ dedicated|standard, zabbix, pacemaker ]
  # service_name: "service name for init scripts"

  if @node.uses_apt?
    unless @config['version'] or @config['package']
      return @node.messages.add('please specify version or package name in your config file, e.g. "version: 9.1"').failed
    end
  end

  return unless install_postgres

  # default cluster on debian-like systems is 'main'
  @config['cluster'] ||= 'main' if @node.uses_apt?


  set_default_directories
  deploy_config
  deploy_recovery
  deploy_certificates if @config['server.crt'] and @config['server.key']
  set_permissions

  # configure pacemaker profile
  if Array(@config['profile']).include?('pacemaker')
    deploy_pacemaker_script if @node.package_installed?('pacemaker')
  end

  # configure zabbix profile
  if Array(@config['profile']).include?('zabbix')
    configure_for_zabbix if zabbix_installed?
  end

  # enable service to start at boot-time
  @node.autostart_service(@config['service_name'])

  # reload/restart postgres if command line option is given
  @node.restart_service(@config['service_name']) if options.restart?
  @node.reload_service(@config['service_name']) if options.reload?
end
status() click to toggle source
# File lib/dust/recipes/postgres.rb, line 46
def status
  return unless @node.package_installed?([ 'postgresql-server', "postgresql-#{@config['version']}" ])
  set_default_directories
  @node.print_service_status(@config['service_name'])
end

Private Instance Methods

configure_for_zabbix() click to toggle source

configures postgres for zabbix monitoring: adds zabbix user to postgres group creates zabbix user in postgres and grant access to postgres database

# File lib/dust/recipes/postgres.rb, line 243
def configure_for_zabbix
  @node.messages.add("configuring postgres for zabbix monitoring\n")
  msg = @node.messages.add('adding zabbix user to postgres group', :indent => 2)
  msg.parse_result(@node.exec('usermod -a -G postgres zabbix')[:exit_code])

  if is_master?(:indent => 2)
    msg = @node.messages.add('checking if zabbix user exists in postgres', :indent => 3)
    ret = msg.parse_result(@node.exec('psql -U postgres -c ' +
                                         '  "SELECT usename FROM pg_user WHERE usename = \'zabbix\'"' +
                                         '  postgres |grep -q zabbix')[:exit_code])

    # if user was not found, create him
    unless ret
      msg = @node.messages.add('create zabbix user in postgres', :indent => 4)
      msg.parse_result(@node.exec('createuser -U postgres zabbix -RSD')[:exit_code])
    end

    msg = @node.messages.add('GRANT zabbix user access to postgres database', :indent => 3)
    msg.parse_result(@node.exec('psql -U postgres -c "GRANT SELECT ON pg_stat_database TO zabbix" postgres')[:exit_code])
  end
end
default_postgres_conf() click to toggle source

default settings for postgresql.conf

# File lib/dust/recipes/postgres.rb, line 128
def default_postgres_conf
  {
    'max_connections' => 100,
    'datestyle' => 'iso, mdy',
    'lc_messages' => 'en_US.UTF-8',
    'lc_monetary' => 'en_US.UTF-8',
    'lc_numeric' => 'en_US.UTF-8',
    'lc_time' => 'en_US.UTF-8',
    'default_text_search_config' => 'pg_catalog.english',
    'log_line_prefix' => '%t [%p] %u@%d '
  }
end
deploy_certificates() click to toggle source

deploy certificates to data-dir

# File lib/dust/recipes/postgres.rb, line 117
def deploy_certificates
  @node.deploy_file("#{@template_path}/#{@config['server.crt']}",
                    "#{@config['postgresql.conf']['data_directory']}/server.crt",
                    :binding => binding)

  @node.deploy_file("#{@template_path}/#{@config['server.key']}",
                    "#{@config['postgresql.conf']['data_directory']}/server.key",
                    :binding => binding)
end
deploy_config() click to toggle source

deploy postgresql.conf, pg_hba.conf and pg_ident.conf

# File lib/dust/recipes/postgres.rb, line 100
def deploy_config
  @node.write("#{@config['conf_directory']}/postgresql.conf", generate_postgresql_conf)
  @node.write("#{@config['conf_directory']}/pg_hba.conf", generate_pg_hba_conf)
  @node.write( "#{@config['conf_directory']}/pg_ident.conf", generate_pg_ident_conf)
end
deploy_pacemaker_script() click to toggle source

deploy the pacemaker script

# File lib/dust/recipes/postgres.rb, line 226
def deploy_pacemaker_script
  @node.deploy_file("#{@template_path}/pacemaker.sh", "#{@config['conf_directory']}/pacemaker.sh", :binding => binding)
  @node.chmod('755', "#{@config['conf_directory']}/pacemaker.sh")
end
deploy_recovery() click to toggle source

copy recovery.conf to either recovery.conf or recovery.done depending on which file already exists.

# File lib/dust/recipes/postgres.rb, line 108
def deploy_recovery
  if @node.file_exists?("#{@config['postgresql.conf']['data_directory']}/recovery.conf", :quiet => true)
    @node.write("#{@config['postgresql.conf']['data_directory']}/recovery.conf", generate_recovery_conf)
  else
    @node.write("#{@config['postgresql.conf']['data_directory']}/recovery.done", generate_recovery_conf)
  end
end
generate_pg_hba_conf() click to toggle source
# File lib/dust/recipes/postgres.rb, line 169
def generate_pg_hba_conf
  @config['pg_hba.conf'] ||= [ 'local   all         postgres                trust' ]
  @config['pg_hba.conf'].join("\n")
end
generate_pg_ident_conf() click to toggle source
# File lib/dust/recipes/postgres.rb, line 174
def generate_pg_ident_conf
  @config['pg_ident.conf'] ||= []
  @config['pg_ident.conf'].join("\n")
end
generate_postgresql_conf() click to toggle source
# File lib/dust/recipes/postgres.rb, line 141
def generate_postgresql_conf
  @config['postgresql.conf'] ||= {}
  @config['postgresql.conf'] = default_postgres_conf.merge(@config['postgresql.conf'])

  # calculate values if dedicated profile is given
  profile_dedicated if Array(@config['profile']).include?('dedicated')

  postgresql_conf = ''
  @config['postgresql.conf'].each do |key, value|
    value = "'#{value}'" if value.is_a? String # enclose strings in ''
    postgresql_conf << "#{key} = #{value}\n"
  end

  postgresql_conf
end
generate_recovery_conf() click to toggle source
# File lib/dust/recipes/postgres.rb, line 157
def generate_recovery_conf
  @config['recovery.conf'] ||= []

  recovery_conf = ''
  @config['recovery.conf'].each do |key, value|
    value = "'#{value}'" if value.is_a? String # enclose strings in ''
    recovery_conf << "#{key} = #{value}\n"
  end

  recovery_conf
end
install_postgres() click to toggle source
# File lib/dust/recipes/postgres.rb, line 55
def install_postgres
  if @config['package']
    package = @config['package']
  elsif @config['version']
    package = "postgresql-#{@config['version']}"
  else
    package = 'postgresql-server'
  end

  @node.install_package(package)
end
is_master?(options = {}) click to toggle source

checks if this server is a postgres master

# File lib/dust/recipes/postgres.rb, line 266
def is_master?(options = {})
  msg = @node.messages.add('checking if this host is the postgres master: ', options)
  if @node.file_exists?("#{@config['postgresql.conf']['data_directory']}/recovery.done", :quiet => true)
    msg.ok('yes')
    return true
    else
    msg.ok('no')
    return false
  end
end
kb2mb(value) click to toggle source

converts plain kb value to “1234MB”

# File lib/dust/recipes/postgres.rb, line 213
def kb2mb value
  "#{(value / 1024).to_i}MB"
end
profile_dedicated() click to toggle source

try to find good values (but don’t overwrite if set in config file) for shared_buffers, work_mem and maintenance_work_mem, effective_cache_size and wal_buffers

# File lib/dust/recipes/postgres.rb, line 181
def profile_dedicated
  @node.collect_facts(:quiet => true)
  system_mem = ::Dust.convert_size(@node['memorysize']).to_f

  msg = @node.messages.add("calculating recommended settings for a dedicated databse server with #{kb2mb system_mem} ram\n")

  # every connection uses up to work_mem memory, so make sure that even if
  # max_connections is reached, there's still a bit left.
  # total available memory / (2 * max_connections)
  @config['postgresql.conf']['work_mem'] ||= kb2mb(system_mem * 0.9 / @config['postgresql.conf']['max_connections'])
  @node.messages.add("work_mem: #{@config['postgresql.conf']['work_mem']}", :indent => 2).ok

  # shared_buffers should be 0.2 - 0.3 of system ram
  # unless ram is lower than 1gb, then less (32mb maybe)
  @config['postgresql.conf']['shared_buffers'] ||= kb2mb(system_mem * 0.25)
  @node.messages.add("shared_buffers: #{@config['postgresql.conf']['shared_buffers']}", :indent => 2).ok

  # maintenance_work_mem, should be a lot higher than work_mem
  # recommended value: 50mb for each 1gb of system ram
  @config['postgresql.conf']['maintenance_work_mem'] ||= kb2mb(system_mem / 1024 * 50)
  @node.messages.add("maintenance_work_mem: #{@config['postgresql.conf']['maintenance_work_mem']}", :indent => 2).ok

  # effective_cache_size between 0.6 and 0.8 of system ram
  @config['postgresql.conf']['effective_cache_size'] ||= kb2mb(system_mem * 0.75)
  @node.messages.add("effective_cache_size: #{@config['postgresql.conf']['effective_cache_size']}", :indent => 2).ok

  # wal_buffers should be between 2-16mb
  @config['postgresql.conf']['wal_buffers'] ||= '12MB'
  @node.messages.add("wal_buffers: #{@config['postgresql.conf']['wal_buffers']}", :indent => 2).ok
end
set_default_directories() click to toggle source

set conf-dir and data-dir as well as service-name according to config file, or use standard values of distribution

# File lib/dust/recipes/postgres.rb, line 69
def set_default_directories
  @config['postgresql.conf'] ||= {} # create empty config, unless present

  # rpm systems place the configuration in the data dir
  if @node.uses_rpm?
    @config['postgresql.conf']['data_directory'] ||= '/var/lib/pgsql/data'
    @config['conf_directory'] ||= @config['postgresql.conf']['data_directory']

  # apt systems specify a cluster for their postgres instances
  elsif @node.uses_apt?
    @config['conf_directory'] ||= "/etc/postgresql/#{@config['version']}/#{@config['cluster']}"
    @config['postgresql.conf']['data_directory'] ||= "/var/lib/postgresql/#{@config['version']}/#{@config['cluster']}"

  # other systems just use this defaults
  else
    @config['conf_directory'] ||= "/etc/postgresql-#{@config['version']}"
    @config['postgresql.conf']['data_directory'] ||= "/var/lib/postgresql/#{@config['version']}/data"
  end

  # set the postgres service name
  if @node.uses_emerge?
    @config['service_name'] ||= "postgresql-#{@config['version']}"
  else
    @config['service_name'] ||= 'postgresql'
  end

  @config['postgresql.conf']['hba_file'] ||= "#{@config['conf_directory']}/pg_hba.conf"
  @config['postgresql.conf']['ident_file'] ||= "#{@config['conf_directory']}/pg_ident.conf"
end
set_permissions() click to toggle source

give the configured dbuser the data_directory

# File lib/dust/recipes/postgres.rb, line 218
def set_permissions
  if @config['dbuser']
    @node.chown("#{@config['dbuser']}:#{@node.get_gid(@config['dbuser'])}", @config['postgresql.conf']['data_directory'])
  end
  @node.chmod('u+Xrw,g-rwx,o-rwx', @config['postgresql.conf']['data_directory'])
end
zabbix_installed?() click to toggle source

check if zabbix is installed

# File lib/dust/recipes/postgres.rb, line 232
def zabbix_installed?
  if @node.uses_emerge?
    return @node.package_installed?('zabbix', :quiet => true)
  else
    return @node.package_installed?('zabbix-agent', :quiet => true)
  end
end