Create database with specific LC_COLLATE parameter

[asoltesz]

I can't seem to find information about how to specify locale-specific settings for initdb and the primary/replica database containers.

I need to create a database with Hungarian character handling and collation.

When I install Postgres on a VM, I have to: 1) install the hu_HU locale on the machine ("locale-gen" on Ubuntu IIRC). 2) either initdb is done with the --locale parameter, or I provide this at the creation of the database itself.

When I work with plain Docker, I usually create a derived image. In its Dockerfile, I generate/install the locale so that Postgres will have it available when databases are created.

How do I replicate this with PGO?

[jkatz]

For the PostgreSQL mechanics: cluster customization including how to handle bootstrapping is discussed here: https://access.crunchydata.com/documentation/postgres-operator/latest/advanced/custom-configuration/

You will need to set the initdb section of the bootstrap and set encoding/locale:

• https://patroni.readthedocs.io/en/latest/SETTINGS.html#bootstrap-configuration

For the container side in general, this is a far more interesting topic, even from my brief research of it. The PostgreSQL container sets a en_US.utf8 as its default from the standard packages that are installed. For the EL8 builds, we added in the en buildpack as this contains the en_US.utf8 locale.

If you have hu_HU already available in your container, the rest of this is a moot point.

There are certainly a few ways to go about solving this if locales that are not in the standard packages are required. We could in theory install all of the locale packages into the base container, but this would create a significantly larger container, so I would not go with that path.

[asoltesz]

@jkatz Thanks for your answer.

I have been thinking on the alternatives:

1) Custom, derived Docker image I could create a derived Postgres container from the CrunchyData one, build it from my Dockerfile (installing the required locale) and place the new image on Docker HUB or an internal registry. Fairly complex solution. For most PGO users, this may be too inconvenient. One would need to unserstand the Dockerfile of the CrunchyData PG image (USER root/non-root, what package manager...etc)

2) A container environment variable and an intelligent entrypoint script. The built-in PGO entrypoint could check an environment variable that requests the necessary locales. Then, it checks if the requested locales are already installed in the container and if not, it installs them with the package manager before the Postgres process starts. This is not very complex, convenient for PGO users but may not work in air-gapped networks. It may also not work if the image runs with a non-root user.

3) Adding the locale files from init containers PGO could optionally attach an init container for every non en_US locale when one or more of them are requested (locale-init-containers). A locale-init-container only contains the locale files (for a specific locale) which it dumps into a shared volume that is also mapped into the Postgres container. PGO would need to release the locale init containers for each supported locale, together with the Postgres images they extend. This would be more development on the PGO side but looks to be the most convenient for PGO users.

In both cases 2) and 3), the PGO user/admin could request the extra locales with a cluster-init parameter.

[oed-gruenerg]

We are also hitting into this problem now. We are using the pgo operator and we have to change the locale for example to "de_DE.utf8". Is there already a good solution for it, that is good documented how to solve it?

[mzwettler2]

@jkatz

We also hit this problem as we require german locales. Is there any convenient solution meanwhile?

[mzwettler2]

Is there any news now that a few years have passed?