# Configuring a Database for GitLab HA

You can choose to install and manage a database server (PostgreSQL/MySQL)
yourself, or you can use GitLab Omnibus packages to help. GitLab recommends
PostgreSQL. This is the database that will be installed if you use the
Omnibus package to manage your database.

## Configure your own database server

If you're hosting GitLab on a cloud provider, you can optionally use a
managed service for PostgreSQL. For example, AWS offers a managed Relational
Database Service (RDS) that runs PostgreSQL.

If you use a cloud-managed service, or provide your own PostgreSQL:

1. Setup PostgreSQL according to the
   [database requirements document](../../install/requirements.md#database).
1. Set up a `gitlab` username with a password of your choice. The `gitlab` user
   needs privileges to create the `gitlabhq_production` database.
1. Configure the GitLab application servers with the appropriate details.
   This step is covered in [Configuring GitLab for HA](gitlab.md).

## Configure using Omnibus

The recommended configuration for an Omnibus managed PostgreSQL instance is:
* A minimum of 2 database servers, a primary and a secondary.
* Use of the bundled [pgbouncer](https://pgbouncer.github.io) to pool database connections on each node.

### Primary database server
1. Download/install GitLab Omnibus using **steps 1 and 2** from
   [GitLab downloads](https://about.gitlab.com/downloads). Do not complete other
   steps on the download page.

1. We need three accounts for the GitLab database, one for the application, and one for pgbouncer.
   Each account needs a password, which we store in an md5 has when possible. The hash for a password can be
   generated by running: `echo -n "${PASSWORD}${USERNAME}" | md5sum`.
   * The default user for the application is `gitlab`. This password will be stored in plaintext so it **really should** be a unique password.
     * On the database server, the hash should be stored in the attribute `postgresql['sql_user_password']`
     * On the application server, the password is stored in the attribute `gitlab_rails['db_password']`
   * The default user for replication is `gitlab_replicator`. Currently, its password is not managed by omnibus, and will be set in a manual step later.
   * The default user for pgbouncer is `pgbouncer`, its password is stored as an md5 has in `postgresql['pgbouncer_user_password']`
     for the database, and as part of the `pgbouncer['databases']` attribute for the pgbouncer instance.

1. Create/edit `/etc/gitlab/gitlab.rb` and use the following configuration.

    ```ruby
    # Disable all unecessary components
    postgresql['enable'] = true
    gitlab_rails['enable'] = false
    bootstrap['enable'] = false
    nginx['enable'] = false
    unicorn['enable'] = false
    sidekiq['enable'] = false
    redis['enable'] = false
    gitlab_workhorse['enable'] = false
    mailroom['enable'] = false
    gitaly['enable'] = false

    # PostgreSQL configuration
    # This will be the list of hosts that can use md5 authentication to connect to the database.
    postgresql['md5_auth_cidr_addresses'] = ['0.0.0.0/0']
    # This will be allowa host to connect to the database without authentication
    postgresql['trust_auth_cidr_addresses'] = ['127.0.0.0/24']
    postgresql['listen_address'] = '0.0.0.0'
    postgresql['sql_replication_user'] = 'gitlab_replicator'
    postgresql['sql_user_password'] = 'PASSWORD_HASH'
    postgresql['pgbouncer_user'] = 'pgbouncer'
    postgresql['pgbouncer_user_password'] = 'PASSWORD_HASH'
    postgresql['wal_level'] = 'hot_standby'
    postgresql['max_wal_senders'] = 5
    postgresql['wal_keep_segements'] = 32

    # Pgbouncer configuration
    pgbouncer['enable'] = true
    pgbouncer['databases'] = {
      gitlabhq_production: {
        host: '127.0.0.1',
        user: 'pgbouncer',
        password: 'PASSWORD_HASH'
      }
    }

    # Disable automatic database migrations
    gitlab_rails['auto_migrate'] = false
    ```

1. Run `sudo gitlab-ctl reconfigure` to install and configure PostgreSQL.

1. Open a database prompt:

    ```
    /opt/gitlab/bin/gitlab-psql -d template1
    # Output:

    psql (9.6.1)
    Type "help" for help.

    template1=#
    ```

1. Run the following command at the database prompt and you will be asked to
   enter the new password for the PostgreSQL superuser.

    ```
    \password

    # Output:

    Enter new password:
    Enter it again:
    ```

1. Similarly, set the password for the `gitlab_replicator` database user.
    ```
    \password gitlab_replicator

    # Output:

    Enter new password:
    Enter it again:
    ```
1. Give the `gitlab_replicator` user replication permissions:
   ```
   ALTER USER gitlab_replicator REPLICATION;

   # Output:
   ALTER ROLE
   ```
1. Exit the database prompt by typing `\q` and Enter.

### Secondary database server
1. Download/install GitLab Omnibus using **steps 1 and 2** from
   [GitLab downloads](https://about.gitlab.com/downloads). Do not complete other
   steps on the download page.

1. You will need the same password hash for the `pgbouncer` user from the primary server.

1. Create/edit `/etc/gitlab/gitlab.rb` and use the following configuration.
    ```ruby
    # Disable all uncessary components
    postgresql['enable'] = true
    bootstrap['enable'] = false
    nginx['enable'] = false
    unicorn['enable'] = false
    sidekiq['enable'] = false
    redis['enable'] = false
    gitlab_workhorse['enable'] = false
    mailroom['enable'] = false
    gitlab_rails['enable'] = false
    gitaly['enable'] = false

    # PostgreSQL configuration
    gitlab_rails['auto_migrate'] = false
    postgresql['hot_standby'] = 'on'
    pgbouncer['enable'] = true
    pgbouncer['databases'] = {
      gitlabhq_production: {
        host: '127.0.0.1',
        user: 'pgbouncer',
        password: 'PASSWORD_HASH'
      }
   }
   ```

1. Run `sudo gitlab-ctl reconfigure` to install and configure PostgreSQL.

1. Stop the database by running `sudo gitlab-ctl stop postgresql`

1. Remove the current database by running `sudo rm -rf /var/opt/gitlab/postgresl/data/*`
   > **WARNING**: Be absolutely sure you're running this on the correct server.
   Especially if you're working with an existing GitLab install

1. Perform the initial data synchronization by running:
```bash
su - gitlab-psql -c '/opt/gitlab/embedded/bin/pg_basebackup -h PRIMARY_DB_HOST -D /var/opt/gitlab/postgresql/data/ -P -U gitlab_replicator --xlog-method=stream'
```
> **Note**: When prompted, enter the password for the `gitlab_replicator` user

1. Create a file `/var/opt/gitlab/postgresql/data/recovery.conf` containing:
```
standby_mode          = 'on'
primary_conninfo      = 'host=PRIMARY_DB_HOST port=5432 user=gitlab_replicator password=CLEARTEXT_PASSWORD'
trigger_file = '/var/opt/gitlab/postgresql/data/trigger'
```

1. Run `sudo gitlab-ctl reconfigure` a final time.

1. If you are running with multiple standby servers, repeat these steps for each server.


After the database servers are configured, move on to configuring the GitLab application servers with the appropriate details.
This step is covered in [Configuring GitLab for HA](gitlab.md).

---

Read more on high-availability configuration:

1. [Configure Redis](redis.md)
1. [Configure NFS](nfs.md)
1. [Configure the GitLab application servers](gitlab.md)
1. [Configure the load balancers](load_balancer.md)