Wednesday, January 8, 2014

Puppet manifest for PostgreSQL Master / Slave setup


# 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";
       
    }

}