PostgreSQL on CentOS

It is not really a possibility to be Windows-only developer these days. Chances are that you will end up connecting to one Linux server or another. And best way to prepare is to have some test environment ready. For cold days there is nothing better than once nice database server.

For this particular installation I (again) opted for minimal install of CentOS 6.3. I will assume that it is installed (just bunch of Nexts) and your network interfaces are already set (e.g. DHCP).

First step after this is actually installing PostgreSQL:

yum install postgresql postgresql-server
 Complete!

Next step is initializing database:

service postgresql initdb
 Initializing database:                                     [  OK  ]

Start service and basic setup is done:

chkconfig postgresql on
service postgresql start
 Starting postgresql service:                               [  OK  ]

Next step is allowing TCP/IP connections to be made. For that we need to edit postgresql.conf:

su - postgres
vi /var/lib/pgsql/data/postgresql.conf

There we find listen_addresses and port parameters and un-comment them (along with small change from all to *):

listen_addresses = '*'
port = 5432

While we are at it, we might add all hosts as friends in pg_hba.conf (yes, don’t do this in production):

vi /var/lib/pgsql/data/pg_hba.conf

Add following line at the bottom:

host    all         all         0.0.0.0/0             trust

Finish up editing and restart service

exit
 logout
/etc/init.d/postgresql restart
 Stopping postgresql service:                               [  OK  ]
 Starting postgresql service:                               [  OK  ]

Quick check with psql is in order (notice that \q is used for exit):

psql -h 192.168.56.101 -U postgres -d postgres
 psql (8.4.13)
 Type "help" for help.
\q

If external connections are needed, we must handle firewall. And easiest way to do this is disabling it. For production environment this is a big no-no. For simple testing of virtual machine it will suffice:

/etc/init.d/iptables stop
chkconfig iptables off

And with this we are ready to accept clients.