Skip to main content

A Shell Script Wrapper for pg_dump

Published: 2021-02-09 • Last updated: 2021-05-03

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"