Installing a Private GitLab CE Server with PostgreSQL

GitLab is great for programming collaboration, but as I've found out, it's become very useful to me for writing. I use it to keep track of revisions and my work in general.

But I like to tinker, so I didn't just stop at installing Gitlab CE on a VM - that would be too easy.

I went with a proper GitLab CE server setup with separate PostgreSQL database server and a replica for failover.

I'll share the same setup with you in this tutorial:

• Setup GitLab server on one system
• External PostgreSQL as primary database for GitLab on another server
• Set up replica server of PostgreSQL in failover mode so that if the main database is lost or damaged, the replica can take over

As you can guess, the setup requires three servers, one for the main GitLab application, one for the primary database, and one for the replica.

I will be using Linode for this project. You may also use DigitalOcean. New users get $100 free credits for 60 days.

Step 1: Install Gitlab CE

SSH into the server where you'll be installing GitLab.

Before you install a new package, it is a good practice to update the package cache and upgrade the packages.

sudo apt update && sudo apt upgrade -y

If you are wondering, the extra -y at the end will automatically 'enter y' when it expects you to manually confirm the beginning of the update process.

Next, install the dependency packages. You may already have some of these packages.

sudo apt install -y ca-certificates curl lsb-release openssh-server tzdata
debian-archive-keyring apt-transport-https software-properties-common -y

Let's add the GitLab repository to your system so that it can fetch packages directly from GitLab. There is a dedicated shell script that does all this for you.

curl -sS https://packages.gitlab.com/install/repositories/gitlab/gitlab-ce/script.deb.sh | sudo bash

Install Gitlab Community Editon, which means you are self-hosting GitLab:

sudo apt install gitlab-ce

Edit the Gitlab configuration file to set the hostname and other parameters:

sudo nano /etc/gitlab/gitlab.rb

external_url 'http://gitlab.xxx.com'

Step 2: Install PostgreSQL

SSH into the server, which will be used for the database.

Update the package cache and upgrade the system:

sudo apt update && sudo apt upgrade -y

Now install PostgreSQL:

sudo apt install postgresql postgresql-contrib -y

Make sure PostgreSQL is running :

systemctl status postgresql

root@db1:~# systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Fri 2023-08-18 16:37:09 UTC; 1h 19min ago
    Process: 505 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
   Main PID: 505 (code=exited, status=0/SUCCESS)
        CPU: 736us

Aug 18 16:37:09 db1.cluster systemd[1]: Starting PostgreSQL RDBMS...
Aug 18 16:37:09 db1.cluster systemd[1]: Finished PostgreSQL RDBMS.
root@db1:~# 

Enable PostgreSQL to automatically run at startup:

sudo systemctl enable postgresql

Step 3: Configure the primary database

Log in to PostgreSQL under the postgres user that would have been automatically created for you on installation.

Create the replica user that PostgreSQL will use to connect to the replica:

su postgres
psql
CREATE ROLE replica_user WITH REPLICATION LOGIN PASSWORD 'P@ssword321';

Create the main database, user and password. Of course, use a strong password you can remember.

CREATE ROLE gitlab WITH SUPERUSER PASSWORD 'P@ssword321';
CREATE DATABASE gitlabprod;
ALTER DATABASE gitlabprod OWNER TO gitlab;
\q

Edit the pg_hba.conf file in /etc/postgresql/xx/main (Change xx to the appropriate version number):

sudo nano /etc/postgresql/13/main/pg_hba.conf

Append this line to the end of the file :

host db_name db_user primary_gitlab_server_IP/32 md5

On the primary GitLab server, you need to make further edits to the gitlab.rb configuration file:

sudo nano /etc/gitlab/gitlab.rb

Disable the bundled Omnibus provided by

PostgreSQL postgresql['enable'] = false

PostgreSQL connection details:

gitlab_rails['db_adapter'] = 'postgresql'
gitlab_rails['db_encoding'] = 'unicode' gitlab_rails
['db_host'] = '10.1.0.5' # IP/hostname of database server 
gitlab_rails['db_password'] = 'DB password'

Back on the database server, edit the postgresql.conf file and change the following values:

sudo nano /etc/postgresql/xx/main/postgresql.conf

• Uncomment and change the "listen" section from "localhost" to *
• Change the wal_level directive from "replica" to "logical"

On the GitLab Server, reconfigure GitLab to apply the settings. This will take about 5 or 6 minutes to complete. :

gitlab-ctl reconfigure

Change the default password. Your username will be root.

gitlab-rake "gitlab:password:reset[root]"

Step 4: Configuring the replica server

SSH into the replica server.

Update the package cache and upgrade the system:

sudo apt update && sudo apt upgrade -y

Now install PostgreSQL:

sudo apt install postgresql postgresql-contrib -y

The newly installed PostgreSQL will start running. However, you have to stop the running server in preparation for sync with the primary database:

sudo systemctl stop postgresql

Remove all current files in PostgreSQL's data directory:

sudo rm -rv /var/lib/postgresql/xx/main/

Run pg_basebackup in order to transfer the primary node's data over to the replica:

sudo pg_basebackup -h <ip of primary node> -U replica_user -X stream -C -S replica_1 -v -R -W -D /var/lib/postgresql/xx/main/

Fix permissions and grant ownership to the correct user :

sudo chown postgres -R /var/lib/postgresql/14/main/

Restart PostgreSQL :

sudo systemctl start postgresql

On the primary database server, enter psql and run this query to test :

SELECT client_addr, state
FROM pg_stat_replication;

If you get a result that says "streaming" - you are now replicating data from the host to the replica. Nice job!

Did you manage to set it up?

The entire procedure takes time and effort. But for a seasoned sysadmin, it should not be much of a trouble.

I have tried to lay out all the steps I used. But if I overlooked any step or if you face any issues, please let me know in the comment section.