POSTGRESQL DATABASE

POSTGRESQL DATABASE INSTALLATION

POSTGRESQL or simply Postgres is an object-based Relational Database Management system.Its primary function is to store data in a secure way and return data in response to the requests from websites and applications.

In this tutorial, We are going to install PostgreSQL on Centos 6.x and basic way of using it.

Before installing ,Add the below line of command to /etc/yum.repos.d/Centos-Base.repo as shown below:

# vi /etc/yum.repos.d/CentOS-Base.repo

Under [base] and [updates] sections,

add, exclude=postgresql*

Install PGDG RPM File:

A PGDG file is available for each distribution/architecture/database version combination.

Browse https://yum.postgresql.org/repopackages.php and find your correct RPM.

#wget https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-6-x86_64/pgdg-centos96-9.6-3.noarch.rpm

Install Postgresql

To list available packages

#yum list postgresql*

To install Postgresql 9.6 server

#yum install postgresql96-server -y

Other packages can be installed according to your needs.

Post Installation Commands

After installing the packages, a database needs to be initialized and configured.

In the commands below, the value of <name> will vary depending on the version of PostgreSQL used.

For PostgreSQL version 9.0 and above, the <name> includes the major.minor version of PostgreSQL, e.g., postgresql-9.4

Data Directory

The PostgreSQL data directory contains all of the data files for the database. The variable PGDATA is used to reference this directory.

Default data directory for Postgresql:

/var/lib/pgsql/<name>/data

Example

/var/lib/pgsql/9.6/data

Initialize database in PGDATA

The command needs only once to initialize the database in PGDATA.

On centos 6.x:

#service postgresql-9.6 initdb

If you want  POSTGRESQL to start automatically when the OS starts.

#chkconfig postgresql-9.6 on

First Step After Installation

Login/Connect to database as root:

#su – postgresql

$psql

postgres=#

TO CREATE A USER WITH PASSWORD:

postgres=#create user username password ‘password’;

Create a SCHEMA called TEST in default DB called POSTGRES:

postgres=#create SCHEMA test;

Grant privileges on new-schema to new-user:

postgres=#grant all on schema TEST to username;

Grant privileges to tables in new-schema to new-user:

postgres=#grant all on all tables in SCHEMA test to username;

To create a TABLE in SCHEMA:

postgres=#create table test.test (coltest varchar(20));

Insert a single record into new table:

postgres=#insert into test.test (coltest) values (‘It works!’);

First SELECT from table:

postgres=#select * from test.test;

Drop test table:

postgres=#drop table test.test;

That’s it.Thanks for reading this tutorial.