Re: Reload only specific databases from pg_dumpall

On 05/02/2008, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:
And a more generic version :}

--------8<--------8<--------8<--------8<--------8<--------8<--------8<--------8<--------
#!/bin/bash
# split.sh: a shell script and wrapper for some (g)awk to extract a single
# database out of a dumpall file - a quick and ugly hack, as
# usual no other warranty as that it worked with my test-case. :}
#
# It relies on GNU awk > 3, may work with nawk/mawk, but I didn't test that.
# The old traditional awk as shipped with Solaris as the default will most
# definitely barf on this one.
#
# The reason for the writing out of the awk-script to /tmp is the invocation;
# I couldn't figure out a way to pass the filename to the BEGIN part other
# than via a variable, and I couldn't stand the thought of having to enter
# it manually on the command line twice. :} And I didn't like the idea of
# writing/saving two separate scripts - shoot me :D
#
# It creates two temporary files that it hopefully wipes after a
# successful run.

# hacked up by andrej
function usage {
echo "Usage: $0 databasename inputfile outputfile"
echo ""
echo " where database is the name of the database you want to isolate"
echo " out of the dump-file, inputfile is the file generated by
pg_dumpall"
echo " from which you want to extract a single database, and
outputfile is"
echo " the target file you want to write the extracted data to"
echo ""
}

if [ $# -ne 3 ]; then
usage
exit 1
fi

database=$1
input=$2
output=$3
pid=$$
temp=/tmp/awk.prog.$pid
cat > $temp <<\END
BEGIN{
system( "fgrep -in \"\\connect \" " file "> /tmp/outPut" )
while( getline line < "/tmp/outPut" > 0 ){
count++
numbers[count]=line
}
for (i=1; i<=count;i++ ){
if ( numbers[i] ~ db ){
start = gensub(/([0-9]+):.+/, "\\1", "g", numbers[i])
stop = gensub(/([0-9]+):.+/, "\\1", "g", numbers[i+1]) - 1
}
}
matchdb="CREATE DATABASE "db".+;"
}
{
if( $0 ~ matchdb ){
print
}
if(( NR >= int(start) ) &&( NR <= int( stop ) ) ){
print
}
}
END

sed -i "s/outPut/outPut.$pid/" /tmp/awk.prog.$pid
awk -v file=$input -v db=$database -f /tmp/awk.prog.$pid $input > $output
rm /tmp/awk.prog.$pid /tmp/outPut.$pid
--------8<--------8<--------8<--------8<--------8<--------8<--------8<--------8<--------

Cheers,
Andrej