PostgreSQL with Grails
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
