Skip to main content

A Shell Script Wrapper for pg_dump

Published: 2021-02-09

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


# Shell Script

Name the file

#!/usr/bin/env bash

set -o allexport;
source .env;
set +o allexport;

pg_dump \
--inserts \
--column-inserts \
--rows-per-insert=1000 \

echo "Completed in ${SECONDS}s";