Dump tables and schema from a PostgreSQL database (nicely).

psqlDump.sh

#!/usr/bin/env zsh -e
# Thu Dec 12 00:04:47 CET 2013
# 2013 (c) [email protected]

# Dump tables and schema from a PostgreSQL database.
# Usage: $0 [database_name] [num_records]

d=${1:=$USER} # database, defaults to current username
n=${2:=1000} # number of records to export, defaults to 1000

tmp=$(mktemp -d -t ${0:t:r}_$d)
trap "{ cd - ; rm -rf $tmp; exit 255; }" SIGINT

a() { # get ugly psql table_name output from information_schema
  psql -c "SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';" $d
}

c=$((2 + $(a | egrep -o '[0-9]+'))) # table count
t=($(a | sed -n "s/^ //; 3,${c}p")) # tables

echo

for i in $t; do
  psql -qec "COPY (SELECT * FROM $i LIMIT ${n}) TO '${tmp}/${i}_${n}.sql';" $d |\
  sed "s,${tmp}/,,"
done

pg_dump -s -Ft $d > $tmp/${d}_schema.tar

echo

cd $tmp
tar cvjf $OLDPWD/${d}_sql_dumps_${n}.tar.bz2 *sql *schema*
cd -

echo "\n\e[0;31m>> \e[0;34m$(du -sh ./${d}_sql_dumps_${n}.tar.bz2)\e[0;0m" # :D

rm -rf $tmp
exit 0