Mysql to Postgres SQL file conversion

mysql-to-postgres.sh

# Adapted from github comment:
# https://github.com/dimitri/pgloader/issues/782#issuecomment-1136067634

if [ -z "$3" ]; then
    echo "Usage: $0 <db name> <mysql dump> <psql dump output>"
    echo "Requirements: docker"
    exit 1
fi

if ! command -v docker &> /dev/null
then
    echo "docker could not be found"
    exit
fi

set +e
docker stop mysql1 psql1 &> /dev/null
set -e

DB_NAME=$1
SOURCE_DUMP=$2
TARGET_DUMP=$3

echo "DB_NAME=$DB_NAME"
echo "SOURCE_DUMP=$SOURCE_DUMP"
echo "TARGET_DUMP=$TARGET_DUMP"

echo "Starting MYSQL"
# Prepare MySQL
docker run --rm \
  --name mysql1 \
  -e MYSQL_ROOT_PASSWORD=pass \
  -p 3300:3306 \
  -d mysql \
  --default-authentication-plugin=mysql_native_password

echo "Waiting for MySQL port"
docker run willwill/wait-for-it -t 10 172.17.0.1:3300 # wait DB to start

echo "Waiting for MySQL to be ready"
while true; do
  set +e
  sleep 1;
  # Wait for two matches of waiting for connections since the first one is a
  # temporary server
  count=`docker logs mysql1 2>&1 | grep -E 'mysqld: ready for connections' -c`
  if [ "$count" -eq 2 ]; then
    set -e
      break
  fi
done

echo "Importing MySQL dump"
docker exec -i mysql1 mysql -ppass < $SOURCE_DUMP

echo "Starting PostgreSQL"
# Prepare Postgres
docker run --rm --name psql1 -e POSTGRES_PASSWORD=pass -p 5500:5432 -d postgres
sleep 3 # wait DB to start
docker exec -e POSTGRES_PASSWORD=pass psql1 createdb -U postgres $DB_NAME

echo "Loading Mysql to Postgres"
# Run pgloader
docker run dimitri/pgloader \
  pgloader mysql://root:[email protected]:3300/$DB_NAME \
  postgresql://postgres:[email protected]:5500/$DB_NAME

echo "Exporting Postgres dump"
# Dump Postgres DB
docker exec -i \
  -e POSTGRES_PASSWORD=pass \
  psql1 pg_dump -U postgres --no-privileges --no-owner $DB_NAME > $TARGET_DUMP

echo "Adding schema rename to dump"
echo "BEGIN TRANSACTION;
  ALTER SCHEMA public          RENAME TO public_original;
  ALTER SCHEMA $DB_NAME        RENAME TO public;
  DROP SCHEMA  public_original CASCADE;
COMMIT;" >> $TARGET_DUMP

docker stop mysql1 psql1