- Install Postgresql on Ubuntu 13.10
- Create Users and database on Postgresql
- Configure a grails application to use Postgresql
- Verify application is connecting to the Database
Install Postgresql on Ubuntu 13.10
There's a number of tutorials on how to install postgres. Some included adding a new repository, but I tend to prefer the packages that is associated with an Ubuntu version. In my case since I am using Ubuntu 13.10, the version available is postgresql 9.1
sudo apt-get install postgresql-9.1
apt-cache search postgresql
Create Users and database on Postgresql
Login to Postgresql. Similar to MySQL, you would want to create a user for your Grails application that would have access to the Postgresql database. In order to do this, you need to login as the default Postgresql user, which is 'postgres'
sudo -u postgres psql template1
template1=# ALTER USER postgres with encrypted password 'newpass';
template1=# \q
Modify a configuration file called pg_hba.conf and change the line below - changing 'peer' to 'md5'.
unix$ sudo vi /etc/postgresql/9.1/main/pg_hba.conf
Restart Postgresql.
sudo /etc/init.d/postgresql restart
Create the new user that would be used in your Grails application. To know what options are available for createuser.
unix$ createuser -U postgres -d -e -E -I -P -r -s myuser<br/>
Enter password for new role: <br/>
Enter it again: <br/>
Password: <br/>
CREATE ROLE myuser ENCRYPTED PASSWORD 'md5eb6108579123bee126149054337490' SUPERUSER CREATEDB CREATEROLE NOINHERIT LOGIN;
Restart the database again using the command above ('postgresql restart');
Verify if the new user was created, login using the username and password. You should be able to login with your new user and password and see the command-line where you can issue SQL queries.
unix$ psql -U myuser -d mydb -W
Password for user myuser:
psql (9.1.10)
Type "help" for help.
mydb=#
unix$ createdb -U myuser -W mydb
Configure a grails application to use Postgresql
After you have successfully installed and configured Postgresql, in your Grails application, you need to modify 2 configuration files, BuildConfig.groovy and DataSource.groovy.
First, we need to add the JDBC jar for Postgresql. Add this line inside the BuildConfig.groovy file inside the dependencies. Verify the jar version that is available on maven. dependencies {
// specify dependencies here under either 'build', 'compile', 'runtime', 'test' or 'provided' scopes e.g.
//runtime 'mysql:mysql-connector-java:5.1.24'
runtime 'postgresql:postgresql:9.1-902.jdbc4'
}
Next, add the following configuration in DataSource.groovy.
dataSource {
driverClassName = "org.postgresql.Driver"
dialect = org.hibernate.dialect.PostgreSQLDialect
username = "myuser"
password = "mypassword"
dbCreate = "update" // one of 'create', 'create-drop','update'
url = "jdbc:postgresql://localhost:5432/mydb"
}
Verify application is connecting to the Database
Run the Grails application (grails run-app) and verify that the tables are created. You can login again using psql and issue the command \dt to list all tables. You should be able to see all your Grails domain classes. unix$ psql -U myuser -d mydb -W
Password for user myuser:
psql (9.1.10)
Type "help" for help.
mydb=# \dt
List of relations
Schema | Name | Type | Owner
--------+-------------------+-------+------------
public | table_one | table | myuser
public | table_two | table | myuser
public | table_three | table | myuser
So far so good. One problem I encountered is that my Grails app had a table called 'User'. Unfortunately, this is a reserved word in Postgresql. It was an easy fix by just mapping the domain class user to the table using 'static mapping'.
