SaFi Bank Space : DB migration process

In case there is need to migrate DBs from one server to another on SaFi project.

Prerequisites:

The DB - PostgreSQL. The DB-instance, databases on it, DB-users and credentials for them are created with terraform
The applications that read/write to these DBs run in k8s.

Stop the applications in k8s cluster.
Remove google_sql_database_instance, random_pet, all google_sql_database, google_sql_user, random_password resources instances from terraform state file
Store secrets from HCV for this DBs (secret/%env%/apps/%app-name%/postgres) and dump the databases with pg_dump
Run terraform plan - it will show that new DB instance with all DBs users and creds will be recreated/updated => terraform apply
Get new credentials from HCV and import the dumps from step 4 to new DBs with pg_restore
Check that all data was correctly restored into new DB
Start/redeploy the applications in k8s cluster

Example of script to dump/restore the DBs from old server to new one for dev environment:

Click here to expand...

#!/bin/bash

echo "This script will make pg_dump of microservices from the list
It requires to be installed:
- Hashicorp vault CLI - to get secrets (passwords for DBs)
- shyaml - to parse yaml-files - https://pypi.org/project/shyaml/
- psql (pg_dump, pg_restore)
#############################################"

for tool in vault shyaml ; do
  if ! [[ $($tool --version) ]]; then
    echo "Error: '$tool' tool wasn't found as installed in you system"
    return 1
  fi
done

echo "Don't forget to export 'VAULT_TOKEN' env before proceed!"
echo "#############################################"
echo "Enter vault server address or press 'Enter' to use default [https://vault.cicd.safibank.online]:"
read vault_server_addr
vault_server_addr=${vault_server_addr:-"https://vault.cicd.safibank.online"}
export VAULT_ADDR=$vault_server_addr

if ! [[ $(vault token lookup) ]] ; then
  echo "Error: vault is not reachable! Did you add correct vault-server address and valid VAULT_TOKEN env?"
  return 1
fi

echo "Your system looks ready. Proceed?"
read answer
if [[ $answer =~ ^(n|no)$ ]] ; then
  return 0
fi

echo "Enter path to microservices.yaml: "
read path_to_microservices_yaml
if ! [[ -f "$path_to_microservices_yaml" ]] ; then
  echo "Error: file '$path_to_microservices_yaml' not found!"
  return 1
fi
ms_list=$(cat $path_to_microservices_yaml | shyaml keys microservices)
echo "List of all found microservices:"
echo $ms_list
echo "#############################################"

echo "Enter OLD PostgreSQL-server public address [34.142.193.99]: "
read psql_addr
echo "#############################################"

echo "Creating 'psql_dumps' folder..."
rm -rf psql_dumps
mkdir -p psql_dumps
echo "#############################################"

echo "MAKING DUMPS of all DBs"
echo $ms_list | while IFS= read -r line ; do
  if [[ $(vault kv get -mount=secret dev/apps/$(echo $line | tr "_" "-")/postgres 2> /dev/null) ]] ; then
    echo ""
    echo "Working with '$line' ms..."
    echo "Getting credentials..."
    username=$(vault kv get -field=user -mount=secret dev/apps/$(echo $line | tr "_" "-")/postgres)
    password=$(vault kv get -field=password -mount=secret dev/apps/$(echo $line | tr "_" "-")/postgres)

    echo "Starting making dump of the DB"
    export PGPASSWORD=$password
    pg_dump \
    --host $psql_addr -p 5432 \
    --dbname $username-dev \
    --username $username \
    --file psql_dumps/$username-dev.dump \
    --no-owner \
    --no-privileges \
    --jobs=8  \
    --format=directory \
    --verbose
    echo "Finished with $username-dev DB"
  fi
done
echo "Finished dumping all DBs"
echo "#############################################"

echo "RESTORING DBs from dumps to new DB-server"
echo "Enter NEW PostgreSQL-server public address:"
read new_psql_addr

echo "Ready to proceed?"
read answr
if [[ $answr =~ ^(n|no)$ ]] ; then
  return 0
fi

echo "#############################################"
echo $ms_list | while IFS= read -r line ; do
  if [[ $(vault kv get -mount=secret dev/apps/$(echo $line | tr "_" "-")/postgres 2> /dev/null) ]] ; then
    echo ""
    echo "Working with '$line' ms..."
    echo "Getting credentials..."
    username=$(vault kv get -field=user -mount=secret dev/apps/$(echo $line | tr "_" "-")/postgres)
    password=$(vault kv get -field=password -mount=secret dev/apps/$(echo $line | tr "_" "-")/postgres)

    echo ""
    echo "Ready to restore '$line' ms DB. Proceed?"
    read ans4
    if [[ $ans4 =~ ^(n|no)$ ]] ; then
      return 0
    fi

    echo "Starting restoring the backup"
    export PGPASSWORD=$password
    pg_restore \
    --host $new_psql_addr -p 5432 \
    --dbname $username-dev  \
    --username $username \
    --no-owner \
    --no-privileges \
    psql_dumps/$username-dev.dump \
    --jobs=8 \
    --format=directory \
    --verbose
    echo "Finished restoring $username-dev DB"
    echo "#############################################"
  fi
done
echo "Dump & Restore process finished"