A simple script for Postgres database export and import

#bash #postgres

I write a simple script that aims to help me work.

Usage

You can put this script to your work directory and export the Postgres data in the same directory. Consider your situation and change these options.

You can check Postgres docs to see Postgres environment variables and check pg_dum docs to understand.

# go to your work directory
cd your-work-dir

# export Postgres database
./pg_helper.sh -o

# import to other Postgres database
./pg_helper.sh -i

pg_helper.sh

#!/usr/bin/env bash
#==============================
# An script help postgres database impoort and exoprt
#==============================

# an error occurred will stop script.
set -e

# an undefined parameter will throw error message.
set -u

# an pipeline failed will stop script.
set -o pipefail

#==============================
# Parameters
# You should change parameters for your Postgres connection.
#==============================
HOST='localhost'
DATABASE='database'
USER='database user'
PASSWD='database password'
TAR_FILE='tar file name'

#==============================
# Ansi colors
#==============================
RED='\033[0;31m'
BLUE='\033[0;34m'
YELLOW='\033[0;33m'
NCOLOR='\033[0m'

#==============================
# Message functions
#==============================
die() {
    echo -e "${RED}ERROR:${NCOLOR} $1" 1>&2
    exit 1
}

usage() {
    echo -e "An script help postgres database impoort and exoprt"
    echo -e "usage: `basename $0` [options]"
    echo ""
    echo -e "options:"
    echo -e "   ${YELLOW}-i${NCOLOR}     import .tar file;    usage: ${YELLOW}./`basename $0` -i${NCOLOR}"
    echo -e "   ${YELLOW}-o${NCOLOR}     export to .tar;      usage: ${YELLOW}./`basename $0` -o${NCOLOR}"
    echo -e "   ${YELLOW}-h${NCOLOR}     help;                usage: ${YELLOW}./`basename $0` -h${NCOLOR}"
    echo ""
    exit $1
}

confirm() {
    local msg
    if [[ -z $1 ]]; then
        msg="Are you sure?"
    else
        msg=$1
    fi

    # call with a prompt string or use a default
    read -r -p "${msg} [y/N/q] " response
    case ${response} in
        [yY][eE][sS]|[yY])
            true
            ;;
        [qQ][uU][iI][tT]|[qQ])
            exit
            ;;
        *)
            false
            ;;
    esac
}

import_data() {
    local file=${TAR_FILE}
    if [[ ! -f ${file} ]]; then
        die "The file ${file} not existed."
    fi

    echo -e "${RED}This option will clean your database ${DATABSE}.${NCOLOR}"
    if confirm ""; then
        pg_restore -d${DATABASE} -U${USER} -c --if-exists -vvv -e ${file}
    fi
}

export_data() {
    local file=${TAR_FILE}
    if [[ -f ${file} ]]; then
        echo -e "rm -f ${file}"
        rm -f ${file}
        pg_dump -U${USER} -Ft ${DATABASE} -vvv > ${file}
    fi
}

#==============================
# Handled user options
#==============================
while getopts "ioh?" opt; do
    case "$opt" in
        "i") import_data ;;
        "o") export_data ;;
        "h") usage 1;;
        "?") usage 1;;
        *) die "Unknown options.";;
    esac
done