How to automatically back up the Postgre database in Docker

## Scheme One: Host Script + Crontab Timed Task

This is the most classic and direct method. By writing a Shell script on the host (Host) and using `docker exec` to call the `pg_dump` tool inside the container.

### Implementation Steps:

1. **Write the script**: Create a `.sh` file, which usually contains:

   ```Bash
   docker exec -t <container_name> pg_dumpall -c -U <username> > /path/to/backups/db_$(date +%Y%m%d).sql
   ```

2. **Set permissions**: Ensure the script has execution permissions.

3. **Configure Cron**: Use `crontab -e` to add a timed task (such as running every day at 2 am).

4. **Cleanup mechanism**: Add a `find` command in the script to automatically delete old backups from X days ago.

### Summary of Advantages and Disadvantages:

- **Advantages**: No need to modify the container image, flexible configuration, and does not occupy extra container resources.
- **Disadvantages**: Depends on the host's environment configuration, if migrated to a new server, Cron and script paths need to be reconfigured.


## Scheme Two: Using a "Sidecar" Container

Add a dedicated backup service in Docker Compose. Currently, there are many mature images in the community, such as `prodrigestivill/postgres-backup-local`.

### Implementation Steps:

1. **Modify the Compose file**: Increase the backup service in `docker-compose.yml`:

   
   ```YAML
   services:
     db:
       image: postgres:15
     backup:
       image: prodrigestivill/postgres-backup-local
       volumes:
         - ./backups:/backups
       links:
         - db
       environment:
         - POSTGRES_HOST=db
         - POSTGRES_USER=user
         - POSTGRES_PASSWORD=pass
         - SCHEDULE=@daily # Using built-in Cron expression
         - BACKUP_KEEP_DAYS=7
   ```

2. **Start the service**: After `docker-compose up -d`, the backup service will automatically run according to the set `SCHEDULE`.

### Summary of Advantages and Disadvantages:

- **Advantages**: Highly integrated, configuration managed with project code; easy to migrate, a set of Compose can run anywhere and back up.
- **Disadvantages**: Increases the overhead of a container, and backup files are stored in volumes by default, still need to consider off-site backup.

## Scheme Three: Cloud/Object Storage Backup (such as S3/OSS)

If your data is very important, it is recommended to directly stream the backup to cloud storage (such as AWS S3 or Alibaba Cloud OSS) to prevent physical server failures.

### Implementation Steps:

1. **Choose an image**: Use an image that supports the S3 protocol (such as `schickling/postgres-backup-s3`).
2. **Configure environment variables**: Configure `S3_ACCESS_KEY_ID`, `S3_SECRET_ACCESS_KEY`, and `S3_BUCKET` in the container.
3. **Run the backup**: The container will automatically complete the "export -> compress -> upload" process.

### Summary of Advantages and Disadvantages:

- **Advantages**: **Off-site backup**, extremely high security; does not occupy local disk space.
- **Disadvantages**: Requires public network bandwidth, and will generate a small amount of cloud storage fees.

## Comprehensive Comparison of Schemes

| **Feature**     | **Scheme One: Host Script** | **Scheme Two: Sidecar Container** | **Scheme Three: Cloud Storage Backup** |
| ------------ | ---------------------- | ------------------------ | ---------------------- |
| **Deployment Difficulty** | Medium (need to configure Cron)     | **Simple (configure YAML)**     | More complex (need cloud authorization)    |
| **Portability** | Poor                     | **Excellent**                 | Good                     |
| **Security**   | Depends on local disk           | Depends on local disk             | **Extremely high (off-site redundancy)**    |
| **Applicable Scenarios** | Single-machine small projects           | Complex applications, microservices         | Production environments, core businesses     |


## Recommendations

- **Do not just back up without testing**: Periodically try to restore data from backup files to ensure that backups are "alive".
- **Handle sensitive information**: Avoid writing passwords in plain text in scripts or Compose files, and suggest using Docker Secrets or `.pgpass` files.
- **Monitoring and alerts**: Regardless of which scheme is adopted, it is best to configure a notification for backup failures (such as enterprise WeChat/DingTalk Hook) to prevent backups from silently failing.