Building a Safer PostgreSQL CI/CD Pipeline with GitHub Actions: Dev → PR Review → Test Promotion

In my previous post, we explored a simple push-to-main deployment strategy. While functional, that model is not considered an industry best practice for most teams.

A more reliable and professional approach is a multi-stage deployment workflow:

• Developers push code to a develop branch
• A Pull Request to main triggers deployment to the Dev environment
• After validation and approval, merging into main promotes the change to the Test environment

This creates better control, safer releases, and cleaner change management.

Why This Approach Is Better Than Direct Push to Main

With a direct push-to-main model:

• No review process
• No early validation environment
• Higher risk of broken deployments
• Harder rollback management

With a staged PR model:

• Code review before merge
• Automated validation in Dev
• Controlled promotion to Test
• Better audit trail
• Aligns with real DevOps workflows

Initial Setup – Create the Develop Branch

We begin from the existing repository.

postgres@oel01db$ cd Psql-Scrtips/

postgres@oel01db$ ls -lrt

total 4

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

postgres@oel01db$ git status

# On branch main

nothing to commit, working directory clean

postgres@oel01db$ git pull origin main

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

 * branch            main       -> FETCH_HEAD

Already up-to-date.

postgres@oel01db$

postgres@oel01db$ git checkout -b develop

Switched to a new branch 'develop'

postgres@oel01db$ git status

# On branch develop

nothing to commit, working directory clean

At this point, development work is isolated from production-style branches.

Modify the Deployment script from the develop branch

We update the file Create_gisdb.sql.

postgres@oel01db$ vi Create_gisdb.sql

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'),

(5, 'ICICI');

\echo ==== Display customer data ====

Select * from customer;

\echo ==== Final database list after change ====

select datname from pg_database;

Commit and Push to Develop

postgres@oel01db$ git status

# On branch develop

# Changes not staged for commit:

#   (use "git add <file>..." to update what will be committed)

#   (use "git checkout -- <file>..." to discard changes in working directory)

#

#       modified:   Create_gisdb.sql

#

no changes added to commit (use "git add" and/or "git commit -a")

postgres@oel01db$ git add .

postgres@oel01db$ git commit -m "added another insert - (5, 'ICICI')"

[develop 91ea18f] added another insert - (5, 'ICICI')

 1 file changed, 2 insertions(+), 1 deletion(-)

postgres@oel01db$ git push origin develop

GitHub now detects a new branch and suggests creating a Pull Request.

Create Pull Request from Develop → Main

Once you click Compare & Pull Request, GitHub begins the review workflow.

After approval, click Merge.

Now the changes are available in the main branch.

Now click on merge.

Now you can see the changes in the main branch

Update GitHub Actions Workflow

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

on:
  push:
    branches:
      - main

  pull_request:
    types:
      - opened
      - synchronize
    branches:
      - main

jobs:
  deploy-dev:
    runs-on: ubuntu-latest
    if: github.event_name == 'pull_request' && github.event.pull_request.base.ref == 'main'
    environment: Dev

    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 }}

  deploy-test:
    runs-on: ubuntu-latest
    if: github.event_name == 'push' && github.ref_name == 'main'
    environment: Test

    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$

In this specific scenario, we should not use needs: deploy-dev in deploy-test job, because deploy-dev and deploy-test are triggered by different GitHub events:

So this workflow will,

Deploy SQL to Dev when a Pull Request is created/updated to main

Deploy SQL to Test when code is pushed/merged to main. 

High-Level Flow

Develope Branch
   ↓
Pull Request to main
   ↓
Deploy to DEV database

PR approved + merged
   ↓
Push to main
   ↓
Deploy to TEST database

1. The develop Branch
Action: You work on your SQL scripts here.
Result: Nothing happens to your databases yet. Your code is isolated.
2. Pull Request (PR) to main
Action: You "propose" your changes to the main branch.
Result: This triggers the deploy-dev job because the event is a pull_request.
Database: Your DEV database is updated. You can now verify that the script works in a real environment.
3. PR Approved + Merged
Action: You or a teammate reviews the code. Since the "Dev" check passed, you hit Merge.
Result: This creates a push event on the main branch.
4. Deploy to TEST Database
Action: The push event triggers the deploy-test job.
Result: The deploy-dev job is skipped (because it’s no longer a PR), and your TEST database is updated with the final, approved code.

Modify SQL Again for New Release

Now we update the script again.

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'),
(5, 'ICICI');

UPDATE customer
SET id = 6
WHERE custname = 'ICICI';

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

\echo ==== Final database list after change ====
select datname from pg_database;
postgres@oel01db$

Push New Changes

postgres@oel01db$ git status

# On branch develop

# Changes not staged for commit:

#   (use "git add <file>..." to update what will be committed)

#   (use "git checkout -- <file>..." to discard changes in working directory)

#

#       modified:   .github/workflows/Create_gisdb_action.yml

#       modified:   Create_gisdb.sql

#

# Untracked files:

#   (use "git add <file>..." to include in what will be committed)

#

#       1

no changes added to commit (use "git add" and/or "git commit -a")

postgres@oel01db$

postgres@oel01db$ git status

# On branch develop

# Changes not staged for commit:

#   (use "git add <file>..." to update what will be committed)

#   (use "git checkout -- <file>..." to discard changes in working directory)

#

#       modified:   .github/workflows/Create_gisdb_action.yml

#       modified:   Create_gisdb.sql

#

# Untracked files:

#   (use "git add <file>..." to include in what will be committed)

#

#       1

no changes added to commit (use "git add" and/or "git commit -a")

postgres@oel01db$ git add .

postgres@oel01db$ git commit -m "modifed files"

[develop ac6d933] modifed files

 3 files changed, 75 insertions(+), 5 deletions(-)

 create mode 100644 1

postgres@oel01db$

postgres@oel01db$ git push origin develop

Counting objects: 11, done.

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

Writing objects: 100% (6/6), 778 bytes | 0 bytes/s, done.

Total 6 (delta 2), reused 0 (delta 0)

remote: Resolving deltas: 100% (2/2), completed with 2 local objects.

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

   91ea18f..ac6d933  develop -> develop

postgres@oel01db$

At this moment:

No action is triggered yet because no PR event occurred.

Trigger Pull Request Again

Once the Pull Request is created or updated:

• deploy-dev runs
• deploy-test is skipped

Exactly as expected.

As you could see the pull request has triggered the deployment to Dev database 

The workflow skipped the deploy-test job because the if condition was not met for a Pull Request event.

Validate Dev Deployment

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> \c gisdb

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)

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

postgres@gisdb> select datname from pg_database;

  datname

-----------

 template0

 template1

 postgres

 rdsadmin

 gisdb

(5 rows)

postgres@postgres> \c gisdb

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)

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

postgres@gisdb> select * from customer ;

 id | custname

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

  1 | ABC Co

  2 | Honda Co

  3 | Yamaha

  4 | Hyundai

  5 | IBM

  6 | ICICI

(6 rows)

Merge Pull Request

After clicking Merge, GitHub triggers:

• push event on main

Now:

• deploy-test runs
• deploy-dev is skipped

Perfect separation of responsibilities.

This time it skip the deploy-dev job

Validate Test Deployment

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> \c gisdb

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)

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

postgres@gisdb> select datname from pg_database;

  datname

-----------

 template0

 template1

 postgres

 rdsadmin

 gisdb

(5 rows)

postgres@postgres> \c gisdb

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)

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

postgres@gisdb> select * from customer ;

 id | custname

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

  1 | ABC Co

  2 | Honda Co

  3 | Yamaha

  4 | Hyundai

  5 | IBM

  6 | ICICI

(6 rows)