How to pass arguments to initdb?

[bobbyd0g]

Hello, I was wondering how to create a database with initdb args --lc-collate=C and --lc-ctype=C, which are required by Matrix Synapse. I see nothing in the CRD reference for patroni bootstrap, nor in any other docs, is this configuration supported in PGO v5?

[jkatz]

Did you try:

spec:
  patroni:
    dynamicConfiguration:
      bootstrap:
        initdb:
          - encoding=C
          - locale=C

[bobbyd0g]

A ha! Thank you for the prompt response. The operator creates the database, so this must be the right key, but still unfortunately not quite running here. I tried many combinations of the flags I provided and the ones you suggested, with double dashes and quotes or without, as well as swapping the equals for a colon-space, but everything returned this error from the app once connected:

     - 'COLLATE' is set to 'en_US.utf-8'. Should be 'C'
     - 'CTYPE' is set to 'en_US.utf-8'. Should be 'C'

I'm not familiar with this configuration, so I should go learn how to double check the encoding myself just in case, but the app starts fine with a bitnami/postgres:latest db using the flags I quoted in OP. I just really prefer to work with PGO now! :) About the other issue comment -- I would never knock the docs, only fine details left to fill -- Crunchy's product and documentation are truly excellent, industry-leading work!

[bobbyd0g]

Going a little deeper -- your specified input [as well as any of mine I've tried yet], puts this in the /pgdata/pg13/patroni.dynamic.json file:

{"bootstrap": {"initdb": ["encoding=C", "locale=C"]}, "loop_wait": 10, "postgresql": {"parameters":  [ . . . ]

and generates these logs:

2021-12-18 00:36:26,546 INFO: No PostgreSQL configuration items changed, nothing to reload.
2021-12-18 00:36:26,555 INFO: Lock owner: None; I am hellsite-matrix-pgha1-nbln-0
2021-12-18 00:36:26,730 INFO: trying to bootstrap a new cluster
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.utf-8".
The default text search configuration will be set to "english".

and this psql \list:

      Name       |  Owner   | Encoding |   Collate   |    Ctype    |       Access privileges        
-----------------+----------+----------+-------------+-------------+--------------------------------
 hellsite-matrix | postgres | UTF8     | en_US.utf-8 | en_US.utf-8 | =Tc/postgres                  +
                 |          |          |             |             | postgres=CTc/postgres         +
                 |          |          |             |             | "hellsite-matrix"=CTc/postgres
 postgres        | postgres | UTF8     | en_US.utf-8 | en_US.utf-8 | 
 template0       | postgres | UTF8     | en_US.utf-8 | en_US.utf-8 | =c/postgres                   +
                 |          |          |             |             | postgres=CTc/postgres
 template1       | postgres | UTF8     | en_US.utf-8 | en_US.utf-8 | =c/postgres                   +
                 |          |          |             |             | postgres=CTc/postgres

[bobbyd0g]

I've been looking through Patroni documentation today and am still having trouble finding the missing link between filling the initdb section of this json file, and getting the actual argument to initdb, or how to tell if it got there and was rejected and overridden somehow. I suppose it must be the right place in the schema, but the wrong inputs?

The Patroni documentation does show how to fill its own configuration file, but it doesn't appear to map to the PGO CRD?

[bm-skutzke]

I've stumbled upon the same problem. As far as I understood the Patroni bootstrap documentation, we would need something like this in PGO:

spec:
  patroni:
    bootstrap:
      initdb:
        encoding: C
        locale: C
      dynamicConfiguration:
        postgresql:
          parameters:
            ...

Both, dynamic configuration settings and initdb, are part of bootstrap. Or am I wrong?

[adyanth]

Did anyone find a solution to this issue? I do not see an initdb bootstrap in the CRD, is there anywhere else we can set this?

[TheEagleByte]

Exec into the master pod of your cluster in k8s and run the following:

psql
DROP DATABASE "synapse-db";
CREATE DATABASE "synapse-db" with template=template0 owner="synapse-db" encoding=UTF8 locale='C';
GRANT ALL PRIVILEGES ON DATABASE "synapse-db" to "synapse-db";

Obviously, replace the user/role/db name with the appropriate information. You can get those values by running \l to get the db name and \du to get the user/role name.

[dsessler7]

We have a dev story in our backlog for allowing encoding/locale to be configured.

[JamesPangn]

can we know the progress of this enhancement, we are willing to create cluster with database other "UTF8" encoding.