Streaming Backup With Fallback WAL Archiving in PostgreSQL

July 15, 2021
()

Streaming Backup With Fallback WAL Archiving in PostgreSQL

In previous blog we saw How To Configure Streaming Backup & WAL Streaming using BARMAN Click here to read more.


In this blog we will configure
WAL archiving via barman-wal-archive along with WAL Streaming  for more robust architecture in PostgreSQL.


This alternate approach requires:

1. An additional SSH connection that allows the postgres user on the PostgreSQL server to connect as barman user on the Barman server.
2. The archive_command in PostgreSQL be configured to ship WAL files to Barman Server.

From Barman 2.6, the recommended way to safely and reliably archive WAL files to Barman via archive_command is to use the barman-wal-archive command contained in the barman-cli package. We can also use rsync/SSH as in archive_command. But with rsync/SSH method there is no mechanism that guarantees that the content of the file is flushed and fsync-ed to disk on Barman Server.

Follow the below steps to Configure Streaming Backup With Fallback WAL Archiving


Follow the same steps as in the blog How To Configure Streaming Backup & WAL Streaming using BARMAN, Click here to read more from Step 1 to Step 6. Once Step 6 is done continue with the below steps.


Step 6.1. Set Password for OS Account barman: Use OS command passwd barman to reset barman OS account in Backup Server.

[root@test-machine01 tmp]# passwd barman
Changing password for user barman.
New password:
Retype new password:
passwd: all authentication tokens updated successfully.
[root@test-machine01 tmp]#


Step 6.2. Set Password for OS Account postgres: Use OS command passwd postgres to reset postgres OS account in PostgreSQL Database Server.

[root@test-machine02 ~]# passwd postgres
Changing password for user postgres.
New password:
BAD PASSWORD: The password contains the user name in some form
Retype new password:
passwd: all authentication tokens updated successfully.
[root@test-machine02 ~]#


Step 6.3. Configure Passwordless Authentication in Barman Server: Use OS command ssh-keygen to generate public and private keys. Once keys are generated use OS command ssh-copy-id to transfer the public key to PostgreSQL Database Server.

[root@test-machine01 ~]# su - barman
Last login: Tue Jun 22 10:02:53 +03 2021 on pts/0
-bash-4.2$
-bash-4.2$ ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/var/lib/barman/.ssh/id_rsa):
Created directory '/var/lib/barman/.ssh'.
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /var/lib/barman/.ssh/id_rsa.
Your public key has been saved in /var/lib/barman/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:fg8qzAkNeneq4qKoYRNJCTNP5+S7z4RQWXb889KpETA barman@test-machine01
The key's randomart image is:
+---[RSA 2048]----+
|+ . o o..        |
|.=.= + .E        |
| o. =    +       |
|. ....    +      |
| o...o  S  = .   |
|  o.ooo.. o +    |
|.o .o=.+. o+     |
|+.o  +*  o.o     |
|*o....o..   .    |
+----[SHA256]-----+
-bash-4.2$
-bash-4.2$ls -ltr .ssh
total 8
-rw-r--r--. 1 barman barman  403 Jun 22 10:12 id_rsa.pub
-rw-------. 1 barman barman 1675 Jun 22 10:12 id_rsa
-bash-4.2$
 
-bash-4.2$ ssh-copy-id -i ~/.ssh/id_rsa.pub postgres@test-machine02
/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/var/lib/barman/.ssh/id_rsa.pub"
The authenticity of host 'test-machine02 (192.168.114.176)' can't be established.
ECDSA key fingerprint is SHA256:NbJdl0Pi8lmjbKHbz7q0GgYAy++evY3rNH7gCupnx+c.
ECDSA key fingerprint is MD5:39:33:1f:a7:bb:09:ac:81:cc:ff:cc:18:46:ca:76:c1.
Are you sure you want to continue connecting (yes/no)? yes
/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
postgres@test-machine02's password:
 
Number of key(s) added: 1
 
Now try logging into the machine, with:   "ssh 'postgres@test-machine02'"
and check to make sure that only the key(s) you wanted were added.
 
-bash-4.2$
-bash-4.2$ ssh 'postgres@test-machine02'
Last login: Tue Jun 22 10:30:29 2021
-bash-4.2$
-bash-4.2$ exit
logout
Connection to test-machine02 closed.
-bash-4.2$


Step 6.4. Configure Passwordless Authentication in PostgreSQL Database Server: Use OS command ssh-keygen to generate public and private keys. Once keys are generated use OS command ssh-copy-id to transfer the public key to Barman Server.

[root@test-machine02 ~]# su - postgres
Last login: Wed Jun 16 17:44:29 +03 2021 on pts/2
-bash-4.2$
-bash-4.2$
-bash-4.2$ ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/var/lib/pgsql/.ssh/id_rsa):
Created directory '/var/lib/pgsql/.ssh'.
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /var/lib/pgsql/.ssh/id_rsa.
Your public key has been saved in /var/lib/pgsql/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:jiouqVEgyZz+dmsHM+H0yzn4GgdhYnqBkbJ3Es/ralw postgres@test-machine02
The key's randomart image is:
+---[RSA 2048]----+
| ..              |
|+o=              |
|=* B o           |
|+.= Bo.          |
| +.+ooo S        |
| .o E=.+         |
|.o = oB.+        |
|o.= o++*         |
|o+oooo+..        |
+----[SHA256]-----+
-bash-4.2$
 
-bash-4.2$ ls -ltr .ssh
total 8
-rw-r--r--. 1 postgres postgres  405 Jun 22 10:12 id_rsa.pub
-rw-------. 1 postgres postgres 1679 Jun 22 10:12 id_rsa
-bash-4.2$
 
-bash-4.2$ ssh-copy-id -i ~/.ssh/id_rsa.pub barman@test-machine01
/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/var/lib/pgsql/.ssh/id_rsa.pub"
/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
barman@test-machine01's password:
 
Number of key(s) added: 1
 
Now try logging into the machine, with:   "ssh 'barman@test-machine01'"
and check to make sure that only the key(s) you wanted were added.
 
-bash-4.2$
-bash-4.2$ ssh 'barman@test-machine01'
Last login: Tue Jun 22 10:32:10 2021
-bash-4.2$
-bash-4.2$ exit
logout
Connection to test-machine01 closed.
-bash-4.2$


Step 6.5. Configure WAL Shipping in PostgreSQL Database Server: Edit postgresql.conf and modify archive_command & archive_mode to copy WAL Files in barman server. As both are static parameter postgresql service needs to be restart.

[root@test-machine02 ~]# cd /var/lib/pgsql/13/data/
[root@test-machine02 data]# vi postgresql.conf
archive_mode = on
archive_command = 'barman-wal-archive test-machine01 test-machine02 %p'
:wq!

[root@test-machine02 system]# systemctl restart postgresql-13
[root@test-machine02 system]#

[root@test-machine02 ~]# su - postgres
Last login: Sun Jul 11 10:56:42 +03 2021 on pts/1
-bash-4.2$ psql
psql (13.2)
Type "help" for help.
postgres=#
postgres=# select name,setting,unit from pg_settings where name in ('archive_mode','wal_level','archive_command');
      name       |                       setting                       | unit
-----------------+-----------------------------------------------------+------
 archive_command | barman-wal-archive test-machine01 test-machine02 %p |
 archive_mode    | on                                                  |
 wal_level       | replica                                             |
(3 rows)

postgres=#


Step 6.6. Check barman-wal-archive working in Barman server: You can check that barman-wal-archive can connect to the Barman server, and that the required PostgreSQL server is configured in Barman to accept incoming WAL files with the following command:

-bash-4.2$
-bash-4.2$ barman-wal-archive --test test-machine01 test-machine02 DUMMY
barman@test-machine01's password:
Ready to accept WAL files for the server test-machine02
-bash-4.2$


Once the above steps are done continue from Step 7 to complete it following the blog How To Configure Streaming Backup & WAL Streaming using BARMAN.


References: BARMAN Document: http://docs.pgbarman.org/release/2.12/
For BARMAN Backup Command: https://dbsguru.com/physical-postgresql-backup-using-barman/
For BARMAN Restore Command: https://dbsguru.com/restore-backup-using-barman-in-postgresql/
For BARMAN PITR Comamnd:  https://dbsguru.com/point-in-time-recovery-using-barman-in-postgresql/

 

This document is just for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.


Hope so you like this article
!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to understand more about our pursuit.

 

 

Related articles

 

Loading

How useful was this post?

Click on a star to rate it!

Average rating / 5. Vote count:

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

Hello and welcome to DBsGuru,I’m Jamsher Khan working as Senior Oracle DBA based in KSA-Jeddah, I have working experience in Oracle DBA, SQL Server, MySql, PostgreSQL, Linux, Golden Gate, ODA.Thanks for the visits!Share Learn Grow!

Leave a Reply

Your email address will not be published. Required fields are marked *