Howto Install PostgreSQL 8.4 Database Server on CentOS, Fedora, Red Hat

This is quick guide howto install PostgreSQL 8.4 (current stable 8.4.2) database server on CentOS, Fedora and Red Hat. Fedora 12 has PostgreSQL 8.4 database server as default so extra repositories is not needed on Fedora 12. The following commands run as root and postgres user, so “su -” or “sudo -i” first.

Install PostgreSQL 8.4 Database Server

Install PostgreSQL repository:

## CentOS
rpm -Uvh http://yum.pgsqlrpms.org/reporpms/8.4/pgdg-centos-8.4-1.noarch.rpm

## Fedora (Not needed on Fedora 12)
rpm -Uvh http://yum.pgsqlrpms.org/reporpms/8.4/pgdg-fedora-8.4-1.noarch.rpm

## Red Hat
rpm -Uvh http://yum.pgsqlrpms.org/reporpms/8.4/pgdg-redhat-8.4-1.noarch.rpm
Install postgresql and postgresql-server packages:

yum install postgresql postgresql-server
Configure PostgreSQL 8.4 Database Server

Initialize the cluster first with initdb command:

service postgresql initdb
## OR ##
/etc/init.d/postgresql initdb
Edit /var/lib/pgsql/data/postgresql.conf file:

nano -w /var/lib/pgsql/data/postgresql.conf
Set PostgreSQL server to listen all addresses. Add/Uncomment/Edit following lines:

listen_addressses = '*'
port = 5432
Edit /var/lib/pgsql/data/pg_hba.conf file:

nano -w /var/lib/pgsql/data/pg_hba.conf
Add (example) your local network with md5 passwords:

# Local networks
host all all xx.xx.xx.xx/xx md5
# Example
host all all 10.20.4.0/24 md5
Start/Restart PostgreSQL Server:

service postgresql start
## OR ##
/etc/init.d/postgresql start
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';
Open PostgreSQL Port (5432) on Iptables Firewall (as root user again)

Edit /etc/sysconfig/iptables file:

nano -w /etc/sysconfig/iptables
Add following line before COMMIT:

-A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 5432 -j ACCEPT
Restart Iptables Firewall:

service iptables restart
## OR ##
/etc/init.d/iptables restart
Test remote connection:

psql -h dbserver -U testuser test