Moving the Ambari Default Database on Sandbox

[#1]

When installing HDB/HAWQ on Sandbox, it is necessary to relocate the default Ambari postgres database to a postgres instance running on a different port.

The following script performs the move in a mostly automated fashion.

When prompted by ambari-server setup, select option 4 for the database configuration and fill in the details.

Note that this is only intended for Sandbox. Please do not use in production.

#!/usr/bin/env bash

#
# Change as needed
#
PGPORT=12346
PGDATA=/var/lib/pgsql/ambari
AMBARI_WEB_USER=admin
AMBARI_WEB_PW=admin
AMBARI_DB_NAME=ambari
AMBARI_DB_USER=ambari
AMBARI_DB_PW=bigdata

#
# Variables
#
PG_INIT_PATH=/etc/init.d/postgresql
DB_BKUP_DIR=/tmp/ambari-db-backup
AMBARI_PROPS=/etc/ambari-server/conf/ambari.properties

#
# Main
#
echo -e "\n#### Stopping ambari-server"
ambari-server stop

echo -e "\n#### Creating the pgpass file"
echo "*:*:*:$AMBARI_DB_USER:$AMBARI_DB_PW" >> $HOME/.pgpass
chmod 600 $HOME/.pgpass

echo -e "\n#### Creating database backup directory"
if [ -d $DB_BKUP_DIR ]; then
  rm -rf $DB_BKUP_DIR
fi
mkdir -p $DB_BKUP_DIR
chown 777 $DB_BKUP_DIR

echo -e "\n#### Backing up ambari-server databases"
pg_dump -U $AMBARI_DB_USER -w -f $DB_BKUP_DIR/ambari.sql

echo -e "\n#### Attempting to stop postgres on port $PGPORT, if running"
service postgresql.${PGPORT} stop

echo -e "\n#### Setting up new postgres data directory"
if [ -d $PGDATA ]; then
  rm -rf $PGDATA
fi
mkdir -p $PGDATA
chown postgres:postgres $PGDATA

echo -e "\n#### Creating new init script"
sed -e 's|^PGPORT=.*|PGPORT='$PGPORT'|g' -e 's|^PGDATA=.*|PGDATA='$PGDATA'|g' $PG_INIT_PATH > ${PG_INIT_PATH}.${PGPORT}
chmod 775 ${PG_INIT_PATH}.${PGPORT}

echo -e "\n#### Initializing new postgres instance on port $PGPORT"
service postgresql.${PGPORT} initdb

echo -e "\n#### Modify postgres config to listen on all interfaces"
sed -i "s|^#\?listen_addresses.*|listen_addresses = '*'|g" $PGDATA/postgresql.conf

echo -e "\n#### Copy existing pg_hba.conf"
cp /var/lib/pgsql/data/pg_hba.conf $PGDATA/pg_hba.conf

echo -e "\n#### Starting new postgres instance on port $PGPORT"
service postgresql.${PGPORT} start

echo -e "\n#### Creating the ambari db"
su - postgres -c "psql -p $PGPORT -c 'CREATE DATABASE ambari;' -d postgres"

echo -e "\n#### Creating the ambari db user role"
su - postgres -c "psql -p $PGPORT -c \"CREATE ROLE $AMBARI_DB_USER LOGIN PASSWORD '$AMBARI_DB_PW';\" -d ambari"

echo -e "\n#### Restoring ambari database backup"
su - postgres -c "psql -p $PGPORT -f $DB_BKUP_DIR/ambari.sql -d ambari"

echo -e "\n#### Updating jdbc config for ambari-server"
grep -v "server.jdbc" $AMBARI_PROPS >${AMBARI_PROPS}.nojdbc
echo "server.jdbc.port=$PGPORT" >> ${AMBARI_PROPS}.nojdbc
echo "server.jdbc.rca.driver=org.postgresql.Driver" >> ${AMBARI_PROPS}.nojdbc
echo "server.jdbc.rca.url=jdbc:postgresql://localhost:${PGPORT}/ambari" >> ${AMBARI_PROPS}.nojdbc
echo "server.jdbc.driver=org.postgresql.Driver" >> ${AMBARI_PROPS}.nojdbc
echo "server.jdbc.user.name=$AMBARI_DB_USER" >> ${AMBARI_PROPS}.nojdbc
echo "server.jdbc.postgres.schema=ambari" >> ${AMBARI_PROPS}.nojdbc
echo "server.jdbc.hostname=localhost" >> ${AMBARI_PROPS}.nojdbc
echo "server.jdbc.rca.user.passwd=/etc/ambari-server/conf/password.dat" >> ${AMBARI_PROPS}.nojdbc
echo "server.jdbc.rca.user.name=$AMBARI_DB_USER" >> ${AMBARI_PROPS}.nojdbc
echo "server.jdbc.url=jdbc:postgresql://localhost:${PGPORT}/ambari" >> ${AMBARI_PROPS}.nojdbc
echo "server.jdbc.user.passwd=/etc/ambari-server/conf/password.dat" >> ${AMBARI_PROPS}.nojdbc
echo "server.jdbc.database=postgres" >> ${AMBARI_PROPS}.nojdbc
echo "server.jdbc.database_name=ambari" >> ${AMBARI_PROPS}.nojdbc
cp ${AMBARI_PROPS}.nojdbc $AMBARI_PROPS

echo -e "\n#### Stopping existing postgres instance"
service postgresql stop

echo -e "\n#### Running ambari-server setup"
ambari-server setup

echo -e "\n#### Starting ambari-server"
service ambari-server start