Mantain multiple environments
How to maintain multiple separate databases with PostgreSQL
Well, one common thing that can go wrong (and believe me, it does most of the time) when trying to set up separate databases with PostgreSQL is that it get's lost with the search_path.
To solve this problem, we have to adjust some simple things into the database to allow it to find the adempiere schema in an easy way.
When you create a database with the name that differs from adempiere, it's search path is set to the new database name. For example, if you set the database name to adempieretrunk, the search_path of the database get's set to adempieretrunk.
The problem is, when Adempiere is starting up, it does not look for a schema with the database name, so it looks for the schema adempiere while the database is pointing to the adempieretrunk schema as the default search path.
To solve this, you should issue this two simple commands against your database:
alter user <name of the db owner> set search_path to 'adempiere', 'user', <all the other schemas that you need>
alter database <name of the db> set search_path to 'adempiere', 'user', <all the other schemas that you need>
So, with our little example database adempieretrunk that has as it's owner the user adempieretrunk, we should issue the following commands.
alter user adempieretrunk set search_path to 'adempiere';
alter database adempieretrunk set search_path to 'adempiere';
Hope that helps out. If you need help regarding this issue, don't hesitate on contacting me on irc or throught e-mail (fer_luck).
