A Shell Script Wrapper for pg_dump

pg_dump is a sweet tool to extract a database to a file (tables, functions, triggers… I mean everything). It has many CLI options, and I’m documenting the ones that were most useful to me here.

The shell script reads in a .env file with database connection info, then exports each env var by using the bash set builtin.

Next, pg_dump is called with the following options:

• --inserts Dump data as INSERT commands (rather than COPY)
• --column-inserts Dump data as INSERT commands with explicit column names
• --rows-per-insertControls the maximum number of rows per INSERT command. Good for reducing network round trips
• --file Send output to the specified file

Secrets #

Name the file .env

PGHOST=localhost
PGPORT=5432
PGDATABASE=some_database
PGUSER=postgres
PGPASSWORD=postgres

Shell Script #

Name the file dump.sh

#!/usr/bin/env bash

set -o allexport
source .env
set +o allexport

pg_dump \
--inserts \
--column-inserts \
--rows-per-insert=1000 \
--file=dump.sql

echo "Completed in ${SECONDS}s"