DEV Community: Amit Chauhan

How to capture the statements from one database Cluster and run them on another YugabyteDB cluster

Amit Chauhan — Fri, 18 Aug 2023 06:18:12 +0000

What is the problem we are trying to solve here?

Often you may want to simulate the transactions from production environment in your PreProd/Dev environment to see if things like database upgrades, configuration changes, etc won't cause any regression. Essentially, We want to record the behavior in one environment and replay it in another environment.

What are we doing here?

Step by Step guide to capture statements from one YugabyteDB database cluster and replay them on another YugabyteDB database cluster. We are going to use pgreplay. This guide uses YugabyteDB. But similar steps can be utilized for Postgres database as well.

I will run a workload simulator to create 3 tables and seed them with 10K rows. You can run your App to carry bunch of database transactions.
Then we will capture postgres log file which will have all the statements (select, inserts, etc)
We will install a pgreplay on an app server and will push this file to that server.
Spin a new cluster. Run the pgreplay with the postgres file and replay the transactions on the new database cluster.

High level flow

Capture statement logs from database Cluster 1

We need to ensure that all statements are logged.
Create a tserver.flagfile file like this:

--ysql_pg_conf_csv=log_destination=csvlog,log_statement=all,log_min_messages=error,log_min_error_statement=log,log_connections=on,log_disconnections=on

Start the yugabyted with the flag file (You can set these as gflags from YBA UI)

yugabyted start --tserver_flags=flagfile=/Users/amitchauhan/Downloads/pgreplay/tserver.flagfile

Now logs in this directory /var/logs/tserver/postgres* will start showing/capturing the statements.

Run some transactions. I ran a workload simulator which created 3 tables and added 10K records.
For simplicity, I combined couple of postgresql-*.csv file into single amit.csv file.

Create a new database cluster and use pgreplay to replay the logs from the previous cluster.

I will go ahead and create a new cluster in AWS.
Spin a new machine (or use existing VM) and install pgreplay (you can build the pgreplay from source. I am going to use their docker image):

git clone https://github.com/laurenz/pgreplay.git
cd pgreplay
docker build -t laurenz/pgreplay -f Dockerfile .

I have copied over my postgres log file to this machine and will now run the pgreplay container, pass in the new database connection details.

sudo docker run --rm -ti -v /home/centos:/app -w /app laurenz/pgreplay pgreplay -h DATABASE-IP-ADDRESS -p 5433 -W XXXXPASSXXXX  -d 3 -c /app/amit.csv

Pgreplay will replay all the statements in my log file.

Verify that same number of rows are inserted in table:

Using pgcli with YugabyteDB

Amit Chauhan — Tue, 27 Jun 2023 23:27:17 +0000

pgcli is popular postgres client that does auto completion and syntax highlighting.

YugabyteDB is popular open source distributed database. If you see the architecture diagram below, YugabyteDB has Postgres Compatible YSQL API.

What this means is that most clients which work for Postgres work out of box with YugabyteDB. Let's take pgcli as an example.

Install the pgcli. On mac, if you are using brew you can simply do this (for linux see instructions here):

brew install pgcli

Once installed, let's connect to the YugabyteDB. Example:

pgcli -h 127.0.0.1 -p 5433 -U yugabyte

That's it - enjoy all the goodies offered by pgcli on YugabyteDB

Automating Jar File Creation and Release Artifact Uploads with GitHub Actions

Amit Chauhan — Tue, 27 Jun 2023 23:03:56 +0000

You have this great java project and every time you make changes to the codebase you need to manually create the jar file and upload it as a release. Let's see how you can automate this release workflow. For this post, I am assuming that your repo sits in github.

We are going to use github actions.
Create a .github/workflows directory in your repository on GitHub if this directory does not already exist.
In the .github/workflows directory, create a file named release.yml.
Copy the following code in release.yml



name: Java CI to create and upload release on pull request
on:
  push:
    branches: [ "main" ]
  pull_request:
    branches: [ "main" ]

env:
  build-number: ${GITHUB_RUN_NUMBER}

jobs:
  build:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v3
      - uses: actions/setup-java@v3
        with:
          java-version: '11'
          distribution: 'temurin'
          cache: 'maven'
      - run: mvn clean package -DskipTests
      - run: mkdir staging && mv target/yb-workload-sim.jar target/yb-workload-sim-${{ env.build-number }}.jar && cp target/*.jar staging
      - uses: actions/upload-artifact@v3
        with:
          name: Package
          path: staging
          retention-days: 1
      - uses: marvinpinto/action-automatic-releases@latest
        with:
          repo_token: "${{ secrets.YOUR-GITHUB-TOKEN }}"
          automatic_release_tag: "${{ github.run_number }}"
          title: "Automated Build ${{ github.run_number }}"
          prerelease: true
          files: staging/*.jar

Let's discuss what we are doing in above code:

on: tag indicates when this action should be triggered. In this example, I am triggering it when someone creates a pull request or when code is pushed to the main branch.
jobs:
This steps defines the various steps involved in this job. We will use ubuntu-latest os version.
Then we will use prebuilt actions checkout to get the code.
After that we setup java for compiling the jar.
We are using maven to build the jar file. We will fire that up to create a jar file.
When jar file is created, we will do some massaging to add the release number and then upload it as artifact using upload-artifact action.
Last, we will use marvinpinto/action-automatic-releases action to create and upload new release. There are multiple actions available in the marketplace to create the release.

That's it!! Now every time you push new code, github actions will create and upload jar for you.

You can see the working example here.