postgres-db-setup

Automated provisioning and deletion of PostgreSQL databases and roles on Civo managed database instances. One workflow per operation, with automatic rollback on failure.

Overview

Workflow	Trigger	Timeout	Description
Provision DB	Manual	10 min	Create database + role on dev, test, and/or prod
Delete DB	Manual	10 min	Drop database + role from selected environments

Infrastructure

Environment	Server	Firewall
Dev + Test	Shared Civo managed PostgreSQL (DEV_TEST_SERVER_HOST)	fw-db-dev-test
Prod	Dedicated Civo managed PostgreSQL (PROD_SERVER_HOST)	fw-db-prod

All connections use SSL (sslmode=require). Firewall rules are opened temporarily for the GitHub runner IP on port 5432 and removed after the workflow completes.

Naming Convention

Input	Database	Role
myapp (dev)	myapp_dev_db	myapp_dev_user
myapp (test)	myapp_test_db	myapp_test_user
myapp (prod)	myapp_db	myapp_user

---

Provision Database

Creates a database and role on one or more environments. Supports create (idempotent) and reset (drop + recreate).

Flow

flowchart TD
    A["Manual trigger: app_name + action per env
    timeout: 10 min"] --> B[For each environment: dev, test, prod]
    B --> C{Action?}
    C -->|skip| D[Skip environment]
    C -->|create / reset| E[Install Civo CLI + psql]
    E --> F[Open firewall port 5432 for runner IP]
    F --> G[Save pre-existing state: role exists? DB exists?]
    G --> H[Ensure role exists with password]
    H --> I{Action?}
    I -->|create| J{DB exists?}
    J -->|Yes| K[Keep existing DB]
    J -->|No| L[CREATE DATABASE]
    I -->|reset| M[Terminate connections + DROP + CREATE DATABASE]
    K & L & M --> N[GRANT privileges to role]
    N --> O[Write summary with JDBC connection string]
    O --> P[Close firewall rule]

    E & F & G & H -->|failure| ROLLBACK

    subgraph ROLLBACK [Rollback Job]
        R1[Download pre-state artifact]
        R1 --> R2{DB was new?}
        R2 -->|Yes| R3[DROP DATABASE]
        R2 -->|No| R4[Keep DB]
        R3 & R4 --> R5{Role was new?}
        R5 -->|Yes| R6[DROP ROLE]
        R5 -->|No| R7[Keep role]
    end

Loading

Usage

Actions > Provision App on Dev, Test and Prod > Run workflow

Input	Required	Options	Description
app_name	Yes	—	Base name (e.g. myapp)
dev_action	Yes	skip / create / reset	Action for dev
dev_password	If not skip	—	Password for dev role
test_action	Yes	skip / create / reset	Action for test
test_password	If not skip	—	Password for test role
prod_action	Yes	skip / create / reset	Action for prod
prod_password	If not skip	—	Password for prod role

Rollback

If provisioning fails, a rollback job runs automatically:

• Reads the pre-state artifact (captured before any changes)
• Only removes resources that were newly created during this run
• Pre-existing databases and roles are never touched during rollback

---

Delete Database

Drops a database and role from selected environments.

Flow

flowchart TD
    A["Manual trigger: app_name + select envs
    timeout: 10 min"] --> B[For each selected environment]
    B --> C[Install Civo CLI + psql]
    C --> D[Open firewall port 5432 for runner IP]
    D --> E{Database exists?}
    E -->|Yes| F[Terminate connections + DROP DATABASE]
    E -->|No| G[Skip]
    F --> H{Role exists?}
    G --> H
    H -->|Yes| I[DROP ROLE]
    H -->|No| J[Skip]
    I & J --> K[Write summary to Step Summary]
    K --> L[Close firewall rule]

Loading

Usage

Actions > Delete App Database and Role > Run workflow

Input	Required	Default	Description
app_name	Yes	—	Base name (e.g. myapp)
dev	Yes	false	Delete on dev
test	Yes	false	Delete on test
prod	Yes	false	Delete on prod

All checkboxes default to off to prevent accidental deletion.

---

Secrets & Variables

Secrets

Secret	Description
CIVO_API_KEY	Civo API key for firewall management
DEV_TEST_SERVER_ADMIN_PASSWORD	Admin password for dev/test PostgreSQL
PROD_SERVER_ADMIN_PASSWORD	Admin password for prod PostgreSQL

Variables

Variable	Value	Description
ADMIN_USER	civo	PostgreSQL admin user
DEV_TEST_SERVER_HOST	(LON1 IP)	Dev/test database host
DEV_TEST_FIREWALL_NAME	fw-db-dev-test	Dev/test database firewall
PROD_SERVER_HOST	(LON1 IP)	Prod database host
PROD_FIREWALL_NAME	fw-db-prod	Prod database firewall