Install and Configure PostgreSQL on Centos

By Harikrishna Doredla

Here is how you go with installation of Postgres 9.3 on Centos 6.5

  • Check Centos server version.
    root@hadoop3 init.d]# cat /etc/redhat-release — shows the server version
    CentOS release 6.5 (Final)
    [root@hadoop3 init.d]#
  1. Edit the repo file – /etc/yum.repos.d/CentOS-Base.repo, [base] and [updates] sections
    In the base and updates sections append a line as below:
    exclude=postgresql*
  2. rpm -Uvh http://yum.postgresql.org/9.3/redhat/rhel-6-x86_64/pgdg-centos93-9.3-1.noarch.rpm
  3. yum list postgres*
  4. yum install postgresql93-server postgresql93
  5. service postgresql-9.3 initdb
  6. service postgresql-9.3 status
  7. chkconfig –add postgresql-9.3
  8. chkconfig postgresql-9.3 on
  9. service postgresql-9.3 start
  10. su – postgres
  11. psql –version
    psql (PostgreSQL) 9.3.3
  12. Setting password for the first time login after installation:
    psql
    ALTER USER postgres with encrypted password ‘postgres';
  13. Enabling remote access:

  • Edit /var/lib/pgsql/9.3/data/postgresql.conf
    listen_addresses = ‘*’
  • Add below line in file – /var/lib/pgsql/9.3/data/pg_hba.conf
    host all         all         0.0.0/0             md5
  • Restart the Postgres server to reflect the connection.
    service postgresql-9.3 start
  1. Test the connection using PSQL clientpsql -h [ip address] -U [username] -d [database name] -p [port]-W
    Example: [root@hadoop3 ~]# psql -h 192.168.1.22 -U postgres -d postgres -p 5432 -W
    Password for user postgres:
    psql (9.3.6)
    Type “help” for help.
    postgres=#
  • Configure postgres to use different data directory.
  1.  Installation of the Postgres through the YUM – creates the Postgres data directory at /var/lib/pgsql.

if root  has less space  to handle the larger data that can be handled , throws  storage errors. So configure for appropriate space.

  1. service postgresql-9.3 status
  2. service postgresql-9.3 stop
  3. Copy the /var/lib/pgsql to where you want to keep.
    cp -a /var/lib/pgsql /home
  4. cd /etc/init.d

ls  postgresql-9.3

  1. Edit the default data directory with new in the script – postgresql-9.3 :%s#/var/lib/pgsql#/home/pgsql#g
  2. Make sure our new data directory has the postgres user as owner.
    [root@hadoop3 /]# ls -l /home/pgsql
    drwx—— 4 postgres postgres 4096 Feb 26 11:10 9.3
    [root@hadoop3 /]#

Check the new data directory  for owner permissions ,use below command to change , if necessary
chown -R postgres:postgres /home/pgsql

  1. service postgresql-9.3 start
  2. Connect to the Postgres server and check your existing settings are working properly.
  3. If everything is fine please remove the default data directory.
    cd /var/lib/pgsql

rm -rf *