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?
Usage:
One liner:
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:
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