PostgreSQL is a database server. It is an object-relational database management system and it’s main purpose is to store data. PostgreSQL calls itself “The world’s most advanced open-source database”. It is a fact that MySQL and PostgreSQL are two most popular open-source relation database management systems (RDMS). It is up to you to choose the one you like the most.

In this how to we will learn how to install PostgreSQL on CentOS 6 and edit basic configuration.

Let’s Install PostgreSQL 9.3 on CentOS 6!

1. Disable SELinux and IPtables

For this guide SELinux and iptables were disabled and CentOS 6 was fully updated!

2. Install PostgreSQL Repository

Install PostgreSQL repository for the desired version of PostgreSQL and CentOS. In this how to we will install the latest stable version of PostgreSQL 9.3 for CentOS 6 64bit. All available repositories can be found HERE.

[root@centos1 ~]# rpm -ivh http://yum.postgresql.org/9.3/redhat/rhel-6-x86_64/pgdg-centos93-9.3-1.noarch.rpm

3. Install PostgreSQL Server and Dependencies

[root@centos1 ~]# yum install postgresql93-server -y

4. Initialize PostgreSQL Database

[root@centos1 ~]# /etc/init.d/postgresql-9.3 initdb

5. Start PostgreSQL and Make it Start at Boot

[root@centos1 ~]# /etc/init.d/postgresql-9.3 start
[root@centos1 ~]# chkconfig postgresql-9.3 on

6. Edit Basic Configuration

We will explain the basic configuration of PostgreSQL in this how to. Detailed explanation will be provided in a separate how to.

By default we install PostgreSQL at /var/lib/pgsql location and there are two configuration files we need to know about:

• PostgreSQL Configuration File (/var/lib/pgsql/data/postgresql.conf)

PostgreSQL configuration file is the main configuration file. Here we can enable/disable all of the functionalities PostgreSQL provides. We can also edit the basic configuration parameters like data directory location, client authentication configuration file location, PostgreSQL listen address and port to listen on, maximum connections to the database, client authentication configuration and alot more…

• PostgreSQL Client Authentication Configuration File (/var/lib/pgsql/data/pg_hba.conf)

This is where client authentication for PostgreSQL is controlled. This file contains records (lines) which specify a connection type and consist of a database name, a user name, and the authentication method. The first record with a matching connection type is used to perform authentication.

Here is some basic configuration guide with explanations before starting our PostgreSQL instance for the first time:

/var/lib/pgsql/postgresql.conf

#data_directory = 'ConfigDir'  # If we want to change the data directory path, uncomment this line and enter full path to the data directory - this change requires PostgreSQL restart.
#hba_file = 'ConfigDir/pg_hba.conf'  # If we want to change thehba file directory path, uncomment this line and enter full path to the hba file - this change requires PostgreSQL restart.
#listen_addresses = 'localhost'  # We uncommented this line and insert an IP address of our server. By default PostgreSQL will only listen on localhost. Reconfigure this is needed - this change requires PostgreSQL restart.
#port = 5432  # If we want to change the port PostgreSQL listens on, uncomment this line and enter the desired number - this change requires PostgreSQL restart.
max_connections = 100  # This is the value of max connection PostgreSQL will accept. Raise this value if needed - this change requires PostgreSQL restart.

/var/lib/pgsql/pg_hba.conf

# TYPE DATABASE USER CIDR-ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all ident  # This line allows local socke connections to the database. Ident authentication can only be used on TCP/IP connections. When specified for local connections, peer authentication will be used instead - this change requires PostgreSQL restart.
# IPv4 local connections: 
host all all 127.0.0.1/32 ident  # This line only allows connections to the database from localhost interface - this change requires PostgreSQL restart.
host all all 192.168.1.0/24 trust # Add this line to allow connections to the database from your network - please change network settings accordingly - this change requires PostgreSQL restart.
# IPv6 local connections:
host all all ::1/128 ident  # Comment out this line to disable PostgreSQL to listen on IPv6 - this change requires PostgreSQL restart.

This is the end of “Install PostgreSQL 9.3 on CentOS 6” guide.

Another post on  how to manage PostgreSQL – create users, grant privileges, create databases with special tablespaces and also advanced feature configuration on PostgreSQL comming soon, so stay tuned!