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

running ofbiz with postgresql on windows

These are the steps I took to get ofbiz talking to a postgresql database running on Windows:

1. Download + install postgres for Windows
2. update /data/pg_hba.conf to include the following lines.

   local all all trust
   host all all 127.0.0.1 255.0.0.0 trust
   

3. Download appropriate driver.
4. fix build.xml to add your driver's jar file to the classpath or replace existing postgres driver with correct one for the version you just installed.
5. Install postgres, making note of username and password. The installation will allow you to name a postgres user. I named mine "postgres". The installation will give you a PGAdmin executable which is useful for doing operations which you aren't allowed to do as admin user.
   
6. initialize database with ofbiz tables, preferably from a pgdump.sql file, alternatively by using the xml import tool.
7. change entityengine.xml to reflect your use of a local windows postgres
1. link to entityengine.xml example
2. url looks like: jdbc:postgresql://127.0.0.1:5432/postgres

Read this for instructions on configuring pg_hba.conf

"host all all ... trust" means no password is checked at all for connects for the hosts indicated, so if you are getting "password failed" in your log files, it isn't reading pg_hba.conf correctly.

For most changes to the .conf files, you can tell postgresql to read them again with the command pg_ctl reload -D D:\psql\data. Certain settings in the .conf files require a postgresql restart which is best done from PGAdmin III. Stopping the service and trying to start it again can turn into a major hassle because this amounts to starting postgresql with administrative privileges. When I did it this way, I couldn't restart the service except by rebooting.

You will also need to run a ant run-install-seed to put default passwords (admin,ofbiz)