Upgrading a 13.1-RELENG-p1 jail from python3.8 - > python3.9 as recommended in UPDATING via pkg and updating net-mgmt/netbox from 3.1.11 to 3.2.8 (most recent version as of today) in this vain results in a dysfunctional netbox installation and in non-working migration procedure, as recommended at the end of the install/upgrade process of net-mgmt/netbox. Issuing the first step: python3.9 manage.py migrate results in: Traceback (most recent call last): File "/usr/local/lib/python3.9/site-packages/django/db/backends/base/base.py", line 244, in ensure_connection self.connect() File "/usr/local/lib/python3.9/site-packages/django/utils/asyncio.py", line 26, in inner return func(*args, **kwargs) File "/usr/local/lib/python3.9/site-packages/django/db/backends/base/base.py", line 227, in connect self.init_connection_state() File "/usr/local/lib/python3.9/site-packages/django/db/backends/postgresql/base.py", line 241, in init_connection_state timezone_changed = self.ensure_timezone() File "/usr/local/lib/python3.9/site-packages/django/db/backends/postgresql/base.py", line 234, in ensure_timezone cursor.execute(self.ops.set_time_zone_sql(), [timezone_name]) psycopg2.errors.InvalidParameterValue: time zone "UTC" appears to use leap seconds DETAIL: PostgreSQL does not support leap seconds. [...] and more follow and it is always the same error report regarding PostgreSQL is not supporting leap second (using databases/postgresql13-{server|client} as from latest official repo). I tried to search the net to figure out what is wrong here, I also can't find ad hoc a solution/workaround.
(In reply to O. Hartmann from comment #0) Hi, thanks for the info. At the outset: I hadn't seen such an error message with PostgreSQL before and I assume that there a few things got mixed up. Even though ports 66b2b44abd34, which primarily fixes another problem, is unlikely to solve your problem, can you please still try the recent version of www/py-dj40-django-timezone-field (= 5.0_1) to see if that solves the issue? If the problem still persists, the following information would be helpful to reproduce it in my lab environment: - Output of date(1) of the jail where the NetBox instance runs - Output of date(1) of the host system that runs the NetBox jail - Output of "grep TIME_ZONE" /usr/local/share/netbox/netbox/configuration.py" - Does the PostgreSQL server runs in the same jail as the NetBox instance? - If not, please provide an output of date(1) of the jail/host of the PostgreSQL server
P(In reply to Kai Knoblich from comment #1) # pkg info -xo www/py-dj40-django-timezone-field py39-dj40-django-timezone-field-5.0_1 www/py-dj40-django-timezone-field Problem still persists. - Output of date(1) of the jail where the NetBox instance runs: Thu Aug 25 07:41:45 CEST 2022 - Output of date(1) of the host system that runs the NetBox jail Thu Aug 25 07:41:39 CEST 2022 - Output of "grep TIME_ZONE" /usr/local/share/netbox/netbox/configuration.py" TIME_ZONE = 'UTC' - Does the PostgreSQL server runs in the same jail as the NetBox instance? No, a remotely operating DB server (also a jail, but another host, same OS basis, same jail-base basis). NOT is fullfilled, so: - If not, please provide an output of date(1) of the jail/host of the PostgreSQL server: Thu Aug 25 07:45:56 CEST 2022
The problem persists, even when setting "TIME_ZONE='Europe/Berlin'" in /usr/local/share/netbox/netbox/configuration.py and also setting on all(!) involved hosts the timezone to "Europe/Berlin". The error stays the same. All clocks on every host are set to UTC per default.
Remark: The DB server backend is running latest databases/postgresql13-server software, the netbox containing host utilizes the latest postgresql13-client package. I tried also to switch to postgresql14-client, with no effect, the error stays the same.
(In reply to O. Hartmann from comment #4) We run our dev and prod netbox 3.2.8 box in jail (pg-server and nb on the same jail), and did not observe this problem. So we need to find a way to reproduce the problem.
Well, pg-server and netbox host are separate instances, separate jails end even not residing on the same host. It might be of use, that the netbox jail had postgresql14-client as the acting pg client when the error occured. On both sides, PG server and netbox jail, /etc/localtime and /usr/share/zoneinfo are readable by world.
This bug is still putting a kind of terror onto my shoulders, I couldn't come by any solution. On the Postgresql Server (recent psql 13 from ports as updated today, Nov 1st '22), setting up some verbose logging, the server reports: [...] Nov 1 07:56:00 <local0.warn> db1 postgres[31631]: [7-1] 2022-11-01 06:56:27.772 GMT [31631] ERROR: time zone "UTC" appears to use leap seconds Nov 1 07:56:00 <local0.warn> db1 postgres[31631]: [7-2] 2022-11-01 06:56:27.772 GMT [31631] DETAIL: PostgreSQL does not support leap seconds. Nov 1 07:56:00 <local0.warn> db1 postgres[31631]: [7-3] 2022-11-01 06:56:27.772 GMT [31631] STATEMENT: SET TIME ZONE 'UTC' [...] The database seems to reject access. How can I check for the proper data type accessed by the netbox framework at that point?
(In reply to O. Hartmann from comment #7) I tried to reproduce the issue with your setup: - One VM with client (= NetBox and WWW server) jail (13.1-RELEASE-p0 amd64 Host + Jail, Timezone UTC) - One VM with server (= PostgreSQL 13 + Redis) jail (13.1-RELEASE-p0 amd64 Host + Jail, Timezone UTC) - Upgrade of NetBox 3.1.11 (Python 3.8, PostgreSQL 13.6) from ports r2022Q2 (local repo) to NetBox 3.2.9 (Python 3.9, PostgreSQL 13.7) from ports r2022Q3 (local repo) I had no issues so far and I must point out that I have only tried the upgrade with 13.1-RELEASE-p0 (and 12.3-RELEASE-p6) so far. I also got feedback that an upgrade of NetBox 3.2.8 (Python 3.8) to NetBox 3.3.6 (Python 3.9) with PostgreSQL 13 to PostgreSQL 14 went smoothly. In the meantime, have you tried updating the jails (and the host systems) from 13.1-RELEASE-p1 to at least 13.1-RELEASE-p2 (contains tzdata 2022b + 2022c) or even better, to 13.1-RELEASE-p3 (contains tzdata 2022d, 2022e and 2022f)?
(In reply to Kai Knoblich from comment #8) Since I do a regular maintenance on our hosts, all hosts are at least at FreeBSD 13.1-RELENG-p2, since today 13.1-RELENG-p3. The jails and the base of all jails get their updates on a regular basis as well. Ports are updated also on a regular basis, since today they are at the last available revision. Last week I tried to setup a complete new instance running Apache 2.4 and netbox. Only the DB backend remains the same. No success so far. As I wrote earlier in this thread, the problem seems to occur on the PostgreSQL host/jail, I can see the fault in the DB server's log and this gets reported back to the netbox base framework. The only step I have'nt performed so far is to backup the netbox DB/tables and restore them. But I fear the result is the same. I suspect while migrating from PostgreSQL 13 to PostgreSQL 14 on the DB server and migrating back to PostgreSQL 13 when it failed the first time, the netbox tables of the netbox DB on the PostgreSQL server itself got corrupted or somehow changed in a way, so netbox is unable to access them in the usual way. So, the big question here is: what variables within the netbox tables I have to look for and check for their types ...
(In reply to O. Hartmann from comment #9) That would be the next step that I would recommend to try: To create a pristine "clone" of the DB backend jail from scratch, then import the SQL dump into it and see if it works again. I assume you have SQL dumps of the state before (= NetBox 3.1.11) and after the upgrade (= NetBox 3.2.8)? If so, I'd try the SQL dump of NetBox 3.1.11 and run the migrations. Regarding your question about the variables in the netbox tables: Checking fields that are of type "timestamp" or "timestamp with time zone" would make sense if there are abnormal values there. However, time/date values out of range should actually already be recognized during user input or import, as far I can recall.
(In reply to Kai Knoblich from comment #10) I have some recent dumps, but all of them result in the same problem as described here. I have the strong suspicion that it's a PostgreSQL setup problem, since I get this error on all jails running postgresql 13.X: postgres=# SET TIME ZONE 'UTC'; ERROR: time zone "UTC" appears to use leap seconds DETAIL: PostgreSQL does not support leap seconds. or postgres=# SET TIME ZONE 'Europe/Berlin'; ERROR: time zone "Europe/Berlin" appears to use leap seconds DETAIL: PostgreSQL does not support leap seconds. and so on ... So, even without any django and django application like netbox, simply setting the timezone in psql console with a regular, valid psql statement ails out in the error django also reports initially when trying to connect the DB server.
psql (13.8) Type "help" for help. postgres=# select * from pg_timezone_names; name | abbrev | utc_offset | is_dst ------+--------+------------+-------- (0 rows) Postgresql seems not to maintain the time zone tables. Isn't the server supposed to read the TZ definitions from $LOCALBASE/share/postgresql/timezonesets/ ?
(In reply to O. Hartmann from comment #12) That's really strange and this will probably the cause of the whole issue. I'm still unable to reproduce the problem and I tried a few things: * Move /usr/share/zoneinfo to another dir -> PostgreSQL won't start * Use an empty /usr/share/zoneinfo -> PostgreSQL won't start * Compile postgresql13-server "--with-system-tzdata=/nonexistent/usr/share/zoneinfo" -> PostgreSQL won't start * Move $LOCALBASE/share/postgresql/timezonesets/ to another dir -> PostgreSQL won't start * Use an empty $LOCALBASE/share/postgresql/timezonesets/Default -> PostgreSQL starts but falls back to /usr/share/zoneinfo To narrow down the issue: * Do you have multiple jails on your host that are running PostgreSQL? * If so, are different "pgsql" UIDs in use? * Are there any custom configurations for the affected jail? * Are there any custom configurations for the DB backend of netbox (e.g. postgresql.conf, etc.)? * Do you use packages of the FreeBSD repo or do you use a local repo with a custom PostgreSQL server?
(In reply to O. Hartmann from comment #12) Hi, some time has passed, NetBox is already at version 3.6.6 and the release 3.7 is in near sight. I have not been able to reproduce the problem you described or it has become noticeable in any way in the due course. I also haven't received any feedback on my questions in comment #13 so far, so I don't know at the moment whether the problem is still current?
(In reply to Kai Knoblich from comment #14) Sorry for the delay. According to your comment #13 and its requests: - no further PostgreSQL instances within the same jail running the PostgeSQL DB - no further PostgreSQL DB on any jail on the host running the DB server jail The problem is back again, after I fixed it somehow (in a hurry in August/September) and moved from PostgreSQL 13 to PostgreSQL 15. The error is definitely initiated by the PostgreSQL server and passed on by the django framework in netbox as initially reported. I have to confess I did manage somehow to fix the problem, forgot to report back here for the solution. I'll check for notes and report back.
I have a test- and a production system running with postgres 15.4, without problems.