PostgreSQL

Overview

The Percona Operator for PostgreSQL simplifies the management of a Postgres DB main, replica (hot-standby) setup.
PG-Operator enables your team to get a PostgreSQL Cluster, or maybe your own database-as-a-service.
This Operator provides all essential features to provide the above described deployment.

If you install our curated Persona-Postgres-Operator from SysEleven, your cluster consists of a

• pg-db (DB main)
• pg-db-repl1 (DB replica)
• pg-db-pgbouncer (DB connection pooler)
• pg-db-backrest (DB backup service)
• pg-exporter-prometheus-postgres-exporter (DB metrics exporter)
• postgres-operator (DB operator)

Adding the Building Block

You are good to go with the recommended cluster configuration to meet the pg-operator recommended configuration. Keep in mind that it also needs to fit to your use case and your requirements.

First we add a pg-operator directory inside our control repo. e.g.

mkdir pg-operator
cd pg-operator

Create a .gitlab-ci.yaml inside this directory and paste the following content.

include:
  - project: syseleven/building-blocks/helmfiles/pg-operator
    file: JobDevelopment.yaml
    ref: 1.2.0

.syseleven-pg-operator-development:
  variables:
    BASEDIR_PG_OPERATOR: $CI_PROJECT_DIR/pg-operator

For this Building Block you need to fulfill some prerequisites if you would like to follow our recommendation to configure an automatic backup.

To get your AWS keys use the openstack cmd.

openstack ec2 credentials list
# get your access key within the first coloum called **Acccess**

openstack ec2 credentials show <Access>
# fetch the values from the field **access** for the AWS_ACCESS_KEY_ID and secret for the AWS_SECRET_ACCESS_KEY

see a detailed instruction and more here

Go to your gitlab repository and enter the above requested attributes.

Within your workspace add the following configuration inside your pg-operator directory.

• values-pg-db.yaml

backup:
  volumeSpec:
    storageclass: sys11-quobyte-external-provisioner

Last thing you proceed with, is to publish the building block to your control repository.

git add .
git commit -m "my new pg-operator"
git push

Take a look into your control-repo and make sure your pipeline passed all the pipeline steps.

Required configuration

If you have provisioned the PG-Operator building block you have already a running Building Block.

By the time the pg-operator was deployed to your cluster successfully, you should see the following pods:

kubectl get pods -n syseleven-pg-operator

NAME                                                        READY   STATUS      RESTARTS   AGE
backrest-backup-pg-db-v6qgp                                 0/1     Completed   0          25m
pg-db-5bf9df4dd5-9tv2z                                      1/1     Running     0          27m
pg-db-backrest-shared-repo-5879c7c449-v6smh                 1/1     Running     0          27m
pg-db-pgbouncer-5d4dd9558d-8m9zz                            1/1     Running     0          26m
pg-db-pgbouncer-5d4dd9558d-ghq26                            1/1     Running     0          26m
pg-db-pgbouncer-5d4dd9558d-nrwgk                            1/1     Running     0          26m
pg-db-repl1-dfbc9ff7f-tc58k                                 1/1     Running     0          24m
pg-exporter-prometheus-postgres-exporter-5dccd4d749-6tb4k   1/1     Running     0          29m
postgres-operator-5d5dccdff8-dm9px                          4/4     Running     0          11d

To access the postgres db from your local workstation, you can forward the postgres with following command.

kubectl -n syseleven-pg-operator port-forward svc/pg-db 5432:5432

Forwarding from 127.0.0.1:5432 -> 5432
Forwarding from [::1]:5432 -> 5432

As the deployment comes with the default user and password, you can retrieve the password for the user postgres with the following command:

kubectl -n syseleven-pg-operator get secrets pg-db-users -o jsonpath='{.data.postgres}' | base64 -d

As you did the port forwarding in the previous step, you can invoke a simple psql command to check your deployment.
Of course, you can use any other db client of your choice.

Example:

psql -h localhost -p 5432 --username=postgres --password

You will get the password prompt where you can paste the password from the above kubectl get secrets command.

Now you should see the following.

psql -h localhost -p 5432 --username=postgres --password
Password:
psql (14.5 (Homebrew), server 14.4)
Type "help" for help.

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 pgdb      | postgres | UTF8     | en_US.utf-8 | en_US.utf-8 | =Tc/postgres         +
           |          |          |             |             | postgres=CTc/postgres+
           |          |          |             |             | pguser=CTc/postgres
 postgres  | postgres | UTF8     | en_US.utf-8 | en_US.utf-8 |
 template0 | postgres | UTF8     | en_US.utf-8 | en_US.utf-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf-8 | en_US.utf-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

postgres=#

Next step: check out your control repo to do more fancy configuration for your pg-operator cluster.

git clone https://code.syseleven.de/<YourGitlabUser>/<youControlRepo>.git

Please replace the <> placeholder with your personal settings e.g.

git clone  https://code.syseleven.de/mka-realm-test/control-repo-testing/pg-operator.git

Scaling Setup and further configuration

You can get more information from our Readme.md.
Please stick to the official documentation of PG-Operator to learn more about scaling and further configuration options.

Known issues

Please stick to the official Percona Operator for PostgresSQL documentation to get more details on that topic.

Release-Notes

Please find more infos on release notes and new features Release notes PG-Operator