sonarqube postgresql db backup and recovery

postgresql is one of the DB supported by sonarqube. In this blog I'll list out the steps involved in taking back-up of postgresql DB used by sonarqube.

As described in the official postgresql 'Backup and Restore' documentation, there are 3 different approaches to back-up postgresql Databases. This blog uses the 'SQL Dump' approach.

In case if you are trying to install sonarqube on CentOS 7 with postgresql as DB, refer this http://frederic-wou.net/sonarqube-installation-on-centos-7-2/ url. It explains the steps in detail.

Creating Database Dump

The sonarqube server stores it's data on a database named 'sonar' under postgresql. So create dump of this database using one of these commands

• pg_dump -U postgres -F t sonar > sonar_db_dump.tar
• -U postges : Specifying the username to connect to DB and in this case it is 'postgres'
• -F t: the format of dump created and in this case it is created in 'tar' format.
• 'sonar' is the database name to create dump

or

• pg_dump -U postgres sonar > sonar_db_dump.tar
• This creates dump in the plain text format

Restoring database dump

You can use either 'psql' or 'pg_restore' utility to restore the database dump. I have used 'pg_restore' for the advantages described in this http://www.postgresqltutorial.com/postgresql-restore-database/ link

• First install a plain sonarqube server. You can refer http://frederic-wou.net/sonarqube-installation-on-centos-7-2/ for centos based installation instructions
• Don't start sonarqube server
• Restore the Database dump
• su - postgres 
• pg_restore -U postgres --dbname=sonar --verbose /tmp/sonar_db_dump.tar
• Start the sonarqube server
• /opt/sonarqube-5.4/bin/linux-x86-64/sonar.sh start
• /opt/sonarqube-5.4/bin/linux-x86-64/sonar.sh status
• tail -f /opt/sonarqube-5.4/logs/sonar.log
• Ensure sonarqube started successfully by referring the sonar.log
• Browse to the new sonarqube server http://<ip>:9000 and there you can see the restored projects

Reference

• http://www.postgresqltutorial.com/postgresql-restore-database/
• https://www.postgresql.org/docs/9.1/static/backup.html
• https://wiki.postgresql.org/wiki/Automated_Backup_on_Linux
• http://frederic-wou.net/sonarqube-installation-on-centos-7-2/
• https://docs.sonarqube.org/display/SONAR/Installing+the+Server
• http://www.postgresqltutorial.com/postgresql-backup-database/