0

I run our company's DBMS in containers, and when creating a container, the ansible deployment role mounts the host file /etc/localtime along the same path into the container (although, as far as I understand, the behavior would be the same if the DBMS was running as a service on the host).

In previous instances, the local time zone (other than UTC) was set in postgresql.conf, but now one of the development teams has decided to work with the UTC time zone in DBMS. I created a new test instance (version 15.3) for them and specified TimeZone = 'Etc/UTC' in the configuration, and an non-obvious problem arose, which cost me a considerable number of working hours before I finally figured out what was happening:

Despite the fact that the query show timezone; showed the time zone Etc/UTC, in fact all requests related to the time output (for example select current_timestamp;) worked as if the time zone from /etc/localtime was really there. If I'm through SET TimeZone TO '…'; specified other time zone except Etc/UTC — the behavior became expected, this other time zone was applied. When returning to Etc/UTC, the time zone from /etc/localtime was returned again.

Etc/UTC behaves as expected only if I did not mount the file from the host at all, in which case a file with the UTC zone from the container image is used. However, I think in this case most likely the behavior is not really different, it's just that here the time zone from the file coincided with the DBMS setting.

Does the Etc/UTC time zone have any special meaning (something like «consider that the system time zone is used as UTC») or is this not a feature but a bug?

What would be more correct for me to do: remove the /etc/localtime mount and use Etc/UTC or mount the file and change the time zone to, for example, GMT or +00?

Addition

Queries (executed in pgcli):

> show timezone;
+----------+
| TimeZone |
|----------|
| Etc/UTC  |
+----------+

> select * from pg_timezone_names limit 2000;
+----------------------------------------+--------+------------------+--------+
| name                                   | abbrev | utc_offset       | is_dst |
|----------------------------------------+--------+------------------+--------|
| …                                      |        |                  |        |
| Etc/GMT0                               | GMT    | 0:00:00          | False  |
| Etc/Greenwich                          | GMT    | 0:00:00          | False  |
| Etc/UCT                                | CEST   | 2:00:00          | True   |
| Etc/UTC                                | CEST   | 2:00:00          | True   |
| Etc/Universal                          | CEST   | 2:00:00          | True   |
| Etc/Zulu                               | CEST   | 2:00:00          | True   |
| …                                      |        |                  |        |
+----------------------------------------+--------+------------------+--------+

> select current_timestamp;
+-------------------------------+
| current_timestamp             |
|-------------------------------|
| 2024-04-04 06:18:05.994608+02 |
+-------------------------------+

> set TimeZone to 'Etc/GMT';
> select current_timestamp;
+-------------------------------+
| current_timestamp             |
|-------------------------------|
| 2024-04-04 04:21:06.581204+00 |
+-------------------------------+
5
  • What client were you connected with and from where while you were running those test queries (show timezone; and select current_timestamp;)? Also, please explicitly show the output, not just your interpretation of it.
    – jjanes
    Apr 3 at 15:53
  • @jjanes I added the information you requested
    – strafer
    Apr 4 at 4:30
  • I will also say that in the examples of requests, the /usr/share/zoneinfo/Europe/Amsterdam zone is mounted in the container. If, for example, I mount /usr/share/zoneinfo/Australia/Brisbane, then all +2 will change to +10 and conceptually the behavior will be the same (Etc/UTC will become the Australian time zone)
    – strafer
    Apr 4 at 4:41
  • I know that /etc/localtime is supposed to be a symlink, not a copy or a hardlink. Maybe the mounting of the file you talk about is messing that up. That mounting business sounds weird to me, is that just how ansible works, or is something you manually configure it to do?
    – jjanes
    Apr 4 at 18:32
  • The ansible has nothing to do with it, it's our standard configuration. Yes, it may look strange, but anyway, should the behavior of the DBMS depend on this if I explicitly specify the desired time zone in its configuration?
    – strafer
    Apr 6 at 11:21

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.