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/ 

Setting up a GitLab Specific runner on CentOS

In this post, I'll list the steps which I followed to configure a GitLab runner on the same host where GitLab server is running.

What are Runners in GitLab?

You can run builds on your merge request or after push in GitLab. Traditionally we used to run builds on a separate CI server like Jenkins. But though GitLab is a Git repository, it is more than that. The 'Runners' are the virtual machine on which GitLab CI runs your build.

My GitLab host environment

• GitLab v8.15.4 EE deployed on AWS
• OS: CentOS 7.2

Creating a Runner

• Install docker
• If you want GitLab to run your builds inside a docker container, you need to install docker


• curl -fsSL https://get.docker.com/ | sh
This script adds the docker.repo repository and installs Docker.
• sudo systemctl enable docker.service
• Enable the service
• sudo systemctl start docker
• Start the Docker daemon
• Verify docker is installed correctly by running a test image in a container.
• sudo docker run --rm hello-world
• Add GitLab's official repository via Yum
• curl -L https://packages.gitlab.com/install/repositories/runner/gitlab-ci-multi-runner/script.rpm.sh | sudo bash
• Install gitlab-ci-multi-runner
• sudo yum install gitlab-ci-multi-runner
• Register the runner
• sudo gitlab-ci-multi-runner register

Please enter the gitlab-ci coordinator URL (e.g. https://gitlab.com )
https://127.0.0.1/ci
Please enter the gitlab-ci token for this runner
WKC4P9FuurUtQZ4h2xCH
Please enter the gitlab-ci description for this runner
master-runner
INFO[0034] fcf5c619 Registering runner... succeeded
Please enter the executor: shell, docker, docker-ssh, ssh?
docker
Please enter the Docker image (eg. ruby:2.1):
ruby:2.1
INFO[0037] Runner registered successfully. Feel free to start it, but if it's
running already the config should be automatically reloaded!

• I wanted my runner to run the same host where GitLab is running and hence I entered https://127.0.0.1/ci for the gitlab-ci coordinator URL
• To get the gitlab-ci token, browse to
• Project -> Settings wheel -> Runners -> Specific Runners -> Use the following registration token during setup: WKC4P9FuurUtQZ4h2xCH
• Verify that Runner is successfully activated
• Project -> Settings wheel -> Runners -> Runners activated for this project -> You should see a green colour runner

Verifying runners with command line

• gitlab-runner list
• List the registered runners
• gitlab-runner verify
• This command checks whether registered runners can connect to GitLab server
• Unregister
• gitlab-runner unregister --url http://127.0.0.1/ci --token ajsbgjav
• or
• gitlab-runner unregister --url http://127.0.0.1/ci --name master-runner

Reference

• https://docs.gitlab.com/ee/ci/runners/README.html#registering-a-specific-runner
• https://docs.gitlab.com/runner/install/linux-repository.html
• https://docs.gitlab.com/ee/ci/quick_start/README.html
• https://docs.docker.com/engine/installation/linux/centos/