Database Backup and Upload #159

	name: Database Backup and Upload

	on:
	schedule:
	- cron: '0 16 * * *' # Run at 4:00 pm UTC every day (Will be the middle of the night in the TW)
	workflow_dispatch:
	inputs:
	name:
	description: Who to greet
	default: Allen

	permissions:
	id-token: write
	contents: read

	jobs:
	backup-and-upload:
	runs-on: ubuntu-latest

	steps:
	- name: Checkout code
	uses: actions/checkout@v4

	- name: Install postgresql client
	run: \|
	sudo apt install -y postgresql-common
	yes '' \| sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
	sudo apt install -y postgresql-client-17

	- name: Set timestamp
	run: echo "TIMESTAMP=$(date -u +'%Y-%m-%d-%H-%M-%S')" >> $GITHUB_ENV

	- name: Dump database
	run: \|
	/usr/lib/postgresql/17/bin/pg_dump -d ${{ secrets.DB_CONNECTION_STRING }} --column-inserts --no-owner -f "${TIMESTAMP}.sql"

	- name: Configure aws credentials
	uses: aws-actions/configure-aws-credentials@v4
	with:
	role-to-assume: arn:aws:iam::154471991214:role/github_action
	aws-region: us-west-2

	- name: Upload backup to S3
	run: \|
	YEAR_MONTH=$(date -u +"%Y/%m")
	aws s3 cp "${TIMESTAMP}.sql" s3://backup.docfunc.com/database/${YEAR_MONTH}/

Provide feedback