Migrating Gitlab non-packaged PostgreSQL into omnibus-packaged

With the release of Gitlab 8.15 it was announced that PostgreSQL needs to be upgraded. As I migrated from a source installation I used to have an external PostgreSQL database instead of using the one shiped with the omnibus package.
So I decided to do the data migration into the omnibus PostgreSQL database now which I skipped before.

Let's have a look into the databases:

$ sudo -u postgres psql -d template1
psql (9.2.18)
Type "help" for help.

gitlabhq_production=# \l
                                             List of databases
         Name          |       Owner       | Encoding | Collate |  Ctype  |        Access privileges
-----------------------+-------------------+----------+---------+---------+---------------------------------
 gitlabhq_production   | git               | UTF8     | C.UTF-8 | C.UTF-8 |
 gitlab_mattermost     | git               | UTF8     | C.UTF-8 | C.UTF-8 |
gitlabhq_production=# \q

Dumping the databases and stop PostgreSQL. Maybe you need to adjust database names and users for your needs.

$ su postgres -c "pg_dump gitlabhq_production -f /tmp/gitlabhq_production.sql" && \
su postgres -c "pg_dump gitlab_mattermost -f /tmp/gitlab_mattermost.sql" && \
/etc/init.d/postgresql stop

Activate PostgreSQL shipped with Gitlab Omnibus

$ sed -i "s/^postgresql\['enable'\] = false/#postgresql\['enable'\] = false/g" /etc/gitlab/gitlab.rb && \
sed -i "s/^#mattermost\['enable'\] = true/mattermost\['enable'\] = true/" /etc/gitlab/gitlab.rb && \
gitlab-ctl reconfigure

Testing if the connection to the databases works

$ su - git -c "psql --username=gitlab  --dbname=gitlabhq_production --host=/var/opt/gitlab/postgresql/"
psql (9.2.18)
Type "help" for help.

gitlabhq_production=# \q
$ su - git -c "psql --username=gitlab  --dbname=mattermost_production --host=/var/opt/gitlab/postgresql/"
psql (9.2.18)
Type "help" for help.

mattermost_production=# \q

Ensure pg_trgm extension is enabled

$ sudo gitlab-psql -d gitlabhq_production -c 'CREATE EXTENSION IF NOT EXISTS "pg_trgm";'
$ sudo gitlab-psql -d mattermost_production -c 'CREATE EXTENSION IF NOT EXISTS "pg_trgm";'

Adjust permissions in the database dumps. Indeed please verify that users and databases might need to be adjusted too.

$ sed -i "s/OWNER TO git;/OWNER TO gitlab;/" /tmp/gitlabhq_production.sql && \
sed -i "s/postgres;$/gitlab-psql;/" /tmp/gitlabhq_production.sql
$ sed -i "s/OWNER TO git;/OWNER TO gitlab_mattermost;/" /tmp/gitlab_mattermost.sql && \
sed -i "s/postgres;$/gitlab-psql;/" /tmp/gitlab_mattermost.sql

(Re)import the data

$ sudo gitlab-psql -d gitlabhq_production -f /tmp/gitlabhq_production.sql
$ sudo gitlab-psql -d gitlabhq_production -c 'REVOKE ALL ON SCHEMA public FROM "gitlab-psql";' && \
sudo gitlab-psql -d gitlabhq_production -c 'GRANT ALL ON SCHEMA public TO "gitlab-psql";'
$ sudo gitlab-psql -d mattermost_production -f /tmp/gitlab_mattermost.sql
$ sudo gitlab-psql -d mattermost_production -c 'REVOKE ALL ON SCHEMA public FROM "gitlab-psql";' && \
sudo gitlab-psql -d mattermost_production -c 'GRANT ALL ON SCHEMA public TO "gitlab-psql";'

Make use of the shipped PostgreSQL

$ sed -i "s/^gitlab_rails\['db_/#gitlab_rails\['db_/" /etc/gitlab/gitlab.rb && \
sed -i "s/^mattermost\['sql_/#mattermost\['sql_/" /etc/gitlab/gitlab.rb && \
gitlab-ctl reconfigure

Now you should be able to connect to all the Gitlab services again.

Optionally remove the external database

apt-get remove postgresql postgresql-client postgresql-9.4 postgresql-client-9.4 postgresql-client-common postgresql-common

Maybe you also want to purge the old database content

apt-get purge postgresql-9.4