--target-tli=current is not working as expected

[JP95Git]

Hi,

I am using PostgreSQL 16.1 and Barman 3.10. Backup works fine, but PITR does not work very well, as described here: https://github.com/EnterpriseDB/barman/issues/881

So I tried to use a different approach to set the recovery time and found this in the manual:

https://docs.pgbarman.org/release/3.10.0/

Barman allows you to specify a target timeline for recovery using the --target-tli option. This can be set to a numeric timeline ID or one of the special values latest (to recover to the most recent timeline in the WAL archive) and current (to recover to the timeline which was current when the backup was taken).

So I had a bunch of backups, created with /path/to/barman --config /path/to/barman.conf backup --wait localhost

I created some tables for testing and created another backup. My idea was to restore the previous backup to get the database without the recently created tables.

/path/to/barman --config /path/to/barman.conf recover localhost 20240410T162031 /path/to/database Restore worked, I started PostgreSQL and the tables were still there, OK.

/path/to/barman --config /path/to/barman.conf recover localhost 20240410T162031 /path/to/database --target-tli=current Restore worked again, I started PostgreSQL and the tables were still there. I expected to get a database without the tables, because the tables did no exists when the backup was created. I created the tables AFTER the backup.

This issue also works with Barman 3.9.

[gcalacoci]

Hi, please help me understand better:

1. You did a backup before creating the tables
2. you created the tables
3. you executed a recovery with timeline current

Right?

I'm asking because if nothing happened between step 1 and 2 that generated a timeline change, then using the current value you are asking to recover everything.

[JP95Git]

No, I tried this:

1. Create backup
2. Create some tables
3. Create another backup
4. Try to restore the database to the state before the tables were created.

Or in other words: --target-tli=current and --target-tli=latest and no --target-tli parameter at all results in the same recovery.

[rprovodenko]

same thing for me

[barthisrael]

Hello @JP95Git @rprovodenko ,

No, I tried this:

1. Create backup
2. Create some tables
3. Create another backup
4. Try to restore the database to the state before the tables were created.

Or in other words: --target-tli=current and --target-tli=latest and no --target-tli parameter at all results in the same recovery.

Thanks for clarifying the steps that were taken.

Please note that between steps 1 and 3 you have never created a new timeline in Postgres.

A new timeline in Postgres is not created when you take backups. A new timeline is created in Postgres whenever recovery ends, for example:

1. If you promote a standby to primary, a new timeline is created;
2. Once you finish recovering a node from a backup, a new timeline is created.

You mentioned that you achieved the same outcomes in these 3 scenarios:

1. --target-tli=current
2. --target-tli=latest
3. No --target-tli

Based on your steps, that is expected because the Postgres timeline was the same in both backups that you took. That way, recovery_target_timeline = current, recovery_target_timeline = latest or absent recovery_target_timeline would all point to the very same timeline of your Postgres instance (the latest timeline). In that case, if you configured no recovery target other than the recovery_target_timeline, during recovery you would simply replay all the WALs from the archive.

Note: recovery_target_timeline is set in Postgres configuration according to the value that you pass through --target-tli argument of barman recover.

For further information about Postgres timelines and recovery_target_timeline, we recommend that you take a look at the following documentation pages from Postgres:

• Timelines;
• recovery_target_timeline.

If you want to recover from a backup and want to stop replaying WALs before your table was created, we recommend using recovery_target_time -- you can use the --target-time option of barman recover when using Barman. Please take a look at the recovery_target_time documentation.

[JP95Git]

I think you are right. In my machines the timeline increases only after recovery. Actually this was a workaround because PITR does not work for me and other people, as you can see here: #881

So you can close this issue. But please fix #881, as there was no process since 6 month and my customer demands some full testing of database recovery soon.

[barthisrael]

Hello @JP95Git ,

So you can close this issue. But please fix https://github.com/EnterpriseDB/barman/issues/881, as there was no process since 6 month and my customer demands some full testing of database recovery soon.

Thanks for confirming!

We are now closing this issue, and taking a deeper look at #881.