1

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.

1 Answer 1

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.

3
  • Thank you! Sounds like what I want is a relocatable build: begriffs.com/posts/2016-05-21-relocatable-postgresql.html
    – Joe
    Nov 24, 2021 at 6:05
  • Perhaps. First, decide if you want to use the system tzdata or the one that comes with PostgreSQL. Don't you install PostgreSQL in a fixed location? Nov 24, 2021 at 6:41
  • The use-case is spinning up ephemeral Postgres databases for each "unit" test. If I can isolate Postgres within my build system (bazel) and not rely on an external system directories, it's easier to onboard new devs and tests are reproducible across machines.
    – Joe
    Jan 8 at 17:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.