🧠 PostgreSQL Auto Backup & Cleanup Service

A cross-platform .NET Worker Service that automatically backs up all PostgreSQL databases at regular intervals, uploads them to IDrive e2 (S3-compatible) cloud storage, and cleans up old backups (both locally and remotely).

---

🚀 Features

✅ Automatic PostgreSQL Backup

• Detects all databases in your PostgreSQL instance (except system ones like template0, template1).
• Uses pg_dump to back up each database individually.
• Compresses all backups into a single .zip file.
• Stores it in a local Backups directory.

✅ Cloud Storage (IDrive e2)

• Uploads each zipped backup to your IDrive bucket.
• Uses AWS S3–compatible SDK with secure credentials.

✅ Automatic Cleanup

• Deletes backups older than 7 days from both:
  • Local storage.
  • IDrive cloud bucket.

✅ Interval Scheduling

• Takes backups every 30 minutes (configurable).
• Cleanup runs once per day automatically.
• Fully asynchronous and non-blocking.

✅ Cross-Platform

• Works on Windows, Linux, or macOS.
• Can run as a Windows Service or Linux systemd service.

---

🧩 Project Structure

Backup_Service/
│
├── Program.cs                       # Application entry point
├── BackupService.cs                  # Periodic backup and upload logic
├── CleanupService.cs                 # Daily cleanup of old backups
├── IDriveService.cs                  # Handles all IDrive S3 operations
│
├── Models/
│   ├── BackupSettings.cs             # Backup configuration
│   └── CleanupSettings.cs            # Cleanup configuration (optional)
│
├── appsettings.json                  # Main configuration
└── README.md

⚙️ Configuration (appsettings.json)

{
  "PostgresSettings": {
    "Host": "localhost",
    "Port": "5432",
    "Username": "postgres",
    "Password": "yourpassword"
  },
  "IDrive": {
    "AccessKey": "YOUR_ACCESS_KEY",
    "SecretKey": "YOUR_SECRET_KEY",
    "BucketName": "backupautomation",
    "Region": "ap-southeast-1",
    "ServiceURL": "https://s3.ap-southeast-1.idrivee2.com"
  },
  "BackupSettings": {
    "LocalBackupFolder": "Backups",
    "IntervalInMinutes": 30,
    "StartTime": "00:00:00",
    "PgDumpPath": "C:\\Program Files\\PostgreSQL\\17\\bin\\pg_dump.exe"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Information"
    }
  }
}

📝 Note:

Ensure your PgDumpPath points to the correct version of PostgreSQL installed.

Region and ServiceURL must match your IDrive e2 region (Singapore example shown above).

🛠️ How It Works

When the service starts, it immediately performs a backup of all databases.

Each backup is saved to a .dump file and zipped.

The zip file is uploaded to the configured IDrive bucket.

Every 30 minutes, this process repeats.

Once per day, the cleanup service deletes files older than 7 days.

🧪 Run Locally

1.Open the project in Visual Studio or VS Code.

2.Configure your connection in appsettings.json.

3.Build and run:

dotnet run

4.Watch the logs in the console for:

Backup successful
Cleanup complete

🧰 Running as a Windows Service 1.Publish the project:

dotnet publish -c Release -r win-x64 --self-contained true

2.Install as a Windows Service (PowerShell):

sc create PostgresBackupService binPath= "C:\path\to\published\Backup_Service.exe"
sc start PostgresBackupService