VS: BUG #16404: PostgreSQL 12.2: Database restore does not accept theEEST time zone. - Mailing list pgsql-bugs

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@sss.pgh.pa.us>
Lähetetty: torstai 30. huhtikuuta 2020 17.09
Vastaanottaja: Suhonen Reijo (Valtori) <reijo.suhonen@valtori.fi>
Kopio: pgsql-bugs@lists.postgresql.org
Aihe: Re: BUG #16404: PostgreSQL 12.2: Database restore does not accept the EEST time zone.

PG Bug reporting form <noreply@postgresql.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
UTCoffsets 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