You can already find several blog posts around PostgreSQL protected by Patroni on our blog. They all have in common, that the setup is on a flavor of Linux. As I got impressed by FreeBSD and played with it during the last weeks (some blog posts here), I wanted to know if Patroni runs well on FreeBSD. Well, here are the results …
As usual you need at least three nodes for automatic fail-over to work, in my case these are:
postgres@freebsd-patroni1:/home/postgres/ [pg133] grep patroni /etc/hosts 192.168.22.71 patroni1-freebsd patroni1-freebsd.it.dbi-services.com 192.168.22.72 patroni2-freebsd patroni2-freebsd.it.dbi-services.com 192.168.22.73 patroni3-freebsd patroni3-freebsd.it.dbi-services.com
On all of these nodes PostgreSQL 13.3 is already installed in the same location:
postgres@freebsd-patroni2:/home/postgres/ [pg133] ls /u01/app/postgres/product/13/db_3/ bin include lib share
Except for this sudo configuration:
postgres@freebsd-patroni1:/home/postgres/ [pg133] sudo grep postgres /usr/local/etc/sudoers postgres ALL=(ALL) NOPASSWD: ALL
… nothing happened on these systems.
The first bit we usually install and configure is etcd but there are no pre-build binaries to download from the GitHub page. So either we need to compile it from source, or we use the packages provided by FreeBSD:
postgres@freebsd-patroni1:/home/postgres/ [pg133] sudo pkg search etcd coreos-etcd-2.3.8_4 Highly-available key value store and service discovery coreos-etcd31-3.1.20_2 Highly-available key value store and service discovery coreos-etcd32-3.2.32 Highly-available key value store and service discovery coreos-etcd33-3.3.23 Highly-available key value store and service discovery coreos-etcd34-3.4.14 Highly-available key value store and service discovery etcd-1.0.1_3 Enhanced Tiny CD, a simple ncurses-based CD player netcdf-4.7.4 C library for machine-independent, array-oriented data access netcdf-cxx-4.3.1_1 C++ library for machine-independent, array-oriented data access netcdf-fortran-4.5.3 Fortran library for machine-independent, array-oriented data access octave-forge-netcdf-1.0.14_2 Octave-forge package netcdf p5-NetCDF-1.2.4_7 Perl5 module to read and write netCDF files pnetcdf-1.8.1_10 Library providing high-performance I/O py37-netCDF4-1.3.1_8 Python Interface to the NetCDF Library (versions 3 and 4) py37-netcdf-flattener-1.2.0 Flatten NetCDF files while preserving references rubygem-ruby-netcdf-0.7.2_3 Ruby interface to the NetCDF scientific IO library setcdboot-1.0 Mark a file bootable within a DEC Alpha ISO-966
Let’s use the latest release and install the FreeBSD package (on all nodes):
postgres@freebsd-patroni1:/home/postgres/ [pg133] sudo pkg install coreos-etcd34-3.4.14
Updating FreeBSD repository catalogue...
FreeBSD repository is up to date.
All repositories are up to date.
The following 1 package(s) will be affected (of 0 checked):
New packages to be INSTALLED:
coreos-etcd34: 3.4.14
Number of packages to be installed: 1
The process will require 35 MiB more space.
9 MiB to be downloaded.
Proceed with this action? [y/N]: y
[1/1] Fetching coreos-etcd34-3.4.14.txz: 100% 9 MiB 2.3MB/s 00:04
Checking integrity... done (0 conflicting)
[1/1] Installing coreos-etcd34-3.4.14...
[1/1] Extracting coreos-etcd34-3.4.14: 100%
Once etcd is installed it needs a configuration file on each host, here they are:
postgres@freebsd-patroni1:/home/postgres/ [pg133] cat /usr/local/etc/etcd.conf name: patroni1 data-dir: /u02/pgdata/etcd initial-advertise-peer-urls: http://192.168.22.71:2380 listen-peer-urls: http://192.168.22.71:2380 listen-client-urls: http://192.168.22.71:2379,http://localhost:2379 advertise-client-urls: http://192.168.22.71:2379 initial-cluster: patroni1=http://192.168.22.71:2380,patroni2=http://192.168.22.72:2380,patroni3=http://192.168.22.73:2380 enable-v2: true postgres@freebsd-patroni1:/home/postgres/ [pg133] ssh patroni2-freebsd cat /usr/local/etc/etcd.conf name: patroni2 data-dir: /u02/pgdata/etcd initial-advertise-peer-urls: http://192.168.22.72:2380 listen-peer-urls: http://192.168.22.72:2380 listen-client-urls: http://192.168.22.72:2379,http://localhost:2379 advertise-client-urls: http://192.168.22.72:2379 initial-cluster: patroni1=http://192.168.22.71:2380,patroni2=http://192.168.22.72:2380,patroni3=http://192.168.22.73:2380 enable-v2: true postgres@freebsd-patroni1:/home/postgres/ [pg133] ssh patroni3-freebsd cat /usr/local/etc/etcd.conf name: patroni3 data-dir: /u02/pgdata/etcd initial-advertise-peer-urls: http://192.168.22.73:2380 listen-peer-urls: http://192.168.22.73:2380 listen-client-urls: http://192.168.22.73:2379,http://localhost:2379 advertise-client-urls: http://192.168.22.73:2379 initial-cluster: patroni1=http://192.168.22.71:2380,patroni2=http://192.168.22.72:2380,patroni3=http://192.168.22.73:2380 enable-v2: true
Testing the configuration can easily be done by starting etcd on all nodes:
postgres@freebsd-patroni2:/home/postgres/ [pg133] etcd --config-file /usr/local/etc/etcd.conf
Once up and running on all three nodes, verify that all is fine with etcd:
postgres@freebsd-patroni1:/home/postgres/ [pg133] etcdctl endpoint status --endpoints=192.168.22.71:2379,192.168.22.72:2379,192.168.22.73:2379 -w table +--------------------+------------------+---------+---------+-----------+------------+-----------+------------+--------------------+--------+ | ENDPOINT | ID | VERSION | DB SIZE | IS LEADER | IS LEARNER | RAFT TERM | RAFT INDEX | RAFT APPLIED INDEX | ERRORS | +--------------------+------------------+---------+---------+-----------+------------+-----------+------------+--------------------+--------+ | 192.168.22.71:2379 | 52009b42a701a943 | 3.4.14 | 25 kB | true | false | 13 | 10 | 10 | | | 192.168.22.72:2379 | 26d9ce490122dc7a | 3.4.14 | 25 kB | false | false | 13 | 10 | 10 | | | 192.168.22.73:2379 | 6141508d7bd79cb5 | 3.4.14 | 25 kB | false | false | 13 | 10 | 10 | | +--------------------+------------------+---------+---------+-----------+------------+-----------+------------+--------------------+--------+
The next step is to configure automatic start of etcd when the nodes comes up. This is a bit different in FreeBSD than what you know from Linux. A detailed guide on how to do that can be found here. We’ll be using this simple script (you should of course extend that script to provide a stop command, add logging to a logfile …):
postgres@freebsd-patroni1:/home/postgres/ [pg133] cat /etc/rc.d/etcd #!/usr/local/bin/bash # PROVIDE: ETCD # REQUIRE: DAEMON FILESYSTEMS NETWORKING # BEFORE: LOGIN # KEYWORD: shutdown . /etc/rc.subr name="etcd" rcvar=etcd_enable start_cmd="su - postgres -c '/usr/sbin/daemon -c -f /usr/local/bin/etcd --config-file /usr/local/etc/etcd.conf'" load_rc_config $name run_rc_command "$1"
To test if it basically works use the “onestart” argument:
ostgres@freebsd-patroni1:/home/postgres/ [pg133] sudo /etc/rc.d/etcd onestart [WARNING] Deprecated '--logger=capnslog' flag is set; use '--logger=zap' flag instead 2021-06-03 17:31:49.344284 I | etcdmain: Loading server configuration from "/usr/local/etc/etcd.conf". Other configuration command line flags and environment variables will be ignored if provided. 2021-06-03 17:31:49.344428 I | etcdmain: etcd Version: 3.4.14 2021-06-03 17:31:49.344461 I | etcdmain: Git SHA: Not provided (use ./build instead of go build) ...
Looks good, let’s add it to rc.conf so etcd will startup automatically:
postgres@freebsd-patroni1:/home/postgres/ [pg133] grep etcd /etc/rc.conf etcd_enable="YES"
Once you’ve done that on all nodes, reboot and check that all is fine:
postgres@freebsd-patroni1:/home/postgres/ [pg133] sudo init 6 postgres@freebsd-patroni1:/home/postgres/ [pg133] ssh patroni2-freebsd init 6 postgres@freebsd-patroni1:/home/postgres/ [pg133] ssh patroni3-freebsd init 6 # wait for reboot postgres@freebsd-patroni1:/home/postgres/ [pg133] etcdctl endpoint status --endpoints=192.168.22.71:2379,192.168.22.72:2379,192.168.22.73:2379 -w table +--------------------+------------------+---------+---------+-----------+------------+-----------+------------+--------------------+--------+ | ENDPOINT | ID | VERSION | DB SIZE | IS LEADER | IS LEARNER | RAFT TERM | RAFT INDEX | RAFT APPLIED INDEX | ERRORS | +--------------------+------------------+---------+---------+-----------+------------+-----------+------------+--------------------+--------+ | 192.168.22.71:2379 | 52009b42a701a943 | 3.4.14 | 33 kB | false | false | 146 | 16 | 16 | | | 192.168.22.72:2379 | 26d9ce490122dc7a | 3.4.14 | 33 kB | true | false | 146 | 16 | 16 | | | 192.168.22.73:2379 | 6141508d7bd79cb5 | 3.4.14 | 25 kB | false | false | 146 | 16 | 16 | | +--------------------+------------------+---------+---------+-----------+------------+-----------+------------+--------------------+--------+
So far, so good for etcd. The next step is to install Patroni, and this is not different from an installation on Linux (no surprise here, it is based on Python):
postgres@freebsd-patroni1:/home/postgres/ [pg133] sudo pkg install py37-pip postgres@freebsd-patroni1:/home/postgres/ [pg133] sudo pip install --upgrade setuptools postgres@freebsd-patroni1:/home/postgres/ [pg133] pip install --user psycopg2-binary postgres@freebsd-patroni1:/home/postgres/ [pg133] pip install --user python-etcd postgres@freebsd-patroni1:/home/postgres/ [pg133] pip install --user wheel postgres@freebsd-patroni1:/home/postgres/ [pg133] pip install --user patroni[etcd]
The configuration of Patroni is of course not different than on Linux, here is an example for the first host:
postgres@freebsd-patroni1:/home/postgres/ [pg133] cat /usr/local/etc/patroni.yml
scope: postgres_patroni
name: pg-patroni1
restapi:
listen: 192.168.22.71:8008
connect_address: 192.168.22.71:8008
etcd:
hosts: 192.168.22.71:2379,192.168.22.72:2379,192.168.22.73:2379
log:
dir: /u02/log/patroni/
file_num: 14
file_size: 10000000
bootstrap:
dcs:
ttl: 30
loop_wait: 10
retry_timeout : 10
maximum_lag_on_failover: 1048576
postgresql:
use_pg_rewind: true
use_slots: true
parameters:
wal_keep_segments: 100
initdb:
- encoding: UTF8
- data-checksums
pg_hba:
- host replication all 0.0.0.0/0 md5
- host all all 0.0.0.0/0 md5
- hostssl all all 0.0.0.0/0 md5
postgresql:
listen: "*:5432"
connect_address: 192.168.22.71:5432
data_dir: /u02/pgdata/13/PG1
bin_dir: /u01/app/postgres/product/13/db_3/bin
authentication:
replication:
username: postgres
password: postgres
superuser:
username: postgres
password: postgres
parameters:
logging_collector: 'on'
log_truncate_on_rotation: 'on'
log_filename: 'postgresql-%a.log'
log_rotation_age: '1440'
log_line_prefix: '%m - %l - %p - %h - %u@%d - %x'
log_directory: '/u02/pgdata/13/PG1/pg_log'
log_min_messages: 'WARNING'
log_autovacuum_min_duration: '60s'
log_min_error_statement: 'NOTICE'
log_min_duration_statement: '30s'
log_checkpoints: 'on'
log_statement: 'ddl'
log_lock_waits: 'on'
log_temp_files: '0'
log_timezone: 'Europe/Zurich'
log_connections: off
log_disconnections: off
log_duration: off
checkpoint_completion_target: 0.9
checkpoint_timeout: '20min'
client_min_messages: 'WARNING'
wal_level: 'replica'
hot_standby_feedback: 'on'
max_wal_senders: '10'
max_replication_slots: '10'
shared_preload_libraries: 'pg_stat_statements'
autovacuum_vacuum_threshold: 50
archive_mode: 'on'
archive_command: '/bin/true'
cluster_name: 'PG1'
If all is fine, you should be able to bootstrap the first node:
postgres@freebsd-patroni1:/home/postgres/ [pg133] .local/bin/patroni /usr/local/etc/patroni.yml
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "C.UTF-8".
The default text search configuration will be set to "english".
Data page checksums are enabled.
creating directory /u02/pgdata/13/PG1 ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Europe/Vaduz
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
/u01/app/postgres/product/13/db_3/bin/pg_ctl -D /u02/pgdata/13/PG1 -l logfile start
localhost:5432 - no response
2021-06-03 18:39:24.699 CEST - 1 - 1495 - - @ - 0LOG: redirecting log output to logging collector process
2021-06-03 18:39:24.699 CEST - 2 - 1495 - - @ - 0HINT: Future log output will appear in directory "/u02/pgdata/13/PG1/pg_log".
localhost:5432 - accepting connections
localhost:5432 - accepting connections
Do exactly the same on the remaining to nodes and then create the rc script for Patroni (note the dependency on etcd):
postgres@freebsd-patroni1:/home/postgres/ [pg133] sudo cat /etc/rc.d/patroni #!/usr/local/bin/bash # PROVIDE: PATRONI # REQUIRE: ETCD # BEFORE: LOGIN # KEYWORD: shutdown . /etc/rc.subr name="patroni" rcvar=patroni_enable start_cmd="su - postgres -c '/usr/sbin/daemon -c -f /home/postgres/.local/bin/patroni /usr/local/etc/patroni.yml'" load_rc_config $name run_rc_command "$1"
Enable autostart in rc.conf:
postgres@freebsd-patroni1:/home/postgres/ [pg133] grep patroni /etc/rc.conf hostname="freebsd-patroni1.it.dbi-services.com" patroni_enable="YES"
… and reboot all nodes. The result should be this:
postgres@freebsd-patroni1:/home/postgres/ [pg133] .local/bin/patronictl -c /usr/local/etc/patroni.yml list + Cluster: postgres_patroni (6969608203440481745) +----+-----------+ | Member | Host | Role | State | TL | Lag in MB | +-------------+---------------+---------+---------+----+-----------+ | pg-patroni1 | 192.168.22.71 | Leader | running | 5 | | | pg-patroni2 | 192.168.22.72 | Replica | running | 5 | 0 | | pg-patroni3 | 192.168.22.73 | Replica | running | 5 | 0 | +-------------+---------------+---------+---------+----+-----------+
Works as expected.
![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/DWE_web-min-scaled.jpg)