PostgreSQL is an object-relational database management system (ORDBMS) available for many platforms including Linux, FreeBSD, Solaris, Microsoft Windows and Mac OS X. It is released under the PostgreSQL License, which is an MIT-style license, and is thus free and open source software. PostgreSQL is developed by the PostgreSQL Global Development Group, consisting of a handful of community volunteers employed and supervised by companies such as Red Hat and EnterpriseDB. It implements the majority of the SQL:2008 standard, is ACID-compliant, is fully transactional (including all DDL statements), has extensible data types, operators, and indexes, and has a large number of extensions written by third parties.
The vast majority of Linux distributions have PostgreSQL available in supplied packages. Mac OS X starting with Lion has PostgreSQL server as its standard default database in the server edition and PostgreSQL client tools in the desktop edition.
New features in PostgreSQL 9.3:
PostgreSQL moving beyond the traditional relational-database feature set with new, ground-breaking functionality that is unique to PostgreSQL. Major enhancements include:
- Make simple views auto-updatable
- Add many features for the JSON data type, including operators and functions to extract elements from JSON values
- Implement SQL-standard LATERAL option for FROM-clause subqueries and function calls
- Allow foreign data wrappers to support writes (inserts/updates/deletes) on foreign tables
- Add a Postgres foreign data wrapper to allow access to other Postgres servers
- Add support for event triggers
- Add optional ability to checksum data pages and report corruption
- Prevent non-key-field row updates from blocking foreign key checks
- Greatly reduce System V shared memory requirements
Here is the tutorial about installing PostgreSQL 9.3.1 on CentOS 7 / RHEL 7.
Open Terminal ( Applications —> System Tools —> Terminal).
Switch to root user.
[raj@itzgeek~/]$ su -
Install PosgreSQL 9.1.3:
PostgreSQL publishes rpm packages for all Linux platforms, their packages are generally fresher than those in the other repository. We need to add the repository on our machine by installing repo rpm.
[root@itzgeek~/]# rpm -Uvh http://yum.postgresql.org/9.3/redhat/rhel-7-x86_64/pgdg-centos93-9.3-1.noarch.rpm
Install PostgreSQL 9.3.1 , enable only PostgreSQL repository to install.
[root@itzgeek~/]# yum install postgresql93 postgresql93-server postgresql93-libs postgresql93-contrib postgresql93-devel --disablerepo=* --enablerepo=pgdg93
Configuring PostgreSQL 9.3.1 server:
Initialize the PostgreSQL.
[root@itzgeek~/]# /usr/pgsql-9.3/bin/postgresql93-setup initdb
PostgreSQL normally listens on the localhosts only, if would you like to enable the PostgreSQL to listen on all ip addresses; edit the /var/lib/pgsql/9.1/data/postgresql.conf .
[root@itzgeek~/]# vi /var/lib/pgsql/9.3/data/postgresql.conf
Go to Connections and Communications section, find the “listen_address” variable. Uncomment the “listen_addresses” and place “*” instead of “localhost”
#listen_addresses = "localhost"
listen_addresses = "*"
Add your network to access database remotely; Edit /var/lib/pgsql/9.3/data/pg_hba.conf.
[root@itzgeek~/]# vi /var/lib/pgsql/9.3/data/pg_hba.conf
Add the following line according to your network configuration with md5 password authentication (Enable remote access of database).
# IPv4 local connections: host all all 127.0.0.1/32 md5 host all all 126.96.36.199/24 md5 # IPv6 local connections: host all all ::1/128 md5
Restart the PostgreSQL server.
[root@itzgeek~/]# systemctl start postgresql-9.3.service
Confirm the PostgreSQL listening.
[root@itzgeek ~/]# netstat -antup | grep 5432 tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN 39620/postgres tcp6 0 0 :::5432 :::* LISTEN 39620/postgres
Auto start the service on startup.
[root@itzgeek~/]# systemctl enable postgresql-9.3.service
Login as postgres user.
[root@geeksite~/]$ su -l postgres
create the database called “test”
-bash-4.2$ createdb test
Login into the database.
-bash-4.2$ psql test
Create a new user called “raj” to manage the databases.
test=# CREATE USER raj WITH SUPERUSER LOGIN PASSWORD 'raj';
Login with the superuser.
sam@geeksite~/$ psql -h localhost -d test -U raj