Install PostgreSQL on RHEL 6.4

OS Version: Rhel 6.4
PostgreSQL Version:- 9.4

Step 1:-Download the repository
yum install http://yum.postgresql.org/9.4/redhat/rhel-6-x86_64/pgdg-centos94-9.4-1.noarch.rpm

vi /etc/yum.repos.d/centos.repo
add the following lines
[centos-6-base]
name=CentOS-$releasever - Base
mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=os
baseurl=http://mirror.centos.org/centos/$releasever/os/$basearch/
enabled=1


step 2:- Install Postgresql required packages:-
#yum install postgresql94-server postgresql94-contrib
Incase if the above command gives error regarding the public key, we can use the following command
cd /etc/pki/rpm-gpg
rpm --import RPM-GPG-KEY-CentOS-6

#yum install postgresql94-server

# service postgresql-9.4 initdb
or
# service postgresql initdb

# chkconfig postgresql-9.4 on

Step 3:-Start postgresql:-
[root@infosystem ~]# service postgresql-9.4 start
Starting postgresql service: [  OK  ]

Check status of postgresql:-
[root@infosystem ~]# service postgresql status
postmaster (pid  4260) is running...

Step 4:- Log into postgre
#su - postgres
$ psql

To create a  user:-
postgres=# CREATE USER soumya WITH password 'redhat';
CREATE ROLE

To check  version:-
SELECT version ();

To change postgres password:-
postgres=# \password postgres
Enter new password:
Enter it again:

To create a table:-
postgres=#CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

To view created tables:-
postgres=#\d

                  List of relations
 Schema |          Name          |   Type   |  Owner
--------+------------------------+----------+----------
 public | article                | table    | postgres
 public | article_article_id_seq | sequence | postgres
 public | company                | table    | postgres
 public | department             | table    | postgres
(4 rows)


To create a database:-
postgres=#  CREATE DATABASE mydb WITH OWNER soumya;
CREATE DATABASE

To see all databases:-

postgres=# \l
List of databases
   Name    |  Owner   | Encoding |  Collation  |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 mydb      | ramesh   | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 mydb1     | soumya   | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
                                                             : postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
                                                             : postgres=CTc/postgres

To drop a database:-
postgres=# drop database mydb;
DROP DATABASE

To backup all database:-
pg_dumpall > all.sql

To verify the backup:-
bash-4.1$ grep "^[\]connect" all.sql
\connect postgres
\connect mydb1
\connect postgres
\connect template1

To create a table under a schema:-
create schema soumya;
set search_path to soumya;
create table foo (id int);

To backup a particular db:-
pg_dump kmi > kmi.sql[mydb1 is db name for  which i'm taking the backup]

SELECT * FROM pg_stat_activity WHERE datname='mydb1';


Restore all the postgres databases

$ psql -f alldb.sql

Restore a single postgres table:-

$ psql -f kmi.sql kmi



Please share your ideas and opinions about this topic.

If you like this post, then please share with others.
Please subscribe on email for every updates on mail.