chore: pg scripts update

supabase · pcnc · Feb 2, 2023 · Jan 18, 2023 · Jan 18, 2023 · Jan 18, 2023
commit 1e29a31187962690501044af45e39f6afa62bd85
@@ -6,40 +6,67 @@
 ## extensions, containing regtypes referencing system OIDs.
 
 # Extensions to be reenabled after pg_upgrade.
-# Running an upgrade with these extensions enabled will result in errors due to 
+# Running an upgrade with these extensions enabled will result in errors due to
 # them depending on regtypes referencing system OIDs. Thus they have been disabled
 # beforehand.
 EXTENSIONS_TO_REENABLE=(
     "pg_graphql"
 )
 
+set -euo pipefail
 
 run_sql() {
     STATEMENT=$1
     psql -h localhost -U supabase_admin -d postgres -c "$STATEMENT"
 }
 
+cleanup() {
+    UPGRADE_STATUS=${1:-"failed"}
+    EXIT_CODE=${?:-0}
+
+    echo "${UPGRADE_STATUS}" > /tmp/pg-upgrade-status
+
+    exit $EXIT_CODE
+}
+
 function complete_pg_upgrade {
+    echo "running" > /tmp/pg-upgrade-status
+
     mount -a -v
 
     # copying custom configurations
     cp -R /data/conf/* /etc/postgresql-custom/
+    chown -R postgres:postgres /var/lib/postgresql/data
+    chown -R postgres:postgres /data/pgdata
 
     service postgresql start
-    su -c 'vacuumdb --all --analyze-in-stages' -s $SHELL postgres
 
     for EXTENSION in "${EXTENSIONS_TO_REENABLE[@]}"; do
         run_sql "CREATE EXTENSION IF NOT EXISTS ${EXTENSION} CASCADE;"
     done
-
-    sleep 5
-    service postgresql restart
 
     sleep 5
     service postgresql restart
+
+    if [[ $(systemctl is-active gotrue) == "inactive" ]]; then
+        echo "starting gotrue"
+        systemctl start --no-block gotrue || true
+    fi
+
+    if [[ $(systemctl is-active postgrest) == "inactive" ]]; then
+        echo "starting postgrest"
+        systemctl start --no-block postgrest || true
+    fi
+
+    echo "Upgrade job completed"
 }
 
-set -euo pipefail
+function start_vacuum_analyze {
+    su -c 'vacuumdb --all --analyze-in-stages' -s $SHELL postgres
+    cleanup "complete"
+}
+
+trap cleanup ERR
 
-complete_pg_upgrade >> /var/log/pg-upgrade-complete.log 2>&1
-echo "Upgrade job completed"
+complete_pg_upgrade >>/var/log/pg-upgrade-complete.log 2>&1
+start_vacuum_analyze >>/var/log/pg-upgrade-complete.log 2>&1 &
@@ -24,10 +24,38 @@ run_sql() {
     psql -h localhost -U supabase_admin -d postgres -c "$STATEMENT"
 }
 
+function shutdown_services {
+    if [[ $(systemctl is-active gotrue) == "active" ]]; then
+        echo "stopping gotrue"
+        systemctl stop gotrue || true
+    fi
+
+    if [[ $(systemctl is-active postgrest) == "active" ]]; then
+        echo "stopping postgrest"
+        systemctl stop postgrest || true
+    fi
+}
+
+function start_services {
+    if [[ $(systemctl is-active gotrue) == "inactive" ]]; then
+        echo "starting gotrue"
+        systemctl start gotrue || true
+    fi
+
+    if [[ $(systemctl is-active postgrest) == "inactive" ]]; then
+        echo "starting postgrest"
+        systemctl start postgrest || true
+    fi
+}
+
 cleanup() {
     UPGRADE_STATUS=${1:-"failed"}
     EXIT_CODE=${?:-0}
 
+    if [ -d "${MOUNT_POINT}/pgdata/pg_upgrade_output.d/" ]; then
+        cp -R "${MOUNT_POINT}/pgdata/pg_upgrade_output.d/" /var/log/
+    fi
+
     if [ -L /var/lib/postgresql ]; then
         rm /var/lib/postgresql
         mv /var/lib/postgresql.bak /var/lib/postgresql
@@ -42,9 +70,8 @@ cleanup() {
     done
 
     run_sql "ALTER USER postgres WITH NOSUPERUSER;"
-    if [ -d "${MOUNT_POINT}/pgdata/pg_upgrade_output.d/" ]; then
-        cp -R "${MOUNT_POINT}/pgdata/pg_upgrade_output.d/" /var/log/
-    fi
+
+    start_services
 
     umount $MOUNT_POINT
     echo "${UPGRADE_STATUS}" > /tmp/pg-upgrade-status
@@ -53,11 +80,17 @@ cleanup() {
 }
 
 function initiate_upgrade {
-    BLOCK_DEVICE=$(lsblk -dpno name | grep -v "/dev/nvme[0-1]")
     echo "running" > /tmp/pg-upgrade-status
 
+    shutdown_services
+
+    # awk NF==3 prints lines with exactly 3 fields, which are the block devices currently not mounted anywhere
+    # excluding nvme0 since it is the root disk
+    BLOCK_DEVICE=$(lsblk -dprno name,size,mountpoint,type | grep "disk" | grep -v "nvme0" | awk 'NF==3 { print $1; }')
+
     mkdir -p "$MOUNT_POINT"
     mount "$BLOCK_DEVICE" "$MOUNT_POINT"
+    resize2fs "$BLOCK_DEVICE"
 
     SHARED_PRELOAD_LIBRARIES=$(cat /etc/postgresql/postgresql.conf | grep shared_preload_libraries |  sed "s/shared_preload_libraries = '\(.*\)'.*/\1/")
     PGDATAOLD=$(cat /etc/postgresql/postgresql.conf | grep data_directory | sed "s/data_directory = '\(.*\)'.*/\1/")    
@@ -73,6 +106,12 @@ function initiate_upgrade {
     cp /root/pg_upgrade_pgsodium_getkey.sh "$PGSHARENEW/extension/pgsodium_getkey"
     chmod +x "$PGSHARENEW/extension/pgsodium_getkey"
 
+    if [ -f "$MOUNT_POINT/pgsodium_root.key" ]; then
+        cp "$MOUNT_POINT/pgsodium_root.key" /etc/postgresql-custom/pgsodium_root.key
+        chown postgres:postgres /etc/postgresql-custom/pgsodium_root.key
+        chmod 600 /etc/postgresql-custom/pgsodium_root.key
+    fi
+
     chown -R postgres:postgres "/tmp/pg_upgrade_bin/$PGVERSION"
 
     for EXTENSION in "${EXTENSIONS_TO_DISABLE[@]}"; do
@@ -108,6 +147,13 @@ EOF
     mv /var/lib/postgresql /var/lib/postgresql.bak
     ln -s /tmp/pg_upgrade_bin/15/share /var/lib/postgresql
 
+    if [ ! -L /var/lib/postgresql.bak/data ]; then
+        if [ -L /var/lib/postgresql/data ]; then
+            rm /var/lib/postgresql/data
+        fi
+        ln -s /var/lib/postgresql.bak/data /var/lib/postgresql/data
+    fi
+
     systemctl stop postgresql
     su -c "$UPGRADE_COMMAND" -s $SHELL postgres
 
@@ -120,5 +166,5 @@ EOF
 
 trap cleanup ERR
 
-initiate_upgrade >> /var/log/pg-upgrade-initiate.log 2>&1
-echo "Upgrade initiate job completed "
+initiate_upgrade >> /var/log/pg-upgrade-initiate.log 2>&1 &
+echo "Upgrade initiate job completed"
@@ -1,14 +1,25 @@
 #! /usr/bin/env bash
-## This script is runs in advance of the database version upgrade, on the newly 
-## launched instance which will eventually be promoted to become the primary 
+## This script is runs in advance of the database version upgrade, on the newly
+## launched instance which will eventually be promoted to become the primary
 ## database instance once the upgrade successfully completes, terminating the
 ## previous (source) instance.
-## The following commands safely stop the Postgres service and unmount 
+## The following commands safely stop the Postgres service and unmount
 ## the data disk off the newly launched instance, to be re-attached to the
 ## source instance and run the upgrade there.
 
 set -euo pipefail
 
+if [[ $(systemctl is-active gotrue) == "active" ]]; then
+    echo "stopping gotrue"
+    systemctl stop gotrue || true
+fi
+
+if [[ $(systemctl is-active postgrest) == "active" ]]; then
+    echo "stopping postgrest"
+    systemctl stop postgrest || true
+fi
+
 systemctl stop postgresql
-umount /data
 
+cp /etc/postgresql-custom/pgsodium_root.key /data/pgsodium_root.key
+umount /data
@@ -1 +1 @@
-postgres-version = "15.1.0.27"
+postgres-version = "15.1.0.28"
Original file line number	Diff line number	Diff line change
		@@ -1 +1 @@
		postgres-version = "15.1.0.27"
		postgres-version = "15.1.0.28"