PostgreSQL Master

Introduction

PostgreSQL(also known as postgres) is one of the most advanced open-source relational database management systems with SQL compliance. AppZ currently offers PostgreSQL-12.2 with following added features out of the box :

  1. Auto-managed stateful PostgreSQL-12.2 deployment using GitOps from Client Git repository.
  2. Configure Master/Slave deployment for HA/DR capability
  3. Store all database secrets in AppZ Vault
  4. Database Changes(New database, user, schema, tables etc) deployed using GitOps
  5. Built-in logging and monitoring using AppZ Dashboard

This stack creates PostgreSQL Master Database using GitOps. Use this stack standalone or optionally add a PostgreSQL Slave database for HA/DR.

Stack Code

  • postgres_master-12.2 - To be mentioned in build.image_template in appz.yml. See Sample AppZ Yaml.

Pre-requisites

Make sure the Vault application is deployed, before deploying Postgres Master.

Sample Project

Postgres Master - Use this sample project to deploy PostgreSQL Master database.

Sample AppZ YAML

If you fork Postgres Master, you will see the following appz.yml in the root of the project. The image is vault enabled so secrets can be pulled from vault. These are placed under properties in appz.yml. See Vault Section <> on how to enter secrets in AppZ Vault. 

app:
    name: Postgres Master
    code: POSTGRES
    notify: appzdev@cloudbourne.co

build:
    version: 12.2
    env: DEV
    build_file: none
    output_files: output/*.zip
    image_template: postgres_master-12.2
    image_name: alpha/postgres_master

deploy:
    context: alpha/DEV
    type: statefulset
    replicas: 1
    port:
    - 5432

volumes:
  - claim: postgres-master-data
    mount: /appz/data
    name: data
    size: 5Gi
    mode: ReadWriteOnce
    class: microk8s-hostpath   

monitor:
    notify: appzops@cloudbourne.co      

properties:
    POSTGRESQL_REPLICATION_MODE: master
    POSTGRESQL_DATA: /appz/data
    POSTGRESQL_CONNECTUSER: postgres
    POSTGRESQL_CONNECTIONDB: postgres
    POSTGRESQL_PORT: 5432
    POSTGRESQL_PASSWORD: 
      vault: POSTGRESQL_PASSWORD
    POSTGRESQL_SYNCHRONOUS_COMMIT_MODE: on
    POSTGRESQL_NUM_SYNCHRONOUS_REPLICAS: 1
    POSTGRESQL_REP_USER: replica
    PGPASSWORD: 
      vault: PGPASSWORD
    APPUSER_PASSWORD:
      vault: APPUSER_PASSWORD
    APPZ_RESTORE_TOKEN: 20200810-1217
    APPZ_LOAD_TOKEN: 20200810-1217
    revision : 56

See below the explanation of appz.yml which user can customize.

Sample Setup YAML

We are passing the User and Database details through setup.yaml similar to appz.yaml. There are three sections in the setup.yaml file, users database and restore. A sample setup.yaml is given below.

  • In the users section, name of each user (to be created) with password and role should be provided.

  • In the databases section, details of each database to be created should be given. The owner name of the database, tablespace details (name and location) and schema details (name of the schema, authorised_user and search_path) should be included in each database section.

---
users:
  - name: appzuser1 
    password: $POSTGRESQL_USER1PASS
    role: superuser

databases:
  - name: appzdb1
    owner: appzuser1
    tablespace:
      - name: tb1
        location: /appz/data/tb1
    schemas:
      - name: appzschema1
        authorised_user: appzuser1
        search_path: true

Properties

  • POSTGRESQL_REPLICATION_MODE - Server replication mode (master/slave)
  • POSTGRESQL_DATA - Data storage location
  • POSTGRESQL_PASSWORD - Password of the user 'postgres'
  • POSTGRESQL_CONNECTUSER - The default user 'postgres' in the PostgreSQL server
  • POSTGRESQL_CONNECTIONDB - The default database 'postgres' in the PostgreSQL server
  • POSTGRESQL_PORT - Default port for PostgreSQL
  • POSTGRESQL_REP_USER - This is replication user to replicate data master to slave database.
  • PGPASSWORD - Replication user password, enter into vault.
  • APPZ_RESTORE_TOKEN - TBD
  • APPZ_LOAD_TOKEN - TBD

Volumes

It is stateful deployment and volumes are created for /appz/data as per volumes: section in appz.yml

Standard AppZ Volumes is also enabled.

Database Change Deployment

To be added soon