PostgreSQL with Grails

05 / Sep / 2012 by Gunpreet 0 comments

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:

[java]sudo passwd postgres[/java]

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

[java]su postgres //switch to the user postgres with password newPassword

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

environments {
development {
dataSource {
dbCreate = “create-drop”
driverClassName = “org.postgresql.Driver”
username = “postgres”
password = “newPassword”


Step 4: Create database

[java]sudo -u postgres createdb [/java]

eg: sudo -u postgres createdb myDB

[java]su postgres //switch to the user postgres with password newPassword[/java]

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 [/java]

View details of a table structure:

[java]\d tablename[/java]

eg: postgres=# \d customer

Some useful postgreSQL commands:
Create database

sudo -u postgres createdb [/java]

Drop database

[java]dropdb myDB [/java]

Access database

[java]psql mydb [/java]

Show databases

[java]postgres=#\l [/java]

Get help

[java]postgres=#\h [/java]

Dump database

[java]pg_dump myDB > db.out [/java]

Reload database

[java]psql -d database -f db.out


Disconnect from psql:

postgres=#\q [/java]

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


Leave a Reply

Your email address will not be published. Required fields are marked *