Configuring Oozie to Use SCM PostgreSQL database

This is a CDH5 cluster managed by CM5.
The goal is to use PostgreSQL database used by CM5 for oozie.
Refer to :
Oozie configuration guide
Manage Oozie service using Cloudera Manager

1. Create PostgreSQL user "oozie".

1 2	CREATE ROLE oozie LOGIN ENCRYPTED PASSWORD 'oozie' NOSUPERUSER INHERIT CREATEDB NOCREATEROLE;

2. Create PostgreSQL database "oozie".

1 2 3 4 5 6

CREATE DATABASE "oozie" WITH OWNER = oozie  ENCODING = 'UTF8'  TABLESPACE = pg_default  LC_COLLATE = 'en_US.UTF8'  LC_CTYPE = 'en_US.UTF8'  CONNECTION LIMIT = -1;

3. Make sure postgresql.conf has set listen_addresses property to * and standard_conforming_strings is set to off.

1 2 3 4 5 6 7 8 9 10 11

template1=# show  standard_conforming_strings;  standard_conforming_strings -----------------------------  off (1 row)   template1=# show listen_addresses;  listen_addresses ------------------  * (1 row)

4. Configure PostgreSQL to accept network connections for user oozie.

Add below line in pg_hba.conf:

1	host    oozie         oozie         0.0.0.0/0             md5

Reload the configuration:

1	sudo -u cloudera-scm pg_ctl reload -s -D /var/lib/cloudera-scm-server-db/data

From the postgreSQL log, you can see:

1	LOG:  received SIGHUP, reloading configuration files

5. Test the connection using "oozie" database user.

1	psql -p 7432 -U oozie oozie

6. Shutdown Oozie server using Cloudera Manager.

7. Change below configurations throught Cloudera Manager for service "oozie"

Oozie Server database type => postgresql
Oozie Server database name => oozie
Oozie Server database host => localhost:7432
Oozie Server database user => oozie
Oozie Server database password => oozie
"Save Changes".

8. Creating the Oozie Database Schema using Cloudera Manager

"Action"=>"Create Database"
Then you can find below tables created:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

oozie=> \dt                 List of relations  Schema |          Name          | Type  | Owner --------+------------------------+-------+-------  public | bundle_actions         | table | oozie  public | bundle_jobs            | table | oozie  public | coord_actions          | table | oozie  public | coord_jobs             | table | oozie  public | oozie_sys              | table | oozie  public | openjpa_sequence_table | table | oozie  public | sla_events             | table | oozie  public | sla_registration       | table | oozie  public | sla_summary            | table | oozie  public | validate_conn          | table | oozie  public | wf_actions             | table | oozie  public | wf_jobs                | table | oozie (12 rows)

9. Start Oozie server using Cloudera Manager.