Copy RDS PG database without roles

aws_rds_copy.sh

#!/bin/bash

set -e
set -u

PS3='Migrate database for an environment. Please enter your choice: '
options=("dev" "uat" "prod" "Quit")
select opt in "${options[@]}"

do
  case $opt in
    "dev")
      ENV="dev"
      break;;
    "uat")
      ENV="uat"
      break;;
    "prod")
      ENV="prod"
      break;;
    "Quit")
      exit
      ;;
    *) echo "invalid option $REPLY";;
  esac
done

S1="${ENV}/aurora"
S2="database-${ENV}/platform"

S1_V=$(aws secretsmanager get-secret-value --secret-id $S1 --output json)
S2_V=$(aws secretsmanager get-secret-value --secret-id $S2 --output json)

DATE=$(date +"%m_%d_%Y")

echo "Current date: ${DATE}"

export PGHOST=$(jq -r '.SecretString | fromjson | .host' <<< "$S1_V")
export PGUSER=$(jq -r '.SecretString | fromjson | .username' <<< "$S1_V")
export PGPORT=$(jq -r '.SecretString | fromjson | .port' <<< "$S1_V")
export PGPASSWORD=$(jq -r '.SecretString | fromjson | .password' <<< "$S1_V")
export PGDATABASE=$(jq -r '.SecretString | fromjson | .database' <<< "$S1_V")

QUERY="SELECT COUNT(datname) FROM pg_database WHERE datname = '${PGDATABASE}';"
echo ${QUERY}
psql -c "${QUERY}"

# DUMP="${DATE}_db.dump"
DUMP="db.dump"

if [ -f "${DUMP}" ]; then
  echo "${DUMP} exists "
else
  pg_dump --no-owner --no-acl --no-privilates -Fc > ${DUMP}
fi

export PGHOST=$(jq -r '.SecretString | fromjson | .host' <<< "$S2_V")
export PGUSER=$(jq -r '.SecretString | fromjson | .username' <<< "$S2_V")
export PGPORT=$(jq -r '.SecretString | fromjson | .port' <<< "$S2_V")
export PGPASSWORD=$(jq -r '.SecretString | fromjson | .password' <<< "$S2_V")
export PGDATABASE=$(jq -r '.SecretString | fromjson | .dbname' <<< "$S2_V")

# RUN_PSQL="psql -X --set AUTOCOMMIT=off --set ON_ERROR_STOP=on"
QUERY="SELECT COUNT(datname) FROM pg_database WHERE datname = '${PGDATABASE}';"
echo ${QUERY}
psql -c "${QUERY}"

QUERY="""
CREATE EXTENSION IF NOT EXISTS plpgsql;
CREATE EXTENSION IF NOT EXISTS postgis;
CREATE EXTENSION IF NOT EXISTS pg_trgm;
"""

psql -c "${QUERY}"
pg_restore -xO --role ${PGUSER} -d ${PGDATABASE} ${DUMP}