Kubernetes and Openshift

The passwords used for the PostgreSQL user accounts are generated by the OpenShift process command. To inspect what value is supplied, you can inspect the primary pod as follows:

This will give you the environment variable values for the database created by default in addition to the username and password of the standard user.

You can use Kubernetes Secrets to set and maintain your database credentials. Secrets requires you base64 encode your user and password values as follows:

You will paste these values into your JSON secrets files for values.

This example allows you to set the PostgreSQL passwords using Kubernetes Secrets.

The secret uses a base64 encoded string to represent the values to be read by the container during initialization. The encoded password value is password. Run the example as follows:

The secrets are mounted in the /pguser, /pgprimary, and /pgroot volumes within the container and read during initialization. The container scripts create a PostgreSQL user with those values, and sets the passwords for the primary user and PostgreSQL superuser using the mounted secret volumes.

When using secrets, you do NOT have to specify the following environment variables if you specify all three secrets volumes:

You can test the container as follows. In all cases, the password is password:

This example deploys the pgadmin4 v2 web user interface for PostgreSQL without TLS.

After running the example, you should be able to browse to http://127.0.0.1:5050 and log into the web application using a user ID of admin@admin.com and password of password.

If you are running this example using Kubernetes or OpenShift, replace 127.0.0.1:5050 with the <NODE_IP>:30000.

To get the node IP, run the following:

See the pgAdmin4 documentation for more details.

To shutdown the instance and remove the container for each example, run the following:

This example deploys the pgadmin4 v2 web user interface for PostgreSQL with TLS.

After running the example, you should be able to browse to https://127.0.0.1:5050 and log into the web application using a user ID of admin@admin.com and password of password.

If you are running this example using Kubernetes or OpenShift, replace 127.0.0.1:5050 with the <NODE_IP>:30000.

To get the node IP, run the following:

See the pgadmin4 documentation for more details.

To shutdown the instance and remove the container for each example, run the following:

Starting in release 1.3.1, the upgrade container will let you perform a pg_upgrade either from a PostgreSQL version 9.5 database to 9.6 or from 9.6 to 10.

Prior to running this example, make sure your CCP_IMAGE_TAG environment variable is using the next major version of PostgreSQL that you want to upgrade to. For example, if you’re upgrading from 9.5 to 9.6, make sure the variable references a PG 9.6 image such as centos7-9.6.10-2.1.0.

This will create the following in your Kubernetes environment:

The crunchy-dba container implements a cron scheduler. The purpose of the crunchy-dba container is to offer a way to perform simple DBA tasks that occur on some form of schedule such as backup jobs or running a vacuum on a single PostgreSQL database container (such as the primary example).

You can either run the crunchy-dba container as a single pod or include the container within a database pod.

The crunchy-dba container makes use of a Service Account to perform the startup of scheduled jobs. The Kubernetes Job type is used to execute the scheduled jobs with a Restart policy of Never.

To shutdown the instance and remove the container for each example, run the following:

You can perform a PostgreSQL vacuum command by running the crunchy-vacuum container. You specify a database to vacuum using environment variables. By default, vacuum is executed against the primary example container.

The crunchy-vacuum container image exists to allow a DBA a way to run a job either individually or scheduled to perform a variety of vacuum operations.

This example performs a vacuum on a single table in the primary PostgreSQL database. The crunchy-vacuum image is executed with the PostgreSQL connection parameters to the single-primary PostgreSQL container. The type of vacuum performed is dictated by the environment variables passed into the job; these are defined with further detail here.

To shutdown the instance and remove the container for each example, run the following:

The crunchy-pg.service is an example of a systemd unit file that starts and stops a container named crunchy-pg that has already been created.

The example scripts are located in the following directory:

There are two scripts within the directory.

This script is called by the systemd start execution. The trick with this script is that it blocks forever after starting the docker crunchy-pg container. The blocking in the script keeps systemd happy and thinking that this is a normal daemon.

This script stops the test-start.sh script and also stops the crunchy-pg Docker container.

The script assumes you are going to backup the primary example and that container is running.

This example assumes you have configured a storage filesystem as described in the Storage Configuration document.

A successful backup will perform pg_dump/pg_dumpall on the primary and store the resulting files in the mounted volume under a directory named <HOSTNAME>-backups as a sub-directory, then followed by a unique backup directory based upon a date and timestamp - allowing any number of backups to be kept.

For more information on how to configure this container, please see the Container Specifications document.

To shutdown the instance and remove the container for each example, run the following:

The script assumes you are going to restore to the primary example and that container is running and a backup has been created using the pgdump example..

This example assumes you have configured a storage filesystem as described in the Storage Configuration document.

Successful use of the crunchy-pgrestore container will run a job to restore files generated by pg_dump/pg_dumpall to a container via psql/pg_restore; then container will terminate successfully and signal job completion.

For more information on how to configure this container, please see the Container Specifications document.

To shutdown the instance and remove the container for each example, run the following:

pgbackrest is a utility that performs a backup, restore, and archive function for a PostgreSQL database. pgbackrest is written and maintained by David Steele, and more information can be found on the official website.

Backups are currently performed by manually executing pgbackrest commands against the desired pod. Restores can now be performed via the crunchy-backrest-restore container, which offers FULL or DELTA restore capability.

pgbackrest is configured using a pgbackrest.conf file that is mounted into the crunchy-postgres container at /pgconf.

If you place a pgbackrest.conf file within this mounted directory, it will trigger the use of pgbackrest within the PostgreSQL container as the archive_command and will turn on the archive_mode to begin archival. It is still required to define the ARCHIVE_TIMEOUT environment variable within your container configuration as it is set to a disable value of 0 by default.

The following changes will be made to the container’s postgresql.conf file:

If you are using a crunchy-postgres image older than 1.7.1, archive_command must specify where the pgbackrest.conf file is located:

When set, WAL files generated by the database will be written out to the /backrestrepo/HOSTNAME-backups mount point.

Additionally, the Crunchy Postgres container can templatize pgbackrest.conf files by searching for the HOSTNAME values in a mounted pgbackrest.conf file.

For example, db-path=/pgdata/HOSTNAME will render to db-path=/pgdata/primary if the container’s hostname is primary. HOSTNAME will be replaced with the value of PGDATA_PATH_OVERRIDE when working with deployments/replicasets.

The PostgreSQL and PostgreSQL GIS containers can enable an SSH daemon to allow developers to do DBA tasks on the database server without the need for exec privileges. An administrator who deploys the SSHD enabled PostgreSQL database can specify the authorized public keys for access to the database server.

In order to activate SSHD in the PostgreSQL containers, the following files need to be mounted to the PostgreSQL container:

SSHD can be enabled in the PostgreSQL containers by adding the following line:

The authorized_keys file is mounted on the /pgconf directory. In order to support using this mount for authentication the following must be set in sshd_config:

For OpenShift deployments, the following configuration needs to be set in sshd_config:

The script assumes you are going to backup the primary container created in the first example, so you need to ensure that container is running. This example assumes you have configured storage as described in the Storage Configuration documentation. Things to point out with this example include its use of persistent volumes and volume claims to store the backup data files.

A successful backup will perform pg_basebackup on the primary container and store the backup in the $CCP_STORAGE_PATH volume under a directory named primary-backups. Each backup will be stored in a subdirectory with a timestamp as the name, allowing any number of backups to be kept.

The backup script will do the following:

When you are ready to restore from the backup, the restore example runs a PostgreSQL container using the backup location. Upon initialization, the container will use rsync to copy the backup data to this new container and then launch PostgreSQL using the original backed-up data.

The restore script will do the following:

To shutdown the instance and remove the container for each example, run the following:

PITR (point-in-time-recovery) is a feature that allows for recreating a database from backup and log files at a certain point in time. This is done using a write ahead log (WAL) which is kept in the pg_wal directory within PGDATA. Changes made to the database files over time are recorded in these log files, which allows it to be used for disaster recovery purposes.

When using PITR as a backup method, in order to restore from the last checkpoint in the event of a database or system failure, it is only necessary to save these log files plus a full backup. This provides an additional advantage in that it is not necessary to keep multiple full backups on hand, which consume space and time to create. This is because point in time recovery allows you to "replay" the log files and recover your database to any point since the last full backup.

More detailed information about Write Ahead Log (WAL) archiving can be found here.

By default in the crunchy-postgres container, WAL logging is not enabled. To enable WAL logging outside of this example, set the following environment variables when starting the crunchy-postgres container:

These variables set the same name settings within the postgresql.conf file that is used by the database. When set, WAL files generated by the database will be written out to the /pgwal mount point.

A full backup is required to do a PITR. crunchy-backup currently performs this role within the example, running a pg_basebackup on the database. This is a requirement for PITR. After a backup is performed, code is added into crunchy-postgres which will also check to see if you want to do a PITR.

There are three volume mounts used with the PITR example.

Some environment variables used to manipulate the point in time recovery logic:

If you don’t specify either of these environment variables, then the PITR logic will assume you want to restore using all the WAL files or essentially the last known recovery point.

The RECOVERY_TARGET_INCLUSIVE environment variable is also available to let you control the setting of the recovery.conf setting recovery_target_inclusive. If you do not set this environment variable the default is true.

Once you recover a database using PITR, it will be in read-only mode. To make the database resume as a writable database, run the following SQL command:

It takes about 1 minute for the database to become ready for use after initially starting.

To shutdown the instance and remove the container for each example, run the following:

Crunchy pgBouncer is a lightweight connection pooler for PostgreSQL databases.

The following examples create the following containers:

In Kubernetes and OpenShift, this example will also create:

To cleanup the objects created by this example, run the following in the pgbouncer example directory:

An example is provided that will run a pgPool II container in conjunction with the primary-replica example provided above.

You can execute both INSERT and SELECT statements after connecting to pgpool. The container will direct INSERT statements to the primary and SELECT statements will be sent round-robin to both the primary and replica.

The container creates a default database called userdb, a default user called testuser and a default password of password.

You can view the nodes that pgpool is configured for by running:

To shutdown the instance and remove the container for each example, run the following:

This example starts a single PostgreSQL container and service, the most simple of examples.

The container creates a default database called userdb, a default user called testuser and a default password of password.

For all environments, the script additionally creates:

And specifically for the Kubernetes and OpenShift environments:

To shutdown the instance and remove the container for each example, run the following:

Starting in release 1.2.8, the PostgreSQL container can accept an environment variable named PGDATA_PATH_OVERRIDE. If set, the /pgdata/subdir path will use a subdirectory name of your choosing instead of the default which is the hostname of the container.

This example shows how a Deployment of a PostgreSQL primary is supported. A pod is a deployment that uses a hostname generated by Kubernetes; because of this, a new hostname will be defined upon restart of the primary pod.

For finding the /pgdata that pertains to the pod, you will need to specify a /pgdata/subdir name that never changes. This requirement is handled by the PGDATA_PATH_OVERRIDE environment variable.

The container creates a default database called userdb, a default user called testuser and a default password of password.

This example will create the following in your Kubernetes and OpenShift environments:

The persisted data for the PostgreSQL primary is found under /pgdata/primary-deployment. If you delete the primary pod, the deployment will create another pod for the primary and will be able to start up immediately since it works out of the same /pgdata/primary-deployment data directory.

To shutdown the instance and remove the container for each example, run the following:

This example starts a primary and a replica pod containing a PostgreSQL database.

The container creates a default database called userdb, a default user called testuser and a default password of password.

For the Docker environment, the script additionally creates:

And specifically for the Kubernetes and OpenShift environments:

To shutdown the instance and remove the container for each example, run the following:

This example deploys a PostgreSQL cluster with a primary, a synchronous replica, and an asynchronous replica. The two replicas share the same service.

To shutdown the instance and remove the container for each example, run the following:

This example deploys a statefulset named statefulset. The statefulset is a new feature in Kubernetes as of version 1.5 and in OpenShift Origin as of version 3.5. Statefulsets have replaced PetSets going forward.

Please view this Kubernetes description to better understand what a Statefulset is and how it works.

This example creates 2 PostgreSQL containers to form the set. At startup, each container will examine its hostname to determine if it is the first container within the set of containers.

The first container is determined by the hostname suffix assigned by Kubernetes to the pod. This is an ordinal value starting with 0. If a container sees that it has an ordinal value of 0, it will update the container labels to add a new label of:

In this example, PG_PRIMARY_HOST is specified as statefulset-primary.

By default, the containers specify a value of name=statefulset-replica.

There are 2 services that end user applications will use to access the PostgreSQL cluster, one service (statefulset-primary) routes to the primary container and the other (statefulset-replica) to the replica containers.

To shutdown the instance and remove the container for each example, run the following:

An example is provided that will run a PostgreSQL with PostGIS pod and service in Kubernetes and OpenShift and a container in Docker.

The container creates a default database called userdb, a default user called testuser and a default password of password.

You can view the extensions that postgres-gis has enabled by running the following command and viewing the listed PostGIS packages:

To validate that PostGIS is installed and which version is running, run the command:

You should expect to see output similar to:

As an exercise for invoking some of the basic PostGIS functionality for validation, try defining a 2D geometry point while giving inputs of longitude and latitude through this command.

You should expect to see output similar to:

To shutdown the instance and remove the container for each example, run the following:

You can use your own version of the SQL file setup.sql to customize the initialization of database data and objects when the container and database are created.

This works by placing a file named setup.sql within the /pgconf mounted volume directory. Portions of the setup.sql file are required for the container to work; please see comments within the sample setup.sql file.

If you mount a /pgconf volume, crunchy-postgres will look at that directory for postgresql.conf, pg_hba.conf, pg_ident.conf, SSL server/ca certificates and setup.sql. If it finds one of them it will use that file instead of the default files.

This example shows how you can configure PostgreSQL to use SSL for client authentication.

The example requires SSL certificates and keys to be created. Included in the examples directory is a script to create self-signed certificates (server and client) for the example: $CCPROOT/examples/ssl-creator.sh.

The example creates a client certificate for the user testuser. Furthermore, the server certificate is created for the server name custom-config-ssl.

If as a client it’s required to confirm the identity of the server, verify-full can be specified in the connection string. This will check if the server and the server certificate have the same name:

To connect via IP, sslmode can be changed to require.

This example can be run as follows for the Docker environment:

And the example can be run in the following directory for the Kubernetes and OpenShift environments:

A required step to make this example work is to define in your /etc/hosts file an entry that maps custom-config-ssl to the service IP address for the container.

For instance, if your service has an address as follows:

Then your /etc/hosts file needs an entry like this:

For production Kubernetes and OpenShift installations, it will likely be preferred for DNS names to resolve to the PostgreSQL service name and generate server certificates using the DNS names instead of the example name custom-config-ssl.

In order to connect via certificate, environment variables must be set that point to the client certificates. Source the env.sh file to set environment varaibles for the example:

If as a client it’s required to confirm the identity of the server, verify-full can be specified in the connection string. This will check if the server and the server certificate have the same name:

To connect via IP, sslmode can be changed to require.

You should see a connection that looks like the following:

This example shows how to run a primary and replica database container on a Docker Swarm (v.1.12) cluster.

First, set up a cluster. The Kubernetes libvirt coreos cluster example works well; see coreos-libvirt-cluster.

Next, on each node, create the Swarm using these Swarm Install instructions.

Include this command on the manager node:

Then this command on all the worker nodes:

Before creating Swarm services, it is necessary to define an overlay network to be used by the services you will create. This can be done as follows:

We want to have the primary database always placed on a specific node. This is accomplished using node constraints as follows:

In the above example, the kubernetes-node-1 node with ID 18yrb7m650umx738rtevojpqy has a user defined label of primary added to it. The primary service specifies primary as a constraint when created; this tells Swarm to place the service on that specific node. The replica specifies a constraint of node.labels.type != primary to have the replica always placed on a node that is not hosting the primary service.

This example shows how to run the crunchy-watch container to perform an automated failover. For the example to work, the host on which you are running needs to allow read-write access to /run/docker.sock. The crunchy-watch container runs as the postgres user, so adjust the file permissions of /run/docker.sock accordingly.

The primary-replica example is required to be run before this example.

To shutdown the instance and remove the container for each example, run the following:

pgbadger is a PostgreSQL tool that reads the log files from a specified database in order to produce a HTML report that shows various PostgreSQL statistics and graphs. This example runs the pgbadger HTTP server against a crunchy-postgres container and illustrates how to view the generated reports.

The port utilized for this tool is port 14000 for Docker environments and port 10000 for Kubernetes and OpenShift environments.

A requirement to build this container from source is golang. On RHEL 7.2, golang is found in the 'server optional' repository which needs to be enabled in order to install this dependency.

The container creates a default database called userdb, a default user called testuser and a default password of password.

To shutdown the instance and remove the container for each example, run the following:

You can collect various PostgreSQL metrics from your database container by running a crunchy-collect container that points to your database container.

This example starts up 5 containers:

Every 5 seconds by default, Prometheus will scrape the Collect container for metrics. These metrics will then be visualized by Grafana.

By default, Prometheus detects which environment its running on (Docker, Kubernetes, or OpenShift Container Platform) and applies a default configuration. If this container is running on Kubernetes or OpenShift Container Platform, it will use the Kubernetes API to discover pods with the label "crunchy-collect": "true".

The collect container must have this label to be discovered in these environments. Additionally, the collect container uses a special PostgreSQL role ccp_monitoring. This user is created by setting the PGMONITOR_PASSWORD environment variable on the PostgreSQL container.

Discovering pods requires a cluster role service account. See the Kubernetes and OpenShift metrics JSON file for more details.

For Docker environments the collect hostname must be specified as an environment variable.

To shutdown the instance and remove the container for each example, run the following:

This example provides an example of enabling pg_audit output. As of release 1.3, pg_audit is included in the crunchy-postgres container and is added to the PostgreSQL shared library list in postgresql.conf.

Given the numerous ways pg_audit can be configured, the exact pg_audit configuration is left to the user to define. pg_audit allows you to configure auditing rules either in postgresql.conf or within your SQL script.

For this test, we place pg_audit statements within a SQL script and verify that auditing is enabled and working. If you choose to configure pg_audit via a postgresql.conf file, then you will need to define your own custom file and mount it to override the default postgresql.conf file.