Integrate PostgreSQL with Grails

In my current Grails project I am using PostgreSQL database so I just wanted to share the method to integrate PostgreSQL with Grails project.

Step 1: Install PostgreSQL on Linux

To install postgreSql use this command:-

sudo apt-get install postgresql

The default username is postgres

To change the password you can use the following command:

sudo passwd postgres

It will ask for new password. You can provide any password eg: 123456

Step 2: Integrate PostgreSQL with Grails Project

There are two ways to accomplish this. You can either include this line in BuildConfig.groovy
like this:-

runtime 'postgresql:postgresql:8.4-702.jdbc4'

It will automatically download the jar file.

The other way is to manually download the PostgreSQL JDBC driver and copy it into your application’s lib folder. For example: ProjectName/lib/postgresql9.1.216.jdbc3.jar

Step 3: Change DataSource file settings

dataSource {
    pooled = true
    driverClassName = "org.postgresql.Driver"
    username = "postgres"
    password = "Password"
    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/ApplicationName"
            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

Here is a list of 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

Disconnect from psql:

postgres=#q

That was all about Postgres.
Hope this will help you. 🙂
Vivek Sadh