blob: e4f87e14a232e2aac35be96cbcb3f3a48f5086f9 (
plain) (
blame)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
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
|
