PostgreSQL learning

To find out the location of PostgreSQL configuration files, just connect with your PostgreSQL database and run the following command.

SELECT name, setting FROM pg_settings WHERE category = 'File Locations';

To describe a table, you should use

\d+ table_name

To list all the databases,you should run following command.

\list
\l

To view all the tables in a schema

\dt+ pg_catalog.pg_t*

To view core settings of a PostgreSQL server, you should use

SHOW ALL;

To view value of a specific setting like effective_cache_size

SHOW effective_cache_size;

To reload the configuration changes

SELECT pg_reload_conf();

To create a new LOGIN user

CREATE ROLE shekhar LOGIN PASSWORD 'p@ssw0rd';

To create a new database

CREATE DATABASE myappdb;

Schema are logical compartments in a database. You can divide your database into small logical schemas.

CREATE SCHEMA etcs;

To view all the available extensions

SELECT * from pg_available_extensions;

To view details about an extension
To view all the available extensions

\dx+ plpgsql;

To install an extension and view its details

postgres=# CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION
postgres=# \dx+ fuzzystrmatch;
                    Objects in extension "fuzzystrmatch"
                             Object Description
----------------------------------------------------------------------------
 function difference(text,text)
 function dmetaphone(text)
 function dmetaphone_alt(text)
 function levenshtein(text,text)
 function levenshtein(text,text,integer,integer,integer)
 function levenshtein_less_equal(text,text,integer)
 function levenshtein_less_equal(text,text,integer,integer,integer,integer)
 function metaphone(text,integer)
 function soundex(text)
 function text_soundex(text)
(10 rows)

To view the active running processes

postgres=# SELECT pid, usename from pg_stat_activity;
 pid  | usename
------+----------
 1982 | postgres
 2742 | postgres
 2236 | postgres
 2723 | shekhar
 2744 | postgres
(5 rows)

To kill a connection

postgres=# SELECT pg_terminate_backend(2723);
 pg_terminate_backend
----------------------
 t
(1 row)

To kill all the connection by user shekhar

SELECT pg_terminate_backend(pid) FROM pg_stat_activity where usename = 'shekhar';

To run a script file, run the following command.

psql -f <path_to_filefile>

You can also run commands with psql non-interactively

psql -d mydb -c "CREATE SCHEMA test;"

The \set command can be used to create user defined shortcuts like as shown below.

\set connections_check 'SELECT pid, usename from pg_stat_activity;'

The .psqlrc file can be used to define configurations for a session. You can use PSQLRC environment variable to control the location of the startup file.

\pset null 'Null'
\encoding latin1
\set PROMPT1 '%n@%M:%>%x %/#'
\set PROMPT2
\timing on
\pset pager always

To turn on timing for the queries execution time

\timing

You can call OS shell commands from within psql as shown below.

admin2pnvtk8:myapp#\! env|grep POSTGRES

To view uptime of your PostgreSQL database in minutes, run the following query.

myapp=# select date_trunc('minute',current_timestamp - pg_postmaster_start_time()) as "postgresql_uptime";
 postgresql_uptime 
-------------------
 00:12:00
(1 row)

How to Host your Java EE Application with Auto-scaling

OpenShift is an auto-scalable Platform as a Service. Auto-scalable means OpenShift can horizontally scale your application up or down depending on the number of concurrent connections. OpenShift supports the JBoss application server, which is a certified platform for Java EE 6 development. As an OpenShift user, you have access to both the community version of JBoss and JBoss EAP 6(JBoss Enterprise Application Platform) for free. In this blog post, we will learn how to host a scalable Java EE 6 application using a JBoss EAP 6 server cluster running on OpenShift. Read the full blog here https://www.openshift.com/blogs/how-to-host-your-java-ee-application-with-auto-scaling

Solving Pusher HTTPS Issue

If you use Pusher then you might face following error. The error happens when you try to use Pusher client library from https.

The page at 'https://shekhargulati.com/blog/1#/' was loaded over HTTPS, but ran insecure content from 'http://js.pusher.com/2.1/pusher.min.js': this content should also be loaded over HTTPS.

To fix this error, you should use Pusher Cloudfront CDN version as shown below. In your HTML or other template use following. Replace 2.1 with your own version.

<script src="//d3dy5gmtp8yhk7.cloudfront.net/2.1/pusher.min.js" type="text/javascript"></script>

How to run Grails Application with Jenkins on OpenShift

Yes, you can run Grails applications on OpenShift. Follow the steps mentioned below to deploy Grails apps via Jenkins on OpenShift.

Step 1 : Create Tomcat 7 application with Jenkins

$ rhc app-create grailsapp tomcat-7 --enable-jenkins

Step 2 : Delete template code

$ cd grailsapp
$ git rm -rf src/ pom.xml
$ git commit -am "deleted template code"

Step 3: Generate Grails app
Use grails command line or IDE to generate a Grails project.

Step 4: Copy the Grails app
Copy the source code of your grails app in the grailsapp folder. The grailsapp corresponds to OpenShift application.

Step 5: Create pre_build action hook
Create an OpenShift action hook

touch .openshift/action_hooks/pre_build
chmod +x .openshift/action_hooks/pre_build

Copy the following in pre_build hook

#!/bin/bash
# This is a simple script and will be executed on your CI system if
# available.  Otherwise it will execute while your application is stopped
# before the build step.  This script gets executed directly, so it
# could be python, php, ruby, etc.
set -x
if [ ! -d $OPENSHIFT_DATA_DIR/grails-2.3.4 ]
then
        mkdir $OPENSHIFT_DATA_DIR/.grails
        cd $OPENSHIFT_DATA_DIR
        wget http://dist.springframework.org.s3.amazonaws.com/release/GRAILS/grails-2.3.4.zip
        unzip grails-2.3.4.zip
        rm -f grails-2.3.4.zip
fi

Step 6: Create build action hook
Create an OpenShift action hook

touch .openshift/action_hooks/build
chmod +x .openshift/action_hooks/build

Copy the following in build hook

#!/bin/bash
# This is a simple script and will be executed on your CI system if
# available.  Otherwise it will execute while your application is stopped
# before the build step.  This script gets executed directly, so it
# could be python, php, ruby, etc.
set -x
export GRAILS_HOME=$OPENSHIFT_DATA_DIR/grails-2.3.4
export PATH=$GRAILS_HOME/bin:$PATH
cd $OPENSHIFT_REPO_DIR
export GRAILS_OPTS="-Xmx512m -Xms256m -XX:MaxPermSize=256m"
grails -Dgrails.work.dir=$OPENSHIFT_DATA_DIR.grails prod war

Step 7: Commit and push the changes

Commit and push the changes

$ git add .
$ git commit -am "app"
$ git push

Now watch your Jenkins build. If it fails, I guess it would be because of memory issues. Try and use bigger gear sizes. Grails is memory hungry.

Github repository with sample app code https://github.com/shekhargulati/grails-jenkins-openshift-example

Day 30: Play Framework–A Java Developer Dream Framework

For the last day of my 30 day challenge, I decided to learn the Play framework. I wanted to write about Scala but after spending few hours I realized I will not be able to do justice with Scala in one day. I will spend time on Scala in December and will share my experience. In this blog post, we will first look at the Play framework basics and then we will develop an application using it. Read the full blog here https://www.openshift.com/blogs/day-30-play-framework-a-java-developer-dream-framework

Day 29: Yeoman Chrome Generator–Write Your First Google Chrome Extension

Today for my 30 day challenge, I decided to learn how to write a Chrome extension. After some research, I found out that there is a Yeoman generator for writing Chrome extensions. The extension that we will write in this blog post blocks us from accessing Facebook, Twitter, LinkedIn, and other social web sites during the office time. This blog post will not cover Yeoman basics so please refer to my day 24 blog for a getting started with Yeoman post. Read the full blog here https://www.openshift.com/blogs/day-29-yeoman-chrome-generator-write-your-first-google-chrome-extension