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/db.sql | iconv -f latin1 -t utf8 …