Hi phillipuniverse,
Thanks for the reply,
I just compared postgres against hsqldb, all tables and data are matching(no. of records) as you have pointed i have corrected boolean type data to make it import to postgres.
But still i am not able to see data in 'HotSauces' link(getting null pointer exception), but in 'Merchandise' all featured products getting displayed.
Seems to be again running with some configuration issues.
Please find below steps i have followed and database backup (postgres) :
Steps followed to migrate to PostgreSQL:
1) PostgreSQL installed, configured
(I am familiar using PostgreSQL for other projects in my work - Openbravo ERP setup and configuation, i.e one of the reason decided to configure broadleaf - postgresql)
2) Added dependency to root pom.xml (i.e Demosite-master/pom.xml)
<dependency>
<groupId>postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>9.0-801.jdbc4</version>
<type>jar</type>
<scope>compile</scope>
</dependency>
3) Added dependency to "site/pom.xml" and "admin/pom.xml"
<dependency>
<groupId>postgresql</groupId>
<artifactId>postgresql</artifactId>
</dependency>
3) Updated driver data in - site/src/main/webapps/WEB-INF/jetty-env.xml, admin/src/main/webapps/WEB-INF/jetty-env.xml
Replaced the following:
<New class="org.apache.commons.dbcp.BasicDataSource">
<Set name="driverClassName">org.hsqldb.jdbcDriver</Set>
<Set name="url">jdbc:hsqldb:hsql://localhost/broadleaf</Set>
<Set name="username">sa</Set>
<Set name="password"></Set>
</New>
with this postgresql:
<New class="org.apache.commons.dbcp.BasicDataSource">
<Set name="driverClassName">org.postgresql.Driver</Set>
<Set name="url">jdbc:postgresql://localhost:5432/broadleaf</Set>
<Set name="username">bl2user</Set>
<Set name="password">bl2pwd</Set>
</New>
4) Update dialect in - core/common-shared.properties
blPU.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
blSecurePU.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
blCMSStorage.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
5) Compared each of the table data (against hsqldb - to do this i have setup two instances one with postgres and one with default hsqldb, see compare.png)
Notes:
1) Initially "hbm2ddl.auto = create" then changed to "hbm2ddl.auto = update" under "site/src/main/resources/development.properties"
2) To note again, i have compared all the tables to see any mismatch for test data
3) Corrected boolean data for some of the tables -
http://www.postgresql.org/docs/8.1/stat ... olean.html(in hsqldb boolean values will be accepted without single quote, example - in hsqldb = boolean x = 0, but in postgres x = '0'
4)Removed startdb from site/build.xml i.e depends="start-db" from jetty-demo = target after following above steps
5) To take backup and dump/restore in postgres(window/linux command console):
1)Backup data with pg_dump
pg_dump -i -h localhost -p 5432 -U postgres -F c -b -v -f "<file_name>.backup" <database_name_to_be_backup>
example - pg_dump -i -h localhost -p 5432 -U postgres -F c -b -v -f "d:\broadleaf.backup" broadleaf-testdb
-p, –port=PORT database server port number
-i, –ignore-version proceed even when server version mismatches ==> very important
-h, –host=HOSTNAME database server host or socket directory
-U, –username=NAME connect as specified database user
-W, –password force password prompt (should happen automatically)
-d, –dbname=NAME connect to database name
-v, –verbose verbose mode
-F, –format=c|t|p output file format (custom, tar, plain text)
-c, –clean clean (drop) schema prior to create
-b, –blobs include large objects in dump
-v, –verbose verbose mode
-f, –file=FILENAME output file name
2) Restore data with pg_restore
pg_restore -i -h localhost -p 5432 -U postgres -d <database_name_to_be_dumped> -v "<filename>.backup"
example - pg_restore -i -h localhost -p 5432 -U postgres -d broadleaf -v "d:\broadleaf.backup"
-p, –port=PORT database server port number
-i, –ignore-version proceed even when server version mismatches
-h, –host=HOSTNAME database server host or socket directory
-U, –username=NAME connect as specified database user
-W, –password force password prompt (should happen automatically)
-d, –dbname=NAME connect to database name
-v, –verbose verbose mode
(backup and restore can also be achieved using psql, pg_dumpall but pg_admin UI tool backup option is not recommended due character encoding issues)
Any suggestions/help are welcome and thanks in advance!!!
Regards,
Srinivas