OpenShift PostgreSQL with Slaves

README.md

Imagine you have a postgresql-ephemeral database set up via the following command:

oc new-app --name my-test-db --template postgresql-persistent -p POSTGRESQL_USER=mytestuser -p POSTGRESQL_PASSWORD=mytestpassword -p POSTGRESQL_DATABASE=mytestdb -p DATABASE_SERVICE_NAME=my-test-db

If the persistent volume is lost or corrupted all your data is gone. Furthermore all reads and writes must go through that single instance which can mean poor performance.

You could use a different database template like crunchy but you would need to migrate your config and data over.

Alternatively you could set up your existing database to support slave replication, add one or more slaves and be all set!

First, we need a master user set up (this would happen automatically for a new cluster, but it doesn't for an existing one). Run the following:

oc rsh dc/my-test-db bash -c "createuser master"

Next, we need to deploy our new template. Since OpenShift/Kubernetes does either create or replace we do this in two steps.

Create new secrets and our slaves:

oc new-app --name my-test-db -f postgresql_replica.json -p POSTGRESQL_USER=mytestuser -p POSTGRESQL_PASSWORD=mytestpassword -p POSTGRESQL_DATABASE=mytestdb -p POSTGRESQL_MASTER_SERVICE_NAME=my-test-db -p POSTGRESQL_SLAVE_SERVICE_NAME=my-test-db-slaves

Patch the existing deployment:

oc new-app --name my-test-db -f postgresql_replica.json -p POSTGRESQL_USER=mytestuser -p POSTGRESQL_PASSWORD=mytestpassword -p POSTGRESQL_DATABASE=mytestdb -p POSTGRESQL_MASTER_SERVICE_NAME=my-test-db -p POSTGRESQL_SLAVE_SERVICE_NAME=my-test-db-slaves -o json | oc replace -f -

All done! You should see your database replicated to our new slave, and any future changes should be available there as well. If your application supports it, you can send reads to the slave service. It will be set up the same as the existing connection, but with a different host name (in this case my-test-db-slaves).

postgresql_replica.json

{
  "kind": "Template",
  "apiVersion": "v1",
  "metadata": {
    "name": "postgresql-replica-persistent",
    "annotations": {
      "openshift.io/display-name": "PostgreSQL Replica",
      "description": "PostgreSQL Replication Example",
      "iconClass": "icon-database",
      "tags": "database,postgresql,replication"
    }
  },
  "message": "The following service(s) have been created in your project: ${POSTGRESQL_MASTER_SERVICE_NAME}.\n\n       Username: ${POSTGRESQL_USER}\n       Password: ${POSTGRESQL_PASSWORD}\n  Database Name: ${POSTGRESQL_DATABASE}\n Connection URL: postgresql://${POSTGRESQL_MASTER_SERVICE_NAME}:5432/\n\nFor more information about using this template, including OpenShift considerations, see https://github.com/sclorg/postgresql-container/.",
  "labels": {
    "template": "postgresql-persistent-template"
  },
  "objects": [
    {
      "kind": "Secret",
      "apiVersion": "v1",
      "metadata": {
        "name": "${POSTGRESQL_MASTER_SERVICE_NAME}",
        "annotations": {
          "template.openshift.io/expose-username": "{.data['database-user']}",
          "template.openshift.io/expose-password": "{.data['database-password']}",
          "template.openshift.io/expose-database_name": "{.data['database-name']}"
        }
      },
      "stringData" : {
        "database-user" : "${POSTGRESQL_USER}",
        "database-password" : "${POSTGRESQL_PASSWORD}",
        "database-name" : "${POSTGRESQL_DATABASE}"
      }
    },
    {
      "kind": "Secret",
      "apiVersion": "v1",
      "metadata": {
        "name": "${POSTGRESQL_MASTER_SERVICE_NAME}-master"
      },
      "stringData" : {
        "user" : "${POSTGRESQL_MASTER_USER}",
        "password" : "${POSTGRESQL_MASTER_PASSWORD}"
      }
    },
    {
      "kind": "Secret",
      "apiVersion": "v1",
      "metadata": {
        "name": "${POSTGRESQL_MASTER_SERVICE_NAME}-admin"
      },
      "stringData" : {
        "password" : "${POSTGRESQL_ADMIN_PASSWORD}"
      }
    },
    {
      "kind": "PersistentVolumeClaim",
      "apiVersion": "v1",
      "metadata": {
        "name": "${POSTGRESQL_MASTER_SERVICE_NAME}"
      },
      "spec": {
        "accessModes": [
          "ReadWriteOnce"
        ],
        "resources": {
          "requests": {
            "storage": "${VOLUME_CAPACITY}"
          }
        }
      }
    },
    {
      "kind": "Service",
      "apiVersion": "v1",
      "metadata": {
        "name": "${POSTGRESQL_MASTER_SERVICE_NAME}",
        "annotations": {
          "template.openshift.io/expose-uri": "postgres://{.spec.clusterIP}:{.spec.ports[?(.name==\"postgresql\")].port}"
        },
        "labels": {
          "name": "${POSTGRESQL_MASTER_SERVICE_NAME}"
        }
      },
      "spec": {
        "ports": [
          {
            "port": 5432,
            "targetPort": 5432
          }
        ],
        "selector": {
          "name": "${POSTGRESQL_MASTER_SERVICE_NAME}"
        },
        "clusterIP": "None"
      }
    },
    {
      "kind": "Service",
      "apiVersion": "v1",
      "metadata": {
        "name": "${POSTGRESQL_SLAVE_SERVICE_NAME}",
        "annotations": {
          "template.openshift.io/expose-uri": "postgres://{.spec.clusterIP}:{.spec.ports[?(.name==\"postgresql\")].port}"
        },
        "labels": {
          "name": "${POSTGRESQL_SLAVE_SERVICE_NAME}"
        }
      },
      "spec": {
        "ports": [
          {
            "port": 5432,
            "targetPort": 5432
          }
        ],
        "selector": {
          "name": "${POSTGRESQL_SLAVE_SERVICE_NAME}"
        },
        "clusterIP": "None"
      }
    },
    {
      "kind": "DeploymentConfig",
      "apiVersion": "v1",
      "metadata": {
        "name": "${POSTGRESQL_MASTER_SERVICE_NAME}"
      },
      "spec": {
        "strategy": {
          "type": "Recreate"
        },
        "triggers": [
          {
            "type": "ImageChange",
            "imageChangeParams": {
              "automatic": true,
              "containerNames": [
                "postgresql-master"
              ],
              "from": {
                "kind": "ImageStreamTag",
                "name": "postgresql:${POSTGRESQL_VERSION}",
                "namespace": "${NAMESPACE}"
              },
              "lastTriggeredImage": ""
            }
          },
          {
            "type": "ConfigChange"
          }
        ],
        "replicas": 1,
        "selector": {
          "name": "${POSTGRESQL_MASTER_SERVICE_NAME}"
        },
        "template": {
          "metadata": {
            "labels": {
              "name": "${POSTGRESQL_MASTER_SERVICE_NAME}"
            }
          },
          "spec": {
            "volumes": [
              {
                "name": "postgresql-data",
                "persistentVolumeClaim": {
                  "claimName": "${POSTGRESQL_MASTER_SERVICE_NAME}"
                }
              }
            ],
            "containers": [
              {
                "name": "postgresql-master",
                "image": " ",
                "args": [
                  "run-postgresql-master"
                ],
                "ports": [
                  {
                    "containerPort": 5432
                  }
                ],
                "readinessProbe": {
                  "timeoutSeconds": 1,
                  "initialDelaySeconds": 5,
                  "exec": {
                    "command": [ "/usr/libexec/check-container" ]
                  }
                },
                "livenessProbe": {
                  "timeoutSeconds": 10,
                  "initialDelaySeconds": 120,
                  "exec": {
                    "command": [ "/usr/libexec/check-container", "--live" ]
                  }
                },
                "env": [
                  {
                    "name": "POSTGRESQL_MASTER_USER",
                    "valueFrom": {
                      "secretKeyRef" : {
                        "name" : "${POSTGRESQL_MASTER_SERVICE_NAME}-master",
                        "key" : "user"
                      }
                    }
                  },
                  {
                    "name": "POSTGRESQL_MASTER_PASSWORD",
                    "valueFrom": {
                      "secretKeyRef" : {
                        "name" : "${POSTGRESQL_MASTER_SERVICE_NAME}-master",
                        "key" : "password"
                      }
                    }
                  },
                  {
                    "name": "POSTGRESQL_USER",
                    "valueFrom": {
                      "secretKeyRef" : {
                        "name" : "${POSTGRESQL_MASTER_SERVICE_NAME}",
                        "key" : "database-user"
                      }
                    }
                  },
                  {
                    "name": "POSTGRESQL_PASSWORD",
                    "valueFrom": {
                      "secretKeyRef" : {
                        "name" : "${POSTGRESQL_MASTER_SERVICE_NAME}",
                        "key" : "database-password"
                      }
                    }
                  },
                  {
                    "name": "POSTGRESQL_DATABASE",
                    "valueFrom": {
                      "secretKeyRef" : {
                        "name" : "${POSTGRESQL_MASTER_SERVICE_NAME}",
                        "key" : "database-name"
                      }
                    }
                  },
                  {
                    "name": "POSTGRESQL_ADMIN_PASSWORD",
                    "valueFrom": {
                      "secretKeyRef" : {
                        "name" : "${POSTGRESQL_MASTER_SERVICE_NAME}-admin",
                        "key" : "password"
                      }
                    }
                  }
                ],
                "volumeMounts": [
                  {
                    "name": "postgresql-data",
                    "mountPath": "/var/lib/pgsql/data"
                  }
                ]
              }
            ]
          }
        }
      }
    },
    {
      "kind": "DeploymentConfig",
      "apiVersion": "v1",
      "metadata": {
        "name": "${POSTGRESQL_SLAVE_SERVICE_NAME}"
      },
      "spec": {
        "strategy": {
          "type": "Recreate"
        },
        "triggers": [
          {
            "type": "ImageChange",
            "imageChangeParams": {
              "automatic": true,
              "containerNames": [
                "postgresql-slave"
              ],
              "from": {
                "kind": "ImageStreamTag",
                "name": "postgresql:${POSTGRESQL_VERSION}",
                "namespace": "${NAMESPACE}"
              },
              "lastTriggeredImage": ""
            }
          },
          {
            "type": "ConfigChange"
          }
        ],
        "replicas": 1,
        "selector": {
          "name": "${POSTGRESQL_SLAVE_SERVICE_NAME}"
        },
        "template": {
          "metadata": {
            "labels": {
              "name": "${POSTGRESQL_SLAVE_SERVICE_NAME}"
            }
          },
          "spec": {
            "volumes": [
              {
                "name": "postgresql-data",
                "emptyDir": {}
              }
            ],
            "containers": [
              {
                "name": "postgresql-slave",
                "image": " ",
                "args": [
                  "run-postgresql-slave"
                ],
                "ports": [
                  {
                    "containerPort": 5432
                  }
                ],
                "readinessProbe": {
                  "timeoutSeconds": 1,
                  "initialDelaySeconds": 5,
                  "exec": {
                    "command": [ "/usr/libexec/check-container" ]
                  }
                },
                "livenessProbe": {
                  "timeoutSeconds": 10,
                  "initialDelaySeconds": 120,
                  "exec": {
                    "command": [ "/usr/libexec/check-container", "--live" ]
                  }
                },
                "env": [
                  {
                    "name": "POSTGRESQL_MASTER_SERVICE_NAME",
                    "value": "${POSTGRESQL_MASTER_SERVICE_NAME}"
                  },
                  {
                    "name": "POSTGRESQL_MASTER_USER",
                    "valueFrom": {
                      "secretKeyRef" : {
                        "name" : "${POSTGRESQL_MASTER_SERVICE_NAME}-master",
                        "key" : "user"
                      }
                    }
                  },
                  {
                    "name": "POSTGRESQL_MASTER_PASSWORD",
                    "valueFrom": {
                      "secretKeyRef" : {
                        "name" : "${POSTGRESQL_MASTER_SERVICE_NAME}-master",
                        "key" : "password"
                      }
                    }
                  },
                  {
                    "name": "POSTGRESQL_USER",
                    "valueFrom": {
                      "secretKeyRef" : {
                        "name" : "${POSTGRESQL_MASTER_SERVICE_NAME}",
                        "key" : "database-user"
                      }
                    }
                  },
                  {
                    "name": "POSTGRESQL_PASSWORD",
                    "valueFrom": {
                      "secretKeyRef" : {
                        "name" : "${POSTGRESQL_MASTER_SERVICE_NAME}",
                        "key" : "database-password"
                      }
                    }
                  },
                  {
                    "name": "POSTGRESQL_DATABASE",
                    "valueFrom": {
                      "secretKeyRef" : {
                        "name" : "${POSTGRESQL_MASTER_SERVICE_NAME}",
                        "key" : "database-name"
                      }
                    }
                  }
                ],
                "volumeMounts": [
                  {
                    "name": "postgresql-data",
                    "mountPath": "/var/lib/pgsql/data"
                  }
                ]
              }
            ]
          }
        }
      }
    }
  ],
  "parameters": [
    {
      "name": "POSTGRESQL_MASTER_USER",
      "description": "The username used for master-slave replication",
      "value": "master",
      "required": true
    },
    {
      "name": "POSTGRESQL_MASTER_PASSWORD",
      "description": "The password for the PostgreSQL replication user",
      "generate": "expression",
      "from": "[a-zA-Z0-9]{12}",
      "required": true
    },
    {
      "name": "POSTGRESQL_USER",
      "description": "The username that clients will use to connect to PostgreSQL server",
      "value": "user",
      "required": true
    },
    {
      "name": "POSTGRESQL_PASSWORD",
      "description": "The password for the PostgreSQL master user",
      "generate": "expression",
      "from": "[a-zA-Z0-9]{12}",
      "required": true
    },
    {
      "name": "POSTGRESQL_DATABASE",
      "description": "The name of the database that will be created",
      "value": "userdb",
      "required": true
    },
    {
      "name": "POSTGRESQL_ADMIN_PASSWORD",
      "description": "The password for the PostgreSQL administrator",
      "generate": "expression",
      "from": "[a-zA-Z0-9]{12}",
      "required": false
    },
    {
      "name": "POSTGRESQL_MASTER_SERVICE_NAME",
      "description": "The name of the PostgreSQL Service (used to DNS lookup, default: 'postgresql-master')",
      "value": "postgresql-master",
      "required": true
    },
    {
      "name": "POSTGRESQL_SLAVE_SERVICE_NAME",
      "description": "The name of the PostgreSQL Service (used to DNS lookup, default: 'postgresql-slave')",
      "value": "postgresql-slave",
      "required": true
    },
    {
      "name": "VOLUME_CAPACITY",
      "description": "Volume space available for data, e.g. 512Mi, 2Gi",
      "value": "1Gi",
      "required": true
    },
    {
      "name": "POSTGRESQL_VERSION",
      "displayName": "Version of PostgreSQL Image",
      "description": "Version of PostgreSQL image to be used (9.4, 9.5, 9.6 or latest).",
      "value": "9.6",
      "required": true
    },
    {
      "name": "NAMESPACE",
      "displayName": "Namespace",
      "description": "The OpenShift Namespace where the ImageStream resides.",
      "value": "openshift"
    }
  ]
}