Database Upgrade

[sslaws]

As SIMS technical team, we need to ensure that our tools and platform are maintained at current versions. Postgres 12 is EOL as of November 2024 and must be upgraded prior to that. As part of this migration, ideally the team would use technologies that treat the DB more as a service.

Acceptance Criteria

• [ ] Deploy new DB and PgBouncer to all environments
  • [ ] Dev
  • [ ] Test
  • [ ] Staging
  • [ ] Production
• [ ] Update application connection to use PgBouncer
• [ ] Document, verify, and execute process to migrate Production data
• [ ] Document, test and execute the removal of the old database
• [ ] Use Helm for Deployment

Technical Context

Decisions: Use Crunchy Operator and Helm to create the DB

Review Items:

• Ideally use PG 16 for longevity but currently installed operator doesn't support this.
• Current DB name is SIMSDB but Crunchy doesn't like that.
• Do we need to expose the postgres super user account?

Sample yaml using Crunchy Operator to deploy Patroni HA Postgres 15 with pgbouncer and pgbackrest.

apiVersion: postgres-operator.crunchydata.com/v1beta1
kind: PostgresCluster
metadata:
  name: simsdb
spec:
  postgresVersion: 15
  instances:
    - name: pgha1
      replicas: 3
      resources:
        limits:
          cpu: 1
          memory: 512Mi
        requests:
          cpu: 250m
          memory: 512Mi
  backups:
    pgbackrest:
      global:
        repo1-retention-archive: "10"
        repo1-retention-full: "7"
        repo1-retention-incremental: "24"
      repos:
      - name: repo1
        schedules:
          full: 0 2 * * *
          incremental: 0 0-1,3-23 * * *
        volume:
          volumeClaimSpec:
            accessModes:
            - ReadWriteOnce
            resources:
              requests:
                storage: 20Gi
  proxy:
    pgBouncer:
      replicas: 3
  users:
    - name: postgres
    - databases:
      - simsdb
      name: simsdb
  openshift: true

Perform a Point in time recovery

apiVersion: postgres-operator.crunchydata.com/v1beta1
kind: PostgresCluster
metadata:
  name: simsdb-pitr
spec:
  dataSource:
    postgresCluster:
      clusterName: simsdb
      repoName: repo1
      options:
      - --type=time
      - --target="2024-05-30 05:00:00-00"
  image: registry.developers.crunchydata.com/crunchydata/crunchy-postgres:ubi8-15.7-0
  postgresVersion: 15
  instances:
    - dataVolumeClaimSpec:
        accessModes:
        - "ReadWriteOnce"
        resources:
          requests:
            storage: 5Gi
  backups:
    pgbackrest:
      image: registry.developers.crunchydata.com/crunchydata/crunchy-pgbackrest:ubi8-2.51-0
      repos:
      - name: repo1
        volume:
          volumeClaimSpec:
            accessModes:
            - "ReadWriteOnce"
            resources:
              requests:
                storage: 5Gi
  users:
  - name: postgres
  - name: simsdb