Steve Singer - Managing PostgreSQL with Puppet @ Postgres Open
0 likes•5,114 views
This document discusses how to manage PostgreSQL databases with Puppet. It describes using Puppet to deploy PostgreSQL binaries, configure files like postgresql.conf and pg_hba.conf, manage users and permissions, handle database initialization, configure backups with cron jobs, manage replication with Slony, and more. Templates, Hiera, and custom defined resources are used to configure PostgreSQL instances in a consistent and automated way.
![postgresql.conf
$shared_buffers= '300MB'
$conf_template='dba/postgresql.91.conf.erb'
file { "${pg_datadir}/postgresql.conf" :
owner => "$pguser",
group => postgres,
mode => 644,
require => Exec["${name}_initdb"],
content => template("$conf_template"),
}
• $](https://image.slidesharecdn.com/puppetpgopen2013-130921144322-phpapp01/85/Steve-Singer-Managing-PostgreSQL-with-Puppet-Postgres-Open-22-320.jpg)
![Hiera 101
Separate Code From Data
http://commons.wikimedia.org/wiki/File:Lower_Antelope_Canyon_478.jpg
By Uploaded by Meckimac [GFDL (http://www.gnu.org/copyleft/fdl.html) or CC-BY-SA-3.0 (http://creativecommons.org/licenses/by-sa/3.0/)],
via Wikimedia Commons](https://image.slidesharecdn.com/puppetpgopen2013-130921144322-phpapp01/85/Steve-Singer-Managing-PostgreSQL-with-Puppet-Postgres-Open-24-320.jpg)
![Slony: Slonik preambles
cluster name=<%= clustername %>;
# A slonik preamble that defines
# the 'node xxx admin conninfo...' data
# and an associated DEFINE for each
# node in the cluster
#
<% slon_conf.each do | node_id, values | %>
<% port=values['port']
if port==nil or port==''
port=complex_port
end %>
node <%= node_id %> admin conninfo='host=<%= values['host'] %> ......
define CONNINFO_<%= node_id %> 'host=<%= values['host'] %> .....
<% end %>
/opt/configs/$clustername/slonik.preamble :
slonik_preamble.erb](https://image.slidesharecdn.com/puppetpgopen2013-130921144322-phpapp01/85/Steve-Singer-Managing-PostgreSQL-with-Puppet-Postgres-Open-34-320.jpg)
Steve Singer - Managing PostgreSQL with Puppet @ Postgres Open
- 1. Managing PostgreSQL with Puppet CCBYSA-NC http://www.flickr.com/photos/urgetopunt/7876067126/ Steve Singer ssinger@ca.afilias.info
- 2. Who is Steve? ● Slony maintainer ● Database Developer @ Afilias (Toronto) http://www.flickr.com/photos/obis/2639893609/
- 5. Technologies
- 6. DevOps
- 8. Goals of Automation: Database servers 'look' the same http://www.flickr.com/photos/oskay/265900118
- 9. Consistency between QA, Staging, Production http://www.flickr.com/photos/tempest/130084735
- 12. Puppet Architecture Puppetmaster Computer Puppet agent Computer Puppet agent Computer Puppet agent
- 13. Puppet Terminology class pgsql puppetmaster catalog class slony Node definition
- 14. Puppet Class class postgresql { package { 'postgresql-server' : ensure => installed, provider => 'yum' } } resource Resource Type
- 15. Declarative file { '/tmp/hello_world.txt' : content => 'Welcome to Chicago', owner => 'pgopen', mode => 0644 } /tmp/hello_world.txt Welcome to Chicago
- 17. Goals for Databases ● Deploy Binaries ● Manage pg_hba.conf and postgresq.conf ● Manage postgres unix users ● Handle init db ● Manage Crontabs ● Manage slons and slonik preambles
- 18. PostgreSQL Module https://github.com/puppetlabs/puppet-postgresql ● Pre-existing module for managing postgresql ● Manages installing, start/stopping postgresql ● We don't actually use it
- 19. pgsql_cluster resource define pgsql_cluster ($clustername,$port,$datadir, $conf_template,$listen_address,$pguser, $pghba_template,$service_name,$pguser_password,$encoding) { ... } • One instance per database $datadir • Performs initdb • Deploys postgresql.conf • Deploys pg_hba.conf • Creates standard database users
- 20. postgresql.conf.erb #------------------------------------------------------------------------------ # RESOURCE USAGE (except WAL) #------------------------------------------------------------------------------ # - Memory - shared_buffers = <%= shared_buffers %> # min 128kB # (change requires restart) Templates
- 21. postgresql.conf #------------------------------------------------------------------------------ # RESOURCE USAGE (except WAL) #------------------------------------------------------------------------------ # - Memory - shared_buffers = 300MB # min 128kB # (change requires restart) Templates
- 22. postgresql.conf $shared_buffers= '300MB' $conf_template='dba/postgresql.91.conf.erb' file { "${pg_datadir}/postgresql.conf" : owner => "$pguser", group => postgres, mode => 644, require => Exec["${name}_initdb"], content => template("$conf_template"), } • $
- 23. listen_addresses and facter Puppet master Database Server Puppet agent facterFacts $::ipaddress_eth0 listen_address= '<%= scope.lookupvar('::ipaddress_eth0') %>'
- 24. Hiera 101 Separate Code From Data http://commons.wikimedia.org/wiki/File:Lower_Antelope_Canyon_478.jpg By Uploaded by Meckimac [GFDL (http://www.gnu.org/copyleft/fdl.html) or CC-BY-SA-3.0 (http://creativecommons.org/licenses/by-sa/3.0/)], via Wikimedia Commons
- 25. Hiera 101: Lookup Values class db_server { $application='postgres' $product='billing' $port = hiera('port') . . }
- 26. Hiera 101: Define a Hierarchy -- :hierarchy: -domains/%{domain}/%{fqdn}/%{application} -domains/%{domain}/%{application}/ -environment_type/%{environment_type}/%{application} -complexes/%{product}/%{application}
- 28. Hiera 101: postgres.yaml port : 5432 postgres_pgversion : '9.2.4' postgres_unixuser : postgres postgres_db_list : pgopen : ssinger pgeu : jwieck
- 29. conf template server type postgresql.90.small.conf.erb postgresql.91.small.conf.erb postgresql.92.small.conf.erb postgresql.90.oltp.conf.erb postgresql.91.oltp.conf.erb postgresql.92.oltp.conf.erb postgresql.90.batch.conf.erb postgresql.91.batch.conf.erb postgresql.92.batch.conf.erb
- 30. Switch Based Config postgresql.90.conf.erb postgresql.92.conf.erb shared_buffers = <%= shared buffers %> max_connections = <%= max_connections %> postgresql.91.conf.erb shared_buffers = <%= shared buffers %> max_connections = <%= max_connections %> shared_buffers = <%= shared buffers %> max_connections = <%= max_connections %>
- 31. Crontabs cron { “backup_${name}” : command => “ /opt/dbs/scripts/backup.sh $clustername”, user => 'postgres', hour => '0', minute => '0', ensure => 'present' }
- 32. Slony cluster name = mydbcluster node 9181 admin conninfo = 'host=db1.on1 user=slony' node 9182 admin conninfo = 'host=db2.on1 user=slony' set add table ( id =1 , fully qualified name = 'public.foo' );
- 33. Slony Settings # YAML for the slons running on a particular server postgres_slons: - 9181 # YAML map of connection data to all slons postgres_slon_nodes: '9181': 'host': 'db1.on1.afilias-int.info' 'port' : '5432' '9192': 'host': 'db2.tx1.afilias-int.info' 'port' : '5432'
- 34. Slony: Slonik preambles cluster name=<%= clustername %>; # A slonik preamble that defines # the 'node xxx admin conninfo...' data # and an associated DEFINE for each # node in the cluster # <% slon_conf.each do | node_id, values | %> <% port=values['port'] if port==nil or port=='' port=complex_port end %> node <%= node_id %> admin conninfo='host=<%= values['host'] %> ...... define CONNINFO_<%= node_id %> 'host=<%= values['host'] %> ..... <% end %> /opt/configs/$clustername/slonik.preamble : slonik_preamble.erb
- 35. Removing Things class db_node { . . } class db_node::remove { file { “$datadir”, ensure => 'absent', force => true, Backup => false } . . }
- 36. pg_basebackup if($datadir_restore_command) { exec { "${name}_initdb" : command => $datadir_restore_command, unless => "/usr/bin/test -d ${pg_datadir}/base" ... } } else { exec {"${name}_initdb" : command => /usr/bin/initdb -D ${pg_datadir} unless => "/usr/bin/test -d ${pg_datadir}/base" } }
- 37. Working with others Photo:: thegoldguys.blogspot.com