×

Overview

OpenShift provides a Docker image for running PostgreSQL. This image can provide database services based on username, password, and database name settings provided via configuration.

Versions

Currently, OpenShift supports version 9.2 of PostgreSQL.

Images

This image comes in two flavors, depending on your needs:

  • RHEL 7

  • CentOS 7

RHEL 7 Based Image

The RHEL 7 image is available through Red Hat’s subscription registry via:

$ docker pull registry.access.redhat.com/openshift3/postgresql-92-rhel7

CentOS 7 Based Image

This image is available on DockerHub. To download it:

$ docker pull openshift/postgresql-92-centos7

To use these images, you can either access them directly from these registries or push them into your OpenShift Docker registry. Additionally, you can create an ImageStream that points to the image, either in your Docker registry or at the external location. Your OpenShift resources can then reference the ImageStream. You can find example ImageStream definitions for all the provided OpenShift images.

Configuration and Usage

Initializing the Database

The first time you use the shared volume, the database is created along with the database administrator user and the PostgreSQL postgres user (if you specify the POSTGRESQL_ADMIN_PASSWORD environment variable). Afterwards, the PostgreSQL daemon starts up. If you are re-attaching the volume to another container, then the database, the database user, and the administrator user are not created, and the PostgreSQL daemon starts.

The following command creates a new database pod with PostgreSQL running in a container:

$ oc new-app -e \
    POSTGRESQL_USER=<username>,POSTGRESQL_PASSWORD=<password>,POSTGRESQL_DATABASE=<database_name> \
    registry.access.redhat.com/rhscl/postgresql-94-rhel7

Running PostgreSQL Commands in Containers

OpenShift uses Software Collections (SCLs) to install and launch PostgreSQL. If you want to execute a PostgreSQL command inside of a running container (for debugging), you must invoke it using bash.

To do so, first identify the name of the running PostgreSQL pod. For example, you can view the list of pods in your current project:

$ oc get pods

Then, open a remote shell session to the desired pod:

$ oc rsh <pod>

When you enter the container, the required SCL is automatically enabled.

You can now run the psql command from the bash shell to start a PostgreSQL interactive session and perform normal PostgreSQL operations. For example, to authenticate as the database user:

bash-4.2$ PGPASSWORD=$POSTGRESQL_PASSWORD psql -h postgresql $POSTGRESQL_DATABASE $POSTGRESQL_USER
psql (9.2.8)
Type "help" for help.

default=>

When you are finished, enter \q to leave the PostgreSQL session.

Environment Variables

The PostgreSQL user name, password, and database name must be configured with the following environment variables:

Table 1. PostgreSQL Environment Variables
Variable Name Description

POSTGRESQL_USER

User name for the PostgreSQL account to be created. This user has full rights to the database.

POSTGRESQL_PASSWORD

Password for the user account.

POSTGRESQL_DATABASE

Database name.

POSTGRESQL_ADMIN_PASSWORD

Optional password for the postgres administrator user. If this is not set, then remote login to the postgres account is not possible. Local connections from within the container are always permitted without a password.

You must specify the user name, password, and database name. If you do not specify all three, the pod will fail to start and OpenShift will continuously try to restart it.

PostgreSQL settings can be configured with the following environment variables:

Table 2. Additional PostgreSQL settings
Variable Name Description Default

POSTGRESQL_MAX_CONNECTIONS

The maximum number of client connections allowed. This also sets the maximum number of prepared transactions.

100

POSTGRESQL_SHARED_BUFFERS

Configures how much memory is dedicated to PostgreSQL for caching data.

32M

Volume Mount Points

The PostgreSQL image can be run with mounted volumes to enable persistent storage for the database:

  • /var/lib/pgsql/data - This is the database cluster directory where PostgreSQL stores database files.

Changing Passwords

Passwords are part of the image configuration, therefore the only supported method to change passwords for the database user (POSTGRESQL_USER) and postgres administrator user is by changing the environment variables POSTGRESQL_PASSWORD and POSTGRESQL_ADMIN_PASSWORD, respectively.

You can view the current passwords by viewing the pod or deployment configuration in the web console or by listing the environment variables with the CLI:

$ oc env pod <pod_name> --list

Changing database passwords through SQL statements or any way other than through the environment variables aforementioned will cause a mismatch between the values stored in the variables and the actual passwords. Whenever a database container starts, it resets the passwords to the values stored in the environment variables.

To change these passwords, update one or both of the desired environment variables for the related deployment configuration(s) using the oc env command. If multiple deployment configurations utilize these environment variables, for example in the case of an application created from a template, you must update the variables on each deployment configuration so that the passwords are in sync everywhere. This can be done all in the same command:

$ oc env dc <dc_name> [<dc_name_2> ...] \
  POSTGRESQL_PASSWORD=<new_password> \
  POSTGRESQL_ADMIN_PASSWORD=<new_admin_password>

Depending on your application, there may be other environment variables for passwords in other parts of the application that should also be updated to match. For example, there could be a more generic DATABASE_USER variable in a front-end pod that should match the database user’s password. Ensure that passwords are in sync for all required environment variables per your application, otherwise your pods may fail to redeploy when triggered.

Updating the environment variables triggers the redeployment of the database server if you have a configuration change trigger. Otherwise, you must manually start a new deployment in order to apply the password changes.

To verify that new passwords are in effect, first open a remote shell session to the running PostgreSQL pod:

$ oc rsh <pod>

From the bash shell, verify the database user’s new password:

bash-4.2$ PGPASSWORD=<new_password> psql -h postgresql $POSTGRESQL_DATABASE $POSTGRESQL_USER -c "SELECT * FROM (SELECT current_database()) cdb CROSS JOIN (SELECT current_user) cu"

If the password was changed correctly, you should see a table like this:

 current_database | current_user
------------------+--------------
 default          | django
(1 row)

From the bash shell, verify the postgres administrator user’s new password:

bash-4.2$ PGPASSWORD=<new_admin_password> psql -h postgresql $POSTGRESQL_DATABASE postgres -c "SELECT * FROM (SELECT current_database()) cdb CROSS JOIN (SELECT current_user) cu"

If the password was changed correctly, you should see a table like this:

 current_database | current_user
------------------+--------------
 default          | postgres
(1 row)

Creating a Database Service from a Template

OpenShift provides a template to make creating a new database service easy. The template provides parameter fields to define all the mandatory environment variables (user, password, database name, etc) with predefined defaults including auto-generation of password values. It will also define both a deployment configuration and a service.

The PostgreSQL templates should have been registered in the default openshift project by your cluster administrator during the First Steps setup process. There are two templates available:

  • PostgreSQL-ephemeral is for development or testing purposes only because it uses ephemeral storage for the database content. This means that if the database pod is restarted for any reason, such as the pod being moved to another node or the deployment configuration being updated and triggering a redeploy, all data will be lost.

  • PostgreSQL-persistent uses a persistent volume store for the database data which means the data will survive a pod restart. Using persistent volumes requires a persistent volume pool be defined in the OpenShift deployment. Cluster administrator instructions for setting up the pool are located here.

You can find instructions for instantiating templates by following these instructions.

Once you have instantiated the service, you can copy the user name, password, and database name environment variables into a deployment configuration for another component that intends to access the database. That component can then access the database via the service that was defined.