CloudNativePG (CNPG) supports bootstrapping a cluster with initdb, letting you supply initialization options declaratively through a Kubernetes manifest. The BootstrapInitDB configuration accepts custom options alongside the standard cluster spec, so users, roles, databases, encoding and checksum settings all live in version-controlled YAML.
An initdb bootstrap manifest
The following manifest creates a cluster named cluster-example-initdb using the initdb bootstrap method.
apiVersion: v1
data:
username: Y3liZXJ0ZWM=
password: Y3liZXJ0ZWMxMjM=
kind: Secret
metadata:
name: app-secret
type: kubernetes.io/basic-auth
---
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
name: cluster-example-initdb
spec:
instances: 1
bootstrap:
initdb:
database: cybertec
owner: cybertec
dataChecksums: true
encoding: 'LATIN1'
secret:
name: app-secret
managed:
roles:
- name: nikola
login: true
superuser: false
createdb: true
- name: tesla
superuser: true
login: true
storage:
size: 1GiIt does four things:
- creates a Kubernetes secret (
app-secret) holding the password for the usercybertec - initializes a database named
cybertecowned by the usercybertec - sets the database encoding to
LATIN1and enables data checksums - creates the roles
nikola(withcreatedb) andtesla(as asuperuser)
Other available options are documented under bootstrapinitdb. Apply the manifest:
kubectl apply -f single_initdb.yamlChecking the cluster
Confirm the pod is running:
kubectl get pods
NAME READY STATUS RESTARTS AGE
cluster-example-initdb-1 1/1 Running 0 2m34s
cnpg-controller-manager-6848689f4-j756l 1/1 Running 0 41hThen enter the pod to reach PostgreSQL:
kubectl exec -it pod/cluster-example-initdb-1 -- /bin/bash
Defaulted container "postgres" out of: postgres, bootstrap-controller (init)
postgres@cluster-example-initdb-1:/$ psql
psql (17.5 (Debian 17.5-1.pgdg110+1))
Type "help" for help.
postgres=# \du
List of roles
Role name | Attributes
----------------+------------------------------------------------------------
cybertec |
nikola | Create DB
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS
streaming_replica | Replication
tesla | Superuser
postgres=# \l cybertec
List of databases
Name | Owner | Encoding | Locale Provider | Collate | Ctype | Locale | ICU Rules | Access privileges
----------+----------+----------+-----------------+---------+--------+--------+-----------+-------------------
cybertec | cybertec | LATIN1 | libc | C | C | | |
(1 row)
postgres=# show data_checksums;
data_checksums
----------------
on
(1 row)Adding roles and databases
Beyond the bootstrap step, roles and databases can be created imperatively through kubectl exec:
kubectl exec -it pod/cluster-example-initdb-1 -- \
psql -U postgres -c "CREATE ROLE marie LOGIN PASSWORD 'secret';"
kubectl exec -it pod/cluster-example-initdb-1 -- \
psql -U postgres -c "CREATE DATABASE curie OWNER marie;"They can also be managed declaratively with the managed and Database custom resources. A separate database.yaml manifest defines a database:
apiVersion: postgresql.cnpg.io/v1
kind: Database
metadata:
name: db-one
spec:
name: one
owner: app
cluster:
name: cybertec-single-instance-exampleApply both manifests:
kubectl apply -f single.yaml
kubectl apply -f database.yamlThis gives you different strategies for managing database infrastructure through IaC. Another manifest shows a further option:
apiVersion: v1
kind: Secret
metadata:
name: cybertec-secret
type: Opaque
stringData:
secret.sql: |
CREATE ROLE cybertec WITH LOGIN PASSWORD 's3cr3t';
GRANT ALL PRIVILEGES ON DATABASE app TO cybertec;
---
apiVersion: v1
kind: ConfigMap
metadata:
name: my-configmap
data:
configmap.sql: |
CREATE TABLE IF NOT EXISTS demo (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL
);
---
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
name: cluster-example-initdb
spec:
instances: 3
bootstrap:
initdb:
database: app
owner: app
postInitApplicationSQLRefs:
secretRefs:
- name: my-secret
key: secret.sql
configMapRefs:
- name: my-configmap
key: configmap.sql
storage:
size: 1Gi
It creates a secret and a config map that are used after database initialization. Depending on the goal, postInitSQL, postInitApplicationSQL or postInitTemplateSQL can be used to customize the cluster and its databases.
Verifying the declarative objects
kubectl exec -it pod/cluster-example-initdb-1 -- psql -U postgres -d app -c "\du"
Defaulted container "postgres" out of: postgres, bootstrap-controller (init)
List of roles
Role name | Attributes
-------------------+------------------------------------------------------------
app |
cybertec |
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS
kubectl exec -it pod/cluster-example-initdb-1 -- psql -U postgres -d app -c "\l app"
Defaulted container "postgres" out of: postgres, bootstrap-controller (init)
List of databases
Name | Owner | Encoding | Locale Provider | Collate | Ctype | Locale | ICU Rules | Access privileges
------+-------+----------+-----------------+---------+-------+--------+-----------+-------------------
app | app | UTF8 | libc | C | C | | | =Tc/app +
| | | | | | | | app=CTc/app +
| | | | | | | | cybertec=CTc/app
(1 row)
kubectl exec -it pod/cluster-example-initdb-1 -- psql -U postgres -d app -c "\d"
Defaulted container "postgres" out of: postgres, bootstrap-controller (init)
List of relations
Schema | Name | Type | Owner
--------+-------------+----------+----------
public | demo | table | postgres
public | demo_id_seq | sequence | postgres
(2 rows)
The cybertec role exists and has been granted user on the app database, and the demo table was created as expected.
Exposing the cluster outside Kubernetes
Connections from outside the cluster require a custom service configuration. This manifest disables the defaults and declares its own access path:
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
name: cluster-example-initdb
spec:
instances: 1
bootstrap:
initdb:
database: cybertec
owner: cybertec
secret:
name: app-secret
managed:
roles:
- name: nikola
login: true
superuser: false
createdb: true
- name: tesla
superuser: true
login: true
services: ## disable the default services
disabledDefaultServices: ["ro", "r"]
additional:
- selectorType: rw
serviceTemplate:
metadata:
name: "test-rw"
labels:
test-label: "true"
annotations:
test-annotation: "true"
spec:
type: LoadBalancer
## In order to static IP address
## LoadBalancerIP: Your Static IP
ports:
- port: 5432
targetPort: 5432
protocol: TCP
name: postgres
storage:
size: 1GiThe configuration exposes the writable pod through a LoadBalancer service named test-rw, and disables the default read-only services (-ro, -r). Without that change, CNPG creates three services by default: rw, ro and r. Apply the cluster configuration:
kubectl apply -f cluster-initdb-lb.yamlWait until the pod and service are ready:
kubectl get service
NAME TYPE CLUSTER-IP EXTERNAL-IP PORT(S) AGE
cluster-example-initdb-rw ClusterIP 10.106.204.97 None 5432/TCP 5s
cnpg-webhook-service ClusterIP 10.98.146.114 None 443/TCP 2d10h
test-rw LoadBalancer 10.109.19.23 10.109.19.23 5432:31306/TCP 5s
kubectl describe service test-rw
Name: test-rw
Namespace: cnpg-system
Labels: cnpg.io/cluster=cluster-example-initdb
cnpg.io/isManaged=true
test-label=true
Annotations: cnpg.io/operatorVersion: 1.26.0
cnpg.io/updateStrategy: patch
test-annotation: true
Selector: cnpg.io/cluster=cluster-example-initdb,cnpg.io/instanceRole=primary
Type: LoadBalancer
IP Family Policy: SingleStack
IP Families: IPv4
IP: 10.109.19.23
IPs: 10.109.19.23
LoadBalancer Ingress: 10.109.19.23 (VIP)
Port: postgres 5432/TCP
TargetPort: 5432/TCP
NodePort: postgres 31306/TCP
Endpoints: 10.244.0.51:5432
Session Affinity: None
External Traffic Policy: Cluster
Internal Traffic Policy: Cluster
Events: NoneInspect the custom service:
kubectl describe service test-rwThe notable value here is LoadBalancer Ingress: 10.109.19.23. Although no static IP was requested, minikube assigned one to the service. That address, together with the target port 5432, is not usable for connecting from outside the cluster; the minikube IP on the local machine is used instead. Retrieve the full connection details:
minikube service test-rw -n cnpg-system --urlA sample output looks like this:
http://192.168.39.229:31306Connect with psql:
psql -h 192.168.39.229 -U postgres -p 31306Supply the password when prompted. Once the configuration is correct, the cluster can be queried as usual.
psql (15.13), server 17.5
SSL connection (protocol: TLSv1.3)
Type "help" for help.Takeaway
CNPG brings infrastructure-as-code discipline to PostgreSQL: bootstrap options, roles and databases are expressed as manifests, which keeps operations consistent and automatable whether the target is Minikube or a full cloud environment.
