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
[java]
sudo apt-get install postgresql
[/java]
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
psql[/java]
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
[java]
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”
}
}
}
[/java]
Step 4: Create database
[java]sudo -u postgres createdb
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
[java]
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
[java]
sudo -u postgres createdb
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
[/java]
Disconnect from psql:
[java]
postgres=#\q [/java]
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
