Install and configure PostgreSQL

PostgreSQL, often simply "Postgres", is a free and open-source object-relational database management system (ORDBMS) with an emphasis on extensibility and standards-compliance. PostgreSQL implements the majority of the SQL 2011 standard,is ACID-compliant and transactional (including most DDL statements) avoiding locking issues using multiversion concurrency control (MVCC), provides immunity to dirty reads and full serializability; handles complex SQL queries using many indexing methods that are not available in other databases; has updateable views and materialized views, triggers, foreign keys; supports functions and stored procedures, and other expandability, and has a large number of extensions written by third parties.

 

Configure your YUM repository

Locate and edit your distributions .repo file, located:
On Fedora: /etc/yum.repos.d/fedora.repo and /etc/yum.repos.d/fedora-updates.repo, [fedora] sections
On CentOS: /etc/yum.repos.d/CentOS-Base.repo, [base] and [updates] sections
On Red Hat: /etc/yum/pluginconf.d/rhnplugin.conf [main] section
To the section(s) identified above, you need to append a line:
exclude=postgresql*

Download and install PGDG RPM file
A PGDG file is available for each distribution/architecture/database version combination.
Browse http://yum.postgresql.org and find your correct RPM.
For example, to install PostgreSQL 9.3 on CentOS 6 64-bit:

curl -O http://yum.postgresql.org/9.3/redhat/rhel-6-x86_64/pgdg-centos93-9.3-1.noarch.rpm

Now install RPM distribution:

rpm -ivh pgdg-centos93-9.3-1.noarch.rpm

Install PostgreSQL

To list available packages:
yum list postgres*
For example, to install a basic PostgreSQL 9.3 server:

yum install postgresql93-server

# su - postgres
$ psql
psql (9.2.4)
Type "help" for help.
postgres=# help
You are using psql, the command-line interface to PostgreSQL.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

Create a schema called test in the default database called postgres

postgres=# CREATE SCHEMA test;

Create a role (user) with password

postgres=# CREATE USER xxx PASSWORD 'yyy';

Grant privileges (like the ability to create tables) on new schema to new role

postgres=# GRANT ALL ON SCHEMA test TO xxx;

Grant privileges (like the ability to insert) to tables in the new schema to the new role

postgres=# GRANT ALL ON ALL TABLES IN SCHEMA test TO xxx;

Disconnect
postgres=# \q