Skip to content

PostgreSQL databases: step by step

Two Templates do the work, so the CLI and the console produce identical objects. See the overview for why the cluster belongs to your project and what the two roles are for.

For the same steps in the console, see the console walkthrough.

Create a cluster

You need at least one. One cluster holds many databases, so most projects create this once.

oc process -n openshift postgresql-cluster \
  -p NAME=postgres \
  -p NAMESPACE=<project> \
  -p STORAGE_SIZE=5Gi \
  | oc apply -f -

That creates two things: the Cluster, and the cluster-owner role that administers it with its generated password in a Secret named postgres-owner.

Watch it come up:

oc get clusters.postgresql.cnpg.io -n <project> -w

It reports Cluster in healthy state in under a minute on default settings.

This step is optional in a project with no cluster

Creating a Database in a project with no cluster generates one automatically - see If no cluster exists. Doing it deliberately is how you choose the storage size.

Create a database

oc process -n openshift postgresql-database \
  -p NAME=<database> \
  -p NAMESPACE=<project> \
  -p CLUSTER_NAME=postgres \
  -p DB_USER=<database>-user \
  | oc apply -f -

That creates the Database and a Secret holding the user's generated password. The user role itself is appended to the cluster by a policy when that Secret lands, so nothing further is needed.

To see the objects before applying them, drop the pipe:

oc process -n openshift postgresql-database \
  -p NAME=<database> -p NAMESPACE=<project> \
  -p CLUSTER_NAME=postgres -p DB_USER=<database>-user -o yaml

List

oc get clusters.postgresql.cnpg.io -n <project>
oc get databases.postgresql.cnpg.io -n <project>

oc get databases is ambiguous

MariaDB installs a Database kind too, so in a project running both the short form returns whichever the API server resolves first. Use the fully qualified name.

Check a database reached the cluster:

oc get databases.postgresql.cnpg.io <database> -n <project> \
  -o jsonpath='{.status.applied} {.status.message}{"\n"}'

Check the roles the cluster actually created:

oc get clusters.postgresql.cnpg.io postgres -n <project> \
  -o jsonpath='{.status.managedRolesStatus.byStatus.reconciled}{"\n"}'

Both the cluster owner and every database user should appear there. A user missing from that list exists as a Secret but not yet as a role in PostgreSQL.

Connect

The credentials are in the Secret named after the database:

envFrom:
  - secretRef:
      name: <database>-db-user

That gives the container username and password. The host is the cluster's Service, named after the cluster.

Change

Rotate a user's password - update the Secret; the operator applies it to the role:

oc patch secret <database>-db-user -n <project> --type=merge \
  -p '{"stringData":{"password":"<new-password>"}}'

Grow the cluster's disk - it can be grown, never shrunk:

oc patch clusters.postgresql.cnpg.io postgres -n <project> --type=merge \
  -p '{"spec":{"storage":{"size":"10Gi"}}}'

Add another database - run the database template again with a new NAME and DB_USER against the same CLUSTER_NAME. You do not need a second cluster.

The database name, the cluster it lives on, and its owner are all fixed after creation.

Delete

oc delete databases.postgresql.cnpg.io <database> -n <project>
oc delete secret <database>-db-user -n <project>

Deleting the object may not drop the database

spec.databaseReclaimPolicy defaults to retain, so the data stays in the cluster, still occupying storage and still holding the name. Check which state you are in:

oc get databases.postgresql.cnpg.io <database> -n <project> \
  -o jsonpath='{.spec.databaseReclaimPolicy}{"\n"}'

With retain, drop it with psql when you are genuinely done with it.

The user's role stays listed on the cluster after its Secret is deleted. Remove it from spec.managed.roles, or set that entry's ensure: absent, to drop the role itself.

Deleting the cluster removes every database on it and the storage behind them. There is no reclaim policy protecting that:

oc delete clusters.postgresql.cnpg.io postgres -n <project>