VS: BUG #16404: PostgreSQL 12.2: Database restore does not accept the EEST time zone.

Hi,

I hope the following commands tell you how the problem occurs.

# date
Mon May 4 10:21:53 EEST 2020

postgresql.conf

restore_command = 'unxz -k < /srv/pgbackup/12/data/archive/%f.xz > %p'
recovery_target_time = '2020-05-04 09:54:30 EEST'
recovery_target_action = 'pause'

touch recovery.signal

# systemctl start postgresql-12
Job for postgresql-12.service failed because the control process exited with error code. See "systemctl status postgresql-12.service" and "journalctl -xe" for details.

-- Unit postgresql-12.service has begun starting up.
May 04 10:03:02 reijo01 postmaster[15774]: 2020-05-04 10:03:02.696 EEST [15774] LOG: invalid value for parameter "recovery_target_time": "2020-05-04 09:54:30 EEST"
May 04 10:03:02 reijo01 postmaster[15774]: 2020-05-04 10:03:02.696 EEST [15774] FATAL: configuration file "/var/lib/pgsql/12/data/postgresql.conf" contains errors
May 04 10:03:02 reijo01 systemd[1]: postgresql-12.service: main process exited, code=exited, status=1/FAILURE
May 04 10:03:02 reijo01 systemd[1]: Failed to start PostgreSQL 12 database server.
-- Subject: Unit postgresql-12.service has failed
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel

postgresql.conf

restore_command = 'unxz -k < /srv/pgbackup/12/data/archive/%f.xz > %p'
recovery_target_time = '2020-05-04 08:54:30 EET' /** I have to reduce one hour because of the time zone.
recovery_target_action = 'pause'

touch recovery.signal

systemctl start postgresql-12

tail -f postgresql-Mon.log
2020-05-04 10:09:13.601 EEST [16152] LOG: database system was interrupted; last known up at 2020-05-04 09:51:21 EEST
sh: /srv/pgbackup/12/data/archive/00000004.history.xz: No such file or directory
2020-05-04 10:09:13.620 EEST [16152] LOG: starting point-in-time recovery to 2020-05-04 09:54:30+03
2020-05-04 10:09:13.631 EEST [16152] LOG: restored log file "00000003.history" from archive
2020-05-04 10:09:13.676 EEST [16152] LOG: restored log file "000000030000000000000036" from archive
2020-05-04 10:09:13.682 EEST [16152] LOG: redo starts at 0/36000028
2020-05-04 10:09:13.684 EEST [16152] LOG: consistent recovery state reached at 0/36000138
2020-05-04 10:09:13.684 EEST [16150] LOG: database system is ready to accept read only connections
2020-05-04 10:09:13.844 EEST [16152] LOG: restored log file "000000030000000000000037" from archive
2020-05-04 10:09:14.265 EEST [16152] LOG: restored log file "000000030000000000000038" from archive
2020-05-04 10:09:14.683 EEST [16152] LOG: restored log file "000000030000000000000039" from archive
2020-05-04 10:09:15.100 EEST [16152] LOG: restored log file "00000003000000000000003A" from archive
2020-05-04 10:09:15.514 EEST [16152] LOG: restored log file "00000003000000000000003B" from archive
2020-05-04 10:09:15.924 EEST [16152] LOG: restored log file "00000003000000000000003C" from archive
2020-05-04 10:09:16.333 EEST [16152] LOG: restored log file "00000003000000000000003D" from archive
2020-05-04 10:09:16.750 EEST [16152] LOG: restored log file "00000003000000000000003E" from archive
2020-05-04 10:09:17.167 EEST [16152] LOG: restored log file "00000003000000000000003F" from archive
2020-05-04 10:09:17.588 EEST [16152] LOG: restored log file "000000030000000000000040" from archive
2020-05-04 10:09:18.014 EEST [16152] LOG: restored log file "000000030000000000000041" from archive
2020-05-04 10:09:18.440 EEST [16152] LOG: restored log file "000000030000000000000042" from archive
2020-05-04 10:09:18.862 EEST [16152] LOG: restored log file "000000030000000000000043" from archive
2020-05-04 10:09:19.301 EEST [16152] LOG: restored log file "000000030000000000000044" from archive
2020-05-04 10:09:19.724 EEST [16152] LOG: restored log file "000000030000000000000045" from archive
2020-05-04 10:09:20.135 EEST [16152] LOG: restored log file "000000030000000000000046" from archive
2020-05-04 10:09:20.469 EEST [16152] LOG: restored log file "000000030000000000000047" from archive
2020-05-04 10:09:21.187 EEST [16152] LOG: restored log file "000000030000000000000048" from archive
2020-05-04 10:09:21.529 EEST [16152] LOG: recovery stopping before abort of transaction 685, time 2020-05-04 09:55:02.93947+03
2020-05-04 10:09:21.529 EEST [16152] LOG: recovery has paused
2020-05-04 10:09:21.529 EEST [16152] HINT: Execute pg_wal_replay_resume() to continue.

Regards,
Reijo Suhonen

-----Alkuperäinen viesti-----
Lähettäjä: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Lähetetty: torstai 30. huhtikuuta 2020 17.09
Vastaanottaja: Suhonen Reijo (Valtori) <reijo(dot)suhonen(at)valtori(dot)fi>
Kopio: pgsql-bugs(at)lists(dot)postgresql(dot)org
Aihe: Re: BUG #16404: PostgreSQL 12.2: Database restore does not accept the EEST time zone.

PG Bug reporting form <noreply(at)postgresql(dot)org> writes:
> Database restore does not accept the EEST time zone. PostgreSQL
> version 10 restore accept EEST timezone.

Works for me:

# select '2020-04-30 10:01:32.277852 EEST'::timestamptz;
timestamptz
-------------------------------
2020-04-30 03:01:32.277852-04
(1 row)

Maybe you are using a non-default timezone_abbreviations file? [1]

In any case, it's not very clear why this would be a database restore issue. Dump/restore should always use numeric UTC offsets in dumped timestamps.

Anyway, you'll need to provide a lot more detail if you want help on this.

regards, tom lane

[1] https://www.postgresql.org/docs/current/datetime-config-files.html