Friday, December 8, 2006

pg_dump problem solved

In setting up ofbiz and postgres on windows, I found that PGAdmin totally barfed when trying to run the SQL file that I had created from a pg_dump command.

It returned errors like "ERROR: syntax error at or near "\" at character 387817 "

This was the result of the default behaviour of pg_dump using COPY commands, which PGAdmin apparently can't stomach. The command line statement,
psql -U postgres -f pgdump.sql reported no errors but FAILED to load a significant portion of the data. I find the limited error reporting very relaxing!

I found there is a command line option to pg_dump to dump the data using INSERT statments instead:
-d, --inserts dump data as INSERT, rather than COPY, commands

so, I did


pg_dump -d -U postgres > pgdump34.sql

from the Windows cmd line and got SQL statements that where recognizable to me and to PGAdmin III