Alfresco : PostgreSQL configuration

Alfresco comes by default with HSQL (HyperSonic) configured as the JCR database (DB). It also comes equipped with MYSQL configuration files, such that enabling these files will now make MYSQL as the JCR DB and leave HSQL aside.
Production environments can have DBs apart from MYSQL and HSQL. One such DB is PostgreSQL. This is an advanced DB, rivaling biggies like Oracle. Here i will be listing down the steps right from downloading PostgreSQL (PG) to getting it up and running with Alfresco.

1. Download PG from any of the mirrors listed on www.postgresql.org , by selecting the binary/8.2.6/win32 directory and the postgresql-8.2.6-1.zip file.
2. Once extracted in a temp folder this file will have the appropriate installer. This installer will install the pgAdmin utility and the JDBC jar required (so no additional download is necessary).
3. Proceed with installation by selecting the appropriate msi (postgresql-8.2.msi).
4. Create a user under your local windows machine account. Give it sufficient permissions to logon, run services and belonging to the 'Users' group. (Control Panel - Administrative Tools - Computer Management - User configuration). Let the user name be postgres.
   
5. Install with the default option of running as service under windows, as the custom install with the option of manual start (no service) has a very cryptic configuration process post-install. And with so many companies enforcing user permission restrictions (and with the advent of Vista) this once 'a fun' task is now a 'tedious and error prone' task. So avoid the temptation of setting PG manually and configure it as a service.
6. Assign the user that you created as the user that will run the service. Its pretty straightforward to follow these things via the wizard. During the installation the wizard will ask for an additional username (i created postgresql) this will be used by utilities like PgAgmin to connect to the main PG server.
   
7. After installation, make sure the service is started and running via the services administrative tool.
8. Go to the bin directory and run pgAdmin3.exe. This will bring up the Admin utility. Make sure you can connect to the PG server by clicking on File - Add Server option. Your host will most probably be localhost and the user will be postgresql.
9. Now you can create database instances and user roles within this server. To make it work with Alfresco, (my configuration has 2 instances of Alfresco running ... workalfresco and deployalfresco) I will be making 2 user and 2 database instances. Here's how, please repeat for the next one. [Note: The reason for this configuration of Alfresco can be found in my previous article].






1	2	3
4	5	6



10. The rest of the configuration is very similar to Alfresco's wiki on Database Configuration... http://wiki.alfresco.com/wiki/Database_Configuration. Although what the wiki doesn't say is that you will need to delete the alf_data directory before starting Alfresco after the configuration changes and if you have any custom coding that will need to wait for a while. so basically start with a fresh alfresco.war file.
11. After you have made the 2 changes above...delete the alf_data and replace the alfresco.war with a new fresh untouched alfresco.war, do the following...
12. Copy the appropriate PostgreSQL driver JAR to /tomcat/common/lib (TomCat) or /jboss/server/default/lib (JBoss).
13. Comment the existing entries and Override following repository properties in custom-repository.properties:
    

    db.driver=org.postgresql.Driver
    db.name=workalfresco
    db.url=jdbc:postgresql://localhost/workalfresco
    db.username=workalfrescoalfresco
    db.password={password}

14. Comment the existing entries and Override the following Hibernate property in custom-hibernate-dialect.properties:
    

    hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
    hibernate.query.substitutions=true TRUE, false FALSE

15. Ensure that the pg_hba.conf has the following entry (it might have the same line, with an 'md5' instead of 'password'. Its fine if thats the case.
    

    host    all    all    127.0.0.1/32    password

16. Restart Alfresco. It will take a longer time to restart since, it has to build all the tables in the database for the first time and also build all indexes.