Note: This command installs PostgreSQL 9.2. If you want to install a different version, you can use zypper search
postgresql to search for an available supported version. See CDH and Cloudera Manager Supported Databases.

Ubuntu

sudo apt-get install postgresql

Configuring and Starting the PostgreSQL Server

Note: If you are making changes to an existing database, make sure to stop any services that use the database before continuing.

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.

Initialize the PostgreSQL database. For some versions of PostgreSQL, this occurs automatically the first time that you start the PostgreSQL server. In this case, issue the command:

sudo service postgresql start

In other versions, you must explicitly initialize the database using one of the following commands:

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 services connect to the database. 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 = '*'

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, the following values may be acceptable:

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.

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

Note:chkconfig may not be available on recent Ubuntu releases. You may need to use Upstart to configure PostgreSQL
to start automatically when the system boots. For more information, see the Ubuntu documentation or the Upstart
Cookbook.

Restart the PostgreSQL database:

sudo service postgresql restart

Creating Databases for Cloudera Software

Create databases and service accounts for components that require databases:

Each Hive metastore

Sentry Server

Sqoop Server

Hue

Oozie

The databases must be configured to support the PostgreSQL UTF8 character set encoding.

Record the values you enter for database names, usernames, and passwords. You will need them to configure the individual services later.

If this documentation includes code, including but not limited to, code examples, Cloudera makes this available to you under the terms of the Apache License, Version 2.0, including any required
notices. A copy of the Apache License Version 2.0 can be found here.