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

Levenshtein Distance

Levenshtein Distance or edit distance between two strings is the number of single character edits to be made to convert one string to another. The edit can be an insertion, deletion or substitution.

Eg: Levenshtein distance between 'Choose' and 'Shoes' is 3.

1. Substitute 'C' with 'S'
2. Substitute 2nd 'o' with 'e'
3. Delete 's'

C h o o s e
S h o o s e
S h o e s e
S h o e s _

The Damerau–Levenshtein Distance is also an edit distance between two strings but it includes one more edit operation viz transposition.

Eg: Damerau–Levenshtein distance 'Reign' and 'Field' is 4

1. Substitute 'R' with 'F'
2. Transpose 'i' and 'e'
3. Substitute 'g' with 'l'
4. Substitute 'n' with 'd'

R e i g n
F e i g n
F i e g n
F i e l n
F i e l d

Levenshtein distance between 'Reign' and 'Field' is 5.

Inaugural Indian PostgreSQL User Group Meetup!

This was a memento given for the first meetup of Postgres Users in and around Pune at BMC Software, Pune.

It was a success. Around 30 people , newbies to developers from different companies had come. There were presentations on Streaming Replication, HA and Postgres XC. The content was good and had learnt quite a lot!...

This was a great experience, it encouraged and motivated me to continue contributing to the community! Hope there are more such meetups and I get to present my contributions too. :-)