Skip to main content

Posts

Showing posts from August, 2014

Extract TABLE data from a large postgres SQL dump (with postgis)

What do you do when postgres refuses to import a dump because it contains invalid byte sequences? Solution: feed the sql script to iconv then import it as usual. That's easier said than done especially if your database contains postgis data which must be restored through a custom postgres dump (instructions here ). I recently experienced this issue on a relatively small table in a large-ish database. Since hand editing the SQL dump is cumbersome and hard (it is over 500MB in size) the only and most elegant alternative was to do it with a script. The following is an awk script which will extract the COPY instructions relative to a table from a postgres SQL dump: File: copy_estract.awk ---------------------- BEGIN {start=0} /^COPY "/ { if(index($0,TBL)!=0) { start=1; } } // {if(start==1) print $0;} /\\\./ {start=0;} Usage: awk -f copy_extract.awk -v TBL=TABLENAME pgdump/database_dump.sql One liner: awk -f copy_extract.awk -v TBL=TEST pgdump/d