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.
Article will continue after the ad
New features in PostgreSQL 9.1:
PostgreSQL moving beyond the traditional relational-database feature set with new, ground-breaking functionality that is unique to PostgreSQL. The streaming replication feature introduced in release 9.0 is significantly enhanced by adding a synchronous-replication option, streaming backups, and monitoring improvements. Major enhancements include:
- Allow synchronous replication
- Add support for foreign tables
- Add per-column collation support
- Add extensions which simplify packaging of additions to PostgreSQL
- Add a true seriaizable isolation level
- Support unlogged tables using the UNLOGGED option in CREATE TABLE
- Allow data-modification commands (INSERT/UPDATE/DELETE) in WITH clauses
- Add nearest-neighbor (order-by-operator) searching to GiST indexes
- Add a SECURITY LABEL command and support for SELinux permissions control
- Update the PL/Python server-side language
Open Terminal ( Applications —> System Tools —> Terminal).
Switch to root user.
[raj@geeksite~/]$ 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.
### 32 bit ###
[root@geeksite~/]# rpm -Uvh http://yum.postgresql.org/9.1/redhat/rhel-6-i386/pgdg-centos91-9.1-4.noarch.rpm
### 64 bit ###
[root@geeksite~/]# rpm -Uvh http://yum.postgresql.org/9.1/redhat/rhel-6-x86_64/pgdg-centos91-9.1-4.noarch.rpm
CentOS and Redhat repos contains the older version of PostgreSQL packages which will not be required any more, so just exclude the PostgreSQL packages by adding following line in proper sections.
On CentOS, edit /etc/yum.repos.d/CentOS-Base.repo, [base] and [updates] sections.
On Red Hat, edit edit /etc/yum/pluginconf.d/rhnplugin.conf [main] section
Example ( in CentOS /etc/yum.repos.d/CentOS-Base.repo, [base] section):
[base] name=CentOS-$releasever - Base mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=os #baseurl=http://mirror.centos.org/centos/$releasever/os/$basearch/ gpgcheck=1 gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-6 exclude=postgresql*
Install PostgreSQL 9.1.3.
[root@geeksite~/]# yum install postgresql91 postgresql91-server postgresql91-libs postgresql91-contrib postgresql91-devel
Configuring PostgreSQL 9.1.3 server:
Initialize the PostgreSQL.
[root@geeksite~/]# service postgresql-9.1 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@geeksite~/]# vi /var/lib/pgsql/9.1/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.1/data/pg_hba.conf .
[root@geeksite~/]# vi /var/lib/pgsql/9.1/data/pg_hba.conf
Add the following line according to your network configuration with md5 password authentication (Enable remote access of database).
# Local networks host all all xx.xx.xx.xx/xx md5 # Example host all all 192.168.0.0/24 md5 host all all 127.0.0.1/32 md5
Restart the PostgreSQL server.
[root@geeksite~/]# service postgresql-9.1 restart
Confirm the PostgreSQL listening.
[root@geekSite ~/]# netstat -ant | grep 5432 tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN tcp6 0 0 :::5432 :::* LISTEN
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 geekdbserver -d test -U raj
The command line administration will be good for the people who has full experience on PostgreSQL, but for the beginner pgAdmin will be best option to manage the databases. Install it by issue the following command.
[root@geeksite:~]# yum install pgadmin3_91
[root@geeksite:~]# pgadmin3 or pgadmin3_91
Connect to the database server using pgAdmin.
Now you can manage the databases through the GUI-administration console.