Initial commit open sourcing Postgresql Ansible role1.0.0

1 files changed, 175 insertions, 0 deletions

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