Install PostgreSQL 9.2 on Fedora, CentOS/RHEL

This is guide, howto install PostgreSQL 9.2 database server on Fedora, CentOS/RHEL. This guide uses PostgreSQL own YUM repos, which are always up-to-date and stable releases are available instantly.
[alert type=”warning”]Note: If you are upgrading PostgresSQL (from earlier version), then make sure that you backup (dump and copy) your database and configs.[/alert]

1. Install PostgreSQL 9.2.1 Database Server on Fedora, CentOS/RHEL

Change root user

su -
## OR ##
sudo -i

Exclude Fedora, CentOS, RHEL own PostgreSQL Packages
This is important step to get PostgreSQL repository working properly. Exclude PostgreSQL packages from the repository of the distro.
Fedora
Add exclude to /etc/yum.repos.d/fedora.repo file [fedora] section:

[fedora]
...
exclude=postgresql*

Add exclude to /etc/yum.repos.d/fedora-updates.repo file [updates] section:

[updates]
...
exclude=postgresql*

CentOS
Add exclude to /etc/yum.repos.d/CentOS-Base.repo file [base] and [updates] sections:

[base]
...
exclude=postgresql*

[updates]
...
exclude=postgresql*

Red Hat (RHEL)
Add exclude to /etc/yum/pluginconf.d/rhnplugin.conf file [main] section:

[main]
...
exclude=postgresql*

Install PostgreSQL 9.2 Repository

Fedora

## Fedora 18 - i686 - 32-bit ##
rpm -Uvh http://yum.postgresql.org/9.2/fedora/fedora-18-i386/pgdg-fedora92-9.2-6.noarch.rpm

## Fedora 18 - x86_64 - 64-bit ##
rpm -Uvh http://yum.postgresql.org/9.2/fedora/fedora-18-x86_64/pgdg-fedora92-9.2-6.noarch.rpm

## Fedora 17 - i686 - 32-bit ##
rpm -Uvh http://yum.postgresql.org/9.2/fedora/fedora-17-i386/pgdg-fedora92-9.2-6.noarch.rpm

## Fedora 17 - x86_64 - 64-bit ##
rpm -Uvh http://yum.postgresql.org/9.2/fedora/fedora-17-x86_64/pgdg-fedora92-9.2-6.noarch.rpm

## Fedora 16 - i686 - 32-bit ##
rpm -Uvh http://yum.postgresql.org/9.2/fedora/fedora-16-i386/pgdg-fedora92-9.2-6.noarch.rpm

## Fedora 16 - x86_64 - 64-bit ##
rpm -Uvh http://yum.postgresql.org/9.2/fedora/fedora-16-x86_64/pgdg-fedora92-9.2-6.noarch.rpm

CentOS

## CentOS 6 - i386 - 32-bit ##
rpm -Uvh http://yum.postgresql.org/9.2/redhat/rhel-6-i386/pgdg-centos92-9.2-6.noarch.rpm

## CentOS 6 - x86_64 - 64-bit ##
rpm -Uvh http://yum.postgresql.org/9.2/redhat/rhel-6-x86_64/pgdg-centos92-9.2-6.noarch.rpm

## CentOS 5 - i386 - 32-bit ##
rpm -Uvh http://yum.postgresql.org/9.2/redhat/rhel-5-i386/pgdg-centos92-9.2-6.noarch.rpm

## CentOS 5 - x86_64 - 64-bit ##
rpm -Uvh http://yum.postgresql.org/9.2/redhat/rhel-5-x86_64/pgdg-centos92-9.2-6.noarch.rpm

Red Hat (RHEL)

## Red Hat Enterprise Linux 6 - i386 - 32-bit ##
rpm -Uvh http://yum.postgresql.org/9.2/redhat/rhel-6-i386/pgdg-redhat92-9.2-7.noarch.rpm

## Red Hat Enterprise Linux 6 - x86_64 - 64-bit ##
rpm -Uvh http://yum.postgresql.org/9.2/redhat/rhel-6-x86_64/pgdg-redhat92-9.2-7.noarch.rpm

## Red Hat Enterprise Linux 5 - i386 - 32-bit ##
rpm -Uvh http://yum.postgresql.org/9.2/redhat/rhel-5-i386/pgdg-redhat92-9.2-7.noarch.rpm

## Red Hat Enterprise Linux 5 - x86_64 - 64-bit ##
rpm -Uvh http://yum.postgresql.org/9.2/redhat/rhel-5-x86_64/pgdg-redhat92-9.2-7.noarch.rpm

Install PostgreSQL 9.2 with YUM

yum install postgresql92 postgresql92-server postgresql92-contrib

Configure PostgreSQL 9.2

Initialize Cluster with initdb Command

Here is multiple alternatives howto do this (like service postgresql-9.x initdb, /etc/init.d/postgresql-9.x initdb) and postgresql-setup initdb, so that’s why I use here universal PostgreSQL initdb method, which should work with Fedora, CentOS/RHEL:

su - postgres -c /usr/pgsql-9.2/bin/initdb

Set PostgreSQL Server to Listen Addresses and Set Port

Open /var/lib/pgsql/9.2/data/postgresql.conf file, and add/uncomment/modify following:

listen_addresses = '*'
port = 5432

If you want just localhost setup, then use following:

listen_addresses = 'localhost'
port = 5432

Or if you want use specific ip, then use following:

listen_addresses = '192.168.1.33'
port = 5432

Set PostgreSQL Permissions

Modify PostgreSQL /var/lib/pgsql/9.2/data/pg_hba.conf (host-based authentication) file:

# Local networks
host              all           all                    xx.xx.xx.xx/xx                    md5
# Example
host              all           all                    10.20.4.0/24                        md5
# Example 2
host              test         testuser          127.0.0.1/32                          md5

You can find more examples and full guide from PostgreSQL pg_hba.conf manual.

Start PostgreSQL Server and Autostart PostgreSQL on Boot

Fedora

## Start PostgreSQL 9.2 ##
systemctl start postgresql-9.2.service

## Start PostgreSQL 9.2 on every boot ##
systemctl enable postgresql-9.2.service

CentOS/RHEL

## Start PostgreSQL 9.2 ##
service postgresql-9.2 start
## OR ##
/etc/init.d/postgresql-9.2 start

## Start PostgreSQL 9.2 on every boot ##
chkconfig --levels 235 postgresql-9.2 on

Create Test Database and Create New User

Change to postgres user

su postgres

Create test database (as postgres user)

createdb test

Login test database (as postgres user)

psql test

Create New “testuser” Role with Superuser and Password

CREATE ROLE testuser WITH SUPERUSER LOGIN PASSWORD 'test';

Test Connection from localhost (as Normal Linux User)

psql -h localhost -U testuser test

Enable Remote Connections to PostgreSQL Server –> Open PostgreSQL Port (5432) on Iptables Firewall

Edit /etc/sysconfig/iptables file

nano /etc/sysconfig/iptables

Add following line before -A INPUT -j REJECT…

-A INPUT -m state --state NEW -m tcp -p tcp --dport 5432 -j ACCEPT

Restart Iptables Firewall
Fedora

systemctl restart iptables.service

CentOS/RHEL

service iptables restart
## OR ##
/etc/init.d/iptables restart

Test remote connection

psql -h dbserver_name_or_ip_address -U testuser -W test