Monitoring Barman with PEM

Since version 8.4, PEM can monitor a Barman server through PEM console, this article shows how to configure this integration.

Here we have used three different servers: Barman, PEM server and Database server. You can still use this article if you have PEM and database on the same server. Here database server is backed up by Barman and PEM will have its own backend database on the same (PEM) server. You can also have more than one database server backed up by barman.

Below are the details of the servers involved in this setup.

• Barman server (v3.5.0): 192.168.216.4 (hostname barman.edb)
• PEM server (v9): 192.168.216.5
• DB server (EPAS15): 192.168.216.2

Before adding a Barman server to the PEM console:

1. You must manually install and configure Barman on the Barman host. For more information about installing and configuring Barman, see Barman.
2. Install the pg-backup-api tool on Barman host. For more information about installing, see pg-backup-api.

We assume that you have the Fatabase instance up and running, backed up by Barman, and Barman is able to take backup of this server.

Host file on Barman server looks like below:

$ cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4  
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6  
192.168.216.4 barman.edb

Install Postgres backup API on Barman server, this will be used for managing PostgreSQL backups.

$ sudo yum install pg-backup-api
$ sudo systemctl start pg-backup-api
$ sudo systemctl enable pg-backup-api

Then you can test it with:

$ curl http://localhost:7480/status    
"OK"

The command above should return the status "OK" as shown above, in case of any issue kindly refer to the barman-api-error.log file under the /var/log/barman/ directory.

This is required to enable secure remote access on Barman server to Postgres backup API. Once completed you will be able to use a client key and certificate to authenticate to Postgres backup API over an encrypted TLS connection.

$ sudo yum install httpd mod_ssl

Once installed you should stop Apache HTTP Server from listening on port 80 and carry out platform-specific configuration tasks.

$ sudo sed -i 's/^Listen 80$/#Listen 80/' /etc/httpd/conf/httpd.conf
$ sudo systemctl restart httpd

You can confirm Apache is running with:

$ sudo systemctl status httpd
httpd.service - The Apache HTTP Server
   Loaded: loaded (/usr/lib/systemd/system/httpd.service; enabled; vendor preset: disabled)
   Active: active (running) since Tue 2023-03-28 19:39:17 PDT; 20s ago
     Docs: man:httpd(8)
           man:apachectl(8)
 Main PID: 72221 (httpd)
   Status: "Total requests: 0; Current requests/sec: 0; Current traffic:   0 B/sec"
   CGroup: /system.slice/httpd.service
           ├─72221 /usr/sbin/httpd -DFOREGROUND
           ├─72222 /usr/sbin/httpd -DFOREGROUND
           ├─72223 /usr/sbin/httpd -DFOREGROUND
           ├─72224 /usr/sbin/httpd -DFOREGROUND
           ├─72225 /usr/sbin/httpd -DFOREGROUND
           └─72226 /usr/sbin/httpd -DFOREGROUND

Mar 28 19:39:17 barman.edb systemd[1]: Starting The Apache HTTP Server...
Mar 28 19:39:17 barman.edb systemd[1]: Started The Apache HTTP Server.

In order to configure Apache HTTP Server for TLS encryption you will need a private key and a certificate signed by a trusted certificate authority (CA). You will also need a client certificate which has been signed by the same CA which signed the server certificate.

In the following steps a number of placeholder variables are used:

• $ORGANIZATION_NAME: The organization name for your certificates.
• $SERVER_FQDN: The fully qualified domain name of the Barman server.
• $ADMIN_EMAIL_ADDRESS: The e-mail address used in the emailAddress x509 attribute.
• $CLIENT_CN: The x509 common name for the client application / host.

For example (on the Barman server):

$ export ORGANIZATION_NAME=EDB 
$ export SERVER_FQDN=barman.edb 
$ export ADMIN_EMAIL_ADDRESS=supriya.khosare@enterprisedb.com 
$ export CLIENT_CN=pem.enterprisedb.com 

Now, generate a self-signed CA as follows:

$ sudo openssl req -nodes -new -x509 -days 999 -keyout /root/ca.key -out /root/ca.cert -subj "/O=$ORGANIZATION_NAME/CN=root.$SERVER_FQDN/emailAddress=$ADMIN_EMAIL_ADDRESS"
Generating a 2048 bit RSA private key
................+++
...................+++
writing new private key to '/root/ca.key'

This will generate a CA key and self-signed certificate which will be stored in the /root directory on the Barman server.

Now you can create a server key and use it to obtain a certificate signed by the CA you just created.

Create the key:

$ sudo openssl genrsa -out /root/server.key 2048
Generating RSA private key, 2048 bit long modulus
...+++
.................+++
e is 65537 (0x10001)

Create a certificate signing request using the newly generated key:

$ sudo openssl req -new -key /root/server.key -out /root/server.csr -subj "/O=$ORGANIZATION_NAME/CN=$SERVER_FQDN/emailAddress=$ADMIN_EMAIL_ADDRESS"

Create the server certificate from the certificate request using the CA:

$ sudo openssl x509 -req -in /root/server.csr -CA /root/ca.cert -CAkey /root/ca.key  -CAcreateserial -out /root/server.cert -days 999 -sha256
Signature ok
subject=/O=EDB/CN=barman.edb/emailAddress=supriya.khosare@enterprisedb.com
Getting CA Private Key

Now we repeat the process to generate the client key and certificate:

$ sudo openssl genrsa -out /root/client.key 2048
Generating RSA private key, 2048 bit long modulus
65537 (0x10001)

$ sudo openssl req -new -key /root/client.key -out /root/client.csr -subj "/O=$ORGANIZATION_NAME/CN=$CLIENT_CN/emailAddress=$ADMIN_EMAIL_ADDRESS"

$ sudo openssl x509 -req -in /root/client.csr -CA /root/ca.cert -CAkey /root/ca.key -CAcreateserial -out /root/client.cert -days 999 -sha256
Signature ok
subject=/O=EDB/CN=pem.enterprisedb.com/emailAddress=supriya.khosare@enterprisedb.com
Getting CA Private Key

Finally, move the CA certificate, the server key and server certificate to a location accessible by the Apache HTTP Server user and ensure correct permissions on the server key (all commands below on the Barman server):

$ sudo mkdir -p /usr/local/lib/pgbapi
$ sudo mv /root/ca.cert /root/server.key /root/server.cert /usr/local/lib/pgbapi
$ sudo chmod 600 /usr/local/lib/pgbapi/server.key
$ sudo restorecon -RvF /usr/local/lib/pgbapi

$ ls -l /usr/local/lib/pgbapi/
total 12
-rw-r--r--. 1 root root 1281 Mar 28 20:07 ca.cert
-rw-r--r--. 1 root root 1155 Mar 28 20:10 server.cert
-rw-------. 1 root root 1679 Mar 28 20:09 server.key
[root@barman barman]#

$ sudo chown -R apache:apache /usr/local/lib/pgbapi

$ ls -l /usr/local/lib/pgbapi/
total 12
-rw-r--r--. 1 apache apache 1281 Mar 28 20:07 ca.cert
-rw-r--r--. 1 apache apache 1155 Mar 28 20:10 server.cert
-rw-------. 1 apache apache 1679 Mar 28 20:09 server.key

If SELinux is enabled you will also need to reset the security context for these files:

$ sudo restorecon -RvF /usr/local/lib/pgbapi
restorecon reset /usr/local/lib/pgbapi context unconfined_u:object_r:lib_t:s0->system_u:object_r:lib_t:s0
restorecon reset /usr/local/lib/pgbapi/ca.cert context unconfined_u:object_r:admin_home_t:s0->system_u:object_r:lib_t:s0
restorecon reset /usr/local/lib/pgbapi/server.key context unconfined_u:object_r:admin_home_t:s0->system_u:object_r:lib_t:s0
restorecon reset /usr/local/lib/pgbapi/server.cert context unconfined_u:object_r:admin_home_t:s0->system_u:object_r:lib_t:s0

Add a file called pgbapi.conf to the Apache HTTP configuration directory which defines the VirtualHost which will act as a proxy. Note that the CA certificate, server certificate and server key are expected to be available in /usr/local/lib/pgabi. If your key and certificates are located elsewhere then update the SSL certificate paths as required. Be sure to change $SERVER_FQDN to its actual value.

$ cd /etc/httpd/conf.d/
$ cat pgbapi.conf
    <VirtualHost *:443>
    ServerName barman.edb 
    SSLEngine on
    SSLCertificateFile /usr/local/lib/pgbapi/server.cert
    SSLCertificateKeyFile /usr/local/lib/pgbapi/server.key
    SSLCACertificateFile /usr/local/lib/pgbapi/ca.cert
    SSLVerifyClient require
    SSLVerifyDepth 1
    ProxyPass "/" "http://localhost:7480/"
    ProxyPassReverse "/" "http://localhost:7480/"
</VirtualHost>

Finally, reload the Apache HTTP Server config:

$ sudo systemctl reload httpd

Postgres backup API is now configured for secure remote access. The CA certificate, client certificate and client key created earlier will all be required by the client application (PEM in our case) so that it can connect and authenticate. The files /root/ca.cert, /root/client.key and /root/client.cert should therefore be copied securely to the PEM server so that the client can be configured to use them.

On Barman server (192.168.216.4) run below:

$ scp /root/client.key /root/client.cert /usr/local/lib/pgbapi/ca.cert root@192.168.216.5:~
The authenticity of host '192.168.216.5 (192.168.216.5)' can't be established.
ECDSA key fingerprint is SHA256:VGaMbNyi8yoxRkJLrg95hWZ4Bmqn7DZQinJNBAnBD4U.
ECDSA key fingerprint is MD5:34:ac:c2:a1:d5:c0:40:26:2a:26:f1:42:44:bf:ac:68.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.216.5' (ECDSA) to the list of known hosts.
root@192.168.216.5's password: 
client.key                                                      100% 1675   469.1KB/s   00:00    
client.cert                                                     100% 1172     1.0MB/s   00:00    
ca.cert                                                         100% 1281     1.2MB/s   00:00    

$ ip r 
default via 192.168.216.1 dev ens33 proto dhcp metric 100 
192.168.122.0/24 dev virbr0 proto kernel scope link src 192.168.122.1 
192.168.216.0/24 dev ens33 proto kernel scope link src 192.168.216.4 metric 100

Note: 192.168.216.5 is PEM server IP address.

On PEM server (192.168.216.5):

$ ls -ltrh
total 20K
-rw-------. 1 root root 2.1K Mar 25 05:04 original-ks.cfg
-rw-------. 1 root root 2.8K Mar 25 05:04 anaconda-ks.cfg
drwxr-xr-x. 2 root root    6 Mar 27 00:59 Videos
drwxr-xr-x. 2 root root    6 Mar 27 00:59 Templates
drwxr-xr-x. 2 root root    6 Mar 27 00:59 Public
drwxr-xr-x. 2 root root    6 Mar 27 00:59 Pictures
drwxr-xr-x. 2 root root    6 Mar 27 00:59 Music
drwxr-xr-x. 2 root root    6 Mar 27 00:59 Downloads
drwxr-xr-x. 2 root root    6 Mar 27 00:59 Documents
drwxr-xr-x. 2 root root    6 Mar 27 00:59 Desktop
-rw-r--r--. 1 root root 1.7K Mar 28 20:52 client.key
-rw-r--r--. 1 root root 1.2K Mar 28 20:52 client.cert
-rw-r--r--. 1 root root 1.3K Mar 28 20:52 ca.cert
$ pwd
/root

If SELinux is enabled you will also need to reset the security context for these files:

$ sudo restorecon -RvF /root
restorecon reset /root/.cache/tracker/locale-for-miner-apps.txt context unconfined_u:object_r:cache_home_t:s0->system_u:object_r:cache_home_t:s0   
restorecon reset /root/.config/nautilus/desktop-metadata context unconfined_u:object_r:config_home_t:s0->system_u:object_r:config_home_t:s0

Now add below entry for Barman in hosts file of PEM server:

$ cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4  
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6  
192.168.216.4 barman.edb

Now try the curl command from PEM server:

$ curl --cacert /root/ca.cert --cert /root/client.cert --key /root/client.key https://barman.edb/status

If you get an error like below, on Barman server:

$ tailf /var/log/httpd/error_log
[Tue Mar 28 21:21:22.912326 2023] [proxy:error] [pid 74322] (13)Permission denied: AH00957: HTTP: attempt to connect to 127.0.0.1:7480 (localhost) failed
[Tue Mar 28 21:21:22.912355 2023] [proxy:error] [pid 74322] AH00959: ap_proxy_connect_backend disabling worker for (localhost) for 60s
[Tue Mar 28 21:21:22.912358 2023] [proxy_http:error] [pid 74322] [client 192.168.216.5:58732] AH01114: HTTP: failed to make connection to backend: localhost

Then on Barman server try this:

$ /usr/sbin/setsebool -P httpd_can_network_connect 1

This is to initiate outbound connections, which is just what mod_proxy attempts to do on barman server. Now after initiating outbound connections above error can be resolved.

Now try the curl command from PEM server again:

$ curl --cacert ca.cert --cert /root/client.cert --key /root/client.key https://barman.edb/status     
"OK"

Using the pemworker command to register Barman server with PEM (run this on the PEM server):

$ /usr/edb/pem/agent/bin/pemworker --register-barman --api-url https://barman.edb --description 'barman-api' --ssl-crt /root/client.cert --ssl-key /root/client.key --ssl-ca-crt /root/ca.cert --owner enterprisedb -c /usr/edb/pem/agent/etc/agent.cfg  
Barman API successfully registered!
BARMAN ID: 1

** NOTE: Please restart the pemAgent to take these changes in effect.

Here, barman.edb is the hostname for the Barman server that we have provided in the hosts file. barman-api can be any name and used to identify the Barman server on PEM and enterprisedb is the database superuser. Note the BARMAN ID returned by the command above.

After registering the Barman server you need to restart the PEM agent.

$ systemctl restart pemagent
$ systemctl status pemagent
● pemagent.service - Postgres Enterprise Manager Agent
   Loaded: loaded (/usr/lib/systemd/system/pemagent.service; enabled; vendor preset: disabled)
   Active: active (running) since Thu 2023-03-30 05:54:55 PDT; 7s ago
  Process: 41012 ExecStart=/usr/edb/pem/agent/bin/pemagent -c /usr/edb/pem/agent/etc/agent.cfg (code=exited, status=0/SUCCESS)
 Main PID: 41014 (pemagent)
    Tasks: 10
   CGroup: /system.slice/pemagent.service
           ├─41014 /usr/edb/pem/agent/bin/pemagent -c /usr/edb/pem/agent/etc/agent.cfg
           └─41015 /usr/edb/pem/agent/bin/pemworker -c /usr/edb/pem/agent/etc/agent.cfg --pid 41014...

Mar 30 05:54:55 localhost.localdomain systemd[1]: Starting Postgres Enterprise Manager Agent...
Mar 30 05:54:55 localhost.localdomain systemd[1]: Started Postgres Enterprise Manager Agent.

Now you can see the Barman related changes have been added to agent.cfg file:

$ locate agent.cfg
/usr/edb/pem/agent/etc/agent.cfg
/usr/edb/pem/agent/etc/agent.cfg.sample

$ cat /usr/edb/pem/agent/etc/agent.cfg
[PEM/agent]
pem_host=127.0.0.1
pem_port=5444
agent_id=1
agent_ssl_key=/root/.pem//agent1.key
agent_ssl_crt=/root/.pem//agent1.crt
agent_ssl_passphrase_script=builtin
log_level=warning
log_location=/var/log/pem/worker.log
agent_log_location=/var/log/pem/agent.log
long_wait=30
short_wait=10
alert_threads=1
enable_smtp=true
enable_snmp=true
enable_webhook=true
max_webhook_retries=3
allow_server_restart=true
max_connections=0
connect_timeout=10
connection_lifetime=0
allow_batch_probes=false
heartbeat_connection=false
enable_nagios=false
[BARMAN/1]
ssl_ca_crt=/root/ca.cert
ssl_crt=/root/client.cert
ssl_key=/root/client.key

Once the Barman server (display name as per the step 4 is barman-api)configured and registered with PEM, then you can check on PEM dashboard; now you should see name barman-api in Barman server tab.

You can right-click the Barman server from the browser tree and select Properties. This will open a new prompt which will have 4 sections:

• General tab: describes the general properties of the Barman server;
• PEM Agent tab: specifies connection details for the PEM Agent;
• Information tab: shows the detailed information about your Barman server;
• Configuration tab: shows the configuration settings of your Barman server.

When you select a monitored Barman server, the following panels are highlighted:

• Barman Activities panel
• Servers panel
• Backups panel

The details of all the associated database servers along with their activities are displayed as a chart on the dashboard in the Barman Activities panel. The Servers panel displays a list of all the database servers managed by that Barman server along with the active status. The Backups panel displays a list of all the database server backups managed by that Barman server.

Similarly, you are able to see the Barman-related details on your PEM dashboard. Select barman-api and click on the Dashboard to see the details.