Postgres 9.2 Upgrade for HDF 3.5.2

While going through HDF upgrade from version 3.4.1 to 3.5.2, I came to realise that Ambari managed HDF does not update the underlying Postgres database automatically and vendor documentation is quiet minimalistic in the detail. Collating from a couple sources together, hopefully this will help others in the same boat.

1. Take backup of all database: sudo -Hiu posgres pg_dumpall > postgres92_backup.sql
2. Stop services using the postgres database: stop ranger service from ambari web portal and stop ambari server from terminal and run command “ambari-server stop”
3. Stop postgresql daemon: systemctl stop postgresql
4. Install the new version: yum install postgresql96-server postgresql96-contrib postgresql96
5. Initialise the database: sudo su postgres /usr/pgsql-9.6/bin/initdb -D /var/lib/pgsql/9.6/data/
6. The next part is tricky because it will fail if you don’t do this change

mv /usr/bin/pg_ctl{,-orig}
echo ‘#!/bin/bash’ > /usr/bin/pg_ctl
echo ‘“$0”-orig “${@/unix_socket_directory/unix_socket_directories}”’ >> /usr/bin/pg_ctl
chmod +x /usr/bin/pg_ctl```

7. Upgrade the DB and it need to run as postgres user and a location in which postgres user can dump upgrade log

Either change user using sudo su postgres or run command below with sudo su postgres Navigate to /tmp directory: cd /tmp Run the following command: /usr/pgsql-9.6/bin/pg_upgrade — old-datadir /var/lib/pgsql/data/ — new-datadir /var/lib/pgsql/9.6/data/ — old-bindir /usr/bin/ — new-bindir /usr/pgsql-9.6/bin/

8. Check configuration files between the old and new postgresql then copy enabled parameters or configuration

/var/lib/pgsql/9.6/data/postgresql.conf and /var/lib/pgsql/data/postgresql.conf /var/lib/pgsql/9.6/data/pg_hba.conf and /var/lib/pgsql/data/pg_hba.conf

9. Start the database: systemctl start postgresql-9.6

10. Revert the change: mv -f /usr/bin/pg_ctl{-orig,}

11. log as postgres user and do a check up: ./analyze_new_cluster.sh

12. Delete the old cluster: ./delete_old_cluster.sh

13. Disable Postgres 9.2: systemctl disable postgresql

14. Enable postgres 9.6 service systemctl enable postgresql-9.6

15. sql — version psql (PostgreSQL) 9.6.11

16. Last but not least Run this from the terminal where ambari server is “ambari-server setup” to reconfigure Ambari to the new database.

Huge thanks to Kliment Andreev for his blog CentOS: Upgrade PostgreSQL from 9.2 to 9.6