Gitlab full Postgresql backup and restore

GitLab can be used with Postgresql as its database engine  (beside MySQL),  and all come ready to make backups and restores of its databases and git repositories by using a ruby script (rake). This is the recommended way because it lets you recover everything wit not pending transactiond on database and Git sides.

The problem

The documentation explains all you have to perform a manual backup and restore, but the latter fails because it tries to insert existing registers that generate conflicts like this one:

ALTER SEQUENCE
psql:/home/git/gitlab/tmp/backups/db/database.sql:812: ERROR: relation "users" already exists

The Solution

The postgresql backup must erase all tables before trying to recreate them and insert the registers. That can be done by including the  –clean r -c  to the pg_dump command. This option must be edited in the ruby script that makes the backup, which is  _/home/git/gitlab/lib/backup/database.rb _by default. In this file you have to find this part of code and substitute the following bold line:

require 'yaml'

 module Backup
 class Database
 attr_reader :config, :db_dir
def initialize
 @config = YAML.load_file(File.join(Rails.root,'config','database.yml'))[Rails.env]
 @db_dir = File.join(Gitlab.config.backup.path, 'db')
 FileUtils.mkdir_p(@db_dir) unless Dir.exists?(@db_dir)
 end
def dump
 success = case config["adapter"]
 when /^mysql/ then
 print "Dumping MySQL database #{config['database']} ... "
 system('mysqldump', *mysql_args, config['database'], out: db_file_name)
 when "postgresql" then
 print "Dumping PostgreSQL database #{config['database']} ... "
 pg_env
 <strong>system('pg_dump', config['database'], out: db_file_name)</strong>
 end
 report_success(success)
 end

This line must be replaced with this other line:

<strong>system('pg_dump', config['database'], '-c', out: db_file_name)</strong>

Here you can see that option -c is passed as argument to command pg_dump. This will force to include all DROPS needed in the generated .sql  for the GitLab backup.