Upgrade process for PG9.4 -> 11

01-disable.bash

#!/usr/bin/env bash

all-databases() {
  cd /tmp && sudo -u postgres psql -U pgbouncer pgbouncer -c 'show databases;' -p 6432 | grep -Po '^ gc_\S+'
}

disable-databases() {
  xargs -n1 -I _DATABASE_ sudo -u postgres psql -U pgbouncer pgbouncer -p 6432 -c 'disable _DATABASE_;'
}

enable-databases() {
  xargs -n1 -I _DATABASE_ sudo -u postgres psql -U pgbouncer pgbouncer -p 6432 -c 'enable _DATABASE_;'
}

all-databases | disable-databases

02-stop-postgres

do this on all machines manually

03-mv-data

do this on all machines manually

04-init-upgrade-rsync.bash

#!/bin/bash

set -euo pipefail

sudo -u postgres \
   /usr/lib/postgresql/11/bin/initdb \
       --pgdata=/data/postgresql \
       --locale=en_US.utf8 \
       --auth=ident \
       --auth-local=peer \
       --auth-host=md5

sudo -u postgres \
   /usr/lib/postgresql/11/bin/pg_upgrade --link --jobs 16 \
     --old-bindir /usr/lib/postgresql/9.4/bin \
     --new-bindir /usr/lib/postgresql/11/bin \
     --old-datadir /data/postgresql.bkp \
     --new-datadir /data/postgresql \
     --old-options "-c 'config_file=/etc/postgresql/9.4/main/postgresql.conf' -c 'data_directory=/data/postgresql.bkp'" \
     --new-options "-c 'config_file=/etc/postgresql/11/main/postgresql.conf' -c 'data_directory=/data/postgresql'"


# On primary
SYNC_NODE=staging-postgresql-1-1b9j
sudo -u postgres rsync --archive --delete --hard-links --size-only --no-inc-recursive /data/postgresql.bkp /data/postgresql postgres@"${SYNC_NODE}:/data"
ASYNC_NODE=staging-postgresql-2-pbph
sudo -u postgres rsync --archive --delete --hard-links --size-only --no-inc-recursive /data/postgresql.bkp /data/postgresql postgres@"${ASYNC_NODE}:/data"

05-ready-nodes.bash

#!/usr/bin/env bash

set -x -e

crm node ready pg00
crm resource cleanup msPostgresql

(crm_mon -Afr || /bin/true)
sleep 3

crm node ready pg01
crm resource cleanup msPostgresql

(crm_mon -Afr || /bin/true)
sleep 3

crm node ready pg02
crm resource cleanup msPostgresql
crm resource cleanup Postgresql

(crm_mon -Afr || /bin/true)
sleep 3

06-stats-live.bash

#!/usr/bin/env bash

sudo -u postgres vacuumdb --jobs 16 --dbname=gc_paysvc_live --analyze-only

07-enable-live.bash

#!/usr/bin/env bash

all-databases() {
  cd /tmp && sudo -u postgres psql -U pgbouncer pgbouncer -c 'show databases;' -p 6432 | grep -Po '^ gc_\S+'
}

disable-databases() {
  xargs -n1 -I _DATABASE_ sudo -u postgres psql -U pgbouncer pgbouncer -p 6432 -c 'disable _DATABASE_;'
}

enable-databases() {
  xargs -n1 -I _DATABASE_ sudo -u postgres psql -U pgbouncer pgbouncer -p 6432 -c 'enable _DATABASE_;'
}

all-databases | grep gc_paysvc_live | enable-databases

08-stats.bash

#!/usr/bin/env bash

set -euo pipefail

trap "kill 0" INT TERM

FAIL=0

DATABASES=(
    gc_paysvc_live
    gc_paysvc_sandbox
    gc_sage
    gc_banking_integrations_live
    gc_dashboard_sandbox
    optimus
    gc_banking_integrations_sandbox
    gc_live
    gc_auth
    gc_dashboard_live
    gc_sandbox
)

main() {
    vacuum
    wait_all
}

vacuum() {
    for database in "${DATABASES[@]}"
    do
        sudo -u postgres vacuumdb -d "$database" --analyze-only --jobs=32 &
    done
}

wait_all() {
  for job in $(jobs -p)
    do
        wait "$job" || ((FAIL+=1))
    done
}

main

exit $FAIL

09-enable.bash

#!/usr/bin/env bash

all-databases() {
  cd /tmp && sudo -u postgres psql -U pgbouncer pgbouncer -c 'show databases;' -p 6432 | grep -Po '^ gc_\S+'
}

disable-databases() {
  xargs -n1 -I _DATABASE_ sudo -u postgres psql -U pgbouncer pgbouncer -p 6432 -c 'disable _DATABASE_;'
}

enable-databases() {
  xargs -n1 -I _DATABASE_ sudo -u postgres psql -U pgbouncer pgbouncer -p 6432 -c 'enable _DATABASE_;'
}

all-databases | enable-databases