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