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

The following bug has been logged on the website:

Bug reference:      16404
Logged by:          Reijo Suhonen
Email address:      reijo.suhonen@valtori.fi
PostgreSQL version: 12.2
Operating system:   CentOS Linux release 7.5.1804 (Core)
Description:

Database restore does not accept the EEST time zone. PostgreSQL version 10
restore accept EEST timezone.

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 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

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

"Suhonen Reijo (Valtori)" <reijo.suhonen@valtori.fi> writes:
> 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'

Ah.  I can reproduce it when I set recovery_target_time that way.

The reason seems to be that while loading the config file for the first
time, timezone_abbreviations hasn't been set yet, so the timestamp parser
doesn't know any timezone abbreviations at all.

It does work to use full time zone names (Europe/Helsinki, say)
or of course you could use a numeric UTC offset.

Another workaround is to explicitly set timezone_abbreviations
before you set recovery_target_time in the config file (i.e.,
move and uncomment the timezone_abbreviations setting).  But ugh.

Maybe it's possible to fix this, but I don't really see any non-kluge
way to do that.  I'm inclined to just document that timezone abbreviations
aren't accepted in this variable.

Note for anybody trying to reproduce this: I was misled for awhile
by the fact that a few abbreviations like "EST" *do* work.  The
reason turns out to be that the tzdata database has entries for those
names, so that our code path for full time zone names successfully
parses them after the abbreviations code fails to.

            regards, tom lane