A simple script for Postgres database export and import
Tags: bash, postgres
Published: 2019-10-17 18:13:05

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](https://www.postgresql.org/docs/11/libpq-envars.html) to see Postgres environment variables and check [pg_dum docs](https://www.postgresql.org/docs/11/app-pgdump.html) to understand.

![usage](https://thepracticaldev.s3.amazonaws.com/i/iqko0im3yh35y81k0ey9.png)

```bash
# 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
```bash
#!/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
```

should provide an executive privilege
```bash
chmod +x pg_helper.sh
```

Hope it will help you :)