Downloaded 17 times
![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/75/Steve-Singer-Managing-PostgreSQL-with-Puppet-Postgres-Open-22-2048.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/75/Steve-Singer-Managing-PostgreSQL-with-Puppet-Postgres-Open-24-2048.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/75/Steve-Singer-Managing-PostgreSQL-with-Puppet-Postgres-Open-34-2048.jpg)
Uploaded byPostgresOpen
Steve Singer - Managing PostgreSQL with Puppet @ Postgres Open
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.
Steve Singer - Managing PostgreSQL with Puppet @ Postgres Open
- 1. Managing PostgreSQL withPuppet 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/
- 3.
- 4.
- 5.
- 6.
- 7.
- 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
- 10.
- 11.
- 12. Puppet Architecture Puppetmaster Computer Puppet agent Computer Puppetagent Computer Puppet agent
- 13. Puppet Terminology class pgsql puppetmastercatalog 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
- 16.
- 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-existingmodule 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 Puppetmaster Database Server Puppet agent facterFacts $::ipaddress_eth0 listen_address= '<%= scope.lookupvar('::ipaddress_eth0') %>'
- 24. Hiera 101 Separate CodeFrom 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: LookupValues class db_server { $application='postgres' $product='billing' $port = hiera('port') . . }
- 26. Hiera 101: Definea Hierarchy -- :hierarchy: -domains/%{domain}/%{fqdn}/%{application} -domains/%{domain}/%{application}/ -environment_type/%{environment_type}/%{application} -complexes/%{product}/%{application}
- 27.
- 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 servertype 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 # YAMLfor 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 clustername=<%= 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
- 38.