Migrate GitBucket database to postgreSQL

Assumed reader

An administrator who has been running GitBucket on an embedded database and is starting to feel uneasy about its stability.

Readers who do not expect much

Administrators who are planning to operate GitBucket + postgreSQL should refer to the following page. → Start GitBucket + PostgreSQL with docker-compose

procedure

We will create a temporary GitBucket server and follow the steps to migrate the data to postgreSQL.

This procedure basically follows the official guide. → https://github.com/gitbucket/gitbucket/wiki/External-database-configuration#postgresql

I am using docker as the server operating environment, but the operation on the Web browser and psql does not change even when running on the actual server.

Start GitBucket server in embedded database

docker run -it --rm --name gitbucket -p 8080:8080 -v `pwd`/gitbucket-data:/gitbucket gitbucket/gitbucket

http://localhost:8080/Access to root/Sign in as root. Users, groups, projects to create data to migrate,Add issues appropriately.



#### Data export

 From the menu at the top right of the web page, System Administration → Data export/import. Check the items to export (basically all are fine) and click the Export button. It is saved locally with a name such as ``` gitbucket-export-7106966519106537149.sql```.

#### GitBucket server stopped

 Stop GitBucket once here.

### PostgreSQL server preparation

    docker run -it --rm --name postgres -p 5432:5432 -v `pwd`/postgres-data:/var/lib/postgresql/data -e POSTGRES_PASSWORD=passwordX postgres:12-alpine

 After the server starts, connect to the postgreSQL server with the psql command and create the database and user. Set the user name and password appropriately. You should also change the password for user postgres.

$ psql -h localhost -p 5432 -U postgres Password for user postgres: psql (9.2.24, server 12.5) WARNING: psql version 9.2, server version 12.0. Some psql features might not work. Type "help" for help.

postgres=# create database gitbucket; CREATE DATABASE postgres=# create user sa password 'sa'; CREATE ROLE postgres=# $


 Once the settings are complete, stop the postgres server.

### Change database settings of GitBucket
 Edit gitbucket-data/database.conf.

 Before editing

db { url = "jdbc:h2:${DatabaseHome};MVCC=true" user = "sa" password = "sa" }


 After editing

db { url = "jdbc:postgresql://postgres:5432/gitbucket" user = "sa" password = "sa" }



#### **`The 5432 part contains the host name and port number where the postgreSQL server actually runs. The user and password were set with the psql command earlier.`**
```//postgres


### Start the server

 Start postgreSQL and gitbubket server in order. But be careful.

#### (Docker environment only) Creating a network bridge

 This is only when the server is running with docker, but it is necessary to create a bridge for communication between docker containers (→ https://knowledge.sakura.ad.jp/16082/).

    docker network create gitbucket-network

 Specify this network when starting the docker container

#### start postgreSQL server

    docker run -it --rm --name postgres --network gitbucket-network -p 5432:5432 -v `pwd`/postgres-data:/var/lib/postgresql/data postgres:12-alpine

#### Start gitbucket server

    docker run -it --rm --name gitbucket --network gitbucket-network -p 8080:8080 -v `pwd`/gitbucket-data:/gitbucket gitbucket/gitbucket

#### Make sure gitbucket and postgreSQL work together

 At this point, gitbucket connects to the postgreSQL server, recognizes that the gitbucket database is empty, and starts up in the initial state.

```root/root```Log in with, open system administration → system settings, and check that the database is postgresql.

 ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3847/4acd94df-78b5-2537-4ca3-20900631f72e.png)


### Data import

 Finally import the data. Open `` `Data export/import```, specify the file downloaded at the time of export from the file selection in his Import section at the bottom of the screen, and press the green import button.

 Next, open the Data viewer and execute the following SQL line by line. You can also use the psql command.

SELECT setval('label_label_id_seq', (select max(label_id) + 1 from label)); SELECT setval('access_token_access_token_id_seq', (select max(access_token_id) + 1 from access_token)); SELECT setval('commit_comment_comment_id_seq', (select max(comment_id) + 1 from commit_comment)); SELECT setval('commit_status_commit_status_id_seq', (select max(commit_status_id) + 1 from commit_status)); SELECT setval('milestone_milestone_id_seq', (select max(milestone_id) + 1 from milestone)); SELECT setval('issue_comment_comment_id_seq', (select max(comment_id) + 1 from issue_comment)); SELECT setval('ssh_key_ssh_key_id_seq', (select max(ssh_key_id) + 1 from ssh_key)); SELECT setval('priority_priority_id_seq', (select max(priority_id) + 1 from priority));


 * Developer takezoe's initial article ([Running GitBucket with PostgreSQL or MySQL](https://takezoe.hatenablog.com/entry/2016/07/16/025154)) also describes SQL related to activity, but this is no longer needed after version 4.34.0.

 done

 Move through and make sure there are no problems.



Recommended Posts

Migrate GitBucket database to postgreSQL
[Rails] rails new to create a database with PostgreSQL
[Java / PostgreSQL] Connect the WEB application to the database
Migrate from JUnit 4 to JUnit 5
Connect from Java to PostgreSQL
Docker Easy Build Database (PostgreSQL)
Change from SQLite3 to PostgreSQL
How to migrate from JUnit4 to JUnit5
[PostgreSQL] If you want to delete the Rails app, delete the database first!
Dynamically switch the database to connect to
How to set Spring Boot + PostgreSQL
Migrate from on-premise Pukiwiki to esa.io \ (⁰⊖⁰) /
Rails DB PostgreSQL changed to MySQL