For dumping and loading cnxarchive database (See https://github.com/Connexions/cnx-archive/issues/197)

cnxarchive_db.sh

#!/bin/bash

DUMP_NAME=cnxarchive_dump
DUMP_TGZ=''
DUMP_SQL=cnxarchive_dump_without_files.sql
DUMP_INDEX_FILES=cnxarchive_index_files.txt
DUMP_INDEX_MFILES=cnxarchive_index_module_files.txt
PRIORITY=19

function nice_this {
    renice -n $PRIORITY $$
}

function load {
    if [ -z "$DUMP_TGZ" ]
    then
        DUMP_TGZ="${DUMP_NAME}.tgz"
    fi
    if [ ! -f "$DUMP_TGZ" ]
    then
        echo "$DUMP_TGZ (generated by dump) not found in the current directory"
        exit 1
    fi

    echo "Dropping database \"$DB_NAME\""
    sudo -u postgres dropdb $DB_NAME
    echo "Creating database \"$DB_NAME\""
    sudo -u postgres createdb -O $DB_USER $DB_NAME
    echo 'Loading database without files'
    tar xf "$DUMP_TGZ" --to-stdout $DUMP_SQL | psql -U $DB_USER $DB_NAME
    echo Loading files and module files table
    tar xf "$DUMP_TGZ" $DUMP_INDEX_FILES $DUMP_INDEX_MFILES
    cat <<EOF | psql -U $DB_USER $DB_NAME && rm $DUMP_INDEX_FILES
\\copy files FROM '$DUMP_INDEX_FILES'
EOF
    dummy_file_id=$(psql -A -U $DB_USER $DB_NAME -c "INSERT INTO files (file) VALUES ('dummy file') RETURNING fileid" | sed -n '2 p')
    sed -i "s/<dummy-file-id>/$dummy_file_id/g" $DUMP_INDEX_MFILES
    cat <<EOF | psql -U $DB_USER $DB_NAME && rm $DUMP_INDEX_MFILES
ALTER TABLE module_files DISABLE TRIGGER ALL;
\\copy module_files FROM '$DUMP_INDEX_MFILES'
ALTER TABLE module_files ENABLE TRIGGER ALL;
EOF
    echo Done
}

function dump {
    echo Dumping database without files
    pg_dump -U $DB_USER --exclude-table-data=files --exclude-table-data=module_files $DB_NAME >$DUMP_SQL
    echo Dumping database module files and files table for index.cnxml, index.cnxml.html, index_auto_generated.cnxml
    cat <<EOF | psql -U $DB_USER $DB_NAME
\\copy ( SELECT * FROM files WHERE fileid IN (SELECT fileid FROM module_files WHERE filename IN ('index.cnxml', 'index.cnxml.html', 'index_auto_generated.cnxml') ) ) TO '$DUMP_INDEX_FILES'
\\copy ( SELECT module_ident, CASE WHEN filename IN ('index.cnxml', 'index.cnxml.html', 'index_auto_generated.cnxml') THEN fileid::text ELSE '<dummy-file-id>' END, filename, mimetype FROM module_files ) TO '$DUMP_INDEX_MFILES'
EOF

    if [ -z "$DUMP_TGZ" ]
    then
        DUMP_TGZ="${DUMP_NAME}.$(date +'%Y-%m-%d').tgz"
    fi
    echo "Creating \"$DUMP_TGZ\" (this may take 10+ minutes)"
    tar czf "$DUMP_TGZ" --remove-files $DUMP_SQL $DUMP_INDEX_FILES $DUMP_INDEX_MFILES $DUMP_INDEX_MFILES_OTHER
    echo Done
}

function usage {
    echo "Usage: $0 [-o output_filename] [-i input_filename] [-n priority] [-h] [load|dump] db_user db_name"
    echo
    echo "  -o output_filename, default ${DUMP_NAME}.YYYY-MM-DD.tgz"
    echo "  -i input_filename, default ${DUMP_NAME}.tgz"
    echo "  -n prority, default 19 (see \"man nice\" and \"man renice\")"
    echo "  -h show this help"
    exit 1
}

# main

while getopts :o:i:n:h FLAG
do
    case $FLAG in
        o)
            DUMP_TGZ="$OPTARG"
            ;;
        i)
            DUMP_TGZ="$OPTARG"
            ;;
        n)
            PRIORITY="$OPTARG"
            ;;
        h)
            usage
            ;;
        *)
            echo "illegal option -- $OPTARG"
            usage
            ;;
    esac
done

shift $((OPTIND-1))

if [ "$#" -ne 3 -o "$1" != "load" -a "$1" != "dump" ]
then
    usage
fi

ACTION=$1
DB_USER=$2
DB_NAME=$3

nice_this

if [ "$ACTION" == "load" ]
then
    load
elif [ "$ACTION" == "dump" ]
then
    dump
else
    usage
fi