From d0bc90e08c29e881c388c6803ed9c49dff1f1776 Mon Sep 17 00:00:00 2001 From: Paul B Date: Fri, 31 Aug 2018 11:49:09 +0200 Subject: Initial commit open sourcing Postgresql Ansible role --- templates/pg_upgrade.sh.j2 | 175 +++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 175 insertions(+) create mode 100755 templates/pg_upgrade.sh.j2 (limited to 'templates/pg_upgrade.sh.j2') diff --git a/templates/pg_upgrade.sh.j2 b/templates/pg_upgrade.sh.j2 new file mode 100755 index 0000000..e4f87e1 --- /dev/null +++ b/templates/pg_upgrade.sh.j2 @@ -0,0 +1,175 @@ +#!/bin/bash -ex +# {{ ansible_managed }} +# +# WARNING: Please read both the documentation and this script if you want to run it +# Authors: +# - Théophile Helleboid +# - Paul Bonaud +# Description: +# This helper tries to strictly follow the official documentation of PostgreSQL +# from the pg_upgrade page: https://www.postgresql.org/docs/9.5/static/pgupgrade.html +# Usage: +# WARNING: Please read both the documentation and this script if you want to run it +# 1. edit this file and remove the "DRY_RUN=1" line +# 2. execute this script as the postgres admin user + +{% if postgres_pgbouncer_uri is defined and postgres_pgbouncer_uri %} +PGBOUNCER_PG_URI={{ postgres_pgbouncer_uri }} +{% endif %} +DATABASE_NAME={{ postgres_dbname }} +DATABASE_PORT={{ postgres_new_cluster_port }} + +OLD_CLUSTER_VERSION={{ postgres_old_cluster_version }} +OLD_CLUSTER_NAME={{ postgres_old_cluster_name }} +OLD_CLUSTER_DATADIR=/var/lib/postgresql/$OLD_CLUSTER_VERSION/$OLD_CLUSTER_NAME +NEW_CLUSTER_VERSION={{ postgres_new_cluster_version }} +NEW_CLUSTER_NAME={{ postgres_new_cluster_name }} +NEW_CLUSTER_DATADIR=/var/lib/postgresql/$NEW_CLUSTER_VERSION/$NEW_CLUSTER_NAME + +STANDBY_SERVER={{ postgres_standby_server }} +STANDBY_OLD_CLUSTER_NAME={{ postgres_standby_old_cluster_name }} +STANDBY_OLD_CLUSTER_DATADIR=/var/lib/postgresql/$OLD_CLUSTER_VERSION/$STANDBY_OLD_CLUSTER_NAME +STANDBY_NEW_CLUSTER_NAME={{ postgres_standby_new_cluster_name }} +STANDBY_NEW_CLUSTER_DATADIR=/var/lib/postgresql/$NEW_CLUSTER_VERSION/$STANDBY_NEW_CLUSTER_NAME + +DRY_RUN=1 + +if [ "$(whoami)" != "postgres" ]; then + echo "ERROR: you must run this script as the 'postgres' admin local user" + exit 1 +fi + +{% if postgres_pgbouncer_uri is defined and postgres_pgbouncer_uri %} +# Test the configuration is correcly deployed on pgbouncer +psql $PGBOUNCER_PG_URI --command 'show databases;' | grep "^ $DATABASE_NAME " +# Test connection to pgbouncer is OK +psql $PGBOUNCER_PG_URI --command 'show pools;' +{% endif %} + +if [ ! -f /usr/lib/postgresql/$NEW_CLUSTER_VERSION/bin/pg_upgrade ]; then + echo "ERROR: /usr/lib/postgresql/$NEW_CLUSTER_VERSION/bin/pg_upgrade is not available on this machine." + exit 1 +fi +# We need ssh access to the standby server +ssh $STANDBY_SERVER "ls -A $STANDBY_OLD_CLUSTER_DATADIR 2>&1>/dev/null" +ssh $STANDBY_SERVER "ls -A $STANDBY_NEW_CLUSTER_DATADIR 2>&1>/dev/null" +if [ ! -d $OLD_CLUSTER_DATADIR ] || [ ! -d $NEW_CLUSTER_DATADIR ]; then + echo "ERROR: Make sure both old cluster and new cluster data dirs exists" + echo "ERROR: on both primary and standby servers ($STANDBY_SERVER)." + echo "ERROR: old_cluster datadir: $OLD_CLUSTER_DATADIR" + echo "ERROR: new_cluster datadir: $NEW_CLUSTER_DATADIR" + #exit 1 +fi + +echo "WARNING: You are about to:" +{% if postgres_pgbouncer_uri is defined and postgres_pgbouncer_uri %} +echo "WARNING: * PAUSE connections to $DATABASE_NAME database." +{% endif %} +echo "WARNING: * STOP the primary database in $OLD_CLUSTER_VERSION version (and standbys)" +echo "WARNING: * dry-run the pg_upgrade from $OLD_CLUSTER_VERSION to $NEW_CLUSTER_VERSION" +echo "WARNING: * run the pg_upgrade in place with hardlinks" +echo "WARNING: * START the primary database in $NEW_CLUSTER_VERSION version (and standbys)" +{% if postgres_pgbouncer_uri is defined and postgres_pgbouncer_uri %} +echo "WARNING: * RESUME connections to $DATABASE_NAME." +{% endif %} +echo "================" +read -p "Are you sure? (y/n)" -n 1 -r +echo "================" +if [[ -z $DRY_RUN ]] && [[ $REPLY =~ ^[Yy]$ ]]; then + {% if postgres_pgbouncer_uri is defined and postgres_pgbouncer_uri %} + # Pause the databases; + psql $PGBOUNCER_PG_URI --command "PAUSE $DATABASE_NAME;" + {% endif %} + + # Stop the $OLD_CLUSTER_VERSION server + pg_ctlcluster $OLD_CLUSTER_VERSION $OLD_CLUSTER_NAME stop -m fast + + # Get latest checkpoint location of primary + echo "INFO: Primary PG controldata with latest checkpoint location" + /usr/lib/postgresql/$OLD_CLUSTER_VERSION/bin/pg_controldata $OLD_CLUSTER_DATADIR | head -n8 + + # Wait for standbys to catch up latest checkpoint + # TODO: how long to wait? + echo "Waiting for 9 seconds..." + sleep 9 + echo "INFO: Standby PG controldata with latest checkpoint location (after shutdown)" + ssh $STANDBY_SERVER pg_ctlcluster $OLD_CLUSTER_VERSION $STANDBY_OLD_CLUSTER_NAME stop -m fast + ssh $STANDBY_SERVER /usr/lib/postgresql/$OLD_CLUSTER_VERSION/bin/pg_controldata $STANDBY_OLD_CLUSTER_DATADIR | head -n8 + + echo "WARNING: 'Latest Checkpoint location' values should match on primary and on standby" + echo "================" + read -p "Do they match? PLEASE ANSWER (y/n)" -n 1 -r + echo "================" + + if [[ $REPLY =~ ^[Yy]$ ]]; then + # Test to upgrade the data + time /usr/lib/postgresql/$NEW_CLUSTER_VERSION/bin/pg_upgrade --check --link \ + -b /usr/lib/postgresql/$OLD_CLUSTER_VERSION/bin \ + -B /usr/lib/postgresql/$NEW_CLUSTER_VERSION/bin \ + -d $OLD_CLUSTER_DATADIR \ + -D $NEW_CLUSTER_DATADIR \ + -o " -c config_file=/etc/postgresql/$OLD_CLUSTER_VERSION/$OLD_CLUSTER_NAME/postgresql.conf" \ + -O " -c config_file=/etc/postgresql/$NEW_CLUSTER_VERSION/$NEW_CLUSTER_NAME/postgresql.conf" + + # Really upgrade the data + time /usr/lib/postgresql/$NEW_CLUSTER_VERSION/bin/pg_upgrade --link \ + -b /usr/lib/postgresql/$OLD_CLUSTER_VERSION/bin \ + -B /usr/lib/postgresql/$NEW_CLUSTER_VERSION/bin \ + -d $OLD_CLUSTER_DATADIR \ + -D $NEW_CLUSTER_DATADIR \ + -o " -c config_file=/etc/postgresql/$OLD_CLUSTER_VERSION/$OLD_CLUSTER_NAME/postgresql.conf" \ + -O " -c config_file=/etc/postgresql/$NEW_CLUSTER_VERSION/$NEW_CLUSTER_NAME/postgresql.conf" + + ## Upgrade standby server + + ### Making sure new datadir is empty + if [ -z "$(ssh $STANDBY_SERVER ls -A $STANDBY_NEW_CLUSTER_DATADIR)" ]; then + echo "INFO: New cluster datadir is empty on standby server. Good." + else + echo "WARNING: New cluster datadir is NOT empty on standby server. Deleting the content of $STANDBY_NEW_CLUSTER_DATADIR on $STANDBY_SERVER now..." + ssh $STANDBY_SERVER rm -rf $STANDBY_NEW_CLUSTER_DATADIR + fi + + ### Save configuration files + TMPDIR=$(ssh $STANDBY_SERVER mktemp -d -t pg_upgrade_XXXX) + ssh $STANDBY_SERVER mv $STANDBY_OLD_CLUSTER_DATADIR/recovery.conf $TMPDIR + + ### Upgrade (via rsync) + rsync --archive --delete --hard-links --size-only --no-inc-recursive $OLD_CLUSTER_DATADIR $NEW_CLUSTER_DATADIR $STANDBY_SERVER:$(dirname $STANDBY_NEW_CLUSTER_DATADIR) + + ### Restore saved configured files + ssh $STANDBY_SERVER mv $TMPDIR/recovery.conf $STANDBY_NEW_CLUSTER_DATADIR/ + ssh $STANDBY_SERVER rm -rf $TMPDIR + + # Start the $NEW_CLUSTER_VERSION primary server + pg_ctlcluster $NEW_CLUSTER_VERSION $NEW_CLUSTER_NAME start + + # Test local primary is accepting connections + psql --port=$DATABASE_PORT --dbname=$DATABASE_NAME --command="SELECT NOW();" + + {% if postgres_pgbouncer_uri is defined and postgres_pgbouncer_uri %} + # Resume the connexions + psql $PGBOUNCER_PG_URI --command "RESUME $DATABASE_NAME;" + {% endif %} + + # Start the $NEW_CLUSTER_VERSION standby server + ssh $STANDBY_SERVER pg_ctlcluster $NEW_CLUSTER_VERSION $STANDBY_NEW_CLUSTER_NAME start + + echo "DONE. Upgrade of PG cluster from $OLD_CLUSTER_VERSION/$OLD_CLUSTER_NAME to $NEW_CLUSTER_VESION/$NEW_CLUSTER_NAME is finished! Well done!" + else + # Start the $OLD_CLUSTER_VERSION primary server + pg_ctlcluster $OLD_CLUSTER_VERSION $OLD_CLUSTER_NAME start + # Start the $OLD_CLUSTER_VERSION standby server + ssh $STANDBY_SERVER pg_ctlcluster $OLD_CLUSTER_VERSION $OLD_CLUSTER_NAME start + + # Test local primary is accepting connections + psql --dbname=$DATABASE_NAME --command="SELECT NOW();" + + {% if postgres_pgbouncer_uri is defined and postgres_pgbouncer_uri %} + # Resume the connexions + psql $PGBOUNCER_PG_URI --command "RESUME $DATABASE_NAME;" + {% endif %} + fi +else + echo "INFO: You didn't want to continue or was in 'dry-run' mode" +fi -- cgit v1.2.3