Support the encoding, locale and template options of CREATE DATABASE in the Database CR #76

Current behavior

DatabaseService.createDatabase() runs dsl.createDatabase(name) without options. PostgreSQL then copies the encoding and the locale of template1. The DatabaseSpec has no fields to set them.

PostgreSQL sets the encoding and the locale only at CREATE DATABASE. ALTER DATABASE cannot change them later. The only fix is a new database and a full dump and restore.

On a CloudNativePG cluster, template1 uses the locale C by default. Every database that the operator creates thus gets the C collation and the C character type:

• ORDER BY on text sorts by byte value. All uppercase letters come before all lowercase letters, and Ä, Ö and Ü come after z.
• lower(), upper() and ILIKE change the case of ASCII letters only. A search for ötztal does not find Ötztal.

We found this in a migration from the official postgres image, which uses en_US.utf8, to CloudNativePG. The Database CR of CloudNativePG supports these options, but it works only with a CloudNativePG cluster. On AWS RDS, Cloud SQL or Azure, no operator can set them.

Proposal

Add these optional fields to DatabaseSpec. Each field maps to the option of CREATE DATABASE with the same name. The names are the same as in the Database CR of CloudNativePG.

Example:

apiVersion: postgresql.aboutbits.it/v1
kind: Database
metadata:
  name: my-database
spec:
  clusterRef:
    name: my-postgres-connection
  name: my_database
  owner: my_role
  template: template0
  encoding: UTF8
  localeProvider: icu
  icuLocale: und

Rules

1. Immutable: The fields are immutable like name. Use a @ValidationRule with self == oldSelf, so that the API server rejects a change before the operator sees it.
2. Cross-field validation:
   • icuLocale and icuRules require localeProvider: icu.
   • builtinLocale requires localeProvider: builtin.
3. Server version: Before CREATE DATABASE, compare the server version with the table above. If the server is too old, set an error in the status that names the field and the minimum version. Do not send the SQL.
4. Existing database: If the database exists, do not alter or drop it. Compare the set fields with pg_database and report a difference in a status condition, for example LocaleMismatch. This agrees with reclaimPolicy: Retain.
5. pg_database columns: The ICU locale is in daticulocale on PostgreSQL 15 and 16, and in datlocale on PostgreSQL 17 and later. The comparison must select the column by server version.
6. No null in the manifest: Like the other optional fields, the operator must not default these fields. Omitted fields stay omitted. Otherwise Terraform shows a change on every plan, see docs/terraform.md.

Documentation

• docs/database.md: Add the fields and the version table.
• docs/database.md: Explain that a locale other than the locale of template1 requires template: template0. Without it, PostgreSQL rejects the command, and its error message does not mention template0.
• docs/terraform.md: Add the new fields to the table "Affected fields".

Acceptance criteria

• A Database with template: template0, encoding: UTF8, localeProvider: icu and icuLocale: und creates a database with these values in pg_database.
• A Database without the new fields creates the database as before.
• The API server rejects a change of a field that is set.
• The API server rejects icuLocale without localeProvider: icu, and builtinLocale without localeProvider: builtin.
• On a server that is too old for a field, the status shows an error that names the field and the minimum version.
• For an existing database with a different locale, the status shows the difference, and the operator does not change the database.
• Tests cover PostgreSQL 15, 16 and 17 or later, because of the pg_database column change.

Out of scope

These are possible follow-ups, each in its own issue:

• connectionLimit and allowConnections. These options can change with ALTER DATABASE, so the operator can reconcile them like the owner.
• An Extension CR. pg_dump --schema does not dump extensions, so a restore needs them in the target database first.