Wednesday, August 27, 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:



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

No comments: