Last active
December 23, 2024 17:44
-
-
Save patgmiller/492cb43bedcad2872fd670ed4f8921bd to your computer and use it in GitHub Desktop.
postgresql dump and restore script
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| #!/usr/bin/env bash | |
| set -euo pipefail | |
| SCRIPT=$(basename "$0") | |
| FILE= | |
| DEBUG=0 | |
| DRYRUN=0 | |
| VERBOSE=0 | |
| declare {HOST,DST_HOST}=localhost | |
| declare {DBASE,DST_DBASE}= | |
| declare {USER,DST_USER}= | |
| declare {PORT,DST_PORT}=5432 | |
| PSQLEXT=( ) # psql extensions array | |
| BKPARGS=(-O -x --quote-all-identifiers) # pg_dump export arguments | |
| DSTARGS=(-O -x) # pg_restore arguments | |
| function usage() { | |
| printf "Export / Dump a source postgres database and optionally import / restore the output into another destination database. Accepts positional arguments to limit which tables are included. | |
| Usage %s -f FILE [-u user] [-u database] [-U dst-user] [-D dst-database] [table [tables...]] | |
| Source Database | |
| -h, --host HOST [default: localhost] Connection host for source database | |
| -d, --database DB Source database to export from | |
| -u, --user USER User to connect with for source database | |
| -p, --port PORT [default: 5432] Connection port on source database | |
| -w, --password Prompt for password when connecting to source database | |
| Destination Database | |
| -H, --dst-host HOST [default: localhost] Connection host for destination database | |
| -D, --dst-database DB Destination database to import into | |
| -U, --dst-user USER User to connect with for destination database | |
| -P, --dst-port PORT [default: 5432] Connection port on destination database | |
| -W, --dst-password Prompt for password when connecting to destination database | |
| Backup Options | |
| -f, --file FILE [REQUIRED] Output file or directory location for database backup | |
| -j, --jobs JOBS Set number of parallel jobs to run in postgres for backup | |
| Restore Options | |
| -c, --clean Clean (drop) database objects before recreating | |
| -J, --dst-jobs JOBS Set number of parallel jobs to run in postgres for restore | |
| Extensions | |
| -e, --extensions E1,E2 Comma separated list of postsgres extensions to create in new database | |
| Global options | |
| -v, --verbose Print commands that are to be executed | |
| -r, --dry-run Only show actions that would be applied, DO NOT execute commands | |
| -x, --debug Enable shell debugging | |
| -?, --help display this help message | |
| " "$SCRIPT" | |
| } | |
| function printUsageExit() { | |
| printf '%s\n\n' "$1" | |
| usage && exit 1 | |
| } | |
| function cleanDropOnRestore() { | |
| DSTARGS=( "${DSTARGS[@]}" "-c" ) | |
| } | |
| function readPsqlExtensions() { | |
| local ext="$1" | |
| if ! IFS="," read -r -a PSQLEXT <<< "$ext"; then | |
| printUsageExit 'Error while reading list of postgres sql extentions' | |
| fi | |
| } | |
| function parallelJobs() { | |
| # set # of parallel jobs for backup / restore | |
| local target="$1" jobs="$2" | |
| # only set if $OPTARG > 0 | |
| if [[ $jobs -gt 0 ]]; then | |
| if [[ "$target" -eq 1 ]]; then | |
| BKPARGS=( "-j${jobs}" "${BKPARGS[@]//-j[0-9]}" ) | |
| elif [[ "$target" -eq 2 ]]; then | |
| DSTARGS=( "-j${jobs}" "${DSTARGS[@]//-j[0-9]}" ) | |
| fi | |
| fi | |
| } | |
| function passwordPrompt() { | |
| if [[ "$1" -eq 1 ]]; then | |
| # prompt for pass during step 1 | |
| BKPARGS=( "${BKPARGS[@]}" "-W" ) | |
| elif [[ "$1" -eq 2 ]]; then | |
| # prompt for pass during step 2 | |
| DSTARGS=( "${DSTARGS[@]}" "-W" ) | |
| fi | |
| } | |
| while getopts ":d:D:h:H:j:J:p:P:u:U:-:e:f:cxrvwW" opt | |
| do | |
| case $opt in | |
| -) | |
| case "${OPTARG}" in | |
| clean) cleanDropOnRestore ;; | |
| database) | |
| DBASE="${!OPTIND}" | |
| shift 1 | |
| ;; | |
| dst-database) | |
| DST_DBASE="${!OPTIND}" | |
| shift 1 | |
| ;; | |
| host) | |
| HOST="${!OPTIND}" | |
| shift 1 | |
| ;; | |
| dst-host) | |
| DST_HOST="${!OPTIND}" | |
| shift 1 | |
| ;; | |
| port) | |
| PORT="${!OPTIND}" | |
| shift 1 | |
| ;; | |
| dst-port) | |
| DST_PORT="${!OPTIND}" | |
| shift 1 | |
| ;; | |
| extensions) | |
| readPsqlExtensions "${!OPTIND}" | |
| shift 1 | |
| ;; | |
| file) | |
| FILE="${!OPTIND}" | |
| shift 1 | |
| ;; | |
| jobs) | |
| parallelJobs 1 "${!OPTIND}" | |
| shift 1 | |
| ;; | |
| dst-jobs) | |
| parallelJobs 2 "${!OPTIND}" | |
| shift 1 | |
| ;; | |
| password) passwordPrompt 1 ;; | |
| dst-password) passwordPrompt 2 ;; | |
| dry-run) DRYRUN=1 ;; | |
| verbose) VERBOSE=1 ;; | |
| debug) DEBUG=1 ;; | |
| help) usage && exit 1 ;; | |
| *) | |
| if [ "$OPTERR" = 1 ] && [ "${OPTSPEC:0:1}" != ":" ]; then | |
| echo "Unknown option --${OPTARG}" >&2 | |
| fi | |
| ;; | |
| esac;; | |
| c) cleanDropOnRestore ;; | |
| d) DBASE="${OPTARG}" ;; | |
| D) DST_DBASE="${OPTARG}" ;; | |
| h) HOST="${OPTARG}" ;; | |
| H) DST_HOST="${OPTARG}" ;; | |
| j) parallelJobs 1 "$OPTARG" ;; | |
| J) parallelJobs 2 "$OPTARG" ;; | |
| p) PORT="${OPTARG}" ;; | |
| P) DST_PORT="${OPTARG}" ;; | |
| u) USER="${OPTARG}" ;; | |
| U) DST_USER="${OPTARG}" ;; | |
| w) passwordPrompt 1 ;; | |
| W) passwordPrompt 2 ;; | |
| f) FILE="${OPTARG}" ;; | |
| e) readPsqlExtensions "${OPTARG}" ;; | |
| r) DRYRUN=1 ;; | |
| v) VERBOSE=1 ;; | |
| x) DEBUG=1 ;; | |
| ?|*) usage && exit 1 ;; esac | |
| done | |
| shift $((OPTIND - 1)) | |
| [[ $DEBUG -eq 1 ]] && set -x | |
| if [[ -z "${FILE}" ]]; then | |
| printUsageExit 'Missing required backup location: -f FILE' | |
| fi | |
| # backup | |
| if [ -n "${DBASE}" ] && [ -n "${USER}" ]; then | |
| if [[ -d "${FILE}" ]]; then | |
| rm -rf "${FILE}" # remove existing back up director | |
| fi | |
| mkdir -p "${FILE}" # make sure location exists | |
| TABLES=( $@ ) | |
| # business end ;D | |
| PSQL="pg_dump -h ${HOST} -p ${PORT} -U ${USER} ${DBASE} -Fd ${BKPARGS[*]} -f ${FILE}" | |
| TBLCMD="" | |
| for table in "${TABLES[@]}"; do | |
| TBLCMD+="-t ${table} " | |
| done | |
| [ $VERBOSE -eq 1 ] && echo "${PSQL[*]} ${TBLCMD[*]}" | |
| [ $DRYRUN -eq 0 ] && (eval "${PSQL[@]}" "${TBLCMD[@]}" || exit $?) | |
| fi | |
| # restore | |
| if [ -n "${DST_DBASE}" ] && [ -n "${DST_USER}" ]; then | |
| # create extensions | |
| if [[ ${#PSQLEXT[@]} -gt 0 ]]; then | |
| for ext in "${PSQLEXT[@]}"; do | |
| PSQL="psql -h ${DST_HOST} -p ${DST_PORT} -U ${DST_USER} ${DST_DBASE} -c 'create extension if not exists \"$ext\";'" | |
| [ $VERBOSE -eq 1 ] && echo "${PSQL[*]}" | |
| [ $DRYRUN -eq 0 ] && (eval "${PSQL[@]}" || exit $?) | |
| done | |
| fi | |
| # business end ;D | |
| PSQL="pg_restore -h ${DST_HOST} -p ${DST_PORT} -U ${DST_USER} -d ${DST_DBASE} ${DSTARGS[*]} ${FILE}" | |
| [ $VERBOSE -eq 1 ] && echo "${PSQL[*]}" | |
| [ $DRYRUN -eq 0 ] && (eval "${PSQL[@]}" || exit $?) | |
| fi |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment