This repository contains a script and a set of GitHub Actions to backup and restore a PostgreSQL database.
The backup script, backuper.sh
, can be used to backup a database to a file. The file is then encrypted and uploaded to a Cloudflare R2 bucket.
The restore script can be used to restore a database from a backup file.
The GitHub Actions are used to automate the backup and restore process. The docker_image.yml
workflow builds a Docker image for the backup script. The backup-wf.yml
workflow runs the backup script, and the production_database.yml
workflow runs the restore script.
- A PostgreSQL database
- A Cloudflare R2 bucket
- The AWS CLI
- The OpenSSL command-line tool
To secure your backup files, you can generate an encryption key pair using OpenSSL. Remember that the private key should be kept secret, and the public key can be shared as needed.
Please note that this self-signed certificate is typically used for encryption and authentication within your own systems. For more secure scenarios, you might want to consider obtaining certificates from a trusted Certificate Authority (CA).
Follow these steps:
-
Open a terminal window.
-
Navigate to the directory where you want to store the keypair.
-
Run the following command:
openssl req -x509 -nodes -newkey rsa:4096 -keyout backup_key.pem -out backup_key.pem.pub
- Create a Cloudflare R2 bucket.
- Generate a Key pair for encryption
- Clone this repository.
- Update the workflow files (docker_image.yml, production_database.yml) to match your configuration and image references.
- In the GitHub repository settings, add the required secrets listed below.
- Run the
docker_image.yml
workflow to build the Docker image. - Run the
backup-wf.yml
workflow to backup the database.
- PGHOST: PostgreSQL host or IP address.
- PGPASSWORD: PostgreSQL password for the specified user.
- R2_ACCESS_KEY_ID: Cloudflare Storage access key.
- R2_SECRET_ACCESS_KEY: Cloudflare Storage secret key.
- BACKUP_PUB_KEY: Public key for backup encryption. ( backup_key.pem.pub )
- BACKUP_PRIVATE_KEY: Private key for backup decryption. ( backup_key.pem )