Create a simple streaming replication for postgres with puppet

I need to build a postgres setup for a important database. The idea is to one master and one-many slaves that can serve read only access. The slaves will work in a hot-standby mode where they continuously receive data from the master. The replication will be synchronous, this means a client that inserts data will get the acknowledgement only if the slave(s) and the master successfully wrote the data to disk. My puppet profile is tested on centos7 machines and uses the puppetlabs/postgresql module in version 4.7.1 and my defined resource for ssh key exchange (the last post). The profile does a few assumptions:

• for example on the hostnames of the machines. they have to be myawesomemachine01, myawesomemachine02 and so on, the trailing numbers are important (one digit is sufficient)
• The first machine will always be the master, the rest configured as slaves
• my master is called psql01.something.com, slaves are psql02.something.com and psql02.something.com
• We do ssh key exchange for the postgres and root user (root needed for corosync later, postgres for initial setup)
• IP of the master is 10.254.4.18 (I need to automate that somehow)

Here is the profile:

class profiles::postgrescluster {
  $password = 'mytotalllysecurepassword'
  $replicationuser = 'repl'
  # psql01 is our master, 02 the slave, if we ever promote the slave we should autokill puppet
  case $::hostname {
    'psql01': {
      $manage_recovery_conf = false
      postgresql::server::config_entry{'synchronous_standby_names':
        value => '*',
      }
    }
    default: {
      $manage_recovery_conf = true
      postgresql::server::config_entry{'hot_standby':
        value => 'on',
      }
      postgresql::server::recovery{'Create a recovery.conf':
        standby_mode      => 'on',
        primary_conninfo  => "host=10.254.4.18 port=5432 user=$replicationuser password=$password",
      }
    }
  }
  class { '::postgresql::globals':
    encoding            => 'UTF-8',
    locale              => 'en_US.UTF-8',
    manage_package_repo => true,
    version             => '9.5',
    manage_recovery_conf  => $manage_recovery_conf,
  }->
  class { '::postgresql::server':
    listen_addresses      => "localhost,${::ipaddress_eth0}",
  }
  package{['pg_activity', 'pgtune', 'zabbix-sender']:
    ensure  => 'present',
  } ->
  # change shell so su is allowed
  user{'postgres':
    shell           => '/bin/bash',
    home            => '/var/lib/pgsql',
    purge_ssh_keys  => true,
  } ->
  file{'/var/lib/pgsql/.ssh':
    ensure  => 'directory',
    owner   => 'postgres',
    group   => 'postgres',
  }
  # installs the contrib package
  include ::postgresql::server::contrib'
  # we need ssh key exchange for two users
  $type = 'ed25519'

  $myhash = {root => '/root', postgres => '/var/lib/pgsql'}
  $myhash.each |$sshuser, $homepath| {
    ## create ssh key for $sshuser
    base::ssh_keygen{$sshuser:
      type  => $type,
      home  => $homepath,
    }
    ## export it
    $pubkey = getvar("::${sshuser}_${type}_pubkey")
    $comment = getvar("::${sshuser}_${type}_comment")
    if $pubkey and $comment {
      @@ssh_authorized_key{$comment:
        ensure  => 'present',
        type    => $type,
        options => ['no-port-forwarding', 'no-X11-forwarding', 'no-agent-forwarding' ],
        user    => $sshuser,
        key     => $pubkey,
        tag     => 'postgrescluster',
      }
    }
    # collect it
    Ssh_Authorized_Key <<| tag == 'postgrescluster' and title != $comment |>>
  }

  ## export host key
  if $::sshecdsakey {
    @@sshkey{$::fqdn:
      host_aliases  => $::ipaddress,
      type          => 'ecdsa-sha2-nistp256',
      key           => $::sshecdsakey,
      # defaults to /etc/ssh/ so all users can use it
      #target       => '/root/.ssh/known_hosts',
      tag           => 'postgrescluster',
    }
  }
  ## import host key
  Sshkey <<| tag == 'postgrescluster' and title != $::fqdn |>>

  ## setup replication user
  postgresql::server::role {$replicationuser:
    login => true,
    replication => true,
    password_hash => postgresql_password($replicationuser, $password),
  }
  postgresql::server::pg_hba_rule{'allow replication user to access server':
    type        => 'host',
    database    => 'replication',
    user        => $replicationuser,
    address     => '10.254.4.0/24', # TODO resrict to /32
    auth_method => 'md5',
  }

  # configure replication, this is only needed on master
  postgresql::server::config_entry{'wal_level':
    value => 'hot_standby',
  }
  postgresql::server::config_entry{'max_wal_senders':
    value => 5,
  }
  postgresql::server::config_entry{'wal_keep_segments':
    value => 32,
  }
  postgresql::server::config_entry{'archive_mode':
    value => 'on',
  }
  postgresql::server::config_entry{'archive_command':
    value => 'cp %p /mnt/backup/%f',
  }
}

what does this all do? This profile can be applied to all postgres nodes in the setup. It will configure the postgresql.org repository and install version 9.5 (9.6 is out soon \o/). The initial setup of the replication is PITA because we need to copy the DB content from the master to the slaves. This is very dirty and the fastest solution to implement I could figure out was doing this by hand :sadface:
We need to do the following on the master after a puppet run happend on master + all slaves:
On the slave:

systemctl stop puppet postgres-9.5

On the master:

su postgres
cd ~
psql -c "SELECT pg_start_backup('label', true)"
rsync -cva --inplace --exclude=*postmaster.pid* /var/lib/pgsql/9.5/data SLAVENODE:/var/lib/pgsql/9.5/data
psql -c "SELECT pg_stop_backup()"

then again on the slave:

puppet agent -t

Since postgres 9.1 the command pg_basebackup is available which is an alternative solution for the rsync (and works from the slave to the master – pulling, not pushing from the master to the slave). However I had a bit of time pressure and it didn’t work on the first try, so I made the fallback to rsync. Let me know if you’ve got some ideas on how to make this more reliable and automated!

In an upcoming post I will discuss the setup to automatically promote a slave as new master if one dies including the management of a service IP (handled by corosync/pacemaker).