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.

automatically backups enabled
AWS_ACCESS_KEY_ID
and
AWS_SECRET_ACCESS_KEY
to your control-repo CI/CD variables

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 Settings -> CICD - Variables 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