Installing Postgresql 9.5

1. 1. To setup YUM repositories, first disable the default postgres repo
      Edit the /etc/yum/pluginconf.d/rhnplugin.conf [main] section and append the following line

      exclude=postgresql*

   2. Install the official PGDG RPM file. Go to http://yum.postgresql.org and find your correct RPM. For e.g. to install Postgres 9.5 on 64 bit RHEL

      1. 1. yum localinstall http://yum.postgresql.org/9.5/redhat/rhel-7-x86_64/pgdg-redhat95-9.5-2.noarch.rpm

      Note: The link for RHEL 64 bit, Postgres 9.5 was incorrect on the webpage yum.postgresql.org. The link above is correct though

   3. Install Postgresql server

      1. 1. yum install postgresql95-server

      Postgresql 9.5 data directory
      /var/lib/pgsql/9.5/data

   4. Initialize the database

      /usr/pgsql-9.5/bin/postgresql95-setup initdb

   5. Start and enable the service to run on system restart

      systemctl enable postgresql-9.5
      systemctl start postgresql-9.5
      systemctl status postgresql-9.5

   6. To enable connecting to Postgres using normal username/password, edit the file /var/lib/pgsql/9.5/data/pg_hba.conf , and change the authentication type from ‘peer’ to ‘md5’. For e.g.

      • local   all             postgres                                md5

   7. To set a password for the default postgres user

      sudo su - postgres
      psql
      \password

   8. To create another user for postgres

      sudo su - postgres
      createuser -P --interactive

   9. Note that postgres by default can only be accessed from localhost. To enable remote access, follow this link. Other option is to install a tool like PhpPostgresAdmin and access postgres remotely using the web UI.

Installing PhpPostgresAdmin on RHEL AWS image with postgres

1. Install phpPostgresAdmin

   yum install phpPgAdmin.noarch

2. To enable phpPgAdmin access from outside, edit the file /etc/httpd/conf.d/phpPgAdmin.conf and add the following line
   Require all granted (in section mod_authz_core.c)
3. Edit file /usr/share/phpPgAdmin/conf/config.inc.php. Change $conf[‘extra_login_security’] = true; to $conf[‘extra_login_security’] = false;
4. Access http://<server>/phpPgAdmin/ and use postgres username/password to access your postgres server.

Posted on: 8th June 2016, by : AdityaBhushan