Version: 1.5

GitHub

Playbook requires the following component’s version to be installed:

Startup preparation is performed on the node from which ansible-playbook will be launched, and includes the following steps:

After filling in the my_inventory file, it is recommended to make sure that all servers are available to connect to them via SSH with the required user. To do this, run the following command in the terminal:

ansible all -i inventory/my_inventory -m ansible.builtin.setup -a "filter=ansible_hostname" -u admin_user

The result of the command above will be a response from each of the available servers (virtual machines) in the following format:

<hostname_from_inventory_file> | SUCCESS => {
    "ansible_facts": {
        "ansible_hostname": "<device_hostname>",
        "discovered_interpreter_python": "/usr/bin/<host_python_version>"
    },
    "changed": false
}

This output for each server described in my_inventory file means successful connection to it via SSH. If as a result of the response from any server (virtual machine) the message differed from the above template - check whether it is possible to connect to it via a key from the user name passed using the -u flag. If it is necessary to connect only with password entry (without using keys) - it is necessary to add -kK flags to the command launch and enter the password for SSH connection (-k flag) and for user to switch to privileged mode (root) (-K flag).

Pay attention to the value of the ansible_hostname variable in the command output. If the value is localhost or localhost.localdomain, check the /etc/hosts file of the machines with incorrect output. Ensure that the real device hostname is set before localhost on the line containing 127.0.0.1.

The playbook allows the possibility of separating the pg_data, pg_wal and pg_log directories. If it is necessary to place WAL logs in a separate folder, it is required to make changes to the inventory/groupvars/patroni.yml file:

In case it is necessary to place LOGs: remove the comment for the variable patroni_pg_pg_log_dir and in it specify the directory for placing LOG logs;

One of the playbook tasks is executed on the same node from which ansible is launched (control server). In case the user under which ansible is run does not have passwordless access to root mode on this server, it is necessary to add the -K flag to the start command and enter the password.

By default, the playbook does not attempt to connect to Tantor repositories and requires the following packages to be available within the system:

Pay attention to last point from the list above. Tantor package should match environment that is used during playbook launch. For example if you want to install tantor-se-1c-server-16 DBMS using command ansible-playbook -i inventory/my_inventory -u admin_user -e "postgresql_vendor=tantordb edition=se-1c major_version=16" pg-cluster.yaml -K make sure that package tantor-se-1c-server-16 is available in your local repository.

If the playbook is run in an environment with internet access, you can leverage the most up-to-date components included in the solution. To do this, add the flag add_nexus_repo=true and provide the connection details for the repositories in the file inventory/group_vars/prepare_nodes.yml.

There are several options to run Ansible, with the option to install:

Use the following command to install Tantor SE-1C:

ansible-playbook -i inventory/my_inventory \
  -u admin_user -e "postgresql_vendor=tantordb edition=se-1c major_version=16" pg-cluster.yaml -K

Use the following command to install the classic PostgreSQL DBMS:

ansible-playbook -i inventory/my_inventory \
  -u admin_user -e "postgresql_vendor=classic major_version=11" pg-cluster.yaml -K

In the commands above, replace the value of the major_version parameter with the DBMS version to be installed, the value of postgresql_vendor with the DBMS vendor and the admin_user parameter with the user who has passwordless access to the servers from the my_inventory file with the ability to switch to privileged mode (root) without prompting the password. For Tantor SE-1C you also need to specify DBMS edition.

It’s possible to launch the playbook with external internet access.

ansible-playbook -i inventory/my_inventory \
  -u admin_user -e "postgresql_vendor=tantordb edition=se-1c major_version=16 add_nexus_repo=true" pg-cluster.yaml -K

In that case, make sure that connection details are provided in the file inventory/group_vars/prepare_nodes.yml.

Below you can find some common commands for working with the software products included in the pg_cluster solution. Note that the commands and their result may differ depending on the software versions used.

# on NODE_1
e_host=(
  /opt/tantor/usr/bin/etcdctl
  --endpoints=https://<HOST_1_IP>:2379,https://<HOST_2_IP>:2379,https://<HOST_N_IP>:2379
  --cacert=/opt/tantor/etc/patroni/ca.pem
  --cert=/opt/tantor/etc/patroni/<NODE1_HOSTNAME>.pem  
  --key=/opt/tantor/etc/patroni/<NODE1_HOSTNAME>-key.pem
)

# list etcd members
ETCDCTL_API=3 "${e_host[@]}" member list --debug

# check version
ETCDCTL_API=3  "${e_host[@]}" version

# get key value ("main" is "patroni_scope")
ETCDCTL_API=3  "${e_host[@]}" get /service/main/config

# cleanup patroni cluster configuration
ETCDCTL_API=3  "${e_host[@]}" del /service/main --prefix

# create user
su - postgres -c "psql -A -t -d postgres -c \"CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'repuserpasswd'\""
# check user
su - postgres -c "psql -A -t -d postgres -c \"select * from pg_roles where rolname = 'replicator'\""

H.4.9.3. Manage Patroni Cluster #

Patroni includes a command called patronictl which can be used to control the cluster. Let`s check the status of the cluster:

root@node1:~# patronictl -c /opt/tantor/etc/patroni/<NODE1_HOSTNAME>.yml list
+ Cluster: main (7351350415269982209) --+---------+-----------+----+-----------+
| Member  | Host            | Role    | State     | TL | Lag in MB |
+---------+-----------------+---------+-----------+----+-----------+
| node1   | xxx.xxx.xxx.xxx | Leader  | running   |  1 |           |
| node2   | yyy.yyy.yyy.yyy | Replica | streaming |  1 |         0 |
| node3   | zzz.zzz.zzz.zzz | Replica | streaming |  1 |         0 |
+---------+-----------------+---------+-----------+----+-----------+

patronictl -c /opt/tantor/etc/patroni/<NODE1_HOSTNAME>.yml edit-config should be used only to manage global cluster configuration. It should not contain any node-specific settings like connect_address, listen, data_dir and so on.

Update DCS pg_hba settings:

cat > pg_hba.conf << EOL
host replication replicator 0.0.0.0/0 md5
local all all  trust
host all all 127.0.0.1/32 trust
host all all localhost trust
EOL

cat pg_hba.conf | jq -R -s 'split("\n") | .[0:-1] | {"postgresql": {"pg_hba": .}}' | \
patronictl -c /opt/tantor/etc/patroni/<NODE1_HOSTNAME>.yml edit-config --apply - --force main

patronictl -c /opt/tantor/etc/patroni/<NODE1_HOSTNAME>.yml show-config

Change postgresql.conf settings:

cat > postgresql.conf << EOL
"postgresql": {
"parameters": {
    "max_connections" : 101
}
}
EOL

cat postgresql.conf | patronictl -c /opt/tantor/etc/patroni/<NODE1_HOSTNAME>.yml edit-config --apply - --force main
patronictl -c /opt/tantor/etc/patroni/<NODE1_HOSTNAME>.yml list
patronictl -c /opt/tantor/etc/patroni/<NODE1_HOSTNAME>.yml restart main

Make switchover:

root@node1:~# patronictl -c /opt/tantor/etc/patroni/<NODE1_HOSTNAME>.yml switchover
Current cluster topology
+ Cluster: main (7351350415269982209) --+---------+-----------+----+-----------+
| Member| Host            | Role    | State     | TL | Lag in MB |
+-------+-----------------+---------+-----------+----+-----------+
| node1 | xxx.xxx.xxx.xxx | Leader  | running   |  1 |           |
| node2 | yyy.yyy.yyy.yyy | Replica | streaming |  1 |         0 |
| node3 | zzz.zzz.zzz.zzz | Replica | streaming |  1 |         0 |
+-------+-----------------+---------+-----------+----+-----------+
Primary [node1]:
Candidate ['node2', 'node3'] []: node2
When should the switchover take place (e.g. 2024-04-02T13:51 )  [now]:
Are you sure you want to switchover cluster main, demoting current leader node1? [y/N]: y
2024-04-02 12:51:28.04774 Successfully switched over to "node2"
+ Cluster: main (7351350415269982209) --+---------+-----------+----+-----------+
| Member| Host            | Role    | State     | TL | Lag in MB |
+-------+-----------------+---------+-----------+----+-----------+
| node1 | xxx.xxx.xxx.xxx | Leader  | streaming |  2 |           |
| node2 | yyy.yyy.yyy.yyy | Replica | running   |  2 |         0 |
| node3 | zzz.zzz.zzz.zzz | Replica | streaming |  2 |         0 |
+-------+-----------------+---------+-----------+----+-----------+

Switch to Asynchronous mode (default mode):

cat > postgresql.conf << EOL
"postgresql": {
"parameters": {
    "synchronous_commit" : "local"
}
}
"synchronous_mode": false
"synchronous_mode_strict": false
EOL
cat postgresql.conf | patronictl -c /opt/tantor/etc/patroni/<NODE1_HOSTNAME>.yml edit-config --apply - --force main

Switch to Synchronous mode:

cat > postgresql.conf << EOL
"postgresql": {
"parameters": {
    "synchronous_commit" : "remote_apply"
}
}
"synchronous_mode": true
"synchronous_mode_strict": true
EOL
cat postgresql.conf | patronictl -c /opt/tantor/etc/patroni/<NODE1_HOSTNAME>.yml edit-config --apply - --force main

Figure H.2. synchronous_commit

Reinit failed node, in case if output of patronictl -c /opt/tantor/etc/patroni/<NODE1_HOSTNAME>.yml list provides the information about failed state of the node:

patronictl -c /opt/tantor/etc/patroni/<NODE1_HOSTNAME>.yml list
>>
root@node1:~# patronictl -c /opt/tantor/etc/patroni/<NODE1_HOSTNAME>.yml list
+ Cluster: main (7351350415269982209) --+------------+-----------+----+-----------+
| Member  | Host            | Role    | State        | TL | Lag in MB |
+---------+-----------------+---------+--------------+----+-----------+
| node1   | xxx.xxx.xxx.xxx | Leader  | running      |  1 |           |
| node2   | yyy.yyy.yyy.yyy | Replica | streaming    |  1 |         0 |
| node3   | zzz.zzz.zzz.zzz | Replica | start failed |  1 |         0 |
+---------+-----------------+---------+--------------+----+-----------+

Failed node can be reconfigured to join the cluster using:

patronictl -c /opt/tantor/etc/patroni/<NODE1_HOSTNAME>.yml reinit node3
>>
root@node1:~# patronictl -c /opt/tantor/etc/patroni/<NODE1_HOSTNAME>.yml list
+ Cluster: main (7351350415269982209) --+---------+-----------+----+-----------+
| Member  | Host            | Role    | State     | TL | Lag in MB |
+---------+-----------------+---------+-----------+----+-----------+
| node1   | xxx.xxx.xxx.xxx | Leader  | running   |  1 |           |
| node2   | yyy.yyy.yyy.yyy | Replica | streaming |  1 |         0 |
| node3   | zzz.zzz.zzz.zzz | Replica | streaming |  1 |         0 |
+---------+-----------------+---------+-----------+----+-----------+

After successful cluster deployment:

# on deployment node run test, the test will take about 5 minutes
# please use the latest possible version of python3
# please run commands from the pg_cluster folder
python3 tools/pg_cluster_backend/pg_cluster_backend.py --operations=10000

To emulate deadlocks, needs to change parameter test.accounts = 100 -> 10 in tools/pg_cluster_backend/conf/pg_cluster_backend.conf.

Simultaneously with the test, you should perform actions with the cluster:

# on NODE_1
patronictl -c /opt/tantor/etc/patroni/<NODE1_HOSTNAME>.yml list
patronictl -c /opt/tantor/etc/patroni/<NODE1_HOSTNAME>.yml restart main
shutdown -r now

# on NODE_2
patronictl -c /opt/tantor/etc/patroni/<NODE2_HOSTNAME>.yml list
patronictl -c /opt/tantor/etc/patroni/<NODE2_HOSTNAME>.yml restart main
shutdown -r now

# on NODE_3
patronictl -c /opt/tantor/etc/patroni/<NODE3_HOSTNAME>.yml list
patronictl -c /opt/tantor/etc/patroni/<NODE3_HOSTNAME>.yml restart main
shutdown -r now

# on NODE_1
patronictl -c /opt/tantor/etc/patroni/<NODE1_HOSTNAME>.yml switchover

# on primary node
su - postgres -c "psql -A -t -d test_db -c \"
    select pg_terminate_backend(pid)
    from pg_stat_activity
    where application_name = 'pg_cluster_backend'\""

# on NODE_1
systemctl stop patroni

# on NODE_2
systemctl stop patroni

# on NODE_1
systemctl start patroni

# on NODE_2
systemctl start patroni

# restart all nodes in random order

SELECT
    sum(balance)::numeric - -- result balance
    ((select count(1) from public.accounts) * 100 + 10000) -- where "--operations=10000"
FROM public.accounts

-- positive value means lost transactions
-- negative value means successfully committed transactions in which the backend received an exception