PITR - cannot stat 'barman_wal/00000001000000000000000B'

[dulh...@mailbox.org]

I have finally managed to setup barman for streaming only backups thanks to the support from here.
Backups can be taken and restored. However upon PITR I run into a problem.

Even though the recovery seems to go well (barman stating success) I can not get the postgres-server up again with a hint towards a  'barman_wal/00000001000000000000000B' not being present

this is my recovery procedure:

1. stopping the postgres server
2. deleting the content of $PGDATA
3. barman recover [postgre-server] [backup_ID] --target-time 20220310T103800 /some/path/
4. cp /some/path/* $PGDATA/
5. chown postgres: $PGDATA/*

this is the error in the postgres Logs upon systemctl start postgresql-14:

2022-03-10 10:42:37.716 UTC [5323] LOG:  redo starts at 0/A000028
2022-03-10 10:42:37.727 UTC [5323] LOG:  consistent recovery state reached at 0/A000100
2022-03-10 10:42:37.728 UTC [5320] LOG:  database system is ready to accept read-only connections
cp: cannot stat 'barman_wal/00000001000000000000000B': No such file or directory
2022-03-10 10:42:37.731 UTC [5323] LOG:  redo done at 0/A000100 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.01 s
2022-03-10 10:42:37.731 UTC [5323] FATAL:  recovery ended before configured recovery target was reached
2022-03-10 10:42:37.732 UTC [5320] LOG:  startup process (PID 5323) exited with exit code 1
2022-03-10 10:42:37.732 UTC [5320] LOG:  terminating any other active server processes
2022-03-10 10:42:37.734 UTC [5320] LOG:  shutting down due to startup process failure
2022-03-10 10:42:37.736 UTC [5320] LOG:  database system is shut down

I can locate a barman_wal folder in $PGDATA which does contain 00000001000000000000000B , but not B. So what is my mistake here?

[dulh...@mailbox.org]

sorry . barman_wal folder container contains 00000001000000000000000A , but not B.

[Luca Ferrari]

On Thu, Mar 10, 2022 at 1:13 PM dulhaver via Barman, Backup and
Recovery Manager for PostgreSQL <pgba...@googlegroups.com> wrote:
> barman recover [postgre-server] [backup_ID] --target-time 20220310T103800 /some/path/

Could it be that the target time is too far in the future? I mean, is
PostgreSQL archiving WALs so that such time has been covered by
backup?
Can you restore without the target time to see if the backup is ok,
and in such case, to which time (commit timestamp)?


Luca

[dulh...@mailbox.org]

hi Luca,

On 10.03.22 14:34, Luca Ferrari wrote:

On Thu, Mar 10, 2022 at 1:13 PM dulhaver via Barman, Backup and Recovery Manager for PostgreSQL <pgba...@googlegroups.com> wrote:

barman recover [postgre-server] [backup_ID] --target-time 20220310T103800 /some/path/

Could it be that the target time is too far in the future?

the complete recover command (maybe I should have posted that from the start) is

barman recover postgres-local 20220310T080939 --target-time 20220310T103800 recover/

so recovery time is 2.5 hours after the backup. Is that too far away?
If so I may have a problem with the understanding. I was thinking PITR is there to prepare for exactly such a situation?

I mean, is PostgreSQL archiving WALs so that such time has been covered by backup?

hm, did we not just establish that I need to have archive_mode = off and/or archive_command - /bin/true as well as archiver = off (in barman) for streaming backups?
In my understanding this qualifies for PostgreSQL not archiving WALs. Maybe I am not getting what you mean here by "PostgreSQL archiving WALs".

Can you restore without the target time to see if the backup is ok,and in such case, to which time (commit timestamp)?

yes, I can recover the backup without a --target-time option.  I have tried with a --target-time 20 seconds after the backup time, as well as a --target-time just 1 second after the backup finished ... same error.

[Luca Ferrari]

On Thu, Mar 10, 2022 at 4:06 PM dulhaver via Barman, Backup and

Recovery Manager for PostgreSQL <pgba...@googlegroups.com> wrote:
>
> hi Luca,
>
> On 10.03.22 14:34, Luca Ferrari wrote:
>
> On Thu, Mar 10, 2022 at 1:13 PM dulhaver via Barman, Backup and Recovery Manager for PostgreSQL <pgba...@googlegroups.com> wrote:
>
> barman recover [postgre-server] [backup_ID] --target-time 20220310T103800 /some/path/
>
> Could it be that the target time is too far in the future?
>
> the complete recover command (maybe I should have posted that from the start) is
>
> barman recover postgres-local 20220310T080939 --target-time 20220310T103800 recover/
>
> so recovery time is 2.5 hours after the backup. Is that too far away?

No, with "too far" I meant that you could be asking the database to
resume to a time it has not seen. Is there activity on the database
after the backup or this is a test and you are not doing anything?
because it could be that you are asking to recover to some time that
did not exist in the database transaction flow. At least, this is what
seems to me since you are able to recover to the backup and not any
second later.

> hm, did we not just establish that I need to have archive_mode = off and/or archive_command - /bin/true as well as archiver = off (in barman) for streaming backups?
> In my understanding this qualifies for PostgreSQL not archiving WALs. Maybe I am not getting what you mean here by "PostgreSQL archiving WALs".
>

Yes, I wrote "archiving" because I did not remember you were doing
streaming. Is streaming active thus?

Luca