bash script allowing to synchronize a postgres database with a backup available remotely via curl at backup_url

sync_db.sh

#!/bin/bash
######################################################################################################
### SCRIPT  : sync_db.sh
### AUTHOR  : CGIL <cgil@lausanne.ch>
### DATE    : 2023-05-10
### DESCRIPTION : this script allows to synchronize the database with a backup available in backup_url
### expects one parameter with the name of the database you want to synchronize
### the current dataabse is not renamed to old if the new one does not contain MIN_NUM_TABLES
######################################################################################################
if [ $# -eq 0 ]
  then
    echo "Expecting the first parameter with the name of the database to synchronize"
    exit 1
fi
DATABASE=$1
DATABASE_NEW="${DATABASE}_new"
LOG="/root/log/sync_${DATABASE}.log"
TODAY=$(date +%F)
TIME_START=$(date)
MIN_NUM_TABLES=100
BACKUP="DBBackup-${DATABASE}-${TODAY}.bak"
BACKUP_URL="https://yourserver.yourdomain.com/db_dump/backup-${DATABASE}.bak"
TEMP_PATH="/tmp/sync_${DATABASE}"
if [ ! -d "${TEMP_PATH}" ]
then
  mkdir -p "${TEMP_PATH}"
fi
echo "***** about to retrieve backup from: ${BACKUP_URL}  and store it in :  ${TEMP_PATH}/"  >> "${LOG}" 2>&1
cd "${TEMP_PATH}" || exit
curl "${BACKUP_URL}" -o "$BACKUP"
chown -R postgres:users "${TEMP_PATH}"
echo "*******************************************************************************************"  >> "${LOG}" 2>&1
echo "******* BEGIN SYNC OF ${DATABASE} at time :${TIME_START}"  >> "${LOG}" 2>&1
cd "${TEMP_PATH}" || exit
echo "*** ABOUT TO CREATE BRAND NEW ${DATABASE_NEW} DB"  >> "${LOG}" 2>&1
su -c "dropdb --if-exists ${DATABASE_NEW}" postgres  >> "${LOG}" 2>&1
su -c "createdb --owner postgres --template template0 --encoding UTF8 --lc-collate fr_CH.UTF-8 --lc-ctype fr_CH.UTF-8 ${DATABASE_NEW}" postgres
echo "*** ABOUT TO ADD EXTENSIONS TO ${DATABASE_NEW} DB"  >> "${LOG}" 2>&1
su -c "psql -c 'CREATE EXTENSION postgis;' ${DATABASE_NEW}" postgres > /dev/null
echo "*** ABOUT TO GET LIST OF CONTENTS OF  ${BACKUP} "  >> "${LOG}" 2>&1
su -c "pg_restore -l  ${TEMP_PATH}/${BACKUP} >DBBackup-${DATABASE}.txt" postgres
echo "*** BEGIN RESTORE OF BACKUP FROM ${BACKUP} "  >> "${LOG}" 2>&1
su -c "pg_restore -j 4 --no-owner --no-privileges -d ${DATABASE_NEW} -L DBBackup-${DATABASE}.txt  ${TEMP_PATH}/${BACKUP}" postgres >> "${LOG}" 2>&1
DURATION=$(( SECONDS ))
TIME_END=$(date)
echo "*** END RESTORE AFTER ${DURATION} seconds OF BACKUP FROM ${BACKUP} at time :${TIME_END}"  >> "${LOG}" 2>&1
echo "*** REMOVING BACKUP ${BACKUP}"  >> "${LOG}" 2>&1
rm -f "${TEMP_PATH}/${BACKUP}"
TABLE_COUNT=$(su -c "psql -AXqtc 'select count(*) from information_schema.tables;' ${DATABASE_NEW}" postgres)
if [[ ${TABLE_COUNT} -gt ${MIN_NUM_TABLES} ]]; then
  echo "*** FOUND ${TABLE_COUNT} tables in DB ${DATABASE_NEW}, assuming sync went well"
  echo "*** STOPPING SERVICE CONNECTED TO DB **" >> "${LOG}" 2>&1
  sudo systemctl stop apache2.service
  echo "*** SWAPPING DB OLD AND NEW"  >> "${LOG}" 2>&1
  su -c "dropdb --if-exists ${DATABASE}_old" postgres  >> "${LOG}" 2>&1
  SWAP_PROD_TO_OLD="ALTER DATABASE ${DATABASE} RENAME TO ${DATABASE}_old;"
  su -c "psql -c '${SWAP_PROD_TO_OLD}' postgres" postgres >> "${LOG}" 2>&1
  SWAP_NEW_TO_PROD="ALTER DATABASE ${DATABASE_NEW} RENAME TO ${DATABASE};"
  su -c "psql -c '${SWAP_NEW_TO_PROD}' postgres" postgres >> "${LOG}" 2>&1
  echo "** RE-STARTING SERVICE CONNECTED TO DB **" >> "${LOG}" 2>&1
  sudo systemctl start apache2.service
else
  echo "nope ${TABLE_COUNT} is not enough will not do the NEW/OLD SWAP"
fi
TIME_END=$(date)
DURATION=$(( SECONDS ))
echo "******* END SYNC OF ${DATABASE} AFTER ${DURATION} seconds at time :${TIME_END}"  >> "${LOG}" 2>&1
echo "*******************************************************************************************"  >> "${LOG}" 2>&1

obviously you need to adjust the script to your needs
may be the line 53 and 61 depending on what service is connected to your local database