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
Leave a Reply