PostgreSQL Version Control with Atlassian Bitbucket

In systems engineering, communication is a key element to achieving success on any project. This is because it’s critical to the entire cycle of development; starting from collecting requirements to delivering a minimum viable product.

Using a distributed version control system (like Git – an industry standard), developers can submit small pieces of code and work together with services like Bitbucket built around it. Hosting Bitbucket is possible when the data generated by its users have a database to stay, just like PostgreSQL, but integrating both requires additional configurations for executing in different machines.

Networking Overview

A local area network can pass the information between the programs without the need of exposing it to the outside network, depending on where the users would be.

 

1.1. Local area network (LAN).

With the separation of concerns, Bitbucket and PostgreSQL can talk to each other for achieving its common objective, providing a distributed version control system platform.

 

1.2. Bitbucket accessing PostgreSQL.

PostgreSQL relies upon a socket provided by the operating system, so the data of the cluster is kept behind a door protected by firewall rules.

 

1.3. PostgreSQL data source.

Getting Started

There is not much to say as this is a pretty simple process. PostgreSQL will need to be set up with a new user and database ready for Bitbucket.

PostgreSQL

# Part 1: Preparing the database.

$ pg_lsclusters

$ sudo systemctl -a | grep postgres

$ sudo -u postgres psql -c "du" -c "l"

 

2.1. Verifying if there are clusters already running.

# Part 2: Creating a new user (role) and database.

$ sudo -u postgres psql -c "create role thiago with createdb login password 'Th14g0_P4ssw0rd'"

$ psql -d postgres -c "create database bitbucket_db"

$ psql -d bitbucket_db -c "du" -c "l"

 

2.2. The owner of the database isn’t the superuser postgres.

# Part 3: Changing the cluster configuration (postgresql.conf).

$ sudo -u postgres psql -c "show config_file"

$ sudo cat /etc/postgresql/11/main/postgresql.conf | grep listen_addresses

$ sudo sed -i "s|#listen_addresses = 'localhost'|listen_addresses = '*'t|" /etc/postgresql/11/main/postgresql.conf

$ sudo cat /etc/postgresql/11/main/postgresql.conf | grep listen_addresses

 

2.3. Allowing remote connections (postgresql.conf).

# Part 4: Changing the cluster configuration (pg_hba.conf).

$ sudo wc -l /etc/postgresql/11/main/pg_hba.conf

$ sudo tail -3 /etc/postgresql/11/main/pg_hba.conf

$ sudo sed -i "$ a # Allow remote connections (listen_addresses = '*') with authentication" /etc/postgresql/11/main/pg_hba.conf

$ sudo sed -i "$ a hosttallttalltt192.168.0.0/16ttmd5" /etc/postgresql/11/main/pg_hba.conf

$ sudo sed -i "$ a hosttallttalltt::/0tttmd5" /etc/postgresql/11/main/pg_hba.conf

$ sudo wc -l /etc/postgresql/11/main/pg_hba.conf

$ sudo tail -3 /etc/postgresql/11/main/pg_hba.conf

 

2.4. Changing access permissions (pg_hba.conf).

# Part 5: Restarting the cluster.

$ sudo -u postgres psql -c "show listen_addresses"

$ ss -nlp | grep 5432

$ sudo systemctl restart postgresql@11-main

$ sudo -u postgres psql -c "show listen_addresses"

$ ss -nlp | grep 5432

 

2.5. Applying the changes.

# Part 6: Opening the door.

$ sudo ufw status

$ sudo ufw allow 5432/tcp

$ sudo ufw status

$ ip addr show

 

2.6. Configuring the firewall and displaying the IP address.

# Part 7: Set a password for the superuser role.

$ sudo -u postgres psql -c "password"

 

2.7. Changing the admin password.

Bitbucket

Here is what you will need to then do on the Bitbucket side.

# Part 1: Verifying if the earlier set up was correct.

$ telnet 192.168.0.106 5432

# (Optional) Using psql.

$ sudo -u postgres psql -h 192.168.0.106 -p 5432 -d bitbucket_db -U thiago -c "conninfo"

 

3.1. Reaching the database remotely.

# Part 2: Extracting the Bitbucket Server.

$ ls

$ tar xzf atlassian-bitbucket-6.10.0

$ ls

$ du -sh atlassian-bitbucket-6.10.0

$ tree -L 1 atlassian-bitbucket-6.10.0

$ tree -L 1 atlassian-bitbucket-6.10.0/bin

 

3.2. Scripts for Linux and Windows.

# Part 3: Modifying the script (set-bitbucket-home.sh).

$ mkdir bitbucket-home

$ echo $(pwd)/bitbucket-home

$ cat atlassian-bitbucket-6.10.0/bin/set-bitbucket-home.sh | grep BITBUCKET_HOME=$

$ sed -i 's|BITBUCKET_HOME=$|BITBUCKET_HOME=/home/thiago/Documents/severalnines.com/database-blog/bitbucket-home|' atlassian-bitbucket-6.10.0/bin/set-bitbucket-home.sh

$ cat atlassian-bitbucket-6.10.0/bin/set-bitbucket-home.sh | grep BITBUCKET_HOME=/

 

3.3. Configuring BITBUCKET_HOME.

# Part 4: Modifying the script (set-jre-home.sh).

$ readlink -f $(which java)

$ cat atlassian-bitbucket-6.10.0/bin/set-jre-home.sh | grep JRE_HOME=$

$ sed -i 's|JRE_HOME=$|JRE_HOME=/usr/lib/jvm/java-11-openjdk-amd64|' atlassian-bitbucket-6.10.0/bin/set-jre-home.sh

$ cat atlassian-bitbucket-6.10.0/bin/set-jre-home.sh | grep JRE_HOME=/

 

3.4. Configuring JRE_HOME (Java).

# Part 5: Checking the hardware.

$ cat /proc/cpuinfo | grep processor | wc -l

$ free -h

 

3.5. CPU cores and RAM at startup.

# Part 6: Running the Bitbucket Server with Elasticsearch.

$ ./atlassian-bitbucket/bin/start-bitbucket.sh

$ free -h

 

3.6. Executing with Elasticsearch (Default).

# Part 7: Running the Bitbucket Server without Elasticsearch.

$ ./atlassian-bitbucket/bin/start-bitbucket.sh --no-search

$ free -h

 

3.7. Executing without Elasticsearch (Saves 1 GB of RAM).

# Part 8: Quick look at BITBUCKET_HOME.

$ du -sh bitbucket-home

$ tree -L 1 bitbucket-home

 

3.8. Inside of BITBUCKET_HOME.

Integrating PostgreSQL & Bitbucket

After configuring PostgreSQL and Bitbucket, their integration must be done through the browser (http://localhost:7990/).

 

4.1. Bitbucket starting up.

# Displaying tables

$ psql -h 192.168.0.106 -d bitbucket_db -c "dt"

 

4.2. Listing current database tables.

You can now setup Bitbucket to use Java Persistence API, with Hibernate as the implementation, for creating the domain model in the database, using the PostgreSQL JDBC driver.

 

4.3. Data Source configuration.

# Displaying tables (again)

$ psql -h 192.168.0.106 -d bitbucket_db -c "dt"

 

4.4. Listing again, showing 10 of 164 rows.

# Verifying the connection pool.

$ psql -h 192.168.0.106 -d bitbucket_db -c "select pid,usename,application_name,state from pg_stat_activity where datname = 'bitbucket_db'"

 

4.5. Displaying the connection pool.

Conclusion

Keep in mind that if your network is using DHCP, it’s a good idea to configure the IP address to static in your router, or Bitbucket may fail trying to find PostgreSQL later on.

Most of this blog has made use of regular expressions for changing configuration files without opening text editors, but they can be used as well on web browsers for debugging purposes, try searching for the error message “could not change directory to ?: Permission denied” with double quotes, or any other issue you may find, replacing the computer specific path with wildcard ‘?’.