rset(1) : Formulas

PostgreSQL Instances

The following tutorial demonstrates how to define the relationship between services using

  1. A configuration file (yaml)
  2. A small library of utility functions (ruby)
  3. Scripts for applying the configuration (rset, ruby)

Service Relationships

I will start by writing a configuration file called _rutils/hostmap.yml that rset(1) will distribute to each host 


# writer   : The master where connections are enabled
# queryN   : Standby servers

services:
  11: postgresql11
  12: postgresql

binaries:
  11: /usr/local/bin/postgresql-11
  12: /usr/local/bin

databases:
  localharvestdb:
    role_assignment:
      writer: 172.16.0.2
      query: 172.16.0.3
    major_version: 12

Now we can define any number of features for each database. To make parsing the configuration easy I would write a small utility library and install it in _rutils/hostmap.rb 

#!/usr/local/bin/ruby

require 'socket'
require 'yaml'

# utility functions

def my_addr()
  ip = Socket.ip_address_list.detect{|intf| intf.ipv4_private?}
  ip.ip_address
end

# a module for interpreting hostmap.yml

module Hostmap
  @@hostmap = YAML.load_file("hostmap.yml")

  def self.db_name()
    addr = my_addr()
    @@hostmap['databases'].each do |db, config|
      return db if config['role_assignment'].values.include? addr
    end
    raise "#{addr} not found"
  end

  def self.role()
    addr = my_addr()
    @@hostmap['databases'].each do |db, config|
      config['role_assignment'].each do |role, host|
        return role if host == addr
      end
    end
    raise "#{addr} not found"
  end

  def self.pgbin(pgver)
    @@hostmap['binaries'][pgver.to_i]
  end

  def self.pgservice(pgver)
    @@hostmap['services'][pgver.to_i]
  end

  def self.master()
    @@hostmap['databases'][db_name()]['role_assignment']['writer']
  end

  def self.pgver()
    @@hostmap['databases'][db_name()]['major_version']
  end

  def self.neighbors()
    roles = @@hostmap['databases'][db_name()]['role_assignment'].values
    roles.delete(my_addr())
    roles
  end
end

This answers all of the common questions we need in order to initialize or reconfigure a database. Since I'll be importing this as a library, there is one more useful function which will provide a shortcut for running commands: 

def run(args)
    pid = spawn(*args)
    _, status = Process.waitpid2(pid)
    status.success?
end

Database Initialization & Configuration

rset can be used with any interpreter capable of executing a script provided on STDIN 

execute_with=doas
interpreter=/usr/local/bin/ruby -I .

init-database:
    require 'hostmap'

    pgver = Hostmap.pgver()
    pgbin = Hostmap.pgbin(pgver)
    role = Hostmap.role()
    master = Hostmap.master()

    exit if File.exists? "/pg_data/#{pgver}/PG_VERSION"

    # Initialize database
    case role
        when "writer"
            run %W{ doas -u _postgresql #{pgbin}/initdb
                    -D /pg_data/#{pgver} --encoding=UTF8 --locale=en_US.UTF-8 }
        else
            run %W{ doas -u _postgresql #{pgbin}/pg_basebackup -R
                    -D /pg_data/#{pgver} -h #{master} }
    end

This will initialize a new database if the host is a master, or use pg_basebackup to create a replica.

The step is to adjust pg_hba.conf and postgresql.conf . 

set-pghba:
    require 'hostmap'
    require 'open3'

    pgver = Hostmap.pgver()
    neighbors = Hostmap.neighbors()

    conf = <<-CONF
    host all all 172.0.0.0/24  password
    CONF

    neighbors.each { |n|
       conf << "host replication _postgresql #{n}/32 trust"
    }

    out, = Open3.capture2("./rsub", "/pg_data/#{pgver}/pg_hba.conf", :stdin_data=>conf)
    print out

set-pgconf:
    require 'hostmap'
    require 'open3'

    pgver = Hostmap.pgver()
    pgservice = Hostmap.pgservice(pgver)
    pgbin = Hostmap.pgbin(pgver)
    role = Hostmap.role()
    master = Hostmap.master()

    run %W{ ./rsub -r [#]listen_addresses.* -l listen_addresses\ =\ '*'
            /pg_data/#{pgver}/postgresql.conf }

    # Replica configuration
    case role
        when "query", "query2", "query3"
            conf = <<~EOF
                wal_level = logical
                archive_mode = on
                primary_conninfo = 'user=_postgresql host=#{master}'
            EOF
            run %W{ doas -u _postgresql touch /pg_data/#{pgver}/standby.signal }
        else
            conf = <<-CONF
            wal_level = logical
            CONF
    end

    out, = Open3.capture2("./rsub", "/pg_data/#{pgver}/postgresql.conf", :stdin_data=>conf)
    print out

    # Promote a new master
    case role
        when "writer"
            if File.exists? "/pg_data/#{pgver}/standby.signal"
                run %W{ doas -u _postgresql #{pgbin}/pg_ctl promote
                        -D /pg_data/#{pgver} }
            end
    end

    # Always try a graceful reload
    run %W{ rcctl reload #{pgservice} }

Finally, ensure the service is enabled and start it 

isready:
    require 'hostmap'

    pgver = Hostmap.pgver()
    pgservice = Hostmap.pgservice(pgver)
    pgbin = Hostmap.pgbin(pgver)

    run %W{ rcctl enable #{pgservice} }

    [0..5].each do |i|
        sleep 2
        if not run %W{ #{pgbin}/pg_isready -q -h localhost }
            run %W{ rcctl restart #{pgservice} }
        end
    end

Further Endhancements

Once you have a mechanism for defining configuration and applying it with a full-featured scripting language, new features are easy to add. In a complex environment there may be many more features such as