Bash scripts for extracting individual databases from a sql file dumped using pg_dumpall

pgx.rst

Postgresql Xtra commands

• pgx_list_dbs: Lists the names of databases in an .sql file dumped using pg_dumpall.
• pgx_extract_db: Extracts a single database from a sql file dumped with pg_dumpall and outputs its content to stdout.

Installation

Download the (re)installer by doing:

sudo wget -O /usr/local/bin/pgx_installer \
     https://gist.github.com/runekaagaard/7924963/raw/pgx_installer \
     && sudo chmod +x /usr/local/bin/pgx_installer 

Then run it with:

pgx_installer

Acknowledgements

Based on:

http://madssj.com/blog/2010/04/09/extracting-a-single-database-from-a-pg_dumpall-postgresql-dump/

pgx_extract_db

#!/bin/bash

if [ $# -ne 2 ]; then
cat << EOF
usage: $0 [FILE]... [DBNAME]...
Extracts a single database from a sql file dumped with pg_dumpall and outputs
its content to stdout.
EOF
    exit 14
fi
 
db_file="$1"
db_name="$2"
 
if [ ! -f "$db_file" -o ! -r "$db_file" ]; then
    echo "error: $db_file not found or not readable" >&2
    exit 14
fi
 
while read line; do
    bytes=$(echo $line | cut -d: -f1)
 
    if [ -z "$start_point" ]; then
        start_point=$bytes
    else
        end_point=$bytes
    fi
done < <(grep -b '^\\\connect' "$db_file" | grep -m 1 -A 1 "$db_name$")

if [ -n "$start_point" -a -z "$end_point" ]; then
    end_point=`wc -c < $db_file`
fi

if [ -z "$start_point" -o -z "$end_point" ]; then
    echo "error: start or end not found" >&2
    exit 14
fi

db_length=$(($end_point - $start_point))
tail -c +$start_point $db_file | head -c $db_length | tail -n +3 \
     | grep -v "OWNER TO $db_name" | grep -v -E "^(REVOKE|GRANT|--)"

pgx_installer

#!/bin/bash

files=(pgx_extract_db pgx_list_dbs)
deleted_files=(pgx_dumped_dbs)

for file in "${deleted_files[@]}"
do
   rm -f /usr/local/bin/$file
done

for file in "${files[@]}"
do
   rm -f /usr/local/bin/$file
   sudo wget -O /usr/local/bin/$file https://gist.github.com/runekaagaard/7924963/raw/$file
   sudo chmod +x /usr/local/bin/$file
done

sudo wget -O /usr/local/bin/pgx_installer https://gist.github.com/runekaagaard/7924963/raw/pgx_installer
sudo chmod +x /usr/local/bin/pgx_installer

pgx_list_dbs

#!/bin/bash

if [ $# -ne 1 ]; then
cat << EOF
usage: $0 [FILE]...
Lists the names of databases in an .sql file dumped using pg_dumpall.
EOF
    exit 14
fi

db_file="$1"

echo "The databases dumped in the file $db_file are:"
grep '^\\\connect' "$db_file" | sed 's/\\connect //g' | \
     grep -vE "postgres|template[0-9]" | sort -u | uniq