PostgreSQL with TPA

Trusted Postgres Architect (TPA) is a software based on Ansible that can be used to deploy database clusters on a variety of platforms.

In this recipe, we will use TPA to configure and deploy a small cluster on our own Linux workstation.

This recipe uses TPA’s docker platform, which is meant to be used only for test clusters. TPA currently supports two other platforms:

• The aws platform, to provision and use instances on AWS EC2
• The bare platform, to use existing instances (including bare-metal and already provisioned servers)

For more information on how to use these platforms, please refer to the corresponding TPA documentation pages:

• https://www.enterprisedb.com/docs/tpa/latest/platform-aws/
• https://www.enterprisedb.com/docs/tpa/latest/platform-bare/

Getting ready

First, we need to install TPA, which is free software, released under the GPL v3 license. Therefore, you can download it from public repositories, as explained in the installation instructions:

https://www.enterprisedb.com/docs/tpa/latest/INSTALL/

Make sure you have the latest version installed; you can check it by typing:

tpaexec info

At the time when this recipe was written, TPA version 23.23 was the latest release available. Given that TPA tries hard to keep compatibility with clusters installed using previous versions, you should definitely always use the latest version of TPA, and be able to repeat this recipe even with releases newer than 23.23.

Then, we need to install Docker. If you don’t have it already on your laptop you can install it as described here: https://www.enterprisedb.com/docs/tpa/latest/platform-docker/#installing-docker.

In the common microservices approach, each container runs a specific service. The way TPA uses Docker is quite different because each container runs a miniature copy of a Linux OS. This approach is not meant for production use, but it is a great way to test the behavior of a cluster with minimal resource use.

How to do it...

This is our first TPA example, so we will deploy the smallest possible PostgreSQL cluster, composed of a single instance with a backup server. No replication, no high availability (which most of the time means no production!)

First, we create the cluster configuration using the tpaexec configure command as follows:

tpaexec configure myfirstcluster --architecture M1 \
  --platform docker --enable-repmgr --postgresql 16

This command creates a directory named myfirstcluster with the following contents:

myfirstcluster/
├── commands
│   ├── status.yml -> /opt/EDB/TPA/architectures/M1/commands/status.yml
│   ├── switchover.sh -> /opt/EDB/TPA/architectures/M1/commands/switchover.sh
│   ├── switchover.yml -> /opt/EDB/TPA/architectures/M1/commands/switchover.yml
│   └── upgrade.yml -> /opt/EDB/TPA/architectures/M1/commands/upgrade.yml
├── config.yml
└── deploy.yml -> /opt/EDB/TPA/architectures/M1/deploy.yml

The commands directory contains some symlinks to commands that are specific to the architecture that we have chosen, while deploy.yml is a symlink to the playbook used for the deploy command. As you can see, all these are files distributed together with TPA, which are linked to this cluster directory so they can easily be used.

The only new file that has been created by this invocation is config.yml, which describes the cluster. It is effectively a template that the user can modify if they want to fine-tune the cluster; in fact, editing that file is quite common because only some of the settings can be specified as options of the tpaexec configure command.

We created a configuration file specifying this architecture:

https://www.enterprisedb.com/docs/tpa/latest/architecture-M1/

As we want a smaller example, we will now edit config.yml to remove some of the instances because in this first example, we just want to deploy one PostgreSQL instance and one Barman instance instead of the full M1 architecture, which by default includes a three-node physical replication cluster plus a Barman node, which also acts as a log server and as a monitoring server.

Let’s locate the instances section, at the end of the file:

instances:
- Name: kennel
  backup: karma
  location: main
  node: 1
  role:
  - primary
- Name: quintet
  location: main
  node: 2
  role:
  - replica
  upstream: kennel
- Name: karma
  location: main
  node: 3
  role:
  - barman
  - log-server
  - monitoring-server
- Name: kinship
  location: dr
  node: 4
  role:
  - replica
  upstream: quintet

The instance names in your example will likely be different every time you run tpaexec configure because TPA by default picks them at random from a built-in list of words; however, the structure will be the same.

From there, we can remove:

• The physical replicas – that is, instances 2 and 4 (here, quintet and kinship)
• The additional roles for the Barman instance – that is, log-server and monitoring-server from instance 3 (here, karma)

We end up with the following instances section:

instances:
- Name: kennel
  backup: karma
  location: main
  node: 1
  role:
  - primary
- Name: karma
  location: main
  node: 3
  role:
  - barman

After making these changes, we can deploy the cluster, which is as simple as issuing the following command:

tpaexec deploy myfirstcluster

This command will display copious output, ending like this after a few minutes:

PLAY RECAP *********************************************************************
karma                      : ok=177  changed=40   unreachable=0    failed=0    skipped=163  rescued=0    ignored=0   
kennel                     : ok=316  changed=97   unreachable=0    failed=0    skipped=222  rescued=0    ignored=1   
localhost                  : ok=4    changed=0    unreachable=0    failed=0    skipped=0    rescued=0    ignored=0   
real	5m35.687s
user	1m13.249s
sys	0m30.098s

The output is also collected in the ansible.log file, with millisecond timestamps, if you need to inspect the (many) steps afterward.

Now that we have a cluster, we can use it. Let’s connect with SSH to the Postgres host:

$ cd myfirstcluster
$ ssh -F ssh_config kennel
[root@kennel ~]# su - postgres
postgres@kennel:~ $ psql
psql (15.4)
Type "help" for help.
postgres=#
We can also open another terminal and connect to the Barman host:
$ ssh -F ssh_config karma
Last login: Mon Sep 18 21:35:41 2023 from 172.17.0.1
[root@karma ~]# su - barman
[barman@karma ~]$ barman list-backup all
kennel 20230918T213317 - Mon Sep 18 21:33:19 2023 - Size: 22.2 MiB - WAL Size: 0 B
kennel 20230918T213310 - Mon Sep 18 21:33:11 2023 - Size: 22.2 MiB - WAL Size: 36.2 KiB
kennel 20230918T213303 - Mon Sep 18 21:33:05 2023 - Size: 22.2 MiB - WAL Size: 36.8 KiB

There’s more

TPA reads the config.yml file, where the cluster is described in a declarative way, and then performs all the actions needed to deploy the cluster, or to modify an already-deployed cluster if config.yml has been changed since the last run of the deploy command.

The tpaexec deploy command automatically performs the preliminary tpaexec provision, which is the step where TPA populates the Ansible inventory based on the contents of config.yml and then creates the required resources, such as SSH keys, passwords, and instances. Here, “instances” means:

• Containers, when using the docker platform
• VMs, when using the aws platform
• Nothing, when using the bare platform (TPA will expect “bare metal” instances, in the sense that they exist already and TPA has sudo SSH access to them)

For more details, please refer to the TPA online documentation:

• The configure command: https://www.enterprisedb.com/docs/tpa/latest/tpaexec-configure/
• The provision command: https://www.enterprisedb.com/docs/tpa/latest/tpaexec-provision/
• The deploy command: https://www.enterprisedb.com/docs/tpa/latest/tpaexec-deploy/

Learn more on Discord

To join the Discord community for this book – where you can share feedback, ask questions to the author, and learn about new releases – follow the QR code below:

https://discord.gg/pQkghgmgdG