{"id":7151,"date":"2012-09-05T10:54:43","date_gmt":"2012-09-05T05:24:43","guid":{"rendered":"http:\/\/www.tothenew.com\/blog\/?p=7151"},"modified":"2016-12-19T15:16:41","modified_gmt":"2016-12-19T09:46:41","slug":"postgresql-with-grails","status":"publish","type":"post","link":"https:\/\/www.tothenew.com\/blog\/postgresql-with-grails\/","title":{"rendered":"PostgreSQL with Grails"},"content":{"rendered":"

Currently in my Grails project<\/a> 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 .<\/p>\n


\nStep 1:<\/strong> Install PostgreSQL on your system<\/strong>
\nTo install postgreSql<\/p>\n

[java]
\nsudo apt-get install postgresql
\n[\/java]<\/p>\n

By default the username is postgres<\/p>\n

For changing password:<\/p>\n

[java]sudo passwd postgres[\/java]<\/p>\n

It will ask for new password. Give any password eg: newPassword<\/p>\n

[java]su postgres \/\/switch to the user postgres with password newPassword
\npsql[\/java]<\/p>\n


\nStep 2: Download PostgreSQL<\/strong><\/p>\n

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


\nStep 3: Change DataSource file settings<\/strong><\/p>\n

[java]
\ndataSource {
\n pooled = true
\n driverClassName = “org.postgresql.Driver”
\n username = “postgres”
\n password = “newPassword”
\n dialect = org.hibernate.dialect.PostgreSQLDialect<\/p>\n

}
\nhibernate {
\n cache.use_second_level_cache = true
\n cache.use_query_cache = true
\n cache.provider_class=’org.hibernate.cache.EhCacheProvider’
\n}<\/p>\n

environments {
\n development {
\n dataSource {
\n url=”jdbc:postgresql:\/\/localhost:5432\/appName”
\n dbCreate = “create-drop”
\n driverClassName = “org.postgresql.Driver”
\n username = “postgres”
\n password = “newPassword”
\n }
\n }
\n}<\/p>\n

[\/java]<\/p>\n

Step 4: Create database <\/strong><\/p>\n

[java]sudo -u postgres createdb [\/java]<\/p>\n

eg: sudo -u postgres createdb myDB<\/em><\/p>\n

[java]su postgres \/\/switch to the user postgres with password newPassword[\/java]<\/p>\n

Type \\l to show all databases in postgreSql<\/p>\n

postgres=# \\l<\/em><\/p>\n

[java]
\npsql -U postgres -d myDB \/\/ now you are connected with your application
\n\\d \/\/ list all the tables in that database [\/java]<\/p>\n

View details of a table structure:<\/p>\n

[java]\\d tablename[\/java]<\/p>\n

eg: postgres=# \\d customer<\/em><\/p>\n

Some useful postgreSQL commands:<\/em><\/strong>
\nCreate database<\/p>\n

[java]
\nsudo -u postgres createdb [\/java]<\/p>\n

Drop database<\/p>\n

[java]dropdb myDB [\/java]<\/p>\n

Access database<\/p>\n

[java]psql mydb [\/java]<\/p>\n

Show databases<\/p>\n

[java]postgres=#\\l [\/java]<\/p>\n

Get help<\/p>\n

[java]postgres=#\\h [\/java]<\/p>\n

Dump database<\/p>\n

[java]pg_dump myDB > db.out [\/java]<\/p>\n

Reload database<\/p>\n

[java]psql -d database -f db.out <\/p>\n

[\/java]<\/p>\n

Disconnect from psql:<\/p>\n

[java]
\npostgres=#\\q [\/java]<\/p>\n


\nNote:<\/strong> 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.
\nAlso, 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.
\nHope this will help you.<\/em> \ud83d\ude42
\nGunpreet Bedi
\ngunpreet@intelligrape.com<\/p>\n

https:\/\/twitter.com\/gunpreet_ginny<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"

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 […]<\/p>\n","protected":false},"author":44,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"iawp_total_views":49},"categories":[7],"tags":[4843,4840,260,942],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/posts\/7151"}],"collection":[{"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/users\/44"}],"replies":[{"embeddable":true,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/comments?post=7151"}],"version-history":[{"count":0,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/posts\/7151\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/media?parent=7151"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/categories?post=7151"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/tags?post=7151"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}