To avoid confusion with time settings globally and to ensure that customers don't have data sync issues, PostgreSQL allows users to set different timezone parameters within their systems. Here are some examples listed below.
EDB recommends using UTC everywhere to ensure that replication works as expected, whether this is for physical or logical replication (including BDR and pglogical).
TPAexec sets the OS timezone to UTC at initdb time, so that the server timezone
and log_timezone are both set to UTC.
Messages sent to the server log are timestamped using the log_timezone
parameter, which is set to the OS timezone at initdb. Server log messages are
timestamped if the log_line_prefix contains %t, which includes the timezone.
Comparing logs may be more difficult if done manually when the server logs on
different nodes use a different log_timezone, which is why TPAexec forces
log_timezone = UTC as best practice.
User sessions start in the server timezone (TimeZone configuration parameter),
which is set to the OS timezone at initdb time.
User sessions may be specifically set away from the server timezone using SET TIME ZONE, a SQL Standard command, supported by PostgreSQL. If you do set
timezones in your sessions you should make sure your data tables use the
timestamp with time zone data type. The timestamp datatype (which is can be
expanded to full data type name of timestamp without time zone) does not store
the timezone information so data inserted into that datatype will have timezone
data truncated. If the data is then viewed in a different timezone to the one in
which the data was originally taken then this can appear to be different times.
We do not recommend using the timestamp datatype, preferring instead
timestamp with time zone, since it occupies same on-disk space, just has
different semantics. Note that changing type using ALTER TABLE is not
optimized (unless you are already in UTC).
When a transaction ends, the COMMIT record is inserted into the WAL with the
commit timestamp set using the current OS time, returned as a timestamp with time zone datatype. This does not use the value of the log_timezone
parameter, the server timezone, nor is it influenced by the current session
timezone, but that doesn't matter because the timezone value is stored. Viewing
commit timestamps can be performed using pg_waldump, noting that the displayed
timezone will be the OS timezone at the time of execution. When two nodes have
differing timezones it may be confusing to compare pg_waldump output, so best
practice would be to always use TZ=UTC when executing pg_waldump.
As an example, here is what we get from pg_waldump with the Europe/Paris
timezone:
postgres@kismet:~ $ TZ=Europe/Paris pg_waldump
/opt/postgres/data/pg_wal/000000010000000000000010 |grep COMMIT|grep 'lsn:
0/10367B70' rmgr: Transaction len (rec/tot): 46/ 46, tx: 459145, top: 0, lsn:
0/10367B70, prev 0/10367B08, desc: COMMIT 2020-01-23 05:45:37.787435 CET
Compared to what we get with the UTC timezone:
postgres@kismet:~ $ TZ=UTC pg_waldump
/opt/postgres/data/pg_wal/000000010000000000000010 |grep COMMIT|grep 'lsn:
0/10367B70' rmgr: Transaction len (rec/tot): 46/ 46, tx: 459145, top: 0, lsn:
0/10367B70, prev 0/10367B08, desc: COMMIT 2020-01-23 04:45:37.787435 UTC
This means that when BDR uses Timestamp Conflict Detection it will correctly compare commit timestamps from multiple nodes even when those servers use different timezone settings. Similarly, other logical replication such as pglogical and PostgreSQL 10+ Logical Replication will also handle timezone differences between servers correctly.
Note also that there are at least 3 timezone abbreviations that are associated with multiple timezones, e.g. IST and CST mean different things and hence have different timezone offsets in different countries. UTC is always the same timezone in all countries, as per the ISO8601 standard. UCT, UT, Z, ZULU are all synonyms for UTC, but we encourage the explicit use of UTC.