How to properly configure automatic backups of PostgreSQL or MySQL databases to a remote cloud S3-compatible service using standard utilities?

Regular database backups are critically important for any project to prevent catastrophic data loss during hardware failures or software bugs. To implement a reliable relational database backup, it is best to use a combination of standard command-line utilities like pg_dump or mysqldump along with object storage tools such as rclone or awscli.

The first step is to write a bash script that generates a database dump with a timestamp appended to the file name to avoid overwriting older versions. It is also important to add a compression stage for the resulting SQL file using an archiver like gzip or zstd, since text database dumps take up a lot of space, and compression significantly reduces the time and cost of data transfer to the cloud.

After successfully creating and compressing the archive, the script must upload it to a secure cloud storage supporting the S3 protocol. The rclone utility, pre-configured to connect to your provider, is well-suited for this. An example send command looks like a call to rclone copy with the local file and the target cloud bucket specified.

To fully automate the process, the created script is added to the cron task scheduler on the database server. Configuring a schedule allows backups to run every night or even multiple times a day depending on data change intensity. You should also make sure to build logging into the script for successful execution and the deletion of local archives older than a certain number of days to prevent the server disk from filling up with old copies.

Finally, it is equally important to regularly verify the health of the created backups by test-restoring them in an isolated environment. A backup that has never been restored is technically not a reliable backup, as it may contain corruption or incomplete data.