# DevOps manifest for setting up primary / warm standby postgres servers
# These postgres are created in pairs with the first being master.
#
# Requires PuppetLabs PostgreSQL module from puppetlabs.com
#
# mfeenstra 12/16/13
# Master Postgres DB
node /your-master-server.com/ {
class { "yum":
enable_base_6_4 => '1',
enable_updates_6_4 => '1',
enable_base_6_3 => '1',
enable_updates_6_3 => '1',
enable_base_6_2 => '1',
enable_updates_6_2 => '1',
enable_base_6_1 => '1',
enable_updates_6_1 => '1',
move_system => 'yes',
epel => 'yes',
pgdg => 'yes',
sernet_samba => 'yes'
}
class { "sudoers":
sudoer => {
"%ops" => [ "ALL=(ALL) ALL" ],
"%secteam" => [ "ALL=(ALL) ALL" ],
"%wheel" => [ "ALL=(ALL) ALL" ],
"%dev" => [ "ALL=(ALL) ALL" ],
"%dbaadmins" => [ "ALL=(ALL) ALL" ],
"%releng" => [ "ALL=(ALL) ALL" ]
}
}
class { "base": }
class { "corp": }
class { "motd": description => 'add your server description here' }
class { "ssh":
pamsshd => {
"dev" => [ "account sufficient pam_succeed_if.so user ingroup" ],
"dbadmins" => [ "account sufficient pam_succeed_if.so user ingroup" ],
"releng" => [ "account sufficient pam_succeed_if.so user ingroup" ],
}
}
# mount filesystem from warm-standby server
file { "/var/lib/pgsql/wal_archive":
ensure => directory,
owner => 'postgres',
group => 'postgres',
mode => 0755,
path => "/var/lib/pgsql/wal_archive"
}
mount { "/var/lib/pgsql/wal_archive":
device => "your-slave-server.com:/var/lib/pgsql/wal_archive",
fstype => "nfs",
ensure => "mounted",
options => "defaults,rw",
atboot => "true",
require => File["/var/lib/pgsql/wal_archive"]
}
### install extra packages
package { "postgresql91-devel":
ensure => present,
require => Package["postgresql91-server"]
}
package { "postgresql91-contrib":
ensure => present,
require => Package["postgresql91-server"]
}
### postgresql91 db configuration
class { "postgresql::globals":
version => '9.1'
}
# package installation and service auto-start are handled by this class
class { "postgresql::server":
ensure => present,
listen_addresses => "*",
encoding => "UTF8",
locale => "en_US.UTF-8",
ip_mask_allow_all_users => "10.0.0.0/8"
}
postgresql::server::role { "myapplication_name":
password_hash => postgresql_password('someuser', 'somepassword'),
createdb => true,
superuser => true,
require => Class["postgresql::server"]
}
# postgresql::server::db { "testdb1":
# user => "alertsapp",
# password => "alertsapp",
# require => Class["postgresql::server"]
# }
postgresql::server::config_entry {
'port' : value => 5432;
'max_connections' : value => 300;
'shared_buffers' : value => "128MB";
'work_mem' : value => "50MB";
'checkpoint_segments' : value => 32;
'log_destination' : value => 'stderr';
'logging_collector' : value => "on";
'log_directory' : value => "pg_log";
'log_filename' : value => "postgresql-%a.log";
'log_truncate_on_rotation' : value => "on";
'log_rotation_age' : value => "1d";
'log_rotation_size' : value => 0;
'log_min_duration_statement' : value => 500;
'datestyle' : value => "iso, mdy";
'lc_messages' : value => "en_US.UTF-8";
'lc_monetary' : value => "en_US.UTF-8";
'lc_numeric' : value => "en_US.UTF-8";
'lc_time' : value => "en_US.UTF-8";
'default_text_search_config' : value => "pg_catalog.english";
############## Master-only config setup (enable archiving) #############
'wal_level' : value => "archive";
'archive_mode' : value => "on";
# this command is run to move logs from pg_xlog to the "WAL archive" directory
'archive_command' : value => 'cp %p /var/lib/pgsql/wal_archive/%f';
'archive_timeout' : value => '120';
}
# postgresql::server::pg_hba_rule { 'allow network app to access db':
# type => 'host',
# database => 'all',
# user => 'all',
# address => '10.0.0.0/8',
# auth_method => 'md5',
# require => Class["postgresql::server"]
# }
}
# Warm-standby Postgres DB
node /your-slave-server.com/ {
class { "yum":
enable_base_6_4 => '1',
enable_updates_6_4 => '1',
enable_base_6_3 => '1',
enable_updates_6_3 => '1',
enable_base_6_2 => '1',
enable_updates_6_2 => '1',
enable_base_6_1 => '1',
enable_updates_6_1 => '1',
move_system => 'yes',
epel => 'yes',
pgdg => 'yes',
sernet_samba => 'yes'
}
class { "sudoers":
sudoer => {
"%ops" => [ "ALL=(ALL) ALL" ],
"%secteam" => [ "ALL=(ALL) ALL" ],
"%wheel" => [ "ALL=(ALL) ALL" ],
"%dev" => [ "ALL=(ALL) ALL" ],
"%dbaadmins" => [ "ALL=(ALL) ALL" ],
"%releng" => [ "ALL=(ALL) ALL" ]
}
}
class { "base": }
class { "corp": }
class { "motd": description => 'add your server description here' }
class { "ssh":
pamsshd => {
"dev" => [ "account sufficient pam_succeed_if.so user ingroup" ],
"dbadmins" => [ "account sufficient pam_succeed_if.so user ingroup" ],
"releng" => [ "account sufficient pam_succeed_if.so user ingroup" ],
}
}
# Setup filesystem and export for wal_archive.
#
# This FS is shared between the master and standby, facilitating the transfer
# or "log shipping" of archive logs across the network.
#
# note: the standby server should export the archive filesystem, in case master
# goes down. The master will mount and write to this disk. Otherwise the shared
# disk should be NFS mounted.
# Cannot mount /var/lib/pgsql with default permission 0700, changed to 755 within the
# postgres module install.pp due to dependency problems
file { "/var/lib/pgsql/wal_archive":
ensure => directory,
owner => 'postgres',
group => 'postgres',
mode => 0755,
path => "/var/lib/pgsql/wal_archive",
require => Package["postgresql91-contrib"]
}
# allow master server to mount
file { "/etc/exports":
notify => Service["nfs"],
ensure => present,
owner => 'root',
group => 'root',
mode => '644',
content => "/var/lib/pgsql/wal_archive master-server-name-here.com(rw,sync)"
}
service { "nfs":
ensure => running,
require => File["/etc/exports"]
}
### install extra packages
package { "postgresql91-devel":
ensure => present,
}
package { "postgresql91-contrib":
ensure => present,
}
### postgresql91 db configuration
class { "postgresql::globals":
version => '9.1'
}
# package installation and service auto-start are handled by this class
class { "postgresql::server":
ensure => present,
listen_addresses => "*",
encoding => "UTF8",
locale => "en_US.UTF-8",
ip_mask_allow_all_users => "10.0.0.0/8",
}
postgresql::server::role { "myapplication_name":
password_hash => postgresql_password('someuser', 'somepass'),
createdb => true,
superuser => true,
require => Class["postgresql::server"]
}
postgresql::server::config_entry {
'port' : value => 5432;
'max_connections' : value => 300;
'shared_buffers' : value => "128MB";
'work_mem' : value => "50MB";
'checkpoint_segments' : value => 32;
'log_destination' : value => 'stderr';
'logging_collector' : value => "on";
'log_directory' : value => "pg_log";
'log_filename' : value => "postgresql-%a.log";
'log_truncate_on_rotation' : value => "on";
'log_rotation_age' : value => "1d";
'log_rotation_size' : value => 0;
'log_min_duration_statement' : value => 500;
'datestyle' : value => "iso, mdy";
'lc_messages' : value => "en_US.UTF-8";
'lc_monetary' : value => "en_US.UTF-8";
'lc_numeric' : value => "en_US.UTF-8";
'lc_time' : value => "en_US.UTF-8";
'default_text_search_config' : value => "pg_catalog.english";
}
}
