My Postgres Trail

gdb commands

Future breakpoints

Set break point on future shared library:

set breakpoint pending on

Debug fork

To hold the forked process by gdb and not allow it to run independently:

set detach-on-fork off

Vim - Change case

Open the document in vim, press v and move to select text and then press one of the following:

U - converts to upper case
u - converts to lower case
~ - toggles the case of selected

To change text of entire file to lower case run the following

ggVGu

replace u with U for uppercase and ~ to toggle.

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

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=#