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
maintriggers deployment to the Dev environment -
After validation and approval, merging into
mainpromotes 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,
mainmain. High-Level Flow
Develope Branch
↓
Pull Request to main
↓
Deploy to DEV database
PR approved + merged
↓
Push to main
↓
Deploy to TEST database1. The
developBranch
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
mainbranch.Result: This triggers the
deploy-devjob because the event is apull_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
pushevent on themainbranch.4. Deploy to TEST Database
Action: The
pushevent triggers thedeploy-testjob.Result: The
deploy-devjob 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-devrunsdeploy-testis skipped
Exactly as expected.
As you could see the pull request has triggered the deployment to Dev database
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:
-
pushevent onmain
Now:
-
deploy-testruns -
deploy-devis 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)
