If you have huge plain text dumps, and just want to restore one table
it's usually painful. Attached is a small shell script that can take a
plain text dump and extract a single table's COPY data commands from it.
If people think it's interesting and should be developed, I can pop it
on pgfoundry or something.
Chris
#!/bin/sh
# This script extracts a single table from a PostgreSQL pg_dumpall plain
# text dump. This is useful for restoring just one table.
#
# Usage: restore.sh <backup bzip> <table name>
# Check that arguments are given
if [ "$1" = "" -o "$2" = "" ]; then
echo "Error: arguments not given"
exit
fi
# Check that we're not going to clobber existing files
if [ -e working.sql -o -e working.sql-e -o -e "$2.sql" ]; then
echo "Error: working files already exist"
exit
fi
# Extract the backup to a working SQL script
bunzip2 < $1 > working.sql
# Find the line before the table's COPY output begins
START=`grep -n "^COPY $2 " working.sql | sed -e 's/:.*//'`
START=$(($START-1))
# Remove all of the working file before the COPY
sed -i -e 1,${START}d working.sql
# Find line number at which COPY ends
END=`grep -n "^\\\\\\." working.sql | head -1 | sed -e 's/:.*//'`
END=$(($END+1))
# Remove all contents of the working file after the end of the COPY
sed -i -e $END,\$d working.sql
# Rename the working file to the table name
mv working.sql "$2.sql"
# Remove sed temporary file
rm working.sql-e
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
