How to restore Bitbucket Pull Requests data from a PostgreSQL backup

[Mike Quentel]

Recently, there was a server crash that corrupted a repo in Bitbucket (self-hosted server edition running on CentOS 7). Eventually, using the Bitbucket user interface, I re-created the repo, which deleted everything from the old version, including Pull Requests (PRs).

Then, I also successfully restored the commit history from existing objects on the server and copies of objects from users. Example:

$BITBUCKET_HOME/shared/data/repositories/$REPO/objects/XX/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

I have a backup of the server where PostgreSQL held PRs. The new repo is currently in use, and has new PR data in its corresponding PostgreSQL table. I would like to be able to restore the missing rows of data pertaining to PRs into the current PostgreSQL database.

Using SQLectron, I can see there is a table

"public"."sta_pull_request"

that appears to have PR data. I also see similar tables that appear to have PR data.

Does anyone know what tables I should examine and use for restoring the PR data? Also, open to other advice on the approach here.

Many thanks.

[Mike Quentel · Accepted solution]

Per Investigating Bitbucket Server Database Schema I downloaded DBVisualizer which has built-in graphing functionality showing a diagram for the Bitbucket database. This at least points me in the right direction.

Screenshot of the diagram in DBVisualizer:

Also see

https://developer.atlassian.com/server/jira/platform/database-schema/