PostgreSQL

Posted: December 5, 2014 in Databases

PostgreSQL is a open source object-relational, highly scalable, SQL compliant database management system. PostgreSQL is developed at the University of California at Berkeley Computer Science Department.

Step 1: Add PostgreSQL Yum Repository
CentOS/RHEL 6, 64-Bit:
# rpm -Uvh http://yum.postgresql.org/9.3/redhat/rhel-6-x86_64/pgdg-redhat93-9.3-1.noarch.rpm
Step 2: Install PostgreSQL Server
# yum install postgresql93-server postgresql93
Step 3: Initialize PGDATA
After installing PostgreSQL server, It’s required to initialize it before using first time. To initialize database use below command
# /etc/init.d/postgresql-9.3 initdb
Initializing database: [ OK ]
PostgreSQL data directory Path: /var/lib/pgsql/9.3/data/
Step 4: Start PostgreSQL Server
[root@fenige_db1 ~]# service postgresql-9.3 start
Starting postgresql-9.3 service: [ OK ]
# chkconfig postgresql-9.3 on
Step 5: Verify PostgreSQL Installation
# su – postgres
Use psql command to access PostgreSQL prompt with admin privileges.
[root@localhost ~]# su – postgres
-bash-4.1$ psql
psql (9.3.5)
Type “help” for help.
-bash-4.1$ createuser redmoon
-bash-4.1$ createdb mydb
-bash-4.1$ psql
psql (9.3.5)
Type “help” for help.
postgres=# alter user redmoon with encrypted password ‘centos’;
ALTER ROLE
postgres=# grant all privileges on database mydb to redmoon;
GRANT
[redmoon@fenige_db1 ~]$ psql -d mydb -U redmoon -W
Password for user redmoon:
psql (9.3.5)
Type “help” for help.
mydb=>
I have created moon user and created mydb databases. And provide previleges to moon
Also created the username master and blaster. And able to access the databases.
For example:
[root@localhost]# su – master
[master@localhost ]$ psql -d postgres
psql (9.3.5)
Type “help” for help.

I have set the necessary roles to the users master and blaster.

postgres=# ALTER USER master WITH SUPERUSER;
ALTER ROLE

postgres=# ALTER USER blaster WITH SUPERUSER;
ALTER ROLE

postgres=# ALTER ROLE master CREATEROLE CREATEDB;
ALTER ROLE

postgres=# ALTER ROLE blaster CREATEROLE CREATEDB;
ALTER ROLE

postgres=# \du
List of roles
Role name | Attributes | Member of
———–+————————————————+———–
master | Superuser, Create role, Create DB | {}
blaster | Superuser, Create role, Create DB | {}
postgres | Superuser, Create role, Create DB, Replication | {}
moon | Create role, Create DB | {}

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s