Re: Reload only specific databases from pg_dumpall

On 05/02/2008, Scott Marlowe <scott.marlowe@gmail.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