Database Backup and Upload #18

	name: Database Backup and Upload

	on:
	schedule:
	- cron: "30 17 * * *" # Run at 5:30pm UTC every day (11:00pm in India)
	workflow_dispatch: # Allows manual triggering from GitHub UI

	permissions:
	id-token: write
	contents: read

	jobs:
	backup-and-upload:
	runs-on: ubuntu-latest
	# strategy:
	# matrix:
	# include:
	# - prefix: PREFIX_1 # I had multiple databases I wanted backed up and each had a secret like:

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

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

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

	- name: Dump database
	run: \|
	/usr/lib/postgresql/16/bin/pg_dump ${{ secrets.CONNECTION_STRING }} \| gzip > "${TIMESTAMP}.sql.gz"

	- name: Configure AWS credentials from Action OIDC
	uses: aws-actions/configure-aws-credentials@v1
	with:
	aws-region: ap-south-1
	role-to-assume: ${{ secrets.AWS_ROLE }}
	role-session-name: GitHubActionSession

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

Provide feedback