Restoring a Database from an Existing Backup

[denisakp]

Restoring a Database from an Existing Backup

Description:

Implement a new command to restore databases from existing backups stored locally, on S3, or on Google Drive. The new restore feature should support PostgreSQL, MySQL/MariaDB, and MongoDB databases, leveraging the same storage backends currently used for backups.

---

Requirements and Specifications

Objective:
Create a command sentinel restore that allows users to restore a database from a previously created backup. The command should seamlessly handle different storage backends (local, S3, Google Drive) by downloading the necessary backup files, performing the restoration, and cleaning up temporary files after the process.

---

Detailed Steps for Implementation

1. Define the CLI Command:

   • Create a new command restore in the CLI.
   • Implement flags to capture database type (postgres, mysql, mariadb, mongodb), connection details (host, port, user, password, database), and storage options (local, s3, google-drive).

2. Handle Storage Backends:

   • Local Storage: Use the provided path to locate the backup file or folder directly.
   • S3: Download the specified backup object from the bucket to a temporary directory using AWS SDK for Go.
   • Google Drive: Use Google Drive API to download the backup file/folder to a temporary directory.

3. Database Restoration Process:

   • Once the backup is retrieved, use the appropriate restoration commands for each database type:
     • PostgreSQL: Use pg_restore or psql based on the output format (directory, custom, tar, plain).
     • MySQL/MariaDB: Use mysql CLI to restore from an SQL dump file.
     • MongoDB: Use mongorestore to restore from a backup archive.
   • Ensure that the command properly handles database connection parameters (e.g., --host, --port, --user, --password).

4. Clean Up Temporary Files:

   • After the restoration process, delete any temporary files that were downloaded for the restore.

5. Logging & Error Handling:

   • Implement logging to indicate the status of the restore operation.
   • Provide clear error messages if the restoration process fails at any step (e.g., missing files, incorrect credentials, network errors).

---

Acceptance Criteria

• sentinel restore command successfully restores databases for PostgreSQL, MySQL/MariaDB, and MongoDB.
• Support for local, S3, and Google Drive storage backends.
• Proper cleanup of temporary files after a successful or failed restore.
• Comprehensive error handling with meaningful error messages.
• Unit tests and integration tests for all supported databases and storage types.
• Update documentation to include examples and usage instructions for the new restore command.

---

Additional Notes

• This feature will set the foundation for the v1.x release, following the initial v1.0 release which focuses solely on database backups.
• Integration with the upcoming scheduling feature (cronJobs) will ensure that scheduled backups can also be automatically restored if needed.

---