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:

	BEGIN {start=0}
	/^COPY "/ { if(index($0,TBL)!=0) { start=1; } }
	// {if(start==1) print $0;}
	/\\\./ {start=0;}

view raw copy_extract.awk hosted with ❤ by GitHub

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 | psql db