Postgresql - Set timing on by default

For the psostgres user create file in the home folder:

vim ~/.psqlrc

and add the following to the file

\timing

When you connect to database and run  a query, the time is also displayed:

postgres=# SELECT current_timestamp;
               now              
----------------------------------
 2014-03-11 10:34:40.378455+05:30
(1 row)
Time: 43.821 ms

Vim - Replace with incremental value

:let @a=1 | %s/<old_word>/\='<new_word>'.(@a+setreg('a',@a+1))/g

If the <old_word> is replaced with abc
and <new_word> with xyz

:let @a=1 | %s/abc/\='xyz'.(@a+setreg('a',@a+1))/g

The command will replace all abc with xyz.<number>. The <number> will start with 1 and increment by 1.

1st abc will be replace by xyz.1
2nd abc will be replace by xyz.2
.
.
.
so on

Find the list of directories with their Sizes

Get Size of all sub-directories

Command: 

du -sh <directory_path>/*

Example:
$ du -sh /home/Beena/*

4.0K /home/Beena/Desktop
4.0K /home/Beena/Documents
64M /home/Beena/Downloads
4.0K /home/Beena/Music
4.0K /home/Beena/Pictures
4.0K /home/Beena/Public
4.0K /home/Beena/Templates
4.0K /home/Beena/Videos

Get all files and sub-directories More than a specified size

Command: 

find <dir_path> -size +<size> -ls

Example:
$ find /home/Beena/ -size +10M -ls

Get all files and sub-directories Less than a specified size

Command: 

find <dir_path> -size -<size> -ls

Example:
$ find /home/Beena/ -size -10M -ls

PostgreSQL : Install using RPM

1. Download rpm

Site: http://yum.postgresql.org/repopackages.php

Download the required rpm from the site. 

Ex. PostgreSQL 9.1 for Cent OS 6 -x86_64 (http://yum.postgresql.org/9.1/redhat/rhel-6-x86_64/pgdg-centos91-9.1-4.noarch.rpm)

2. Install rpm

$ cd <rpm_download_dir>

$ su

Password: 

# rpm -ivh pgdg-centos91-9.1-4.noarch.rpm

# yum install postgresql91-server postgresql91

If yum install fails with 'PG key retrieval failed: [Errno 14]' then perform the steps mentioned at http://www.rackspace.com/knowledge_center/article/installing-rhel-epel-repo-on-centos-5x-or-6x  before trying again.
If it is unable to find /etc/pki/rpm-gpg/RPM-GPG-KEY-oracle then run the command: wget https://public-yum.oracle.com/RPM-GPG-KEY-oracle-ol6 -O /etc/pki/rpm-gpg/RPM-GPG-KEY-oracle --no-check-certificate

3. Run PostgreSQL

# /etc/init.d/postgresql-9.1 initdb

# service postgresql-9.1 start

# su postgres

bash-4.1$ psql

psql (9.1.11)

Type "help" for help.

4. Load Extension

Download the required extension rpm from their site.

$ cd <rpm_download_dir>

$ su

Password: 

# rpm -ivh <rpm_name>.rpm

Login to Postgres and load the extension via CREATE EXTENSION command.

5. Uninstall

Remove the installation and the database cluster.

# rpm -e postgresql91-server postgresql91

# rm -rf /var/lib/pgsql/

PostgreSQL - Connect using SSL

Used software: PostgreSQL 9.2.5

1. Install Postgres

Use the --with-openssl option with ./configure while installing postgres

$ CFLAGS="-g -O0" ./configure --enable-debug --enable-cassert --enable-depend --prefix=<install_path> --with-openssl

$ make

$ make install

2. Create a Data Directory

$ cd <postgres_install_path>/bin

$ ./initdb -d <cluster_path>

3. Modify the postgresql.conf file

ssl  = on

4. Create self signed certificate

[Ref: http://www.postgresql.org/docs/current/static/ssl-tcp.html#SSL-CERTIFICATE-CREATION]

$ cd <cluster_path>

$ openssl req -new -text -out server.req

Enter a pass phrase (at least 4 char long).
Fill in other details if required.
Challenge password can be left blank.

$ openssl rsa -in privkey.pem -out server.key

Enter the same pass phrase entered before.

$ openssl req -x509 -in server.req -text -key server.key -out server.crt

$ chmod og-rwx server.key

4. Start postgres

$ cd <postgres_install_path>/bin

$ ./pg_ctl -D <cluster_path> start

5. Connect using ssl

$ ./psql "sslmode=require host=localhost dbname=postgres"

psql (9.2.5)

SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)

Type "help" for help.

postgres=#

Linux Users

List users

$ cat /etc/passwd | grep "/home"

Beena:x:500:500:Beena Emerson:/home/Beena:/bin/bash

Only list names:

$ cat /etc/passwd |grep /bin/bash |grep [5-9][0-9][0-9] |cut -d: -f1

Beena

Add New User

You have to be root user to be able to add new users.

# useradd test_user

# cat /etc/passwd |grep /bin/bash |grep [5-9][0-9][0-9] |cut -d: -f1

Beena

test_user

Switch Users:

# su test_user

[test_user@localhost ~]$

Add Password

# passwd test_user

Changing password for user test_user.

New password:

Retype new password:

passwd: all authentication tokens updated successfully.

Delete User

# userdel test_user

# cat /etc/passwd |grep /bin/bash |grep [5-9][0-9][0-9] |cut -d: -f1

Beena

Git: Generating patches

Generate patch for given commit

1. git format-patch

git format-patch -1 <commit_id>

The patch file has a 4 digit numeral followed by the commit message separated by hyphens. 

Example:

For the following commit:

commit 6d432152b9e5627532c52d6f1c9959cb3be52e29

Author: Bruce Momjian <bruce@momjian.us>

Date:   Mon Jul 1 12:46:13 2013 -0400

    Update LSB URL in pg_ctl

    Update Linux Standard Base Core Specification 3.1 URL mention in pg_ctl

    comments.

git format-patch -1 generates patch file 0001-Update-LSB-URL-in-pg_ctl.patch

 

2. git show

 git show <commit_id>

This will will simply display the patch in stdout. We can redirect this output to a patch file. In the following command, testfile.patch will have the patch for the specified commit.

git show 6d432152b9 > testfile.patch

Special Keyword HEAD

1. git format-patch

HEAD refers to the latest commit. The following will generate 2 patch for the last two commits.

git format-patch -2 HEAD

~n is appended to HEAD to refer to a nth commit before latest one. The following command generates a patch for the 2nd commit before the latest.

git format-patch -1 HEAD~2

2. git show

The following will display the patch for latest HEAD 

git show HEAD

The following will display the changes since the last n commits 

git show HEAD~n

n commits before specified

git format-patch

git format-patch -n <commit_id>

This command will generate n patch files for each of the n commits starting from (n-1) commit before the specified <commit_id>. 

For n > 2, patches will be generated from (n-1) commits before the specified commit.

git format-patch -3 6d432152b9 generates 3 files:

       0001-Optimize-pglz-compressor-for-small-inputs.patch

       0002-Remove-undocumented-h-help-option.patch

       0003-Update-LSB-URL-in-pg_ctl.patch

0001 file is patch for 2 commits before 6d432152b9 and 0003 file is the patch for specified commit.

We can avoid specifying commit id when we want patches for the last n commits. The following generates patch for the last 7 commits.

git format-patch -7

We can avoid specifying commit id when we want patches for the last n commits. The following generates patch for the last 7 commits.

Difference Between 2 commits

git diff

To check the changes between 2 known commits use git diff command, this output again displayed on stdout and so will need to be redirected to a file 

git diff <from_commit> <to_commit> 

Note that the changes of the <from_commit> are not included. The <from_commit> should be older (committed before) than <to_commit>. If we reverse the <from_commit> and <to_commit>, we would be getting a patch to reverse all the changes made.

git diff 031cc55b 6d432152 > diff.patch