Easily manipulate a Postgres backup file inside a Docker container.

README.md

pg_quick_recover

Ran into an issue where I needed to access a Postgres backup today. To my dismay I realized it was compressed and could only be accessed by importing it into a Postgres database. After fumbleing around for about 20 minutes setting one up, I decided I never wanted to do it again.

TL;DR

Put this in your PATH and use it (pg_quick_recover backup.sql.db) to quickly spin up a database with Docker, import the backup file, open up a shell to the databse, and then close it all up and clean up Docker whhen you are done.

Suggestions

If you are trying to recover data, one easy way is by setting the output format to CSV.

\pset format csv

pg_quick_recover.sh

# pg_quick_recover <path to recovery file>
# 
# email: [email protected]
# about: Recover a Postgres backup to a temp database with Docker and open
# a psql connection to it. Automatically cleans everything up when you exit 
# the psql REPL.
#

if [ -z "$1" ]; then
  echo 'Arguments: [path to recovery file]'
  exit
fi

# find the absolute path, docker needs this to link a single file
export ABS_PATH=$(python -c "import sys;import os;sys.stdout.write(os.path.realpath('$1'))")

# declare docker database defaults
export PGPORT="8839"
export PGPASSWORD=password

# setup a docker postgres container with a port that shouldn't collide with anything else
docker run -d --name pg_recovery -p $PGPORT:5432 -v "$ABS_PATH:/tmp/recovery" -e "POSTGRES_PASSWORD=$PGPASSWORD" postgres:13

# wait for container to get setup
until docker exec pg_recovery pg_isready -U postgres; do sleep 1; done

# connect to the recovery container and create a table
docker exec pg_recovery psql -U postgres -c 'CREATE DATABASE tmp_recovery;'

# restore the backup to the new table
docker exec pg_recovery pg_restore -d tmp_recovery -U postgres -v /tmp/recovery

# setup an interactive prompt to the database
docker exec -it pg_recovery psql -U postgres -d tmp_recovery

# clean up the docker instance
docker stop pg_recovery
docker rm pg_recovery