mongo2rds.sh

#!/bin/bash

# Copies an entire mongodb collection into an RDS table, skipping existing rows.

MONGOHOST=""
RDS=""
TABLE=""
DB="cureatr"
COLLECTION=""

usage () { echo "$0 [-h] -r <RDS url> -t <RDS table> -m <mongo host> -d <mongo database> -c <mongo collection>"; }

OPTIND=1
while getopts "h?r:t:m:d:c:" opt; do
    case "$opt" in
    h|\?)
        usage
        exit 0
        ;;
    r)  RDS=$OPTARG;;
    t)  TABLE=$OPTARG;;
    m)  MONGOHOST=$OPTARG;;
    d)  DB=$OPTARG;;
    c)  COLLECTION=$OPTARG;;
    esac
done

shift $((OPTIND-1))

if [ ! "$MONGOHOST " ] || [ ! "$RDS " ] || [ ! "$TABLE" ] || [ ! "$DB" ] || [ ! "$COLLECTION" ]; then
    usage
    exit 1
fi

mongoexport --quiet --host ${MONGOHOST} --db ${DB} --collection ${COLLECTION} --ssl --sslAllowInvalidCertificates | \
psql --single-transaction \
    -c "CREATE TEMPORARY TABLE \"#${TABLE}\"(document jsonb);" \
    -c "COPY \"#${TABLE}\"(document) FROM stdin csv QUOTE e'\x01' DELIMITER e'\x02' ENCODING 'utf-8';" \
    -c "CREATE TABLE IF NOT EXISTS \"${TABLE}\"(_id CHAR(24) PRIMARY KEY, document JSONB);" \
    -c "INSERT INTO \"${TABLE}\" (_id, document) SELECT document#>>'{_id,\$oid}',document FROM \"#${TABLE}\" ON CONFLICT DO NOTHING;" \
    -c "DROP TABLE \"#${TABLE}\";" \
    "${RDS}"