Thread: 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.
Attachment
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,