# 登錄到舊的PG容器中
docker exec -it postgres bash
# 備份數(shù)據(jù)庫(kù)
pg_dumpall -c -U postgres > dump_`date +%Y-%m-%d"_"%H_%M_%S`.sql
# 退出容器
exit
# 退出容器后，復(fù)制出備份文件到主機(jī)中
docker cp postgres:/dump_2024-10-26_05_58_04.sql dump_2024-10-26_05_58_04.sql

# 停止老的PG容器
docker stop postgres
# 創(chuàng)建新的PG容器
docker run --name postgres2 -e POSTGRES_PASSWORD=<密碼> -p 5432:5432 -d postgres -c max_connections=800

# 復(fù)制備份數(shù)據(jù)文件到新PG容器中
docker cp dump_2024-10-26_05_58_04.sql postgres2:/
# 登錄到新PG容器中
docker exec -it postgres2 bash
# 恢復(fù)備份數(shù)據(jù)庫(kù)
psql -f dump_2024-10-26_05_58_04.sql -U postgres postgres

# 刪除舊pg容器
docker rm postgres
# 改名老pg容器
docker rename postgres2 postgres

# vi /opt/postgresql/conf/backup/pg_backup.sh
#!/bin/bash
# 設(shè)置數(shù)據(jù)庫(kù)連接參數(shù)
DB_HOST="localhost"
DB_PORT="5432"
DB_NAME="PGDB"
DB_USER="root"
DB_PASS="Admin@123"
# 設(shè)置備份目錄
BACKUP_DIR="/mnt"
# 獲取當(dāng)前時(shí)間戳
TIMESTAMP=$(date +"%Y%m%d%H%M%S")
# 設(shè)置備份文件名
BACKUP_FILE="$BACKUP_DIR/backup_$DB_NAME_$TIMESTAMP.sql"
# 使用 pg_dump 命令備份數(shù)據(jù)庫(kù)
pg_dump -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -Fc -b -v -f "$BACKUP_FILE"
# 檢查備份是否成功，并導(dǎo)出備份日志
if [ $? -eq 0 ]; then
    echo "$TIMESTAMP Backup completed successfully: $BACKUP_FILE" >> /mnt/pg_backup.log
else
    echo "$TIMESTAMP Backup failed." >> /mnt/pg_backuo.log
fi
# 檢查并刪除十天前的備份文件  
find "$BACKUP_DIR" -type f -name "$DB_NAME_*.sql" -mtime +10 -exec rm {} \;  
echo "$TIMESTAMP Backup completed and old backups cleaned up." >> /mnt/pg_backup.log

chmod +x  /opt/postgresql/conf/backup/pg_backup.sh

# vi /opt/postgresql
version: "3"
services:
  postgresql:
    image: postgres:16.3-alpine
    restart: always
    privileged: true
    environment:
      POSTGRES_USER: root
      POSTGRES_PASSWORD: Admin@123
      POSTGRES_DB: PGDB
    ports:
      - 5432:5432
    volumes:
      - ./conf/postgres/data:/var/lib/postgresql/data
      - ./conf/backup:/mnt			#備份腳本和備份文件存放目錄

# cd /opt/ postgresql
# docker-compose up -d

# 每天凌晨一點(diǎn)自動(dòng)執(zhí)行數(shù)據(jù)庫(kù)備份腳本
# crontab -l
0 1 * * *  docker exec postgresql   /bin/bash -c "/mnt/pg_backup.sh"