Currently in my Grails project I am using PostgreSQL database so I thought to share my knowledge with everyone. I am using it on Linux operating system. I am mentioning all the steps that I followed to integrate PostgreSQL with Grails .

Step 1: Install PostgreSQL on your system
To install postgreSql

sudo apt-get install postgresql

By default the username is postgres

For changing password:

sudo passwd postgres

It will ask for new password. Give any password eg: newPassword

su postgres    //switch to the user postgres with password newPassword
psql

Step 2: Download PostgreSQL

Download latest PostgreSQL JDBC driver and copy it into your application’s lib folder. For example: myProject/lib/postgresql9.1.216.jdbc3.jar

Step 3: Change DataSource file settings

dataSource {
    pooled = true
    driverClassName = "org.postgresql.Driver"
    username = "postgres"
    password = "newPassword"
    dialect = org.hibernate.dialect.PostgreSQLDialect

}
hibernate {
    cache.use_second_level_cache = true
    cache.use_query_cache = true
    cache.provider_class='org.hibernate.cache.EhCacheProvider'
}

environments {
    development {
        dataSource {
            url="jdbc:postgresql://localhost:5432/appName"
            dbCreate = "create-drop"
            driverClassName = "org.postgresql.Driver"
            username = "postgres"
            password = "newPassword"
        }
    }
}

Step 4: Create database

sudo -u postgres createdb <databaseName>

eg: sudo -u postgres createdb myDB

su postgres   //switch to the user postgres with password newPassword

Type \l to show all databases in postgreSql

postgres=# \l

psql -U postgres -d myDB    // now you are connected with your application
\d                         // list all the tables in that database 

View details of a table structure:

\d tablename

eg: postgres=# \d customer

Some useful postgreSQL commands:
Create database

sudo -u postgres createdb <databaseName>   

Drop database

dropdb myDB       

Access database

psql mydb         

Show databases

postgres=#\l             

Get help

postgres=#\h           

Dump database

pg_dump myDB > db.out   

Reload database

psql -d database -f db.out 

Disconnect from psql:

postgres=#\q 

Note: In my project I got problem while using “user” table name because user is a keyword in PostgreSQL so I changed the name of user table. Instead of changing domain name I just mentioned the table name corresponding to User domain in mapping closure.
Also, one should keep in mind that postgresql maintains id in sequence unlike mysql. It means that the id generator generates id in continous sequence for all the tables instead of generating from 1 for each table.
Hope this will help you.
Gunpreet Bedi
gunpreet@intelligrape.com

https://twitter.com/gunpreet_ginny