PostgreSQL is a powerful, open source object-relational database system. It supports a large part of the SQL standard and offers many modern features to ensure the reliability, data integrity, and correctness. This makes it very easy to manage and use by any person with a little knowledge in databases.
StackGres is a full stack PostgreSQL distribution for Kubernetes, packed into an easy deployment unit. The StackGres stack consists of connection pooling, automatic failover and HA, monitoring, backups and DR, centralized logging, and many more. This stack is made with the help of Kubernetes operator and makes the easy way of packaging, deploying and managing PostgreSQL applications in Kubernetes ecosystem. A StackGres cluster is basically a StatefulSet where each pod is a database instance. The StatefulSet guarantees that each pod is always bound to its own persistent volume therefore the database instance data will be mapped to the state of a patroni instance inside kubernetes
This guide explains the basic installation for StackGres PostgreSQL on OpenEBS Local PV device. It also contains the creation of a sample database, tables and tuples. StackGres PostgreSQL is a StatefulSet type and the high performance of OpenEBS Local PV storage engine will be suitable for a persistent storage solution for PostgreSQL databases which can deal with heavy load.
We will use GKE, where we will install Stackgres PostgreSQL with OpenEBS storage engine. The Local PV volume will be provisioned on a node where Stackgres PostgreSQL pod is getting scheduled and uses one of the matching unclaimed block devices, which will then use the entire block device for storing data. No other application can use this device. If users have limited blockdevices attached to some nodes, they can use
nodeSelector in the application YAML to provision applications on particular nodes where the available block device is present. The recommended configuration is to have at least three nodes and one unclaimed external disk to be attached per node.
- Install OpenEBS
- Select OpenEBS storage engine
- Configure OpenEBS Local PV StorageClass
- Installing StackGres PostgreSQL Operator
- Installing PostgreSQL Database
- Accessing PostgreSQL database
If OpenEBS is not installed in your K8s cluster, this can be done from here. If OpenEBS is already installed, go to the next step.
A storage engine is the data plane component of the IO path of a Persistent Volume. In CAS architecture, users can choose different data planes for different application workloads based on a configuration policy. OpenEBS provides different types of storage engines and chooses the right engine that suits your type of application requirements and storage available on your Kubernetes nodes. More information can be read from here.
In this document, we are deploying StackGres PostgreSQL using OpenEBS Local PV device.
There are 2 ways to use OpenEBS Local PV.
openebs-hostpath- Using this option, it will create Kubernetes Persistent Volumes that will store the data into OS host path directory at: /var/openebs/<"postgresql-pv-name">/. Select this option, if you don’t have any additional block devices attached to Kubernetes nodes. You would like to customize the directory where data will be saved, create a new OpenEBS Local PV storage class using these instructions.
openebs-device- Using this option, it will create Kubernetes Local PVs using the block devices attached to the node. Select this option when you want to dedicate a complete block device on a node to a StackGres PostgreSQL application pod. You can customize which devices will be discovered and managed by OpenEBS using the instructions here.
The Storage Class
openebs-device has been chosen to deploy StackGres PostgreSQL in the Kubernetes cluster.
Note: Ensure that you have a disk with the required capacity is added to the corresponding nodes. In this example, we have added 100G disks to each node.
In this section, we will install the StackGres operator. We will later deploy the latest available version of PostgreSQL application using StackGres.
Once it’s ready, you will see that the two pods are
Running and the other pods are in
Users can manage the StackGres PostgreSQL database using a web console. This can be done accessing the Rest API service. Get the StackGres PostgreSQL service using the following command.
Now, manage the PostgreSQL cluster using the Load Balancer IP on your web browser.
In this example following is the web address.
Default username is admin
Passwords can be obtained by running the following command.
To create your first StackGres cluster you have to create a simple custom resource that reflects the cluster configuration. The following configuration file has been saved as stackgres.yaml.
In the above PostgreSQL cluster configuration file, the Storage Class used is openebs-device with a capacity of 90Gi. So while scheduling Postgres pod, NDM will assign a matched block device which matches as per the requested capacity.
Install StackGres PostgreSQL application using the following way.
Verify the PostgreSQL cluster creation is successfully running under the default namespace.
Verify the PostgreSQL persistent volume details.
Verify PostgreSQL service status.
Since we have mentioned 2 replicas and capacity with 90G in Postgres cluster spec, any two disks with capacity more than 90G from the scheduled node will be claimed. In this case, 100G disks are present in all the nodes in the cluster. Verify whether 100G disks are claimed for provisioning PostgreSQL clusters.
Verify the master and replicas configuration.
Out of all of the PostgreSQL servers, one will be elected as the master, the rest will remain as read-only replicas.
Get the details of PostgreSQL database service.
Install postgresql-client on your master node or a node from where you have access to the Kubernetes cluster.
Let’s access the database by accessing one of the application pods.