Обсуждение: Shell script to extract a table from a plain text dump

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

On Fri, Oct 07, 2005 at 04:46:12PM +0800, Christopher Kings-Lynne wrote:
> 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.

Hmm, what I usually use is:

bzcat $file | sed -ne "/^COPY \"$table\" /,/^\\\.\$/p"

However, error checking and wrapping it into a script is a good idea.
If it got given a couple of switches to control the output, maybe we
can have a pg_restore for text dumps :)

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

On Fri, Oct 07, 2005 at 11:36:27AM +0200, Martijn van Oosterhout wrote:
> On Fri, Oct 07, 2005 at 04:46:12PM +0800, Christopher Kings-Lynne wrote:
> > 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.
>
> Hmm, what I usually use is:
>
> bzcat $file | sed -ne "/^COPY \"$table\" /,/^\\\.\$/p"
>
> However, error checking and wrapping it into a script is a good idea.
> If it got given a couple of switches to control the output, maybe we
> can have a pg_restore for text dumps :)

If only the text dump could have a TOC, by means of which it would be
possible to seek to the exact position of the dump that has a table's
dump, it would certainly be useful.  Otherwise, you need to read the
whole file anyway, which is bad.  Of course, if it's compressed then
there's not much you can do.

--
Alvaro Herrera                 http://www.amazon.com/gp/registry/CTMLCN8V17R4
"Los dioses no protegen a los insensatos.  Éstos reciben protección de
otros insensatos mejor dotados" (Luis Wu, Mundo Anillo)

Argh!  That's some sed coolness :)

Chris

Martijn van Oosterhout wrote:
> On Fri, Oct 07, 2005 at 04:46:12PM +0800, Christopher Kings-Lynne wrote:
>
>>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.
>
>
> Hmm, what I usually use is:
>
> bzcat $file | sed -ne "/^COPY \"$table\" /,/^\\\.\$/p"
>
> However, error checking and wrapping it into a script is a good idea.
> If it got given a couple of switches to control the output, maybe we
> can have a pg_restore for text dumps :)
>
> Have a nice day,