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:
  10: postgresql10
  11: postgresql

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

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

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 "doas", "-u", "_postgresql", "#{pgbin}/initdb",
                "-D", "/pg_data/#{pgver}", "--encoding=UTF8", "--locale=en_US.UTF-8"
        else
          run "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/8  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'
    require 'tempfile'

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

    # Replica configuration
    tmp_recovery = Tempfile.new
    case role
        when "query", "query2", "query3"
            conf = <<~EOF
                standby_mode = 'on'
                primary_conninfo = 'user=_postgresql host=#{master}'
                recovery_target_timeline = 'latest'
            EOF
            tmp_recovery.write(conf)
    end
    tmp_recovery.close

    run %W{ ./rsub -r [#]listen_addresses.* -l listen_addresses\ =\ '*'
            /pg_data/#{pgver}/postgresql.conf }
    conf = <<-CONF
    wal_level = hot_standby
    CONF
    out, = Open3.capture2("./rsub", "/pg_data/#{pgver}/postgresql.conf", :stdin_data=>conf)
    print out

    if File.exists? "/pg_data/#{pgver}/recovery.conf"
        run %W{ ./rinstall -o _postgresql:_postgresql #{tmp_recovery.path}
                /pg_data/#{pgver}/recovery.conf }
    end

    # Reload config
    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 "#{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

Last updated on September 26, 2019