aboutsummaryrefslogtreecommitdiffhomepage
path: root/templates/pg_upgrade.sh.j2
diff options
context:
space:
mode:
Diffstat (limited to 'templates/pg_upgrade.sh.j2')
-rwxr-xr-xtemplates/pg_upgrade.sh.j2175
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 @@
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 %}
17PGBOUNCER_PG_URI={{ postgres_pgbouncer_uri }}
18{% endif %}
19DATABASE_NAME={{ postgres_dbname }}
20DATABASE_PORT={{ postgres_new_cluster_port }}
21
22OLD_CLUSTER_VERSION={{ postgres_old_cluster_version }}
23OLD_CLUSTER_NAME={{ postgres_old_cluster_name }}
24OLD_CLUSTER_DATADIR=/var/lib/postgresql/$OLD_CLUSTER_VERSION/$OLD_CLUSTER_NAME
25NEW_CLUSTER_VERSION={{ postgres_new_cluster_version }}
26NEW_CLUSTER_NAME={{ postgres_new_cluster_name }}
27NEW_CLUSTER_DATADIR=/var/lib/postgresql/$NEW_CLUSTER_VERSION/$NEW_CLUSTER_NAME
28
29STANDBY_SERVER={{ postgres_standby_server }}
30STANDBY_OLD_CLUSTER_NAME={{ postgres_standby_old_cluster_name }}
31STANDBY_OLD_CLUSTER_DATADIR=/var/lib/postgresql/$OLD_CLUSTER_VERSION/$STANDBY_OLD_CLUSTER_NAME
32STANDBY_NEW_CLUSTER_NAME={{ postgres_standby_new_cluster_name }}
33STANDBY_NEW_CLUSTER_DATADIR=/var/lib/postgresql/$NEW_CLUSTER_VERSION/$STANDBY_NEW_CLUSTER_NAME
34
35DRY_RUN=1
36
37if [ "$(whoami)" != "postgres" ]; then
38 echo "ERROR: you must run this script as the 'postgres' admin local user"
39 exit 1
40fi
41
42{% if postgres_pgbouncer_uri is defined and postgres_pgbouncer_uri %}
43# Test the configuration is correcly deployed on pgbouncer
44psql $PGBOUNCER_PG_URI --command 'show databases;' | grep "^ $DATABASE_NAME "
45# Test connection to pgbouncer is OK
46psql $PGBOUNCER_PG_URI --command 'show pools;'
47{% endif %}
48
49if [ ! -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
52fi
53# We need ssh access to the standby server
54ssh $STANDBY_SERVER "ls -A $STANDBY_OLD_CLUSTER_DATADIR 2>&1>/dev/null"
55ssh $STANDBY_SERVER "ls -A $STANDBY_NEW_CLUSTER_DATADIR 2>&1>/dev/null"
56if [ ! -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
62fi
63
64echo "WARNING: You are about to:"
65{% if postgres_pgbouncer_uri is defined and postgres_pgbouncer_uri %}
66echo "WARNING: * PAUSE connections to $DATABASE_NAME database."
67{% endif %}
68echo "WARNING: * STOP the primary database in $OLD_CLUSTER_VERSION version (and standbys)"
69echo "WARNING: * dry-run the pg_upgrade from $OLD_CLUSTER_VERSION to $NEW_CLUSTER_VERSION"
70echo "WARNING: * run the pg_upgrade in place with hardlinks"
71echo "WARNING: * START the primary database in $NEW_CLUSTER_VERSION version (and standbys)"
72{% if postgres_pgbouncer_uri is defined and postgres_pgbouncer_uri %}
73echo "WARNING: * RESUME connections to $DATABASE_NAME."
74{% endif %}
75echo "================"
76read -p "Are you sure? (y/n)" -n 1 -r
77echo "================"
78if [[ -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
173else
174 echo "INFO: You didn't want to continue or was in 'dry-run' mode"
175fi
generated by cgit v1.2.3 (git 2.25.1) at 2024-11-21 19:57:42 +0000