PostgreSQL Instances
The following tutorial demonstrates how to define the relationship between services using
- A configuration file (yaml)
- A small library of utility functions (ruby)
- 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
- Auto-upgrade from one release to the next
- Implement delayed replicas
-
Set
shared_buffers,
work_mem,
andmaintenance_work_mem
based on physical RAM - Install maintenance cron jobs
- Configure PgBouncer and pgBackRest
-
Display information from
pg_control_checkpoint()
andpg_stat_replication