(678) 345-3456
380 Albert St, Melbourne, Australia

Blog Details

  • Stormatics
  • Blog
  • Recovery Hack: Restoring Tablespace Using Barman on Windows

Recovery Hack: Restoring Tablespace Using Barman on Windows

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: 

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:

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 -y

Setup 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_keys

Setup barman server

Login to the barman server and switch the user to barman.

sudo su barman

We 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 rsa

Paste the content of ~/.ssh/ 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 [email protected]
Last login: Wed Feb 28 13:01:57 2024 from
Administrator@EC2AMAZ-O46A12I ~

Restoring the backup (Default behavior)

Unable to resolve Windows path while performing the backup

barman recover --remote-ssh-command "ssh [email protected]" 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 [email protected]' pg latest /cygdrive/c/recover/data
Starting remote restore for server pg using backup 20240226T084830
Destination directory: /cygdrive/c/recover/data
Remote command: ssh [email protected]
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)


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 [email protected]" pg latest /cygdrive/c/recover/data
Starting remote restore for server pg using backup 20240305T080638
Destination directory: /cygdrive/c/recover/data
Remote command: ssh [email protected]
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_tablespace

Start 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".
server started

Leave A Comment