templates/pg_upgrade.sh.j2

   1 #!/bin/bash -ex
   2 # {{ ansible_managed }}
   3 #
   4 # WARNING: Please read both the documentation and this script if you want to run it
   5 # Authors:
   6 # - Théophile Helleboid
   7 # - Paul Bonaud
   8 # Description:
   9 #   This helper tries to strictly follow the official documentation of PostgreSQL
  10 #   from the pg_upgrade page: https://www.postgresql.org/docs/9.5/static/pgupgrade.html
  11 # Usage:
  12 #   WARNING: Please read both the documentation and this script if you want to run it
  13 #   1. edit this file and remove the "DRY_RUN=1" line
  14 #   2. execute this script as the postgres admin user
  15
  16 {% if postgres_pgbouncer_uri is defined and postgres_pgbouncer_uri %}
  17 PGBOUNCER_PG_URI={{ postgres_pgbouncer_uri }}
  18 {% endif %}
  19 DATABASE_NAME={{ postgres_dbname }}
  20 DATABASE_PORT={{ postgres_new_cluster_port }}
  21
  22 OLD_CLUSTER_VERSION={{ postgres_old_cluster_version }}
  23 OLD_CLUSTER_NAME={{ postgres_old_cluster_name }}
  24 OLD_CLUSTER_DATADIR=/var/lib/postgresql/$OLD_CLUSTER_VERSION/$OLD_CLUSTER_NAME
  25 NEW_CLUSTER_VERSION={{ postgres_new_cluster_version }}
  26 NEW_CLUSTER_NAME={{ postgres_new_cluster_name }}
  27 NEW_CLUSTER_DATADIR=/var/lib/postgresql/$NEW_CLUSTER_VERSION/$NEW_CLUSTER_NAME
  28
  29 STANDBY_SERVER={{ postgres_standby_server }}
  30 STANDBY_OLD_CLUSTER_NAME={{ postgres_standby_old_cluster_name }}
  31 STANDBY_OLD_CLUSTER_DATADIR=/var/lib/postgresql/$OLD_CLUSTER_VERSION/$STANDBY_OLD_CLUSTER_NAME
  32 STANDBY_NEW_CLUSTER_NAME={{ postgres_standby_new_cluster_name }}
  33 STANDBY_NEW_CLUSTER_DATADIR=/var/lib/postgresql/$NEW_CLUSTER_VERSION/$STANDBY_NEW_CLUSTER_NAME
  34
  35 DRY_RUN=1
  36
  37 if [ "$(whoami)" != "postgres" ]; then
  38     echo "ERROR: you must run this script as the 'postgres' admin local user"
  39     exit 1
  40 fi
  41
  42 {% if postgres_pgbouncer_uri is defined and postgres_pgbouncer_uri %}
  43 # Test the configuration is correcly deployed on pgbouncer
  44 psql $PGBOUNCER_PG_URI --command 'show databases;'  | grep "^ $DATABASE_NAME "
  45 # Test connection to pgbouncer is OK
  46 psql $PGBOUNCER_PG_URI --command 'show pools;'
  47 {% endif %}
  48
  49 if [ ! -f /usr/lib/postgresql/$NEW_CLUSTER_VERSION/bin/pg_upgrade ]; then
  50     echo "ERROR: /usr/lib/postgresql/$NEW_CLUSTER_VERSION/bin/pg_upgrade is not available on this machine."
  51     exit 1
  52 fi
  53 # We need ssh access to the standby server
  54 ssh $STANDBY_SERVER "ls -A $STANDBY_OLD_CLUSTER_DATADIR 2>&1>/dev/null"
  55 ssh $STANDBY_SERVER "ls -A $STANDBY_NEW_CLUSTER_DATADIR 2>&1>/dev/null"
  56 if [ ! -d $OLD_CLUSTER_DATADIR ] || [ ! -d $NEW_CLUSTER_DATADIR ]; then
  57     echo "ERROR: Make sure both old cluster and new cluster data dirs exists"
  58     echo "ERROR: on both primary and standby servers ($STANDBY_SERVER)."
  59     echo "ERROR: old_cluster datadir: $OLD_CLUSTER_DATADIR"
  60     echo "ERROR: new_cluster datadir: $NEW_CLUSTER_DATADIR"
  61     #exit 1
  62 fi
  63
  64 echo "WARNING: You are about to:"
  65 {% if postgres_pgbouncer_uri is defined and postgres_pgbouncer_uri %}
  66 echo "WARNING: * PAUSE connections to $DATABASE_NAME database."
  67 {% endif %}
  68 echo "WARNING: * STOP the primary database in $OLD_CLUSTER_VERSION version (and standbys)"
  69 echo "WARNING: * dry-run the pg_upgrade from $OLD_CLUSTER_VERSION to $NEW_CLUSTER_VERSION"
  70 echo "WARNING: * run the pg_upgrade in place with hardlinks"
  71 echo "WARNING: * START the primary database in $NEW_CLUSTER_VERSION version (and standbys)"
  72 {% if postgres_pgbouncer_uri is defined and postgres_pgbouncer_uri %}
  73 echo "WARNING: * RESUME connections to $DATABASE_NAME."
  74 {% endif %}
  75 echo "================"
  76 read -p "Are you sure? (y/n)" -n 1 -r
  77 echo "================"
  78 if [[ -z $DRY_RUN ]] && [[ $REPLY =~ ^[Yy]$ ]]; then
  79     {% if postgres_pgbouncer_uri is defined and postgres_pgbouncer_uri %}
  80     # Pause the databases;
  81     psql $PGBOUNCER_PG_URI --command "PAUSE $DATABASE_NAME;"
  82     {% endif %}
  83
  84     # Stop the $OLD_CLUSTER_VERSION server
  85     pg_ctlcluster $OLD_CLUSTER_VERSION $OLD_CLUSTER_NAME stop -m fast
  86
  87     # Get latest checkpoint location of primary
  88     echo "INFO: Primary PG controldata with latest checkpoint location"
  89     /usr/lib/postgresql/$OLD_CLUSTER_VERSION/bin/pg_controldata $OLD_CLUSTER_DATADIR | head -n8
  90
  91     # Wait for standbys to catch up latest checkpoint
  92     # TODO: how long to wait?
  93     echo "Waiting for 9 seconds..."
  94     sleep 9
  95     echo "INFO: Standby PG controldata with latest checkpoint location (after shutdown)"
  96     ssh $STANDBY_SERVER pg_ctlcluster $OLD_CLUSTER_VERSION $STANDBY_OLD_CLUSTER_NAME stop -m fast
  97     ssh $STANDBY_SERVER /usr/lib/postgresql/$OLD_CLUSTER_VERSION/bin/pg_controldata $STANDBY_OLD_CLUSTER_DATADIR | head -n8
  98
  99     echo "WARNING: 'Latest Checkpoint location' values should match on primary and on standby"
 100     echo "================"
 101     read -p "Do they match? PLEASE ANSWER (y/n)" -n 1 -r
 102     echo "================"
 103
 104     if [[ $REPLY =~ ^[Yy]$ ]]; then
 105         # Test to upgrade the data
 106         time /usr/lib/postgresql/$NEW_CLUSTER_VERSION/bin/pg_upgrade --check --link        \
 107              -b /usr/lib/postgresql/$OLD_CLUSTER_VERSION/bin                                \
 108              -B /usr/lib/postgresql/$NEW_CLUSTER_VERSION/bin                                \
 109              -d $OLD_CLUSTER_DATADIR                  \
 110              -D $NEW_CLUSTER_DATADIR                  \
 111              -o " -c config_file=/etc/postgresql/$OLD_CLUSTER_VERSION/$OLD_CLUSTER_NAME/postgresql.conf" \
 112              -O " -c config_file=/etc/postgresql/$NEW_CLUSTER_VERSION/$NEW_CLUSTER_NAME/postgresql.conf"
 113
 114         # Really upgrade the data
 115         time /usr/lib/postgresql/$NEW_CLUSTER_VERSION/bin/pg_upgrade         --link        \
 116              -b /usr/lib/postgresql/$OLD_CLUSTER_VERSION/bin                                \
 117              -B /usr/lib/postgresql/$NEW_CLUSTER_VERSION/bin                                \
 118              -d $OLD_CLUSTER_DATADIR                  \
 119              -D $NEW_CLUSTER_DATADIR                  \
 120              -o " -c config_file=/etc/postgresql/$OLD_CLUSTER_VERSION/$OLD_CLUSTER_NAME/postgresql.conf" \
 121              -O " -c config_file=/etc/postgresql/$NEW_CLUSTER_VERSION/$NEW_CLUSTER_NAME/postgresql.conf"
 122
 123         ## Upgrade standby server
 124
 125         ### Making sure new datadir is empty
 126         if [ -z "$(ssh $STANDBY_SERVER ls -A $STANDBY_NEW_CLUSTER_DATADIR)" ]; then
 127             echo "INFO: New cluster datadir is empty on standby server. Good."
 128         else
 129             echo "WARNING: New cluster datadir is NOT empty on standby server. Deleting the content of $STANDBY_NEW_CLUSTER_DATADIR on $STANDBY_SERVER now..."
 130             ssh $STANDBY_SERVER rm -rf $STANDBY_NEW_CLUSTER_DATADIR
 131         fi
 132
 133         ### Save configuration files
 134         TMPDIR=$(ssh $STANDBY_SERVER mktemp -d -t pg_upgrade_XXXX)
 135         ssh $STANDBY_SERVER mv $STANDBY_OLD_CLUSTER_DATADIR/recovery.conf $TMPDIR
 136
 137         ### Upgrade (via rsync)
 138         rsync --archive --delete --hard-links --size-only --no-inc-recursive $OLD_CLUSTER_DATADIR $NEW_CLUSTER_DATADIR $STANDBY_SERVER:$(dirname $STANDBY_NEW_CLUSTER_DATADIR)
 139
 140         ### Restore saved configured files
 141         ssh $STANDBY_SERVER mv $TMPDIR/recovery.conf $STANDBY_NEW_CLUSTER_DATADIR/
 142         ssh $STANDBY_SERVER rm -rf $TMPDIR
 143
 144         # Start the $NEW_CLUSTER_VERSION primary server
 145         pg_ctlcluster $NEW_CLUSTER_VERSION $NEW_CLUSTER_NAME start
 146
 147         # Test local primary is accepting connections
 148         psql --port=$DATABASE_PORT --dbname=$DATABASE_NAME --command="SELECT NOW();"
 149
 150         {% if postgres_pgbouncer_uri is defined and postgres_pgbouncer_uri %}
 151         # Resume the connexions
 152         psql $PGBOUNCER_PG_URI --command "RESUME $DATABASE_NAME;"
 153         {% endif %}
 154
 155         # Start the $NEW_CLUSTER_VERSION standby server
 156         ssh $STANDBY_SERVER pg_ctlcluster $NEW_CLUSTER_VERSION $STANDBY_NEW_CLUSTER_NAME start
 157
 158         echo "DONE. Upgrade of PG cluster from $OLD_CLUSTER_VERSION/$OLD_CLUSTER_NAME to $NEW_CLUSTER_VESION/$NEW_CLUSTER_NAME is finished! Well done!"
 159     else
 160         # Start the $OLD_CLUSTER_VERSION primary server
 161         pg_ctlcluster $OLD_CLUSTER_VERSION $OLD_CLUSTER_NAME start
 162         # Start the $OLD_CLUSTER_VERSION standby server
 163         ssh $STANDBY_SERVER pg_ctlcluster $OLD_CLUSTER_VERSION $OLD_CLUSTER_NAME start
 164
 165         # Test local primary is accepting connections
 166         psql --dbname=$DATABASE_NAME --command="SELECT NOW();"
 167
 168         {% if postgres_pgbouncer_uri is defined and postgres_pgbouncer_uri %}
 169         # Resume the connexions
 170         psql $PGBOUNCER_PG_URI --command "RESUME $DATABASE_NAME;"
 171         {% endif %}
 172     fi
 173 else
 174     echo "INFO: You didn't want to continue or was in 'dry-run' mode"
 175 fi