]>
Commit | Line | Data |
---|---|---|
d0bc90e0 P |
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 |