Обсуждение: BUG #16404: PostgreSQL 12.2: Database restore does not accept the EEST time zone.
BUG #16404: PostgreSQL 12.2: Database restore does not accept the EEST time zone.
От
PG Bug reporting form
Дата:
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
VS: BUG #16404: PostgreSQL 12.2: Database restore does not accept theEEST time zone.
От
"Suhonen Reijo (Valtori)"
Дата:
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