PostgreSQL Declaration

A methodical specification for instantiating and managing PostgreSQL databases using Salt. Features included are intended to be easy to use and be widely applicable.

Supported Platforms

Enhancements to this project were tested on the following platforms

  • RHEL 7/CentOS 7
  • Fedora 26
  • Debian 9
  • Ubuntu 17
  • OpenBSD 6.2

PostgreSQL Installation

Out of the box the following major versions are set to install

  • 10
  • 9.6

SSH Keys

Replica verification scripts require an SSH key to be used between hosts. (Enter for a blank password)

ssh-keygen -t ecdsa -f postgres/id_ecdsa -C postgres@localhost
chown :salt postgres/id_ecdsa
chmod g+r postgres/id_ecdsa

Also, in many cases the ability to copy files and scripts between hosts is very useful.

Minion Initialization

Each client is expected to push network.ip_addrs to the Salt mine. This data can be collected when a machine is first booted using Salt's mine.send function from each machine

salt-call mine.send network.ip_addrs

At any time can be cleared using

salt '*' mine.delete network.ip_addrs

And refresh by selecting an interface, subnet or private/private address

salt '*' mine.send network.ip_addrs interface=vio0
salt '*' mine.send network.ip_addrs cidr=''
salt '*' mine.send network.ip_addrs type=private
salt '*' mine.send network.ip_addrs type=public

Server Initialization

List all connected hosts and test connectivity

salt-key -L
salt '*'

Then bring up each server

salt 'db1.*' state.highstate
salt 'db2.*' state.highstate

Or all at once

salt '*' state.highstate

The convention used is to instantiate databases under /pg_data/{{pgver}}.

Replication Status

Get the status for a master and connected standby servers using

salt-run state.sls replication-status pillar='{dbname: localharvestdb}'

Promote New Master

Promoting a new master is handled in nearly the same way that any other configuration is. The only requirement is that the previous master be stopped first. A Salt runner can be employed to enforce this rule

vim pillar/hostmap.sls
salt-run state.sls postgres-failover.localharvestdb