Express setup for worlds most advanced PostgreSQL 9.3 server on a CentOS-6.5 x64 instance

Setup a PostgreSQL 9.3 database on CentOS 6.5 Server


Worlds most advanced database is a killer punchline though.

Assuming you are a root user and looking for just the command that will get you through the installation.
  - CentOS 6.5 x64 ISO minimal installation 
    Download Link: 
  - I prefer to install most of the dev dependencies beforehand via 
    $ sudo yum groupinstall 'Development Tools'

1. Create a User
   $ adduser postgres
   $ passwd postgres

2. Add this user to the sudoers list
   $ vim /etc/sudoers
   postgres ALL=(ALL)    ALL
3. Get the postgres repo for installation
   $ yum localinstall
     yum localinstall
   $ yum list postgres*
   $ yum install postgresql93-server

4. Data folder is at the location
   $ ls /var/lib/pgsql/9.3/
5. Make this run as a service 
   $ service postgresql-9.3 initdb
     /usr/pgsql-9.3/bin/pg_ctl init -D /var/lib/pgsql/9.3/data/ -o --pwprompt -U postgres
     /usr/pgsql-9.3/bin/initdb --pgdata=/var/lib/pgsql/9.3/data/ -W -U postgres
   $ chkconfig postgresql-9.3 on
6. Control commands for the server
   $ service postgresql-9.3 start
     /usr/pgsql-9.3/bin/pg_ctl -D /var/lib/pgsql/9.3/data -l logfile start
   $ service postgresql-9.3 stop
     /usr/pgsql-9.3/bin/pg_ctl -D /var/lib/pgsql/9.3/data -l logfile stop
   $ service postgresql-9.3 restart
7. Change to user postgres for accessing the database through psql client
   $ su postgres
   $ psql

8. Modify the IPv4 access to this machine,
   by editing "/var/lib/pgsql/9.3/data/pg_hba.conf"

   host    all             all               md5
   host    all             all             md5
   host    all             all                  ident

9. Modify the service host to listen for all external clients
   by editing "/var/lib/pgsql/9.3/data/postgresql.conf"

   listen_addresses = '*'

10. Update the iptables policy for database server access
   $ vim /etc/sysconfig/iptables
     -A INPUT -m state --state NEW -m tcp -p tcp --dport 5432 -j ACCEPT
   $ service iptables restart
   $ service iptables restart 
   Diagnostics for port forwarding. The DB server is unreachable hence switching the firewall OFF
   $ netstat -nlp | grep 5432
   $ sudo service iptables save
   $ sudo service iptables stop
   $ chkconfig iptables off

11. Update the password of the default user postgres. 
   This is done by logging in via the localhost psql client as above via default postgres user. 
   postgres# GRANT ALL privileges ON database postgres TO postgres; 
   postgres# ALTER USER postgres WITH password 'postgres'; 

   This will allow password based authentication via user postgres. 
   Verification can be done by attempting access as below: 
   $ psql -h -U postgres -d postgres

Direct Reference :

Universal Database Manager:

In case the YUM repository is not set properly use the relevant EPEL repo 

## RHEL/CentOS 7 64-Bit ##
# wget
# rpm -ivh epel-release-7-5.noarch.rpm
## RHEL/CentOS 6 64-Bit ##
# wget
# rpm -ivh epel-release-6-8.noarch.rpm




A quick way to have a portable version running on windows locally is mentioned below,


Extract the archive into a folder at “C:/Dev/pgsql”

Init: "C:/Dev/pgsql/bin\pg_ctl" -D  "C:/Dev/pgsql/data" initdb
Start: "C:/Dev/pgsql/bin\pg_ctl" -D "C:/Dev/pgsql/data" -l logfile start
Stop: "C:/Dev/pgsql/bin\pg_ctl" -D "C:/Dev/pgsql/data" -l logfile stop
Connect: "C:/Dev/pgsql/bin\psql" -U postgres

You can initially connect to database postgres, and later use the following to have a new user created with privilege on a db

CREATE USER postgres WITH PASSWORD 'postgres';