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:
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
To list available packages:
yum list postgres*
For example, to install a basic PostgreSQL 9.3 server:
yum install postgresql93-server
# su - postgres
Type "help" for 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;