Deploy PostgreSQL Database Code to Dev and Test Environments Sequentially Using GitHub Actions

Modern database deployments should be repeatable, automated, and version-controlled. Instead of manually logging into servers and running SQL scripts, we can use GitHub + GitHub Actions to automatically deploy PostgreSQL changes whenever code is pushed to the main branch.

In this walkthrough, I’ll show how to:

• Test your script in the sandpit first to play it safe
• Store PostgreSQL scripts in GitHub
• When code gets pushed to main , automatically deploy to Development first
• Then deploy to Test only if Dev succeeds
• Use GitHub Environment Secrets for secure credentials
• Maintain a simple CI/CD pipeline for PostgreSQL

---

Architecture Overview

When a developer pushes SQL code to GitHub:

1. GitHub Actions starts automatically.
2. The SQL script is deployed to the Dev PostgreSQL database.
3. If successful, deployment continues to the Test PostgreSQL database.
4. This ensures controlled promotion of changes across environments.

Git Push to Main
      ↓
GitHub Actions
      ↓
Deploy to DEV
      ↓ (Success Only)
Deploy to TEST


Step 1: Generate SSH Key for GitHub Access
From the PostgreSQL sandpit server, generate an SSH key:
postgres@oel01db$ ssh-keygen -t ed25519 -C "mahekarthya@gmail.com"
Output:
Generating public/private ed25519 key pair.
Enter file in which to save the key (/var/lib/pgsql/.ssh/id_ed25519):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Key files created:
Private Key: /var/lib/pgsql/.ssh/id_ed25519
Public Key: /var/lib/pgsql/.ssh/id_ed25519.pub
Step 2: Copy Public Key
postgres@oel01db$ cd /var/lib/pgsql/.ssh/
postgres@oel01db$ cat id_ed25519.pub
Output:
ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAIBSVTawU0nB11iPH2Qj7x5MWOwDBdAzdwysdQxwhVqHN mahekarthya@gmail.com

Step 3: Add SSH Key to GitHub

In GitHub:

• Click Profile Picture → Settings
• Select SSH and GPG Keys
• Click New SSH Key
• Title: OEL-Database-Server
• Paste the public key
• Click Add SSH Key

Step 4: Verify GitHub SSH Connectivity

postgres@oel01db$ ssh -T git@github.com

Expected result:

Hi mahekarthya! You've successfully authenticated, but GitHub does not provide shell access.

Step 5: Create PostgreSQL Deployment Script

Current working directory:

postgres@oel01db$ pwd
/var/lib/pgsql/Psql-Scrtips

SQL file:

postgres@oel01db$ cat Create_gisdb.sql

\conninfo

\echo ==== List Databases before change ====
select datname from pg_database;

\echo ==== Dropping Database gisdb if exist ====
DROP DATABASE IF EXISTS gisdb;

\echo ==== Creating Database gisdb ====
CREATE DATABASE gisdb;

\echo ==== Switching to gisdb ====
\c gisdb

\conninfo

\echo ==== Creating customer table in gisdb ====
CREATE TABLE customer (
  id INT,
  custname VARCHAR(100)
);

\echo ==== Inserting rows into customer  ====
INSERT INTO customer (id, custname) VALUES
(1, 'ABC Co'),
(2, 'Honda Co'),
(3, 'Yamaha'),
(4, 'Hyundai'),
(5, 'IBM');

\echo ==== Display customer data ====
Select * from customer;

\echo ==== Final database list after change ====
select datname from pg_database;

---

Step 6: Validate Script Locally First

Before automation, always test locally (on Sandpit)

postgres@postgres# \i /var/lib/pgsql/Psql-Scrtips/Create_gisdb.sql

The script successfully:

• Dropped old database if present
• Created gisdb
• Created customer table
• Inserted rows
• Verified output

postgres@postgres# \i /var/lib/pgsql/Psql-Scrtips/Create_gisdb.sql

You are connected to database "postgres" as user "postgres" via socket in "/run/postgresql" at port "5432".

==== List Databases before change ====

  datname

-----------

 postgres

 template1

 template0

 hrdb

 salesdb

 repmgrdb

(6 rows)

==== Dropping Database gisdb if exist ====

psql:/var/lib/pgsql/Psql-Scrtips/Create_gisdb.sql:7: NOTICE:  database "gisdb" does not exist, skipping

DROP DATABASE

==== Creating Database gisdb ====

CREATE DATABASE

==== Switching to gisdb ====

psql (15.17, server 15.13)

You are now connected to database "gisdb" as user "postgres".

You are connected to database "gisdb" as user "postgres" via socket in "/run/postgresql" at port "5432".

==== Creating customer table in gisdb ====

CREATE TABLE

==== Inserting rows into customer ====

INSERT 0 5

==== Display customer data ====

 id | custname

----+----------

  1 | ABC Co

  2 | Honda Co

  3 | Yamaha

  4 | Hyundai

  5 | IBM

(5 rows)

==== Final database list after change ====

  datname

-----------

 postgres

 template1

 template0

 hrdb

 salesdb

 repmgrdb

 gisdb

(7 rows)

postgres@gisdb#

Step 7: Target AWS RDS Environments

Two PostgreSQL RDS instances were prepared.

Development Database

postgres-db-1.ckp44ikeq3w2.us-east-1.rds.amazonaws.com

Test Database

postgres-db-test.ckp44ikeq3w2.us-east-1.rds.amazonaws.com

Both instances were configured with public accessibility enabled.

In production environments, private networking with VPN, VPC peering, or self-hosted GitHub runners is recommended instead of public exposure.

Verif the Dev connectivity:

postgres@oel01db$  psql -h postgres-db-1.ckp44ikeq3w2.us-east-1.rds.amazonaws.com -U postgres -p 5432 -d postgres

Password for user postgres:

psql (15.17, server 17.6)

WARNING: psql major version 15, server major version 17.

         Some psql features might not work.

SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES128-GCM-SHA256, compression: off)

Type "help" for help.

postgres@postgres>  SELECT inet_server_addr(), inet_server_port();

 inet_server_addr | inet_server_port

------------------+------------------

 172.31.12.65     |             5432

(1 row)

postgres@postgres>  select datname from pg_database;

  datname

-----------

 template0

 template1

 postgres

 rdsadmin

(4 rows)

postgres@postgres>

Verif the Test connectivity:

postgres@oel01db$  psql -h postgres-db-test.ckp44ikeq3w2.us-east-1.rds.amazonaws.com -U postgres -p 5432 -d postgres

Password for user postgres:

psql (15.17, server 18.3)

WARNING: psql major version 15, server major version 18.

         Some psql features might not work.

SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES128-GCM-SHA256, compression: off)

Type "help" for help.

postgres@postgres> SELECT inet_server_addr(), inet_server_port();

 inet_server_addr | inet_server_port

------------------+------------------

 172.31.89.220    |             5432

(1 row)

postgres@postgres>  select datname from pg_database;

  datname

-----------

 template0

 template1

 postgres

 rdsadmin

(4 rows)

postgres@postgres>

Step 8: Create GitHub Environments

Inside GitHub repository:

Settings → Secrets and variables → Actions

Create two environments:

• Dev
• Test

For each environment, add secrets:

Secret Name	Description
DB_HOSTNAME	RDS endpoint
DB_USER	PostgreSQL username
DB_NAME	Database name
DB_PASSWORD	Password

This allows the same workflow to use different credentials safely.

Select your  git repo , then click Setting --> secret and variable and then action 

Click on New environment 

Now we can add envioemnet secret here 

Configure the DB_HOSTNAME, DB_USER, DB_NAME, and DB_PASSWORD as GitHub Secrets to be utilized by the workflow

add DB_USER

Add DB_NAME

& finally DB_PASSWORD

Now we have 4 secret for envioemnt Dev 

Create a 'Test' environment and configure the corresponding database secrets following the same procedure

And add similar secret that we added for Dev.

Step 9: Create GitHub Actions Workflow

Create folder:

postgres@oel01db$ mkdir -p .github/workflows/
postgres@oel01db$ cd .github/workflows/
postgres@oel01db$ vi Create_gisdb_action.yml

Note: The .github/workflows/ directory is a special folder recognized by GitHub. It is where you store GitHub Actions workflow files (YAML files) that automate tasks for your repository.

Workflow file:

postgres@oel01db$ cat .github/workflows/Create_gisdb_action.yml
name: Deploy Postgres script
run-name: Setting up new postgres database & deploy scritpt

on:
  push:
    branches:
      - main

jobs:
  # --- DEVELOPMENT JOB ---
  deploy-dev:
    runs-on: ubuntu-latest
    environment: Dev  # <--- This keyword tells GitHub to use Dev secrets/vars
    steps:
      - name: Checkout Code
        uses: actions/checkout@v4

      - name: Run SQL file on DEV
        run: |
          psql -h "${{ secrets.DB_HOSTNAME }}" \
               -p 5432 \
               -U "${{ secrets.DB_USER }}" \
               -d "${{ secrets.DB_NAME }}" \
               -f "Create_gisdb.sql"
        env:
          PGPASSWORD: ${{ secrets.DB_PASSWORD }}

  # --- TEST JOB ---
  deploy-test:
    runs-on: ubuntu-latest
    needs: deploy-dev # Ensures Test only runs after Dev is successful
    environment: Test # <--- This keyword swaps the secrets to Test values
    steps:
      - name: Checkout Code
        uses: actions/checkout@v4

      - name: Run SQL file on TEST
        run: |
          psql -h "${{ secrets.DB_HOSTNAME }}" \
               -p 5432 \
               -U "${{ secrets.DB_USER }}" \
               -d "${{ secrets.DB_NAME }}" \
               -f "Create_gisdb.sql"
        env:
          PGPASSWORD: ${{ secrets.DB_PASSWORD }}
postgres@oel01db$

---

Why This Workflow Is Good

Serial Deployment Control

needs: deploy-dev

This ensures:

• Dev runs first
• Test runs only if Dev succeeds

Environment-Based Secrets

Same YAML file, different credentials.

Branch Protection

Only pushes to main trigger deployments.

Step 10: Initialize Git Repository

postgres@oel01db$ git init
postgres@oel01db$ git remote add origin git@github.com:mahekarthya/devops-demo-pub.git
postgres@oel01db$ git add .
postgres@oel01db$ git commit -m "Initial commit"
postgres@oel01db$ git branch -M main
postgres@oel01db$ git push -u origin main

Step 11: Automatic Deployment Triggered

Since the workflow trigger is:

on:
  push:

Any push to main automatically starts:

1. Deploy to Dev
2. Then Deploy to Test

No manual intervention required.

Let’s push the code and watch the GitHub Action workflow in action!

postgres@oel01db$ ls -lart

total 8

-rw-r--r--  1 postgres postgres  712 Apr 25 22:16 Create_gisdb.sql

drwxr-xr-x  3 postgres postgres   23 Apr 25 23:17 .github

drwx------. 7 postgres postgres 4096 Apr 25 23:33 ..

drwxr-xr-x  3 postgres postgres   45 Apr 25 23:39 .

postgres@oel01db$

postgres@oel01db$  git config --global user.name "mahekarthya"

postgres@oel01db$

postgres@oel01db$ git config --global user.email "mahekarthya@gmail.com"

postgres@oel01db$

postgres@oel01db$ git init

Initialized empty Git repository in /var/lib/pgsql/Psql-Scrtips/.git/

postgres@oel01db$

postgres@oel01db$  git remote -v

postgres@oel01db$

postgres@oel01db$ git remote add origin git@github.com:mahekarthya/devops-demo-pub.git

postgres@oel01db$

postgres@oel01db$  git remote -v

origin  git@github.com:mahekarthya/devops-demo-pub.git (fetch)

origin  git@github.com:mahekarthya/devops-demo-pub.git (push)

postgres@oel01db$

postgres@oel01db$ git add .

postgres@oel01db$

postgres@oel01db$ git status

# On branch master

#

# Initial commit

#

# Changes to be committed:

#   (use "git rm --cached <file>..." to unstage)

#

#       new file:   .github/workflows/Create_gisdb_action.yml

#       new file:   Create_gisdb.sql

#

postgres@oel01db$  git commit -m "Initial commit"

[master (root-commit) e4a7760] Initial commit

 2 files changed, 77 insertions(+)

 create mode 100644 .github/workflows/Create_gisdb_action.yml

 create mode 100644 Create_gisdb.sql

postgres@oel01db$

postgres@oel01db$  git branch -M main

postgres@oel01db$

postgres@oel01db$ git status

# On branch main

nothing to commit, working directory clean

postgres@oel01db$

postgres@oel01db$ git branch

* main

postgres@oel01db$ git push -u origin main

To git@github.com:mahekarthya/devops-demo-pub.git

 ! [rejected]        main -> main (fetch first)

error: failed to push some refs to 'git@github.com:mahekarthya/devops-demo-pub.git'

hint: Updates were rejected because the remote contains work that you do

hint: not have locally. This is usually caused by another repository pushing

hint: to the same ref. You may want to first merge the remote changes (e.g.,

hint: 'git pull') before pushing again.

hint: See the 'Note about fast-forwards' in 'git push --help' for details.

postgres@oel01db$

postgres@oel01db$ git pull origin main

warning: no common commits

remote: Enumerating objects: 85, done.

remote: Counting objects: 100% (85/85), done.

remote: Compressing objects: 100% (56/56), done.

remote: Total 85 (delta 21), reused 19 (delta 6), pack-reused 0 (from 0)

Unpacking objects: 100% (85/85), done.

From github.com:mahekarthya/devops-demo-pub

 * branch            main       -> FETCH_HEAD

Already up-to-date!

Merge made by the 'recursive' strategy.

postgres@oel01db$ git push origin main

Counting objects: 8, done.

Compressing objects: 100% (6/6), done.

Writing objects: 100% (7/7), 1.26 KiB | 0 bytes/s, done.

Total 7 (delta 1), reused 0 (delta 0)

remote: Resolving deltas: 100% (1/1), done.

To git@github.com:mahekarthya/devops-demo-pub.git

   63b8816..12083ba  main -> main

postgres@oel01db$

Since the trigger is set to 'push,' the workflow will kick off automatically every time we commit and push our code,  you could see it has been completed .

Step 12: Verify Deployment

After workflow completion, connect to both RDS databases and validate:

select datname from pg_database;
select * from customer;

You should see:

• gisdb
• customer table
• Inserted sample rows

Dev database:

Test database:

Understanding github.event.inputs in GitHub Actions

When working with GitHub Actions, you often want your workflows to be flexible rather than hard-coded. One powerful way to achieve this is by using custom inputs with the workflow_dispatch event.

The github.event.inputs context allows you to access values that are provided manually at the time of triggering a workflow. This makes your automation dynamic and reusable across different scenarios.

---

🔹 What is workflow_dispatch?

workflow_dispatch is a trigger that lets you manually run a workflow from the GitHub UI. While triggering it, you can pass custom input values, which can then be used inside the workflow.

---

🔹 Why use github.event.inputs?

Instead of hardcoding values like environment names or version numbers, you can:

• Provide inputs at runtime
• Make workflows reusable
• Reduce duplication
• Improve control over deployments and scripts

---

🔹 Example 1: Basic Input Usage

This example demonstrates how to define a simple input and print it during workflow execution.

on:
  workflow_dispatch:
    inputs:
      logLevel:
        description: 'Log level'
        required: true
        default: 'warning'

jobs:
  print-log:
    runs-on: ubuntu-latest
    steps:
      - name: Print the input
        run: echo "The log level is ${{ github.event.inputs.logLevel }}"

✅ What happens here?

• A user manually triggers the workflow
• They can enter a value for logLevel (or use the default)
• The workflow prints the selected value

---

🔹 Example 2: Advanced Inputs with Choices

You can also define more structured inputs, such as dropdowns and optional parameters.

1. Defining Inputs

on:
  workflow_dispatch:
    inputs:
      environment:
        description: 'Target environment'
        required: true
        default: 'development'
        type: choice
        options:
          - development
          - staging
          - production
      db_patch_version:
        description: 'Version number for the patch'
        required: false
        type: string

2. Accessing Inputs in Workflow

jobs:
  deploy:
    runs-on: ubuntu-latest
    steps:
      - name: Run Migration
        run: |
          echo "Deploying to: ${{ github.event.inputs.environment }}"
          echo "Applying patch: ${{ github.event.inputs.db_patch_version }}"

✅ What happens here?

• The user selects an environment from a dropdown
• Optionally provides a database patch version
• The workflow uses these inputs during execution

---

🔹 Key Takeaways

• github.event.inputs is used to access user-provided values in manually triggered workflows
• Inputs must be defined under on: workflow_dispatch

• Values are accessed using:

  ${{ github.event.inputs.<input_name> }}
  

• Supports multiple types like:
  • string
  • choice (dropdown)
  • boolean (in newer versions)

JFrog Artifactory - How to install

JFrog Artifactory OSS Installation Guide

CentOS 9 + PostgreSQL 17

This guide provides a structured workflow to install JFrog Artifactory OSS using PostgreSQL as an external database. This setup is preferred for production-like environments because it offers better performance and reliability than the default embedded database.

---

1. System Preparation

Before installing the application, ensure the host environment meets the software requirements.

• Update System: Refresh repository metadata and installed packages.

  Bash

  yum update -y
  

• Java Runtime: Artifactory is a Java-based application and requires JDK 21.

  Bash

  dnf install -y java-21-openjdk java-21-openjdk-devel
  java -version

2. External Database Setup (PostgreSQL 17)

Using an external database ensures that your artifact metadata is stored robustly.

• Initialization: Here we use a postgres database to store the metadata. After installing the PostgreSQL server, initialize the data directory and enable the service.

  Bash

  /usr/pgsql-17/bin/postgresql-17-setup initdb
  systemctl enable postgresql-17 --now
  

• Access Control: Edit /var/lib/pgsql/17/data/pg_hba.conf to allow Artifactory to connect. Change the local connection method from ident or peer to md5 (or trust for initial testing).

• Database Provisioning: Create a dedicated database and user for Artifactory.

  SQL

  CREATE USER artifactory WITH PASSWORD 'your_password';
  CREATE DATABASE artifactory WITH OWNER artifactory;
  GRANT ALL PRIVILEGES ON DATABASE artifactory TO artifactory;

3. JFrog Artifactory Installation

• Repository Configuration: Download the official JFrog RPM repository definition and move it to the system’s repo directory.

  Bash

  curl -L https://bintray.com/jfrog/artifactory-rpms/rpm -o jfrog-artifactory-rpms.repo
  mv jfrog-artifactory-rpms.repo /etc/yum.repos.d/
  

• Package Installation: Install the Open Source (OSS) version.

  Bash

  yum install jfrog-artifactory-oss -y

4. Security & Connectivity Configuration

• Encryption (Master Key): Artifactory requires a unique 16-bit hex key to encrypt sensitive system data (like database passwords).

  Bash

  mkdir -p $JFROG_HOME/artifactory/var/etc/security
  openssl rand -hex 16 > $JFROG_HOME/artifactory/var/etc/security/master.key
  chown -R artifactory: /opt/jfrog/artifactory/var/etc/security
  

• Database Linking: Configure the system.yaml file to tell Artifactory to use PostgreSQL instead of the default Derby database.The system.yaml file is the primary configuration file for all Artifactory services. You must explicitly define the connection string to the external database.

  YAML

  shared:
    database:
      type: postgresql
      driver: org.postgresql.Driver
      url: "jdbc:postgresql://localhost:5432/artifactory"
      username: artifactory
      password: your_password
  

5. Service Launch & Verification

• Network Access: Disable the firewall or open ports 8081 (Artifact data) and 8082 (UI/Router).

  Bash

  systemctl stop firewalld && systemctl disable firewalld
  

• Startup: Start the Artifactory service and monitor the console logs for a "Ready" state.

  Bash

  systemctl start artifactory.service
  tail -f /opt/jfrog/artifactory/var/log/console.log
  

• Dashboard Access: Navigate to http://<SERVER_IP>:8082.

• Login: admin

• Password: password (You will be prompted to change this immediately).