Install and Configure PostgreSQL for Cloudera Software

Install the PostgreSQL packages as follows:

RHEL:

sudo yum install postgresql-server

SLES:

sudo zypper install --no-recommends postgresql96-server

Ubuntu:

sudo apt-get install postgresql

Hue in CDH 6 requires version 2.5.4 or higher of the psycopg2 Python package for connecting to a PostgreSQL database. The psycopg2 package is automatically installed as a dependency of Cloudera Manager Agent, but the version installed is often lower than 2.5.4.

If you are installing or upgrading to CDH 6 and using PostgreSQL for the Hue database, you must install psycopg2 2.5.4 or higher on all Hue hosts as follows. These examples install version 2.7.5 (2.6.2 for RHEL 6):

RHEL 7 Compatible

1. Install the python-pip package:

   sudo yum install python-pip

2. Install psycopg2 2.7.5 using pip:

   sudo pip install psycopg2==2.7.5 --ignore-installed

RHEL 6 Compatible

1. Make sure that you have installed Python 2.7. You can verify this by running the following commands:

   source /opt/rh/python27/enable
   python --version

2. Install the python-pip package:

   sudo yum install python-pip

3. Install the postgresql-devel package:

   sudo yum install postgresql-devel

4. Install the gcc* packages:

   sudo yum install gcc*

5. Install psycopg2 2.6.2 using pip:

   sudo bash -c "source /opt/rh/python27/enable; pip install psycopg2==2.6.2 --ignore-installed"

Ubuntu / Debian

1. Install the python-pip package:

   sudo apt-get install python-pip

2. Install psycopg2 2.7.5 using pip:

   sudo pip install psycopg2==2.7.5 --ignore-installed

SLES 12

Install the python-psycopg2 package:

sudo zypper install python-psycopg2

By default, PostgreSQL only accepts connections on the loopback interface. You must reconfigure PostgreSQL to accept connections from the fully qualified domain names (FQDN) of the hosts hosting the services for which you are configuring databases. If you do not make these changes, the services cannot connect to and use the database on which they depend.

1. Make sure that LC_ALL is set to en_US.UTF-8 and initialize the database as follows:
   • RHEL 7:

     echo 'LC_ALL="en_US.UTF-8"' >> /etc/locale.conf
     sudo su -l postgres -c "postgresql-setup initdb"

   • RHEL 6:

     echo 'LC_ALL="en_US.UTF-8"' >> /etc/default/locale
     sudo service postgresql initdb

   • SLES 12:

     sudo su -l postgres -c "initdb --pgdata=/var/lib/pgsql/data --encoding=UTF-8"

   • Ubuntu:

     sudo service postgresql start

2. Enable MD5 authentication. Edit pg_hba.conf, which is usually found in /var/lib/pgsql/data or /etc/postgresql/<version>/main. Add the following line:

   host all all 127.0.0.1/32 md5

   If the default pg_hba.conf file contains the following line:

   host all all 127.0.0.1/32 ident

   then the host line specifying md5 authentication shown above must be inserted before this ident line. Failure to do so may cause an authentication error when running the scm_prepare_database.sh script. You can modify the contents of the md5 line shown above to support different configurations. For example, if you want to access PostgreSQL from a different host, replace 127.0.0.1 with your IP address and update postgresql.conf, which is typically found in the same place as pg_hba.conf, to include:

   listen_addresses = '*'

3. Configure settings to ensure your system performs as expected. Update these settings in the /var/lib/pgsql/data/postgresql.conf or /var/lib/postgresql/data/postgresql.conf file. Settings vary based on cluster size and resources as follows:
   • Small to mid-sized clusters - Consider the following settings as starting points. If resources are limited, consider reducing the buffer sizes and checkpoint segments further. Ongoing tuning may be required based on each host's resource utilization. For example, if the Cloudera Manager Server is running on the same host as other roles, the following values may be acceptable:
     • max_connection - In general, allow each database on a host 100 maximum connections and then add 50 extra connections. You may have to increase the system resources available to PostgreSQL, as described at Connection Settings.
     • shared_buffers - 256MB
     • wal_buffers - 8MB
     • checkpoint_segments - 16
     • checkpoint_completion_target - 0.9
   • Large clusters - Can contain up to 1000 hosts. Consider the following settings as starting points.
     • max_connection - For large clusters, each database is typically hosted on a different host. In general, allow each database on a host 100 maximum connections and then add 50 extra connections. You may have to increase the system resources available to PostgreSQL, as described at Connection Settings.
     • shared_buffers - 1024 MB. This requires that the operating system can allocate sufficient shared memory. See PostgreSQL information on Managing Kernel Resources for more information on setting kernel resources.
     • wal_buffers - 16 MB. This value is derived from the shared_buffers value. Setting wal_buffers to be approximately 3% of shared_buffers up to a maximum of approximately 16 MB is sufficient in most cases.
     • checkpoint_segments - 128. The PostgreSQL Tuning Guide recommends values between 32 and 256 for write-intensive systems, such as this one.
     • checkpoint_completion_target - 0.9.
4. Configure the PostgreSQL server to start at boot.

   OS	Command
   RHEL 7 compatible	sudo systemctl enable postgresql
   RHEL 6 compatible	sudo chkconfig postgresql on
   SLES	sudo chkconfig --add postgresql
   Ubuntu	sudo chkconfig postgresql on

5. Restart the PostgreSQL database:
   • RHEL 7 Compatible:

     sudo systemctl restart postgresql

   • All Others:

     sudo service postgresql restart

After completing the above instructions to install and configure PostgreSQL databases for Cloudera software, continue to Step 5: Set up the Cloudera Manager Database to configure a database for Cloudera Manager.