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:

GitHub Actions starts automatically.
The SQL script is deployed to the Dev PostgreSQL database.
If successful, deployment continues to the Test PostgreSQL database.
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:

Deploy to Dev
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:

Shell-scripting

Sunday, 26 April 2026