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:
[email protected]:/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:
[email protected]:/home/postgres/ [pg133] ls /u01/app/postgres/product/13/db_3/ bin include lib share
Except for this sudo configuration:
[email protected]:/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:
[email protected]:/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):
[email protected]:/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:
[email protected]:/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 [email protected]:/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 [email protected]:/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:
[email protected]:/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:
[email protected]:/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 …):
[email protected]:/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:
[email protected]:/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:
[email protected]:/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:
[email protected]:/home/postgres/ [pg133] sudo init 6 [email protected]:/home/postgres/ [pg133] ssh patroni2-freebsd init 6 [email protected]:/home/postgres/ [pg133] ssh patroni3-freebsd init 6 # wait for reboot [email protected]:/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):
[email protected]:/home/postgres/ [pg133] sudo pkg install py37-pip [email protected]:/home/postgres/ [pg133] sudo pip install --upgrade setuptools [email protected]:/home/postgres/ [pg133] pip install --user psycopg2-binary [email protected]:/home/postgres/ [pg133] pip install --user python-etcd [email protected]:/home/postgres/ [pg133] pip install --user wheel [email protected]:/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:
[email protected]:/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 - %[email protected]%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:
[email protected]:/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):
[email protected]:/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:
[email protected]:/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:
[email protected]:/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)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/10/MDI_web-min-scaled.jpg)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/JGU_web-min-scaled.jpg)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/09/SNA_web-min-scaled.jpg)