Postgresql 13 invalid value recovery_target_time

[cortex...@gmail.com]

Hi,

restored backup of the postgresql 13 via barman cli with defined "2021-04-15 10:00:00 CEST" as recovery target:

Doing PITR. Recovery target time: '2021-04-15 10:00:00+02:00'

barman generated postgresql.auto.conf:

# Do not edit this file manually!

# It will be overwritten by the ALTER SYSTEM command.

restore_command = 'cp barman_wal/%f %p'

recovery_end_command = 'rm -fr barman_wal'

recovery_target_time = '2021-04-15 10:00:00 CEST'

Fails with:

2021-04-15 10:31:35.405 CEST [4880] LOG:  invalid value for parameter "recovery_target_time": "2021-04-15 10:00:00 CEST"

The fix was setting recovery_target_time to:

recovery_target_time = '2021-04-15 10:00:00 Europe/Prague' (or UTC+XX)

Based on postgresql 13 documentation:

recovery_target_time (timestamp)

This parameter specifies the time stamp up to which recovery will proceed. The precise stopping point is also influenced by recovery_target_inclusive.

The value of this parameter is a time stamp in the same format accepted by the timestamp with time zone data type, except that you cannot use a time zone abbreviation (unless the timezone_abbreviations variable has been set earlier in the configuration file). Preferred style is to use a numeric offset from UTC, or you can write a full time zone name, e.g., Europe/Helsinki not EEST.

[Abhijit Menon-Sen]

On Thu, Apr 15, 2021 at 2:30 PM cortex...@gmail.com
<cortex...@gmail.com> wrote:
>
> restored backup of the postgresql 13 via barman cli with defined "2021-04-15 10:00:00 CEST" as recovery target:
>
> Doing PITR. Recovery target time: '2021-04-15 10:00:00+02:00'
>
> barman generated postgresql.auto.conf:
>
> # Do not edit this file manually!
> # It will be overwritten by the ALTER SYSTEM command.
>
> restore_command = 'cp barman_wal/%f %p'
> recovery_end_command = 'rm -fr barman_wal'
> recovery_target_time = '2021-04-15 10:00:00 CEST'
>
> Fails with:
>
> 2021-04-15 10:31:35.405 CEST [4880] LOG: invalid value for parameter "recovery_target_time": "2021-04-15 10:00:00 CEST"
>
> The fix was setting recovery_target_time to:
>
> recovery_target_time = '2021-04-15 10:00:00 Europe/Prague' (or UTC+XX)

I'm just checking if I fully understand the situation here: did you
run barman recover with `--recovery-target-time "2021-04-15 10:00:00
CEST"`?

I think it might be reasonable for Barman to warn you to specify the
timezone as a numeric offset from UTC, rather than use an abbreviated
timezone name. I do not think it would be reasonable for Barman to
change the value, however.

Or have I misunderstood the problem?

-- Abhijit

[cortex...@gmail.com]

Sorry for late reply.

Yes, i was using format from postgresql logs (so, CET/CEST, etc etc) in "barman recovery ..." cli syntax - restoration on target server worked until Postgresql 13.