8000 feat: add database upgrade scripts (#250) · greboid/postgres@e2b58da · GitHub
[go: up one dir, main page]
Skip to content

Commit e2b58da

Browse files
pcncpcnc
authored
feat: add database upgrade scripts (supabase#250)
* feat: add database upgrade scripts
1 parent 1433f6f commit e2b58da

File tree

5 files changed

+181
-0
lines changed

5 files changed

+181
-0
lines changed

‎ansible/files/admin_api_scripts/pg_upgrade_check.sh

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,16 @@
1+
#! /usr/bin/env bash
2+
## This script provides a method to check the status of the database upgrade
3+
## process, which is updated in /tmp/pg-upgrade-status by pg_upgrade_initiate.sh
4+
## This runs on the old (source) instance.
5+
6+
set -euo pipefail
7+
8+
STATUS_FILE="/tmp/pg-upgrade-status"
9+
10+
if [ -f "${STATUS_FILE}" ]; then
11+
STATUS=$(cat "${STATUS_FILE}")
12+
echo -n "${STATUS}"
13+
else
14+
echo -n "unknown"
15+
fi
16+

‎ansible/files/admin_api_scripts/pg_upgrade_complete.sh

Lines changed: 45 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,45 @@
1+
#! /usr/bin/env bash
2+
3+
## This script is run on the newly launched instance which is to be promoted to
4+
## become the primary database instance once the upgrade successfully completes.
5+
## The following commands copy custom PG configs and enable previously disabled
6+
## extensions, containing regtypes referencing system OIDs.
7+
8+
# Extensions to be reenabled after pg_upgrade.
9+
# Running an upgrade with these extensions enabled will result in errors due to
10+
# them depending on regtypes referencing system OIDs. Thus they have been disabled
11+
# beforehand.
12+
EXTENSIONS_TO_REENABLE=(
13+
"pg_graphql"
14+
)
15+
16+
17+
run_sql() {
18+
STATEMENT=$1
19+
psql -h localhost -U supabase_admin -d postgres -c "$STATEMENT"
20+
}
21+
22+
function complete_pg_upgrade {
23+
mount -a -v
24+
25+
# copying custom configurations
26+
cp /data/conf/* /etc/postgresql-custom/
27+
28+
service postgresql start
29+
su -c 'vacuumdb --all --analyze-in-stages' -s $SHELL postgres
30+
31+
for EXTENSION in "${EXTENSIONS_TO_REENABLE[@]}"; do
32+
run_sql "CREATE EXTENSION IF NOT EXISTS ${EXTENSION} CASCADE;"
33+
done
34+
35+
sleep 5
36+
service postgresql restart
37+
38+
sleep 5
39+
service postgresql restart
40+
}
41+
42+
set -euo pipefail
43+
44+
complete_pg_upgrade >> /var/log/pg-upgrade-complete.log 2>&1
45+
echo "Upgrade job completed"

‎ansible/files/admin_api_scripts/pg_upgrade_initiate.sh

Lines changed: 102 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,102 @@
1+
#! /usr/bin/env bash
2+
3+
## This script is run on the old (source) instance, mounting the data disk
4+
## of the newly launched instance, disabling extensions containing regtypes,
5+
## and running pg_upgrade.
6+
## It reports the current status of the upgrade process to /tmp/pg-upgrade-status,
7+
## which can then be subsequently checked through pg_upgrade_check.sh.
8+
9+
# Extensions to disable before running pg_upgrade.
10+
# Running an upgrade with these extensions enabled will result in errors due to
11+
# them depending on regtypes referencing system OIDs.
12+
EXTENSIONS_TO_DISABLE=(
13+
"pg_graphql"
14+
)
15+
16+
set -eEuo pipefail
17+
18+
PGVERSION=$1
19+
20+
MOUNT_POINT="/data_migration"
21+
22+
run_sql() {
23+
STATEMENT=$1
24+
psql -h localhost -U supabase_admin -d postgres -c "$STATEMENT"
25+
}
26+
27+
c 57A0 leanup() {
28+
UPGRADE_STATUS=${1:-"failed"}
29+
EXIT_CODE=${?:-0}
30+
31+
systemctl start postgresql
32+
33+
for EXTENSION in "${EXTENSIONS_TO_DISABLE[@]}"; do
34+
run_sql "CREATE EXTENSION IF NOT EXISTS ${EXTENSION} CASCDE;"
35+
done
36+
37+
run_sql "ALTER USER postgres WITH NOSUPERUSER;"
38+
39+
umount $MOUNT_POINT
40+
echo "${UPGRADE_STATUS}" > /tmp/pg-upgrade-status
41+
42+
exit $EXIT_CODE
43+
}
44+
45+
function initiate_upgrade {
46+
BLOCK_DEVICE=$(lsblk -dpno name | grep -v "/dev/nvme[0-1]")
47+
echo "running" > /tmp/pg-upgrade-status
48+
49+
mkdir -p "$MOUNT_POINT"
50+
mount "$BLOCK_DEVICE" "$MOUNT_POINT"
51+
52+
mkdir -p "/tmp/pg_upgrade_bin"
53+
tar zxvf "/tmp/persistent/pg_upgrade_bin.tar.gz" -C "/tmp/pg_upgrade_bin"
54+
chown -R postgres:postgres "/tmp/pg_upgrade_bin/$PGVERSION"
55+
56+
for EXTENSION in "${EXTENSIONS_TO_DISABLE[@]}"; do
57+
run_sql "DROP EXTENSION IF EXISTS ${EXTENSION} CASCADE;"
58+
done
59+
60+
run_sql "ALTER USER postgres WITH SUPERUSER;"
61+
62+
PGDATAOLD=$(cat /etc/postgresql/postgresql.conf | grep data_directory | sed "s/data_directory = '\(.*\)'.*/\1/");
63+
PGDATANEW="$MOUNT_POINT/pgdata"
64+
PGBINNEW="/tmp/pg_upgrade_bin/$PGVERSION/bin"
65+
PGSHARENEW="/tmp/pg_upgrade_bin/$PGVERSION/share"
66+
67+
chown -R postgres:postgres "$MOUNT_POINT/"
68+
rm -rf "$PGDATANEW/"
69+
su -c "$PGBINNEW/initdb -L $PGSHARENEW -D $PGDATANEW/" -s $SHELL postgres
70+
71+
# running upgrade using at least 1 cpu core
72+
WORKERS=$(nproc | awk '{ print ($1 == 1 ? 1 : $1 - 1) }')
73+
74+
# upgrade job outputs a log in the cwd; needs write permissions
75+
cd /tmp
76+
77+
UPGRADE_COMMAND=$(cat <<EOF
78+
time ${PGBINNEW}/pg_upgrade \
79+
--old-bindir="/usr/lib/postgresql/bin" \
80+
--new-bindir=${PGBINNEW} \
81+
--old-datadir=${PGDATAOLD} \
82+
--new-datadir=${PGDATANEW} \
83+
--jobs="${WORKERS}" \
84+
--old-options='-c config_file=/etc/postgresql/postgresql.conf' \
85+
--new-options="-c data_directory=${PGDATANEW}"
86+
EOF
87+
)
88+
89+
systemctl stop postgresql
90+
su -c "$UPGRADE_COMMAND" -s $SHELL postgres
91+
92+
# copying custom configurations
93+
mkdir -p $MOUNT_POINT/conf
94+
cp /etc/postgresql-custom/* $MOUNT_POINT/conf/
95+
96+
cleanup "complete"
97+
}
98+
99+
trap cleanup ERR
100+
101+
initiate_upgrade >> /var/log/pg-upgrade-initiate.log 2>&1
102+
echo "Upgrade initiate job completed "

‎ansible/files/admin_api_scripts/pg_upgrade_prepare.sh

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,14 @@
1+
#! /usr/bin/env bash
2+
## This script is runs in advance of the database version upgrade, on the newly
3+
## launched instance which will eventually be promoted to become the primary
4+
## database instance once the upgrade successfully completes, terminating the
5+
## previous (source) instance.
6+
## The following commands safely stop the Postgres service and unmount
7+
## the data disk off the newly launched instance, to be re-attached to the
8+
## source instance and run the upgrade there.
9+
10+
set -euo pipefail
11+
12+
systemctl stop postgresql
13+
umount /data
14+

‎ansible/files/adminapi.sudoers.conf

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -10,6 +10,10 @@ Cmnd_Alias PGBOUNCER = /bin/systemctl start pgbouncer.service, /bin/systemctl st
1010
%adminapi ALL= NOPASSWD: /root/disable_walg.sh
1111
%adminapi ALL= NOPASSWD: /root/enable_walg.sh
1212
%adminapi ALL= NOPASSWD: /root/manage_readonly_mode.sh
13+
%adminapi ALL= NOPASSWD: /root/pg_upgrade_prepare.sh
14+
%adminapi ALL= NOPASSWD: /root/pg_upgrade_initiate.sh
15+
%adminapi ALL= NOPASSWD: /root/pg_upgrade_complete.sh
16+
%adminapi ALL= NOPASSWD: /root/pg_upgrade_check.sh
1317
%adminapi ALL= NOPASSWD: /usr/bin/systemctl daemon-reload
1418
%adminapi ALL= NOPASSWD: /usr/bin/systemctl restart postgresql.service
1519
%adminapi ALL= NOPASSWD: /usr/bin/systemctl restart adminapi.service

0 commit comments

Comments
 (0)
0