Unable to recover after specific incremental backup 20190324T054002 at Mar 24 05:40:02 2019

[wasiualhasib]

Suppose I have several backups like below where one full backup and rest of them are incremental backup, I want to recover after a specific incremental backup 20190324T054002 at Mar 24 05:40:02 2019. Where full backup id is: 20190324T050002 . How can I do it?

barman recover --target-time "2024-03-24 05:00:02" pg 20190324T050002 /data/pgsql/14/data/

I put here full backup id : 20190324T050002 and --target-time which is in between incremental id: 20190324T054002 and 20190324T054502 .

`pg1 20190602T041001 - Sun Jun 2 05:09:26 2019 - Size: 141.2 GiB - WAL Size: 14.5 GiB

pg1 20190324T055802 - Sun Mar 24 05:58:07 2019 - Size: 140.5 GiB - WAL Size: 15.4 GiB
pg1 20190324T055502 - Sun Mar 24 05:55:35 2019 - Size: 139.5 GiB - WAL Size: 21.6 GiB
pg1 20190324T055001 - Sun Mar 24 05:50:01 2019 - Size: 138.5 GiB - WAL Size: 19.7 GiB
pg1 20190324T054502 - Sun Mar 24 05:45:30 2019 - Size: 137.7 GiB - WAL Size: 14.4 GiB
pg1 20190324T054002 - Sun Mar 24 05:40:02 2019 - Size: 136.8 GiB - WAL Size: 12.0 GiB
pg1 20190324T053601 - Sun Mar 24 05:36:20 2019 - Size: 136.0 GiB - WAL Size: 11.8 GiB
pg1 20190324T053002 - Sun Mar 24 05:30:23 2019 - Size: 135.1 GiB - WAL Size: 13.5 GiB
pg1 20190324T051901 - Sun Mar 24 05:19:35 2019 - Size: 173.8 GiB - WAL Size: 50.8 GiB
pg1 20190324T051002 - Sun Mar 24 05:10:10 2019 - Size: 172.4 GiB - WAL Size: 11.8 GiB
pg1 20190324T050002 - Sun Mar 24 05:00:08 2019 - Size: 170.8 GiB - WAL Size: 11.5 GiB`

Here restore to desired location but when I try to start postgers service I found error like below :

---

2024-05-25 15:26:37.058 +06 [2468431]: [33-1] db=,user=,app=,client= LOG: restored log file "00000005000000880000000C" from archive
2024-05-25 15:26:37.088 +06 [2468431]: [34-1] db=,user=,app=,client= LOG: restored log file "00000005000000880000000D" from archive
cp: cannot stat 'barman_wal/00000005000000880000000E': No such file or directory
2024-05-25 15:26:37.104 +06 [2468431]: [35-1] db=,user=,app=,client= LOG: redo done at 88/D0001F8 system usage: CPU: user: 1.96 s, system: 0.39 s, elapsed: 3.16 s
2024-05-25 15:26:37.104 +06 [2468431]: [36-1] db=,user=,app=,client= LOG: last completed transaction was at log time 2024-05-25 13:50:45.748273+06
2024-05-25 15:26:37.104 +06 [2468431]: [37-1] db=,user=,app=,client= FATAL: recovery ended before configured recovery target was reached
2024-05-25 15:26:37.117 +06 [2468425]: [11-1] db=,user=,app=,client= LOG: startup process (PID 2468431) exited with exit code 1
2024-05-25 15:26:37.117 +06 [2468425]: [12-1] db=,user=,app=,client= LOG: terminating any other active server processes
2024-05-25 15:26:37.119 +06 [2468425]: [13-1] db=,user=,app=,client= LOG: shutting down due to startup process failure
2024-05-25 15:26:37.244 +06 [2468425]: [14-1] db=,user=,app=,client= LOG: database system is shut down

---

Because of that WAL unable to start. How to restore incase of incremental backup. How to solve this?

[andremagui]

Hello @wasiualhasib! I ll try to help you as far as I know!

"Where full backup id is: 20190324T053102".

Why you used 20190324T050002 if you said that the full backup was 20190324T053102. Isn't the latter (20190324T053102) the last full backup before the incremental one you want to recover? It looks like you used an older full backup to recover an incremental backup that is not related to that full.

Also, "Recovery target via time, XID and LSN must be subsequent to the end of the backup." as stated in the docs.

So, always think about the last full_backup as the parent and the incremental backups as the children to recover.
Maybe you can try the following:

barman recover --target-time "2024-03-24 05:44:00" --exclusive pg 20190324T053102 /data/pgsql/14/data/

--target-time should be a timestamp that is after the last incremental backup you want to recover. In this case, you will recover until this backup_ip = 20190324T054002.
--exclusive - the --target-time will be non inclusive.
20190324T053102 - this is the last full backup_id as I understood.

[wasiualhasib]

Sorry, this is full backup id: 20190324T050002, it was my mistake pls ignore 20190324T053102. I just updated it. Please verify my barman backup command. I think this way I will be able to backup between 20190324T054502 and 20190324T054002 backup id.

barman recover --target-time "2024-03-24 05:44:00" --exclusive pg 20190324T050002 /data/pgsql/14/data/

[andremagui]

OK, no prob. I guess if you use --exclusive, it will consider the --target-time non inclusive and will backup until the previous backup 20190324T054002. If you want to consider that one you set in the command in the recover, maybe try using a target-time higher than 20190324T054502.

[wasiualhasib]

I executed this command to recover for specific time. It is recovered successfully but postgrsql server has not started.

barman recover --target-time "2024-06-11 08:30:00" --remote-ssh-command "ssh [email protected]" pg 20240611T080602 /data/pgsql/14/data/

I noticed that the target-time is the issue. I found that it is due to the recovery target time not matched. Look at the log line number 5. Where it is mentioned that last completed transaction was at log time 2024-06-11 08:29:42.747135+06. That means you have to put that target time otherwise you will not be able to recover.

2024-06-11 12:37:29.094 +06 [445955]: [12-1] db=,user=,app=,client= LOG:  restored log file "00000005000000E9000000E2" from archive
2024-06-11 12:37:29.135 +06 [445955]: [13-1] db=,user=,app=,client= LOG:  restored log file "00000005000000E9000000E3" from archive
cp: cannot stat 'barman_wal/00000005000000E9000000E4': No such file or directory
2024-06-11 12:37:29.161 +06 [445955]: [14-1] db=,user=,app=,client= LOG:  redo done at E9/E3002748 system usage: CPU: user: 0.00 s, system: 0.01 s, elapsed: 0.21 s
2024-06-11 12:37:29.162 +06 [445955]: [15-1] db=,user=,app=,client= LOG:  last completed transaction was at log time 2024-06-11 08:29:42.747135+06
2024-06-11 12:37:29.162 +06 [445955]: [16-1] db=,user=,app=,client= FATAL:  recovery ended before configured recovery target was reached
2024-06-11 12:37:29.164 +06 [445950]: [12-1] db=,user=,app=,client= LOG:  startup process (PID 445955) exited with exit code 1
2024-06-11 12:37:29.164 +06 [445950]: [13-1] db=,user=,app=,client= LOG:  terminating any other active server processes
2024-06-11 12:37:29.165 +06 [445950]: [14-1] db=,user=,app=,client= LOG:  shutting down due to startup process failure
2024-06-11 12:37:29.252 +06 [445950]: [15-1] db=,user=,app=,client= LOG:  database system is shut down

Solution:

Look at the below command I changed --target-time "2024-06-11 08:29:42" to recover again. After executing this command it is successfully recovers and starts the server successfully.

barman recover --target-time "2024-06-11 08:29:42" --remote-ssh-command "ssh [email protected]" pg 20240611T080602 /data/pgsql/14/data/

@martinmarques is it due to a transaction not found in that time or something else? Need your opinion.

Note: You will not get any issue recovering but you will get an issue at the PostgreSQL server start.

[martinmarques]

@wasiualhasib When this happens you should run barman show-backup with the server and the backup-id of the backup recovered, and look at the End time of the backup summary there. You can't use a backup to recover to a target time that is not after the end time of the backup.