Streaming Incremental Backup Configuration with PostgreSQL 17 using Docker Sidecar

[stephane-klein]

Hello,

(This note in french: https://notes.sklein.xyz/2025-02-13_1450/zen/)

In the poc-barman repository, I'm trying to use barman in a Docker sidecar container to backup a PostgreSQL container.

One of my constraints is to make minimal changes to the PostgreSQL container that I want to backup. I want to be able to use a mainstream Docker PostgreSQL image https://hub.docker.com/_/postgres, without modifications.

I want to use the barman backup mode called streaming backups method: backup_method = postgres which is based on the pg_basebackup command (official command integrated into PostgreSQL).

I want to use the new pg_basebackup --incremental... feature from PostgreSQL version 17.

Here is my barman configuration: https://github.com/stephane-klein/poc-barman/blob/4df58ecc5af6d2d1f7607c364400f8c5ba012496/docker-compose.yml#L15

And here is my PostgreSQL 17 configuration:

• https://github.com/stephane-klein/poc-barman/blob/4df58ecc5af6d2d1f7607c364400f8c5ba012496/docker-compose.yml#L15
• and https://github.com/stephane-klein/poc-barman/blob/4df58ecc5af6d2d1f7607c364400f8c5ba012496/init-barman.sh#L1

I implemented a script called ./scripts/reset.sh that performs an end-to-end test automatically.

Here's its screencast: https://www.youtube.com/watch?v=d2Iqw8KppCY

Here's what it does:

• It stops all containers and erases the volumes
• It starts the postgres1 and barman containers and injects some data into postgres1
• It initializes barman
• It performs a full backup of postgres1
• It restores the backup to postgres2, starts postgres2, and displays the data from the dummy table
• It performs an incremental backup after injecting some new data into postgres1
• It restores postgres1 backup using pg_combinebackup to postgres2 which was previously stopped and erased
• Here I have a failure when starting postgres2 based on the incremental backup restoration

Questions:

• Why does the restoration based on the incremental backup fail?
• Is my barman usage test scenario correct, am I missing steps or am I performing unnecessary operations?
• Have I made any significant mistakes?

Best regards,
Stephane

[stephane-klein]

ping @martinmarques

[stephane-klein]

More information:

$ docker compose exec barman sh -c "rm -rf /var/lib/postgres2/data/*; rm -rf /var/lib/postgres2/data/.* 2>/dev/null; chown -R barman:barman /var/lib/postgres2/data/; barman restore postgres1 last /var/lib/postgres2/data/ --no-get-wal --recovery-staging-path=/var/lib/barman/tmp/; chown -R 999:999 /var/lib/postgres2/data/; ls -lha /var/lib/postgres2/data/"
Start combining backup via pg_combinebackup for backup 20250217T071525 on /var/lib/barman/staging
End combining backup via pg_combinebackup for backup 20250217T071525
Starting local restore for server postgres1 using backup 20250217T071525
Destination directory: /var/lib/postgres2/data/
WARNING: IMPORTANT: You have requested a recovery operation for a backup that does not have yet all the WAL files that are required for consistency.
Copying the base backup.
WARNING: IMPORTANT: The backup we have restored IS NOT VALID. Required WAL files for consistency are missing. Please verify that WAL archiving is working correctly or evaluate using the 'get-wal' option for recovery
Copying required WAL segments.
Generating archive status files
Identify dangerous settings in destination directory.

Restore operation completed (start time: 2025-02-17 07:27:04.808004+00:00, elapsed time: less than one second)
Your PostgreSQL server has been successfully prepared for recovery!

$ docker compose up postgres2
[+] Running 1/1
 ✔ Container poc_barman_148718276ec5-postgres2-1  Created                                                                                                  0.0s
Attaching to postgres2-1
postgres2-1  |
postgres2-1  | PostgreSQL Database directory appears to contain a database; Skipping initialization
postgres2-1  |
postgres2-1  | 2025-02-17 07:28:06.995 UTC [1] LOG:  starting PostgreSQL 17.2 (Debian 17.2-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
postgres2-1  | 2025-02-17 07:28:06.995 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
postgres2-1  | 2025-02-17 07:28:06.995 UTC [1] LOG:  listening on IPv6 address "::", port 5432
postgres2-1  | 2025-02-17 07:28:06.997 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
postgres2-1  | 2025-02-17 07:28:06.999 UTC [1] LOG:  could not open directory "pg_tblspc": No such file or directory
postgres2-1  | 2025-02-17 07:28:07.002 UTC [29] LOG:  database system was interrupted; last known up at 2025-02-17 07:15:26 UTC
postgres2-1  | 2025-02-17 07:28:07.043 UTC [29] LOG:  could not open directory "pg_tblspc": No such file or directory
postgres2-1  | 2025-02-17 07:28:07.043 UTC [29] LOG:  starting backup recovery with redo LSN 0/6000028, checkpoint LSN 0/6000080, on timeline ID 1
postgres2-1  | 2025-02-17 07:28:07.044 UTC [29] LOG:  could not open directory "pg_tblspc": No such file or directory
postgres2-1  | 2025-02-17 07:28:07.048 UTC [29] FATAL:  could not open directory "pg_tblspc": No such file or directory
postgres2-1  | 2025-02-17 07:28:07.051 UTC [1] LOG:  startup process (PID 29) exited with exit code 1
postgres2-1  | 2025-02-17 07:28:07.051 UTC [1] LOG:  aborting startup due to startup process failure
postgres2-1  | 2025-02-17 07:28:07.052 UTC [1] LOG:  database system is shut down
postgres2-1 exited with code 1

[barthisrael]

@stephane-klein I took a quick look over your logs and poc, and it seems you have a few different issues there.

Why does the restoration based on the incremental backup fail?

Neither the restore of full backup nor of the incremental backup failed.
It seems you are actually able to finish the barman restore command in both cases.

What you are getting in this case is an issue with the second execution of docker compose up postgres2. In that case, it identifies PGDATA already exists in /var/lib/postgresql/data and attempt to start Postgres, but that fails with FATAL: could not open directory "pg_tblspc": No such file or directory.

Please note that both of your barman restore attempts are writing to /var/lib/postgres2/data, which doesn't seem to be used at all. Your docker compose up attempts seem to be using the default PGDATA of the image.

Is my barman usage test scenario correct, am I missing steps or am I performing unnecessary operations?

The main thing that you might be lacking here is barman cron executing on schedule.

When you install barman from RPM or DEB packages, as you did from your Dockerfile, it puts an entry in the crontab to run barman cron every minute.

However, I don't think your container is running cron/barman cron by any means. That routine is mandatory in Barman, because barman cron takes care of performing maintenance, and that includes, among other things:

• Start pg_receivewal, if not running.
• Move WALs from the streaming directory to the final wals directory, effectively getting them archived in the Barman server.
• Change status of backups based on the conditions.

For this last entry, we can see you have a WARNING in your barman restore attempt:

WARNING: IMPORTANT: You have requested a recovery operation for a backup that does not have yet all the WAL files that are required for consistency.
Copying the base backup.
WARNING: IMPORTANT: The backup we have restored IS NOT VALID. Required WAL files for consistency are missing. Please verify that WAL archiving is working correctly or evaluate using the 'get-wal' option for recovery
Copying required WAL segments.

That means Barman has not yet archived the WALs required for the backup to reach the consistent point. Maybe they are under streaming directory already, but were not processed because barman cron is not running from time to time.

Have I made any significant mistakes?

1. Apparently missed barman cron running reguarly.
2. Is using a shared volume between all the containers. That requires your workaround of running chown before and after the barman restore, as the volume is owned by Postgres, and Barman is writing files through barman restore. There might be better ways of handling this. Note that barman restore has support for remote connections through SSH, if you're able to set that up.

I didn't dig too much into details, but I'm outlining here a few issues that I observed along the way.