Script and Usage Guide for Backing Up PostgreSQL Database in Docker

This solution utilizes PostgreSQL's native `pg_dump` logical backup tool, combined with host machine scripts and scheduled tasks, to achieve regular data snapshots and secure recovery.

> The article content is based on a Linux environment. Please modify accordingly for other environments like Windows.

## 1. Script Preparation

```bash
#!/bin/bash

# --- Configuration ---
CONTAINER_NAME="pgsql"                 # Your container name
DB_USER="postgres"                     # Database username
DB_NAME="loden"                        # Database name (Use "all" to backup all databases)
BACKUP_DIR="/root/pgsql/backup"        # Backup path on the host machine
DATE=$(date +%Y%m%d_%H%M%S)            # Filename date format
RETENTION_DAYS=10                      # Retention days

# --- Ensure directory exists ---
mkdir -p $BACKUP_DIR

# --- Execute backup ---
# Principle: Execute pg_dump inside the container, redirecting the standard output stream (stdout) to a host machine file
echo "Backing up container [$CONTAINER_NAME] ..."

if [ "$DB_NAME" == "all" ]; then
    # Backup all databases
    docker exec $CONTAINER_NAME pg_dumpall -c -U $DB_USER > "$BACKUP_DIR/full_backup_$DATE.sql"
else
    # Backup a single database (Recommended to use custom format -Fc for compression and faster restore)
    docker exec $CONTAINER_NAME pg_dump -U $DB_USER -F c $DB_NAME > "$BACKUP_DIR/${DB_NAME}_${DATE}.dump"
fi

# Check if the backup file was generated (and its size is not 0)
if [ -s "$BACKUP_DIR/${DB_NAME}_${DATE}.dump" ] || [ -s "$BACKUP_DIR/full_backup_$DATE.sql" ]; then
    echo "Backup successful!"
else
    echo "Backup failed, please check container status or logs."
    # Optional: Send an alert email upon failure
    exit 1
fi

# --- Clean up old backups ---
find $BACKUP_DIR -type f -mtime +$RETENTION_DAYS -name "*.dump" -delete
find $BACKUP_DIR -type f -mtime +$RETENTION_DAYS -name "*.sql" -delete

```

## 2. Environment Configuration

Before running the script, ensure your host machine environment is ready:

1.  **Create backup directory**: It is recommended to store backup files in an independent path outside the Docker mount point:

```Bash
mkdir -p /root/pgsql/backup
```

2.  **Grant script execution permission**:

```Bash
chmod +x /root/pgsql/pg_backup.sh
```

3.  **Configure password-free environment (Critical)**: To allow the script to run automatically unattended, you need to avoid password prompts. There are two native methods:

    -   **Method A (Recommended)**: Add `PGPASSWORD: your_password` to the `environment` section in `docker-compose.yml`.
    -   **Method B**: Add an environment variable to the backup command in the script, e.g., `docker exec -e PGPASSWORD=... $CONTAINER_NAME pg_dump ...`.

## 3. Enable Automated Backup

Use Linux's built-in `crontab` scheduled tasks to make the backup script run automatically during off-peak hours like early morning. For example:

1.  Open the editor with the command: `crontab -e`

2.  Add a configuration line (execute backup daily at 3:00 AM):

```Bash
0 3 * * * /bin/bash /root/pgsql/pg_backup.sh >> /root/pgsql/backup.log 2>&1
```

*Note: The `>> ...log` part saves the backup log, making it easier for you to check if the backup was successful.*

## 4. Data Recovery

When data is accidentally deleted (e.g., `TRUNCATE` or `DROP`), please follow these steps for recovery.

Check the backup file format before recovery. If your script backs up a specified database (`-F c`), the filename suffix is usually `.dump`. Such files must be restored using the `pg_restore` tool.

Recovery command: Restore the backup directly from the host machine to the specified database

```Bash
docker exec -i pgsql pg_restore -U postgres -d loden_new --clean --if-exists < /root/pgsql/backup/loden_20260106_164814.dump
```

Explanation of common recovery command parameters:

-   `-d <db_name>`: Specify the target database (It is recommended to create a test database first for recovery drills, like `loden_new` in the command).
-   `--clean`: Clean up existing data objects before restoration (dangerous but effective).
-   `-j <n>`: Run recovery tasks in parallel. Use `-j 4` to speed up the process if the database is very large.