Migrate Oozie DB from Derby to Postgresql

Below are the steps for Oozie database migration from Derby to Postgresql.

.

Step 1 - Have Postgresql server installed and ready to be configured.

.

Step 2 - Stop Oozie service from Ambari UI.

.

Step 3 - Install Postgresql JDBC connector.

yum install postgresql-jdbc

.

ambari-server setup --jdbc-db=postgres --jdbc-driver=/usr/share/java/postgresql-jdbc.jar

Note - Please pass appropriate driver if /usr/share/java/postgresql-jdbc.jar does not exists. .

.

Step 5 - Login to Postgesql DB as postgres user and create a blank 'oozie' database and grant required permissions to the 'oozie' user.

[root@ambaview ~]# su - postgres
-bash-4.1$ psql
psql (8.4.20)
Type "help" for help.

— postgres=# CREATE DATABASE oozie;
CREATE DATABASE
postgres=#

— CREATE USER oozie WITH PASSWORD 'oozie’;
postgres=# CREATE USER oozie WITH PASSWORD 'oozie';
CREATE ROLE
postgres=#

— GRANT ALL PRIVILEGES ON DATABASE oozie TO oozie;

postgres=# GRANT ALL PRIVILEGES ON DATABASE oozie TO oozie;
GRANT
postgres=#

.

Step 6 - Add Oozie Server IP address and 'oozie' user information to pg_hba configuration file and restart postgresql service.

host "oozie oozie 17X.2X.X9.2X0/0 md5" to /var/lib/pgsql/data/pg_hba.conf
[root@ambaview ~]# service postgresql restart
Stopping postgresql service: [ OK ]
Starting postgresql service: [ OK ]

.

Step 7 - Add postgres database server details in Oozie configuration via Ambari UI.

.

Step 8 - Copy postgresql-jdbc.jar to Oozie's libext directory.

cp /usr/share/java/postgresql-jdbc.jar /usr/hdp/<hdp-version>/oozie/libext/

.

Step 9 - Prepare Oozie war file

/usr/hdp/<version>/oozie/bin/oozie-setup.sh prepare-war

Note - Run above command on oozie server as oozie user.

.

Step 10 - Prepare Oozie schema using below command (Run below command on Oozie host as oozie user)

/usr/hdp/<version>/oozie/bin/oozie-setup.sh db create -run

.

Step 11 - Start Oozie server via Ambari.

.

Happy Hadooping!! Please comment your feedback or questions in the comment section.