Setting up PostgreSQL in RHEL/CentOS 6.x

After you've installed PostgreSQL and its required dependencies in your CentOS server, configure it using the following steps.

1. Add the user postgres to your server. You might have to change your server's user and group policy settings in the file "/etc/adduser.conf":

adduser <options> postgres;

2. Create the directory data to store datafiles of the PostgreSQL database:

mkdir -p /usr/local/pgsql/data;

3. Change ownership of the directory data from root to postgres:

chown <options> postgres /usr/local/pgsql/data;

4. Log in as the user postgres:

su - postgres;

5. Create a default PostgreSQL database using datafiles to be stored in the directory "/usr/local/pgsql/data":

initdb <options> -D /usr/local/pgsql/data;

6. Start the PostgreSQL database service:

postgres <options> -D /usr/local/pgsql/data;

7.  Create a new default PostgreSQL database:

createdb <options> <db name>;

9. Automatically start your PostgreSQL service on boot up:

update-rc.d  postgresql <options>;

10. Edit the file "/etc/hosts.allow" that's the hosts access control list for allowing access to services on your server from specific hostnames, IP addresses, networks, and FQDNs:

<service or wildcard>: <hostname> <ip address>/<subnet mask> <fqdn>

11. Edit the file "/etc/hosts.deny" that's the hosts access control list for denying access to services on your server from specific hostnames, IP addresses, networks, and FQDNs:

<service or wildcard>: <hostname> <ip address>/<subnet mask> <fqdn>

12.  Allow incoming and outgoing client connections to your PostgreSQL service through your firewall:

iptables -A INPUT -i <interface> -p tcp --dport 5432 -j ACCEPT

iptables -A OUTPUT -o <interface> -p tcp --sport 5432 -j ACCEPT

Do you have a suggestion about how to improve this blog? Let's talk about it. Contact me at David.Brenner.Jr@Gmail.com or 720-584-5229.