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.

No comments:

Post a Comment