Infrastructure at your Service

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:

p[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.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Daniel Westermann
Daniel Westermann

Principal Consultant & Technology Leader Open Infrastructure