Allow Creation of Multiple Databases

[MarkCupitt]

The current bootstrap: initdb allows for the creation of a single database with its associated user/owner, for example:

  bootstrap:
    initdb:
      database: bonanza
      owner: $POSTGRES_APP_USER
      secret:
        name: $DB_CLUSTER_NAME.app.user
      dataChecksums: true
      encoding: 'UTF8'

We have a K8's application that uses several databases and we would like to create them all at once in this manner if at all possible

  bootstrap:
    initdb:
      - database: db1
        owner: user1
        secret:
          name: app.user1
        dataChecksums: true
        encoding: 'UTF8'
      - database: db2
        owner: user2
        secret:
          name: app.user2
        dataChecksums: false
        encoding: 'LATIN1'

[phisco]

Not supporting multiple databases was a design decision we took to push a "1 database per cluster" approach. So I don't think we are going to support it in the future.
Maybe @gbartolini can add something about it, otherwise I'd say we can close this issue.

[gbartolini]

I confirm though what Philippe says that for the moment our priority was microservice databases. So for example, in your case @MarkCupitt you can probably get better results by having each database being mapped in a separate Postgres cluster. There's a FAQ item that explains the reasoning behind this approach: https://github.com/cloudnative-pg/cloudnative-pg/blob/main/docs/src/faq.md

In any case, there is an open item in the aspirational roadmap called "Declarative Database Management" - see https://github.com/orgs/cloudnative-pg/projects/1. This is also related to the "Declarative Role Management" item.

[MarkCupitt]

Thanks @phisco and @gbartolini, really appreciate your time to comment, We actually read and reviewed your Blog, which makes some excellent points,. our issue is that we are likely to have well over 20 databases when we hit production, (our use case is in the FinTech area) whilst we totally agree with the one database per micro-service approach, the pod count will simply drown us if we adopt one database per db cluster .. We will be watching closely as we really like the approach you have adopted .. and when it makes sense we will split back out that way. Im thinking we may group the lighter use databases in one cluster and split out the heavy use ones in the final deployment approach

[stevehipwell]

I'm also interested in this behaviour. I don't think multiple databases per cluster breaks the single database per microservice concept (which I consider an almost un-breakable requirement), I do agree that single database per cluster is architecturally superior but a pragmatic approach should be taken. Adding init support for multiple databases is better than leaving database creation to imperative operations, but I think declarative database (and role) management is a strong requirement. IMHO it makes more sense for this operator to support database/role management than requiring the addition of something like CrossPlane.

[onedr0p]

I agree with @stevehipwell, not supporting creating multiple databases just leaves it up to having people use external tools like terraform, ansible or bash glue or whatever to have that support where having it built into cnpg would be only a few extra lines of config. Not everyone can follow the "1 database per cluster" method so easily.

[sxd]

Turning this into a discussion, so we can make a decision after discussing and iterate into a good idea.
Probably having a definition of a database that can be pulled from one side (backup or other postgresql instance)
and being push into a cnpg cluster is one

[bo0tzz]

An approach I took in my home setup for multiple applications to use the same Postgres instance is to use https://github.com/movetokube/postgres-operator, that might be useful to some here. I do agree that having something like that built into CNPG would be quite nice.

[leonardoce]

I know it's nothing more than a hack and you'll not have your passwords reconciled, but until we the "Declarative Database/Role Management" is done you could do something like this:

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: cluster-example
spec:
  bootstrap:
    initdb:
      database: app1
      owner: app1
      postInitSQL:
        - CREATE USER app2
        - CREATE DATABASE app2 OWNER app2
        - CREATE USER app3
        - CREATE DATABASE app3 OWNER app3

  instances: 3

  storage:
    size: 1Gi

This will result in:

postgres=# \l
                             List of databases
   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges
-----------+----------+----------+---------+-------+-----------------------
 app1      | app1     | UTF8     | C       | C     |
 app2      | app2     | UTF8     | C       | C     |
 app3      | app3     | UTF8     | C       | C     |
 postgres  | postgres | UTF8     | C       | C     |
 template0 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
 template1 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres

Will this help?

[ilbarone87]

@leonardoce in case of restoring the DB from a backup (e.g. from an S3 bucket), will all DBs created in this way be restored as well?

[paraddise]

I'm getting such erorr

initdb Error: while configuring new instance: could not execute init Application queries: ERROR: CREATE DATABASE cannot run inside a transaction block (SQLSTATE 25001)

when trying to evaluate this script

CREATE DATABASE secondb; GRANT ALL PRIVILEGES ON DATABASE seconddb TO app;

Did anybody face this error?

[paraddise]

CREATE DATABASE secondb OWNER app;

doesnt change anything

[paraddise]

Here https://github.com/cloudnative-pg/cloudnative-pg/blob/main/pkg/management/postgres/initdb.go#L267
post init scripts execute as non superuser, so we need a way to run scripts as superuser in initdb.

[paraddise]

Sorry, my fault
postInitSQL scripts run as super user
postInitApplicationSQL scripts run as created user on the specified database

[lwj5]

One MariaDB Operator has such a feature inbuilt which uses multiple CRDs to achieve user/database management. Hope that this will help pave the way for the PostgreSQL operator

See
https://github.com/mariadb-operator/mariadb-operator/blob/main/examples/manifests/mariadb_v1alpha1_database.yaml
https://github.com/mariadb-operator/mariadb-operator/blob/main/examples/manifests/mariadb_v1alpha1_user.yaml
https://github.com/mariadb-operator/mariadb-operator/blob/main/examples/manifests/mariadb_v1alpha1_grant.yaml

This helps to decouple the cluster management and the database management so that the devops and the dev team can manage them separately

[red-lichtie]

I was having the same thoughts @lwj5 - Allow declarative management of Databases #3109

[fmiqbal]

having multiple database with each, has minimum 2 replica, has its own pooler, has its own metric collector,

I think for my current usage its overkill, although my biggest concern right now is the PVC usage that directly increase Longhorn manager memory usage linearly (with each cnpg pvc created), I think I'd rather have single clustered postgresql, rather than many clustered postgresql

Also in the end, I still can create database manually, wait I still can create database manually and it wont be synced or purged by CNPG right ?, or am I stuck withh having single DB if I want to use CNPG ?

[aleksasiriski]

You can create additional DBs and users manually, it will be backed up and restored from WALs correctly. You can even use the mentioned operator for making declarative DBs and users using K8s CRDs. I've been running like this for almost a year now, no problems encountered. It would still be great if it was part of CNPG itself.