]> git.immae.eu Git - github/fretlink/ansible-postgresql-role.git/blob - templates/pg_upgrade.sh.j2
Merge pull request #11 from paulrbr-fl/postgresql-12
[github/fretlink/ansible-postgresql-role.git] / 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