Geo with external PostgreSQL instances - Gitlabhq

Tier: Premium, Ultimate
Offering: GitLab Self-Managed

This document is relevant if you are using a PostgreSQL instance that is not managed by the Linux package. This includes cloud-managed instances, or manually installed and configured PostgreSQL instances.

Ensure that you are using one of the PostgreSQL versions that the Linux package ships with to avoid version mismatches in case a Geo site has to be rebuilt.

[!note] If you're using GitLab Geo, we strongly recommend running instances installed by using the Linux package or using validated cloud-managed instances, as we actively develop and test based on those. We cannot guarantee compatibility with other external databases.

Primary site

SSH into a Rails node on your primary site and login as root:
shell
```
sudo -i
```

Edit /etc/gitlab/gitlab.rb and add:

ruby

##
## Geo Primary role
## - configure dependent flags automatically to enable Geo
##
roles ['geo_primary_role']

##
## The unique identifier for the Geo site. See
## https://docs.gitlab.com/administration/geo_sites/#common-settings
##
gitlab_rails['geo_node_name'] = '<site_name_here>'

Reconfigure the Rails node for the change to take effect:
shell
```
gitlab-ctl reconfigure
```
Execute the command below on the Rails node to define the site as primary site:
shell
```
gitlab-ctl set-geo-primary-node
```
This command uses your defined external_url in /etc/gitlab/gitlab.rb.

Configure the external database to be replicated

To set up an external database, you can either:

Set up streaming replication yourself (for example Amazon RDS, or bare metal not managed by the Linux package).
Manually perform the configuration of your Linux package installations as follows.

Leverage your cloud provider's tools to replicate the primary database

Given you have a primary site set up on AWS EC2 that uses RDS. You can now just create a read-only replica in a different region and the replication process is managed by AWS. Make sure you've set Network ACL (Access Control List), Subnet, and Security Group according to your needs, so the secondary Rails nodes can access the database.

The following instructions detail how to create a read-only replica for common cloud providers:

Amazon RDS - Creating a Read Replica
Azure Database for PostgreSQL - Create and manage read replicas in Azure Database for PostgreSQL
Google Cloud SQL - Creating read replicas

When your read-only replica is set up, you can skip to configure your secondary site

[!warning] The use of logical replication methods such as AWS Database Migration Service or Google Cloud Database Migration Service to, for instance, replicate from an on-premise primary database to an RDS secondary are not supported.

Manually configure the primary database for replication

The geo_primary_role configures the primary node's database to be replicated by making changes to pg_hba.conf and postgresql.conf. Make the following configuration changes manually to your external database configuration and ensure that you restart PostgreSQL afterwards for the changes to take effect:

plaintext

##
## Geo Primary Role
## - pg_hba.conf
##
host    all         all               <trusted primary IP>/32       md5
host    replication gitlab_replicator <trusted primary IP>/32       md5
host    all         all               <trusted secondary IP>/32     md5
host    replication gitlab_replicator <trusted secondary IP>/32     md5

plaintext

##
## Geo Primary Role
## - postgresql.conf
##
wal_level = hot_standby
max_wal_senders = 10
wal_keep_segments = 50
max_replication_slots = 1 # number of secondary instances
hot_standby = on

Secondary sites

Manually configure the replica database

Make the following configuration changes manually to your pg_hba.conf and postgresql.conf of your external replica database and ensure that you restart PostgreSQL afterwards for the changes to take effect:

plaintext

##
## Geo Secondary Role
## - pg_hba.conf
##
host    all         all               <trusted secondary IP>/32     md5
host    replication gitlab_replicator <trusted secondary IP>/32     md5
host    all         all               <trusted primary IP>/24       md5

plaintext

##
## Geo Secondary Role
## - postgresql.conf
##
wal_level = hot_standby
max_wal_senders = 10
wal_keep_segments = 10
hot_standby = on

Configure secondary site to use the external read-replica

With Linux package installations, the geo_secondary_role has three main functions:

Configure the replica database.
Configure the tracking database.
Enable the Geo Log Cursor (not covered in this section).

To configure the connection to the external read-replica database and enable Log Cursor:

SSH into each Rails, Sidekiq and Geo Log Cursor node on your secondary site and login as root:
shell
```
sudo -i
```

Edit /etc/gitlab/gitlab.rb and add the following

ruby

##
## Geo Secondary role
## - configure dependent flags automatically to enable Geo
##
roles ['geo_secondary_role']

# note this is shared between both databases,
# make sure you define the same password in both
gitlab_rails['db_password'] = '<your_primary_db_password_here>'

gitlab_rails['db_username'] = 'gitlab'
gitlab_rails['db_host'] = '<database_read_replica_host>'

# Disable the bundled Omnibus PostgreSQL because we are
# using an external PostgreSQL
postgresql['enable'] = false

Save the file and reconfigure GitLab

Configure the tracking database

Secondary sites use a separate PostgreSQL installation as a tracking database to keep track of replication status and automatically recover from potential replication issues. The Linux package automatically configures a tracking database when roles ['geo_secondary_role'] is set. If you want to run this database external to your Linux package installation, use the following instructions.

Understanding internal and external tracking databases

You can configure the tracking database to be either:

Internal (geo_postgresql['enable'] = true): The tracking database runs as a managed PostgreSQL instance on the same server as the Rails application. This is the default.
External (geo_postgresql['enable'] = false): The tracking database runs on a separate server or as a cloud-managed service.

In multi-node secondary site setups, if you enable the tracking database on one Rails node, it becomes "external" to all other Rails nodes in the site. All other Rails nodes must set geo_postgresql['enable'] = false and specify connection details to connect to that tracking database.

Cloud-managed database services

If you are using a cloud-managed service for the tracking database, you may need to grant additional roles to your tracking database user (by default, this is gitlab_geo):

Amazon RDS requires the rds_superuser role.
Azure Database for PostgreSQL requires the azure_pg_admin role.
Google Cloud SQL requires the cloudsqlsuperuser role.

This is for the installation of extensions during installation and upgrades. As an alternative, ensure the extensions are installed manually, and read about the problems that may arise during future GitLab upgrades.

[!note] If you want to use Amazon RDS as a tracking database, make sure it has access to the secondary database. Unfortunately, just assigning the same security group is not enough as outbound rules do not apply to RDS PostgreSQL databases. Therefore, you need to explicitly add an inbound rule to the read-replica's security group allowing any TCP traffic from the tracking database on port 5432.

Create the tracking database

Create and configure the tracking database in your PostgreSQL instance:

Set up PostgreSQL according to the database requirements document.
Set up a gitlab_geo user with a password of your choice, create the gitlabhq_geo_production database, and make the user an owner of the database. You can see an example of this setup in the self-compiled installation documentation.
If you are not using a cloud-managed PostgreSQL database, ensure that your secondary site can communicate with your tracking database by manually changing the pg_hba.conf that is associated with your tracking database. Remember to restart PostgreSQL afterwards for the changes to take effect:
plaintext
```
##
## Geo Tracking Database Role
## - pg_hba.conf
##
host    all         all               <trusted tracking IP>/32      md5
host    all         all               <trusted secondary IP>/32     md5
# In multi-node setups, add entries for all Rails nodes that will connect
```

Configure GitLab

Configure GitLab to use this database. These steps are for Linux package and Docker deployments.

SSH into a GitLab secondary server and login as root:
shell
```
sudo -i
```

Edit /etc/gitlab/gitlab.rb with the connection parameters and credentials for the machine with the PostgreSQL instance:

ruby

geo_secondary['db_username'] = 'gitlab_geo'
geo_secondary['db_password'] = '<your_tracking_db_password_here>'

geo_secondary['db_host'] = '<tracking_database_host>'
geo_secondary['db_port'] = <tracking_database_port>      # change to the correct port
geo_postgresql['enable'] = false     # don't use internal managed instance

In multi-node setups, apply this configuration to each Rails node that needs to connect to the external tracking database.

Save the file and reconfigure GitLab

Set up the database schema

The reconfigure command in the previously listed steps for Linux package and Docker deployments should handle these steps automatically.

This task creates the database schema. It requires the database user to be a superuser.
shell
```
sudo gitlab-rake db:create:geo
```
Applying Rails database migrations (schema and data updates) is also performed by reconfigure. If geo_secondary['auto_migrate'] = false is set, or the schema was created manually, this step will be required:
shell
```
sudo gitlab-rake db:migrate:geo
```