Postgres initdb with custom timezone directory

Question

How can I run Postgres initdb with a custom timezone folder?

I've tried to use the -L flag and PGSHAREDIR env var to control where initdb searches for Postgres share data. However, those options don't seem to control how Postgres finds the timezone folder.

# CUSTOM_PG is a directory containing a complete, extracted
# installation of Postgres.
CUSTOM_PG=/my-custom-pg

PGSHAREDIR="$CUSTOM_PG/share/postgresql" \
DYLD_LIBRARY_PATH="$CUSTOM_PG/lib"
$CUSTOM_PG/bin/initdb \
    --pgdata /tmp/my-data \
    --username=joe \
    --debug \
    -L "$CUSTOM_PG/share/postgresql"

Full context: I'm running Postgres with Bazel and I'd like to manage all of Postgres's dependencies with Bazel. The Postgres install, taken from Homebrew, searches for timezone data in /opt/homebrew/share/postgresql/timezone (which is outside Bazel's control), and I can't figure out how to override the timezone directory to the one managed by Bazel.

Here's the relevant logs from the initdb invocation above:

creating directory /tmp/my-data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 20
selecting default shared_buffers ... 400kB
selecting default time zone ... America/Los_Angeles
creating configuration files ... ok 
running bootstrap script ... 2021-11-23 01:56:52.567
[18352] LOG:  could not open directory "/opt/homebrew/share/postgresql/timezone": No such file or directory
INFO    pginit/cluster.go:478   [postgres] invalid value for parameter "log_timezone": "UTC"
[18352] LOG:  could not open directory "/opt/homebrew/share/postgresql/timezone": No such file or directory
[18352] LOG:  invalid value for parameter "TimeZone": "UTC"
[18352] FATAL:  configuration file "/var/folders/sb/f9x1r1p13/T/test-cluster-3607517384/postgresql.conf" contains errors

It looks like initdb starts a Postgres process (with PID 18352) after creating a default config file. The started process doesn't seem to use PGSHAREDATA to locate the the timezone folder.

Related resources:

• pgsql-bugs #4344 initdb -L timezone directory: Bug from 2008 about setting a custom location for the timezone folder. Tom Lane mentions that -L is probably the wrong method and hints that PGSHAREDIR might work. Peter Eisentraut confirms that -L exists only for legacy reasons. This is the same question I've asked above.

Answer 1

You cannot specify that during initdb. The location of the time zone files is compiled into PostgreSQL, and you can only change it when you configure and build PostgreSQL.

See the documentation:

--with-system-tzdata=DIRECTORY

PostgreSQL includes its own time zone database, which it requires for date and time operations. This time zone database is in fact compatible with the IANA time zone database provided by many operating systems such as FreeBSD, Linux, and Solaris, so it would be redundant to install it again. When this option is used, the system-supplied time zone database in DIRECTORY is used instead of the one included in the PostgreSQL source distribution. DIRECTORY must be specified as an absolute path. /usr/share/zoneinfo is a likely directory on some operating systems. Note that the installation routine will not detect mismatching or erroneous time zone data. If you use this option, you are advised to run the regression tests to verify that the time zone data you have pointed to works correctly with PostgreSQL.

You can check how your PostgreSQL was built with

pg_config --configure

If that option was not used, PostgreSQL will use its own time zone database, located in the SHAREDIR, which you can find with

pg_config --sharedir

The -L option of initdb specifies where initdb will look for postgres.bki, pg_hba.conf.sample, pg_ident.conf.sample, postgresql.conf.sample, snowball_create.sql, information_schema.sql, sql_features.txt, system_constraints.sql, system_functions.sql and system_views.sql.

You misread the pgsql-bugs thread: there is no way to specify the location of the share directory during initdb. That is also hard-wired.