I recently had the opportunity to contribute to a customer project, where the objective was to establish a system for PostgreSQL full backups and seamless restoration. Considering Barman’s successful functionality on Linux, we decided to explore its compatibility with Windows. Secondly, no other tool claims to work on Windows to take backups and perform a restore
From official documentation it is mentioned that:
Backup of a PostgreSQL server on Windows is possible, but it is still experimental because it is not yet part of our continuous integration system.
Problem statement
The strategy involved setting up Barman on a Linux instance, referred to as the barman server, to back up a PostgreSQL server running on a Windows instance, referred to as the database server. Furthermore, Barman on the Linux instance was expected to restore the backups to another Windows instance, the recovery server. Additionally, the client’s PostgreSQL server had a tablespace configured in a custom location outside the data directory. so that tablespace must also be part of the backup and restore process
Servers information
barman server: Ubuntu 22.04
database server: Windows server 2022
recovery server: Windows server 2022
Setup the barman and PostgreSQL server
I recommend taking a look at our previous barman blog, where we’ve provided comprehensive instructions on setting up and configuring Barman and database servers. We’ve covered various settings and backup methods in detail. You can find the blog post using the following link: https://stormatics.tech/alis-planet-postgresql/postgresql-backup-and-recovery-management-using-barman
Setup the recovery server
To enable backup recovery, it’s essential to establish an SSH connection from the barman to the recovery server without requiring a password. To achieve this, we’ll utilize Cygwin
Download the Cygwin installer from this URL: https://www.cygwin.com/setup-x86_64.exe
Proceed to the next screen by leaving everything as default. Once the Select Packages screen appears, click on the view and choose Full.
Search and select rsync, and OpenSSH packages Proceed to install the packages by using the drop-down menu from the New column, and selecting the latest version for each package. Once all selections are made, click Next to start the installation
Setup recovery server
Launch the Cygwin64 Terminal from the Windows Start menu. Execute the following command to configure the host for SSH, which will create a new service named cygsshd. This service can then be started manually.
ssh-host-config -ySetup authorized_keys for passwordless access
Create a new file under C:\cygwin64\home\Administrator\.ssh named authorized_keys. The authorized_keys file is used in SSH for public key authentication, allowing users to log in securely without needing to enter passwords.
sets the permissions of the authorized_keys file to read and write for the owner only, Which is necessary for SSH security.
chmod 0600 /cygdrive/c/cygwin64/home/Administrator/.ssh/authorized_keysSetup barman server
Login to the barman server and switch the user to barman.
sudo su barmanWe first need to generate public and private key pairs for authentication. Press the Enter key multiple times to proceed with the default options.
ssh-keygen -t rsaPaste the content of ~/.ssh/id_rsa.pub from barman server into the newly created authorized_keys file on Windows.
Now it’s time to test passwordless authentication from the barman server to the recovery server.
barman@ip-172-31-51-120:~$ ssh administrator@172.31.54.29
Last login: Wed Feb 28 13:01:57 2024 from 172.31.51.120
Administrator@EC2AMAZ-O46A12I ~
$
Restoring the backup (Default behavior)
Unable to resolve Windows path while performing the backup
barman recover --remote-ssh-command "ssh administrator@172.31.54.29" pg latest C:\recover\data
ERROR: The destination directory parameter cannot contain the ':' character
HINT: If you want to do a remote recovery you have to use the --remote-ssh-command option
If we use cygdrive path then it fails to resolve the tablespace path properly
barman@ip-172-31-51-120:~/$ barman recover --remote-ssh-command 'ssh administrator@172.31.54.29' pg latest /cygdrive/c/recover/data
Starting remote restore for server pg using backup 20240226T084830
Destination directory: /cygdrive/c/recover/data
Remote command: ssh administrator@172.31.54.29
16405, prod_tablespace, F:\prod_tablespace
Copying the base backup.
ERROR: Failure copying base backup: data transfer failure
rsync error:
rsync: [Receiver] change_dir#1 "/home/Administrator/F:\prod_tablespace" failed: No such file or directory (2)
rsync error: errors selecting input/output files, dirs (code 3) at main.c(749) [Receiver=3.2.7]
What do list-backups show
barman@ip-172-31-51-120:~/pg/base/20240305T080638$ barman list-backups pg
pg 20240305T080638 - Tue Mar 5 08:06:45 2024 - Size: 46.9 MiB - WAL Size: 0 B (tablespaces: prod_tablespace:F:\postgres\tblspc)Workaround
On barman server
Rename the tablespace directory inside the data directory
cd /var/lib/barman/pg/base/<BACKUP_DATE_TIME>/data
mv "F:\\prod_tablespace" tablespace
Rename talespace name inside backup.conf file
cd /var/lib/barman/pg/base/<BACKUP_DATE_TIME>/
Rename original tablespace from tablespaces=[('prod_tablespace', 16422, 'F:\\prod_tablespace')] to tablespaces=[('prod_tablespace', 16422, 'tablespace')]
NOTE: 16422 is your OID it is needed while setting up symlinks in the recovery server
Executing the backup command
barman@ip-172-31-51-120:~/pg/base/20240305T080638$ barman recover --remote-ssh-command "ssh administrator@172.31.54.29" pg latest /cygdrive/c/recover/data
Starting remote restore for server pg using backup 20240305T080638
Destination directory: /cygdrive/c/recover/data
Remote command: ssh administrator@172.31.54.29
16422, prod_tablespace, tablespace
Copying the base backup.
Copying required WAL segments.
Generating archive status files
Recovery completed (start time: 2024-03-05 08:37:20.026758+00:00, elapsed time: 14 seconds)
Your PostgreSQL server has been successfully prepared for recovery!
On windows server
Move tablespace from c:\recover\data\tablespace to F:\postgres\prod_tablespace
Create a symlink to the tablespace inside C:\recover\data\pg_tblspc (Delete existing if exists). Run the following command via PowerShell
New-Item -ItemType SymbolicLink -Path C:\recover\data\pg_tblspc\16422 -Target F:\postgres\prod_tablespaceStart the server
PS C:\Program Files\PostgreSQL\16\bin> .\pg_ctl.exe -D C:\data\ start
waiting for server to start....2024-03-05 08:48:51.243 UTC [2604] LOG: redirecting log output to logging collector process
2024-03-05 08:48:51.243 UTC [2604] HINT: Future log output will appear in directory "log".
done
server started
