How do you usually deploy your PostgreSQL clusters? Do you use in-house Ansible playbooks? Or are you using other orchestration tools, like Chef, Salt or Puppet? I still remember my early years as a system administrator when I would install servers manually, sometimes with diskettes or CDs.
In this blog, I’ll be discussing a tool originally developed at 2ndQuadrant and now being open-sourced by EDB, called Trusted Postgres Architect (or TPA for short). This is a tool for deploying PostgreSQL clusters declaratively, in a trusted way.
The trusted part of TPA comes from years of experience that engineers have gathered on the best Postgres architectures, how to provide backups in a reliable way, and the best ways to configure replication. We'll be expanding more on the trusted part later.
TPA uses Ansible orchestration under the hood. The key difference between using TPA and just writing Ansible directly is that TPA provides a much more concise ‘Postgres-friendly’ syntax for specifying the cluster architecture. While both Ansible and TPA are declarative in nature, the number of low-level statements required to deploy and configure a high-availability Postgres cluster in Ansible can leave it feeling more like an imperative, step-by-step tool. With TPA you just specify what you want the final cluster to look like and TPA creates the playbooks that need to be executed in order to deploy the nodes, and configure the different applications. In other words, the ‘imperativeness’ of Ansible is abstracted away by TPA.
We won’t go over how to install TPA in this blog, as that is explained in the TPA installation document.
Introduction to 'configure' and defining a cluster
The way TPA defines the cluster is through a `config.yml` file. This file lists cluster variables and instances. An instance represents a single server (also known as a ‘node’), and the roles assigned to the instance determine what that server does in the cluster. The deployment step will take care of setting up each node accordingly. This goes from installing software packages based on the node’s roles to setting up replication, failover manager, backups, and so on.
This `config.yml` file provides lots of flexibility in defining the cluster. At the same time, it might be complex to write for those not familiar with YAML and TPA internals. To overcome this complexity, TPA offers a `configure` command that generates a basic `config.yml` according to a few parameters. The user can extend the cluster’s definition by manually modifying the YAML file afterward.
What better way to understand how this works than getting our hands dirty? Let's start with a simple example.
Deploying a single primary cluster
We’ll start by setting up a PostgreSQL cluster with four nodes: one primary, two standbys, and a backup server.
This is known in TPA as an `M1` architecture (single master). We’ll use Patroni as the failover manager for the three Postgres nodes and the fourth node will be set with Barman to manage the backups.
tpaexec configure ~/clusters/patroni-tpa --architecture M1 --platform docker --os Rocky \
--postgresql 15 --enable-patroni
The above command will create `~/clusters/patroni-tpa/config.yml`, a declarative configuration file which will be used to deploy a cluster with the options specified. That is, use Rocky Linux Docker containers, and install PostgreSQL 15. This file would look something like this (I did manually modify the node names here, but we'll see later how to provide them before running `configure`):
---
architecture: M1
cluster_name: patroni-tpa
cluster_tags: {}
cluster_vars:
enable_pg_backup_api: false
etcd_location: main
failover_manager: patroni
postgres_flavour: postgresql
postgres_version: '15'
preferred_python_version: python3
use_volatile_subscriptions: false
locations:
- Name: main
- Name: dr
instance_defaults:
image: tpa/rocky:8
platform: docker
vars:
ansible_user: root
instances:
- Name: endor
backup: hoth
location: main
node: 1
role:
- primary
- Name: kamino
location: main
node: 2
role:
- replica
upstream: endor
- Name: hoth
location: main
node: 3
role:
- barman
- log-server
- monitoring-server
- Name: naboo
location: dr
node: 4
role:
- replica
upstream: endor
- Name: tatooine
location: dr
node: 5
role:
- etcd
vars:
etcd_location: main
- Name: bespin
location: main
node: 6
role:
- etcd
vars:
etcd_location: main
- Name: alderaan
location: dr
node: 7
role:
- etcd
vars:
etcd_location: main
Choosing the platform
In the example above, we chose the Docker platform, which is great for testing (our support team uses this to spin up clusters to reproduce issues that our customers have). But for production, you may want to deploy in VMs, instances in the cloud, or even on bare-metal servers. For these cases, you can use the `--platform bare` option. You'll need IP addresses for each node, which you can't specify from the command line. The good thing is that TPA has an optional argument for specifying node names, and we can add IP addresses for those node names.
$ cat /home/martin/cluster_nodes.txt
endor 192.168.130.5
naboo 192.168.130.6
bespin 192.168.130.7
hoth 192.168.130.15
alderaan 192.168.130.11
kamino 192.168.130.12
tatooine 192.168.130.13
We have seven nodes in a `Patroni` cluster as it requires an additional set of three `etcd` nodes for the DCS.
With that, we can use the following `configure` command to set up the `config.yml`
tpaexec configure ~/clusters/patroni-tpa-bare --architecture M1 --platform bare --os Rocky \
--postgresql 15 --enable-patroni --hostnames-from /home/martin/cluster_nodes.txt
This will result in the following `config.yml`:
---
architecture: M1
cluster_name: patroni-tpa-bare
cluster_tags: {}
cluster_vars:
enable_pg_backup_api: false
etcd_location: main
failover_manager: patroni
postgres_flavour: postgresql
postgres_version: '15'
preferred_python_version: python3
use_volatile_subscriptions: false
locations:
- Name: main
- Name: dr
instance_defaults:
platform: bare
vars:
ansible_user: root
instances:
- Name: endor
backup: bespin
ip_address: 192.168.130.5
location: main
node: 1
role:
- primary
- Name: naboo
ip_address: 192.168.130.6
location: main
node: 2
role:
- replica
upstream: endor
- Name: bespin
ip_address: 192.168.130.7
location: main
node: 3
role:
- barman
- log-server
- monitoring-server
- Name: hoth
ip_address: 192.168.130.15
location: dr
node: 4
role:
- replica
upstream: naboo
- Name: alderaan
ip_address: 192.168.130.11
location: dr
node: 5
role:
- etcd
vars:
etcd_location: main
- Name: kamino
ip_address: 192.168.130.12
location: main
node: 6
role:
- etcd
vars:
etcd_location: main
- Name: tatooine
ip_address: 192.168.130.13
location: dr
node: 7
role:
- etcd
vars:
etcd_location: main
Choosing a different failover manager
In the examples above, we used `Patroni` as the failover manager. But TPA provides two other failover managers:
To use either of these two, you'd have to replace `--enable-patroni` with `--enable-repmgr` or `--enable-efm` respectively. In order to use `EFM`, you would have to have an EDB subscription. See the next section on setting up the repositories in TPA.
Postgres flavors
In addition to installing PostgreSQL, TPA is also able to install clusters with other flavors of Postgres provided by EDB for customers with subscriptions. You can set the flavor to `EPAS` or `PGE` using the `--edb-postgres-advanced` or `--edb-postgres-extended` respectively.
Repositories
You can configure a variety of repositories using the `yum_repositories:` or `apt_repositories:`, or by setting the `edb_repositories:`. This last one is only available for customers with valid EDB subscriptions.
As an example, to configure `PGDG` and `EPEL` repositories for a cluster of Rocky Linux nodes, you can add the following to the `cluster_vars:` section of the `config.yml`:
cluster_vars:
yum_repository_list:
- PGDG
- EPEL
If, on the other hand, you are an EDB customer, you can use one of the internal EDB repositories (subject to entitlements, you'll be able to choose one or the other). Currently, there are four EDB repositories: `community360`, `standard`, `enterprise`, and `postgres_distributed`.
An important feature that TPA has is that it will decide and add appropriate repositories depending on which architecture and tools you have decided to deploy. For example, if you choose `PGD-Always-ON` it will add the `postgres_distributed` repository, but if it's an M1 architecture with `EPAS` as the Postgres flavor, it will use `enterprise`, or `PGDG` if installing PostgreSQL and open source tools. Usually, you don't need to take any actions with respect to setting up the repositories via command line arguments of `configure` or manually in the `config.yml`
To set up, as an example, the `standard` repository, which gives access to `PGE`, `EFM, and `PEM`, you can add this option to the `tpaexec configure` invocation: `--edb-repositories standard`. This will include the following lines in the `cluster_vars` of your `config.yml`:
cluster_vars:
edb_repositories:
- standard
In order to be able to access the EDB repositories, you'll have to set up the Subscription Token:
export EDB_SUBSCRIPTION_TOKEN=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
You can find your subscription token at https://www.enterprisedb.com/accounts/profile after logging into the EDB website.
Easy setup of EDB Postgres Distributed (PGD) clusters
One of the most sophisticated HA architectures available is EDB's multi-master database server called EDB Postgres Distributed, or PGD for short. EDB Postgres Distributed enables up to five nines of extreme high availability and is available as an add-on to EDB’s Enterprise or Standard Plan, which enables access to the PGD packages.
EDB has been using TPA to deploy PGD on customer production environments with great success.
Just like with M1, you can also set up PGD clusters using the configure command:
tpaexec configure -a PGD-Always-ON --platform aws --postgresql 15 --bdr-database martin \
--pgd-proxy-routing local --data-nodes-per-location 2 --owner martin \
--location-names DC1 DC2 DC3 --instance-type t3.micro --witness-only-location DC3 \
~/clusters/PGD5
The above will produce a `config.yml` which deploys the `PGD-Always-ON` architecture on AWS using three locations, two nodes in each of the first two locations, and the third one with a witness node.
You can read more about the additional options that can be used in the TPA documentation for PGD-Always-ON. I’d also suggest getting familiar with PGD by going over the PGD 5 documentation.
Provisioning nodes: Docker and AWS
In the first example, we were declaring a cluster that would run on Docker containers. We also mentioned that you could select the platform `bare` which would just connect to existing nodes, whether they’re in the cloud, on VMs, or actual bare-metal.
In the case of Docker, TPA will provision the Docker containers, provided that the user has permission to do so. We'll see a little later how this works.
Another platform you can use is `aws`. When using this platform option, TPA will provision nodes in AWS for you, given appropriate credentials are set in environment variables or in the AWS configuration file (typically `~/.aws/credentials`).
The provisioning can be done using `tpaexec provision`, which will only provision the nodes, or with `tpaexec deploy` which provisions and deploys (we'll cover deploy next).
Deploying with TPA
Once you have the configuration set up, and if using the `bare` platform, the nodes have been provisioned, deploying the cluster is as simple as running `tpaexec deploy` with the cluster (the last argument is always the cluster directory where `config.yml` lives):
tpaexec deploy ~/clusters/patroni-tpa/
TPA will generate ssh key pairs and install them on each node of the cluster. In the cluster configuration directory, you’ll find the private and public keys and a `ssh_config` file. This file will come in handy when trying to `ssh` into the nodes. From the cluster examples above, connecting to the `endor` node via `ssh` is as easy as:
cd ~/clusters/patroni-tpa/ && ssh -F ssh_config endor
Deploying other tools
TPA is able to not only deploy PostgreSQL with a failover manager and a Barman server for backups, but also enables monitoring through EDB's `PEM` server, or a pooler with pgbouncer.
1. PEM
Setting up a PostgreSQL cluster with a PEM server monitoring is as simple as adding `--enable-pem`. This will enable the `pem-agent` on all PostgreSQL nodes and the Barman node if `--enable-pg-backup-api` is used.
You can find more about how to configure PEM with TPA in the EDB docs.
2. pgbouncer
Although this doesn't come as a `configure` option, TPA has the capability for setting up `pgbouncer` on instances that contain the `pgbouncer` role. You can easily have `pgbouncer` installed in the Postgres instances by adding the role to it, plus some other options specific to `pgbouncer`:
instances:
- Name: endor
backup: hoth
location: main
node: 1
role:
- primary
- pgbouncer
- Name: kamino
location: main
node: 2
role:
- replica
- pgbouncer
upstream: endor
Or you can set up a proxy with `pgbouncer` which would connect to either of the two `PostgreSQL` nodes, as in the following example:
instances:
- Name: endor
backup: hoth
location: main
node: 1
role:
- primary
- Name: kamino
location: main
node: 2
role:
- replica
upstream: endor
- Name: proxy
role:
- pgbouncer
vars:
pgbouncer_backend: endor
pgbouncer_databases:
- name: luke
options:
pool_mode: transaction
- name: luke-ro
options:
host: kamino
Additional things TPA handles
Besides all the above, TPA is also able to set up the following tools based on configuration:
- HAProxy
- pglogical
It also offers the possibility of running `tpaexec cmd` which lets you run Ansible ad-hoc commands on the instances (see `tpaexec help cmd` for more information).
Give TPA a try!
Trusted Postgres Architect offers break/fix support for self-managed EDB Subscription plans plus a number of other useful features. But don’t just take our word for it. Install TPA here and experience the benefits for yourself!