You are looking at the documentation of a prior release. To read the documentation of the latest release, please
visit here.
New to KubeDB? Please start here.
Running PostgreSQL
This tutorial will show you how to use KubeDB to run a PostgreSQL database.
āā
Before You Begin
At first, you need to have a Kubernetes cluster, and the kubectl command-line tool must be configured to communicate with your cluster. If you do not already have a cluster, you can create one by using kind.
Now, install KubeDB cli on your workstation and KubeDB operator in your cluster following the steps here.
To keep things isolated, this tutorial uses a separate namespace called demo throughout this tutorial.
$ kubectl create ns demo
namespace/demo created
Note: YAML files used in this tutorial are stored in docs/examples/postgres folder in GitHub repository kubedb/docs.
We have designed this tutorial to demonstrate a production setup of KubeDB managed PostgreSQL. If you just want to try out KubeDB, you can bypass some of the safety features following the tips here.
Install pgAdmin
This tutorial will also use a pgAdmin to connect and test PostgreSQL database, once it is running.
Run the following command to install pgAdmin,
$ kubectl create -f https://github.com/kubedb/docs/raw/v2022.03.28/docs/examples/postgres/quickstart/pgadmin.yaml
deployment.apps/pgadmin created
service/pgadmin created
$ kubectl get pods -n demo --watch
NAME READY STATUS RESTARTS AGE
pgadmin-5b4b96779-lfpfh 0/1 ContainerCreating 0 1m
pgadmin-5b4b96779-lfpfh 1/1 Running 0 2m
^Cā
Now, you can open pgAdmin on your browser using following address http://<cluster ip>:<NodePort of pgadmin service>.
If you are using minikube then open pgAdmin in your browser by running minikube service pgadmin -n demo. Or you can get the URL of Service pgadmin by running following command
$ minikube service pgadmin -n demo --url
http://192.168.99.100:31983
To log into the pgAdmin, use username admin and password admin.
Find Available StorageClass
We will have to provide StorageClass in Postgres crd specification. Check available StorageClass in your cluster using following command,
$ kubectl get storageclass
NAME PROVISIONER AGE
standard (default) k8s.io/minikube-hostpath 5h
Here, we have standard StorageClass in our cluster.
Find Available PostgresVersion
When you have installed KubeDB, it has created PostgresVersion crd for all supported PostgreSQL versions. Let’s check available PostgresVersions by,
$ kubectl get postgresversion
NAME VERSION DISTRIBUTION DB_IMAGE DEPRECATED AGE
10.16 10.16 Official postgres:10.16-alpine 63s
10.16-debian 10.16 Official postgres:10.16 63s
10.19 10.19 Official postgres:10.19-bullseye 63s
10.19-bullseye 10.19 Official postgres:10.19-bullseye 63s
11.11 11.11 Official postgres:11.11-alpine 63s
11.11-debian 11.11 Official postgres:11.11 63s
11.14 11.14 Official postgres:11.14-alpine 63s
11.14-bullseye 11.14 Official postgres:11.14-bullseye 63s
11.14-bullseye-postgis 11.14 PostGIS postgis/postgis:11-3.1 63s
12.6 12.6 Official postgres:12.6-alpine 63s
12.6-debian 12.6 Official postgres:12.6 63s
12.9 12.9 Official postgres:12.9-alpine 63s
12.9-bullseye 12.9 Official postgres:12.9-bullseye 63s
12.9-bullseye-postgis 12.9 PostGIS postgis/postgis:12-3.1 63s
13.2 13.2 Official postgres:13.2-alpine 63s
13.2-debian 13.2 Official postgres:13.2 63s
13.5 13.5 Official postgres:13.5-alpine 63s
13.5-bullseye 13.5 Official postgres:13.5-bullseye 63s
13.5-bullseye-postgis 13.5 PostGIS postgis/postgis:13-3.1 63s
14.1 14.1 Official postgres:14.1-alpine 63s
14.1-bullseye 14.1 Official postgres:14.1-bullseye 63s
14.1-bullseye-postgis 14.1 PostGIS postgis/postgis:14-3.1 63s
9.6.21 9.6.21 Official postgres:9.6.21-alpine 63s
9.6.21-debian 9.6.21 Official postgres:9.6.21 63s
9.6.24 9.6.24 Official postgres:9.6.24-alpine 63s
9.6.24-bullseye 9.6.24 Official postgres:9.6.24-bullseye 63s
timescaledb-2.1.0-pg11 11.11 TimescaleDB timescale/timescaledb:2.1.0-pg11-oss 63s
timescaledb-2.1.0-pg12 12.6 TimescaleDB timescale/timescaledb:2.1.0-pg12-oss 63s
timescaledb-2.1.0-pg13 13.2 TimescaleDB timescale/timescaledb:2.1.0-pg13-oss 63s
timescaledb-2.5.0-pg14.1 14.1 TimescaleDB timescale/timescaledb:2.5.0-pg14-oss 63s
Notice the DEPRECATED column. Here, true means that this PostgresVersion is deprecated for current KubeDB version. KubeDB will not work for deprecated PostgresVersion.
In this tutorial, we will use 10.2-v5 PostgresVersion crd to create PostgreSQL database. To know more about what is PostgresVersion crd and why there is 10.2 and 10.2-v5 variation, please visit here. You can also see supported PostgresVersion here.
Create a PostgreSQL database
KubeDB implements a Postgres CRD to define the specification of a PostgreSQL database.
Below is the Postgres object created in this tutorial.
apiVersion: kubedb.com/v1alpha2
kind: Postgres
metadata:
name: quick-postgres
namespace: demo
spec:
version: "13.2"
storageType: Durable
storage:
storageClassName: "standard"
accessModes:
- ReadWriteOnce
resources:
requests:
storage: 1Gi
terminationPolicy: DoNotTerminate
Here,
spec.versionis name of the PostgresVersion crd where the docker images are specified. In this tutorial, a PostgreSQL 10.2 database is created.spec.storageTypespecifies the type of storage that will be used for Postgres database. It can beDurableorEphemeral. Default value of this field isDurable. IfEphemeralis used then KubeDB will create Postgres database usingEmptyDirvolume. In this case, you don’t have to specifyspec.storagefield. This is useful for testing purposes.spec.storagespecifies the size and StorageClass of PVC that will be dynamically allocated to store data for this database. This storage spec will be passed to the StatefulSet created by KubeDB operator to run database pods. You can specify any StorageClass available in your cluster with appropriate resource requests. If you don’t specifyspec.storageType: Ephemeral, then this field is required.spec.terminationPolicyspecifies what KubeDB should do when user try to delete Postgres crd. Termination policyDoNotTerminateprevents a user from deleting this object if admission webhook is enabled.
Note:
spec.storagesection is used to create PVC for database pod. It will create PVC with storage size specified instorage.resources.requestsfield. Don’t specifylimitshere. PVC does not get resized automatically.
Let’s create Postgres crd,
$ kubectl create -f https://github.com/kubedb/docs/raw/v2022.03.28/docs/examples/postgres/quickstart/quick-postgres.yaml
postgres.kubedb.com/quick-postgres created
KubeDB operator watches for Postgres objects using Kubernetes api. When a Postgres object is created, KubeDB operator will create a new StatefulSet and two ClusterIP Service with the matching name. KubeDB operator will also create a governing service for StatefulSet with the name kubedb, if one is not already present.
If you are using RBAC enabled cluster, PostgreSQL specific RBAC permission is required. For details, please visit here.
KubeDB operator sets the status.phase to Running once the database is successfully created.
$ kubectl get pg -n demo quick-postgres -o wide
NAME VERSION STATUS AGE
quick-postgres 10.2-v5 Creating 13s
Let’s describe Postgres object quick-postgres
$ kubectl dba describe pg -n demo quick-postgres
Name: quick-postgres
Namespace: demo
CreationTimestamp: Thu, 07 Feb 2019 17:03:11 +0600
Labels: <none>
Annotations: <none>
Replicas: 1 total
Status: Running
StorageType: Durable
Volume:
StorageClass: standard
Capacity: 1Gi
Access Modes: RWO
StatefulSet:
Name: quick-postgres
CreationTimestamp: Thu, 07 Feb 2019 17:03:11 +0600
Labels: app.kubernetes.io/name=postgreses.kubedb.com
app.kubernetes.io/instance=quick-postgres
Annotations: <none>
Replicas: 824641589664 desired | 1 total
Pods Status: 1 Running / 0 Waiting / 0 Succeeded / 0 Failed
Service:
Name: quick-postgres
Labels: app.kubernetes.io/name=postgreses.kubedb.com
app.kubernetes.io/instance=quick-postgres
Annotations: <none>
Type: ClusterIP
IP: 10.100.86.27
Port: api 5432/TCP
TargetPort: api/TCP
Endpoints: 172.17.0.8:5432
Service:
Name: quick-postgres-replicas
Labels: app.kubernetes.io/name=postgreses.kubedb.com
app.kubernetes.io/instance=quick-postgres
Annotations: <none>
Type: ClusterIP
IP: 10.103.133.93
Port: api 5432/TCP
TargetPort: api/TCP
Endpoints: 172.17.0.8:5432
Database Secret:
Name: quick-postgres-auth
Labels: app.kubernetes.io/name=postgreses.kubedb.com
app.kubernetes.io/instance=quick-postgres
Annotations: <none>
Type: Opaque
Data
====
POSTGRES_PASSWORD: 16 bytes
POSTGRES_USER: 8 bytes
Topology:
Type Pod StartTime Phase
---- --- --------- -----
primary quick-postgres-0 2019-02-07 17:03:12 +0600 +06 Running
No Snapshots.
Events:
Type Reason Age From Message
---- ------ ---- ---- -------
Normal Successful 51s KubeDB operator Successfully created Service
Normal Successful 51s KubeDB operator Successfully created Service
Normal Successful 25s KubeDB operator Successfully created StatefulSet
Normal Successful 25s KubeDB operator Successfully created Postgres
Normal Successful 25s KubeDB operator Successfully created appbinding
Normal Successful 25s KubeDB operator Successfully patched StatefulSet
Normal Successful 25s KubeDB operator Successfully patched Postgres
KubeDB has created two services for the Postgres object.
$ kubectl get service -n demo --selector=app.kubernetes.io/name=postgreses.kubedb.com,app.kubernetes.io/instance=quick-postgres
NAME TYPE CLUSTER-IP EXTERNAL-IP PORT(S) AGE
quick-postgres ClusterIP 10.108.152.107 <none> 5432/TCP 3m
quick-postgres-replicas ClusterIP 10.105.175.166 <none> 5432/TCP 3m
Here,
- Service
quick-postgrestargets only one Pod which is acting as primary server - Service
quick-postgres-replicastargets all Pods created by StatefulSet
KubeDB supports PostgreSQL clustering where Pod can be either primary or standby. To learn how to configure highly available PostgreSQL cluster, click here.
Here, we have created a PostgreSQL database with single node, primary only.
Connect with PostgreSQL database
KubeDB operator has created a new Secret called quick-postgres-auth for storing the username and password for postgres database.
$ kubectl get secret -n demo quick-postgres-auth -o yaml
apiVersion: v1
data:
POSTGRES_PASSWORD: REQ4aTU2VUJJY3M2M1BWTw==
POSTGRES_USER: cG9zdGdyZXM=
kind: Secret
metadata:
creationTimestamp: 2018-09-03T11:25:39Z
labels:
app.kubernetes.io/name: postgreses.kubedb.com
app.kubernetes.io/instance: quick-postgres
name: quick-postgres-auth
namespace: demo
resourceVersion: "1677"
selfLink: /api/v1/namespaces/demo/secrets/quick-postgres-auth
uid: 15b3e8a1-af6c-11e8-996d-0800270d7bae
type: Opaque
This secret contains superuser name for postgres database as POSTGRES_USER key and
password as POSTGRES_PASSWORD key. By default, superuser name is postgres and password is randomly generated.
If you want to use custom password, please create the secret manually and specify that when creating the Postgres object using spec.authSecret.name. For more details see here.
Note: Auth Secret name format:
{postgres-name}-auth
Now, you can connect to this database from the pgAdmin dashboard using quick-postgres.demo service and username and password created in quick-postgres-auth secret.
Connection information:
Host name/address: you can use any of these
- Service:
quick-postgres.demo - Pod IP: (
$ kubectl get pods quick-postgres-0 -n demo -o yaml | grep podIP)
- Service:
Port:
5432Maintenance database:
postgresUsername: Run following command to get username,
$ kubectl get secrets -n demo quick-postgres-auth -o jsonpath='{.data.\POSTGRES_USER}' | base64 -d postgresPassword: Run the following command to get password,
$ kubectl get secrets -n demo quick-postgres-auth -o jsonpath='{.data.\POSTGRES_PASSWORD}' | base64 -d DD8i56UBIcs63PVO
Now, go to pgAdmin dashboard and connect to the database using the connection information as shown below,
Halt Database
KubeDB takes advantage of ValidationWebhook feature in Kubernetes 1.9.0 or later clusters to implement DoNotTerminate termination policy. If admission webhook is enabled, it prevents user from deleting the database as long as the spec.terminationPolicy is set DoNotTerminate.
In this tutorial, Postgres quick-postgres is created with spec.terminationPolicy: DoNotTerminate. So if you try to delete this Postgres object, admission webhook will nullify the delete operation.
$ kubectl delete pg -n demo quick-postgres
Error from server (BadRequest): admission webhook "postgres.validators.kubedb.com" denied the request: postgres "quick-postgres" can't be halted. To delete, change spec.terminationPolicy
To halt the database, we have to set spec.terminationPolicy: to Halt by updating it,
$ kubectl edit pg -n demo quick-postgres
spec:
terminationPolicy: Halt
Now, if you delete the Postgres object, KubeDB operator will create a matching DormantDatabase object. This DormantDatabase object can be used to resume the database. KubeDB operator will delete the StatefulSet and its Pods but leaves the Secret, PVCs unchanged.
Let’s delete the Postgres object,
$ kubectl delete pg -n demo quick-postgres
postgres.kubedb.com "quick-postgres" deleted
Check DormantDatabase has been created successfully,
$ kubectl get drmn -n demo quick-postgres
NAME STATUS AGE
quick-postgres Halted 5m
In KubeDB parlance, we say that Postgres quick-postgres has entered into the dormant state.
Let’s see, what we have in this DormantDatabase object
$ kubectl get drmn -n demo quick-postgres -o yaml
apiVersion: kubedb.com/v1alpha2
kind: DormantDatabase
metadata:
creationTimestamp: "2019-02-07T11:05:44Z"
finalizers:
- kubedb.com
generation: 1
labels:
app.kubernetes.io/name: postgreses.kubedb.com
name: quick-postgres
namespace: demo
resourceVersion: "39020"
selfLink: /apis/kubedb.com/v1alpha2/namespaces/demo/dormantdatabases/quick-postgres
uid: 50a9c42e-2ac8-11e9-9d44-080027154f61
spec:
origin:
metadata:
creationTimestamp: "2019-02-07T11:03:11Z"
name: quick-postgres
namespace: demo
spec:
postgres:
authSecret:
name: quick-postgres-auth
leaderElection:
leaseDurationSeconds: 15
renewDeadlineSeconds: 10
retryPeriodSeconds: 2
podTemplate:
controller: {}
metadata: {}
spec:
resources: {}
replicas: 1
storage:
accessModes:
- ReadWriteOnce
dataSource: null
resources:
requests:
storage: 1Gi
storageClassName: standard
storageType: Durable
terminationPolicy: Halt
version: "10.2"-v5
status:
observedGeneration: 1$8378748355133368567
pausingTime: "2019-02-07T11:05:56Z"
phase: Halted
Here,
spec.origincontains original Postgres object.status.phasepoints to the current database stateHalted.
Resume DormantDatabase
To resume the database from the dormant state, create same Postgres object with same Spec.
In this tutorial, the DormantDatabase quick-postgres can be resumed by creating original Postgres object.
Let’s create the original Postgres object,
$ kubectl create -f https://github.com/kubedb/docs/raw/v2022.03.28/docs/examples/postgres/quickstart/quick-postgres.yaml
postgres.kubedb.com/quick-postgres created
This will resume the previous database. All data that was inserted in previous database will be available again.
When the database is resumed, respective DormantDatabase object will be removed. Verify that the DormantDatabase object has been removed,
$ kubectl get drmn -n demo quick-postgres
Error from server (NotFound): dormantdatabases.kubedb.com "quick-postgres" not found
WipeOut DormantDatabase
You can wipe out a DormantDatabase while deleting the object by setting spec.wipeOut to true. KubeDB operator will delete any relevant resources of this PostgresSQL database (i.e, PVCs, Secrets, Snapshots). It will also delete snapshot data stored in the Cloud Storage buckets.
$ kubectl edit drmn -n demo quick-postgres
spec:
wipeOut: true
If spec.wipeOut is not set to true while deleting the dormantdatabase object, then only this object will be deleted and kubedb-operator won’t delete related Secrets, PVCs, and Snapshots. So, users still can access the stored data in the cloud storage buckets as well as PVCs.
Delete DormantDatabase
As it is already discussed above, DormantDatabase can be deleted with or without wiping out the resources. To delete the dormantdatabase,
$ kubectl delete drmn -n demo quick-postgres
dormantdatabase.kubedb.com "quick-postgres" deleted
Cleaning up
To cleanup the Kubernetes resources created by this tutorial, run:
kubectl patch -n demo pg/quick-postgres -p '{"spec":{"terminationPolicy":"WipeOut"}}' --type="merge"
kubectl delete -n demo pg/quick-postgres
kubectl delete ns demo
Tips for Testing
If you are just testing some basic functionalities, you might want to avoid additional hassles due to some safety features that are great for production environment. You can follow these tips to avoid them.
- Use
storageType: Ephemeral. Databases are precious. You might not want to lose your data in your production environment if database pod fail. So, we recommend to usespec.storageType: Durableand provide storage spec inspec.storagesection. For testing purpose, you can just usespec.storageType: Ephemeral. KubeDB will use emptyDir for storage. You will not require to providespec.storagesection. - Use
terminationPolicy: WipeOut. It is nice to be able to resume database from previous one. So, we createDormantDatabaseand preserve all yourPVCs,Secrets,Snapshotsetc. If you don’t want to resume database, you can just usespec.terminationPolicy: WipeOut. It will not createDormantDatabaseand it will delete everything created by KubeDB for a particular Postgres crd when you delete the crd. For more details about termination policy, please visit here.
Next Steps
- Learn about backup and restore PostgreSQL database using Stash.
- Learn about initializing PostgreSQL with Script.
- Learn about custom PostgresVersions.
- Want to setup PostgreSQL cluster? Check how to configure Highly Available PostgreSQL Cluster
- Monitor your PostgreSQL database with KubeDB using built-in Prometheus.
- Monitor your PostgreSQL database with KubeDB using Prometheus operator.
- Detail concepts of Postgres object.
- Use private Docker registry to deploy PostgreSQL with KubeDB.
- Want to hack on KubeDB? Check our contribution guidelines.