五分钟完成PolarDB替换postgresql

原创已于 2025-05-20 15:42:23 修改 · 934 阅读

17 ·

CC 4.0 BY-SA版权

文章标签：

#数据库

于 2025-05-19 17:09:02 首次发布

数据库专栏收录该内容

2 篇文章

订阅专栏

前提：内网项目，无互联网环境，当前操作系统：Centos7.6

1.离线安装docker（如果已有docker可跳过）

（1）将docker的压缩包传入服务器，并解压

tar -zxvf docker-18.06.3-ce.tgz

（2）将解压内容复制到/usr/bin/下

cp docker/* /usr/bin/

（3）创建docker服务

vi /etc/systemd/system/docker.service

输入以下内容

[Unit]

Description=Docker Application Container Engine

Documentation=https://docs.docker.com

After=network-online.target firewalld.service

Wants=network-online.target

[Service]

Type=notify

ExecStart=/usr/bin/dockerd

ExecReload=/bin/kill -s HUP $MAINPID

TimeoutSec=0

RestartSec=2

ExecStartPost=/usr/sbin/iptables -P FORWARD ACCEPT

Restart=always

TimeoutStartSec=0

LimitNOFILE=infinity

LimitNPROC=infinity

LimitCORE=infinity

Delegate=yes

KillMode=process

StartLimitBurst=3

StartLimitInterval=60s

[Install]

WantedBy=multi-user.target

（4）保存后依次执行以下命令

systemctl daemon-reload

systemctl start docker

systemctl enable docker

systemctl status docker

2.运行PolarDB容器

（1）将打包好的镜像传入服务器，并引入

docker load < polardb.tar

（2）执行docker run命令

docker run -d \

--cap-add=SYS_PTRACE \

--privileged=true \

--restart=always \

--name polardb_container \

--env POLARDB_PORT=5432 \

--env POLARDB_USER=postgres \

--env POLARDB_PASSWORD=postgres \

-p 5432:5432 \

polardb/polardb_pg_local_instance:11.9.20.0

3.设置数据库定时备份，以及测试恢复数据库功能

（1）新建存放备份内容的文件夹

mkdir -p /polar_backup/backup

mkdir -p /polar_backup/backup_log

（2）编写备份脚本

vi /polar_backup/db_backup.sh

输入以下内容

#!/bin/bash
# === 配置部分 ===
CONTAINER_NAME="polardb_container" # 容器名称
DB_NAME="postgres" # 数据库名称
DB_USER="postgres" # 数据库用户名
BACKUP_DIR="/polar_backup/backup" # 宿主机备份目录
RETENTION_DAYS=30 # 保留天数
# === 生成备份文件名 ===
TIMESTAMP=$(date +"%Y%m%d_%H%M")
BACKUP_FILE="${DB_NAME}_${TIMESTAMP}.backup"
CONTAINER_PATH="/tmp/${BACKUP_FILE}"
# === 创建宿主机备份目录（如不存在） ===
mkdir -p "$BACKUP_DIR"
# === 执行数据库备份 ===
echo "Starting backup of database '${DB_NAME}' from container '${CONTAINER_NAME}'..."
docker exec "$CONTAINER_NAME" mkdir -p /var/polardb_backup
docker exec "$CONTAINER_NAME" pg_dump -U "$DB_USER" -F c -b -v -f "$CONTAINER_PATH" "$DB_NAME"
if [ $? -ne 0 ]; then
echo "❌ Backup failed."
exit 1
fi
# === 拷贝备份文件到宿主机 ===
docker cp "${CONTAINER_NAME}:${CONTAINER_PATH}" "${BACKUP_DIR}/${BACKUP_FILE}"
if [ $? -eq 0 ]; then
echo "✅ Backup successful: ${BACKUP_DIR}/${BACKUP_FILE}"
else
echo "❌ Failed to copy backup file to host."
exit 1
fi
# === 清理超过指定天数的旧备份 ===
echo " Cleaning up backups older than ${RETENTION_DAYS} days..."
find "$BACKUP_DIR" -name "${DB_NAME}_*.backup" -type f -mtime +${RETENTION_DAYS} -exec rm -f {} \;
echo "✅ Cleanup complete."

（3）给脚本赋权

chmod +x /polar_backup/db_backup.sh

可以执行sh /polar_backup/db_backup.sh 测试一下脚本

（4）设置每日定时

执行命令 crontab -e

在里面输入
0 2 * * * /polar_backup/db_backup.sh >> /polar_backup/backup_log/backup_log.txt 2>&1

意思是每天凌晨两点执行，备份的日志保存到polar_backup下的backup_log中

（5）恢复备份内容脚本

vi /polar_backup/db_restore.sh

输入以下内容

#!/bin/bash
# === Config===
CONTAINER_NAME="polardb_container"
DB_NAME="postgres"
DB_USER="postgres"
BACKUP_DIR="/polar_backup/backup"
BACKUP_FILE="$1"
if [ -z "$BACKUP_FILE" ]; then
echo "please input your backup file name：for example"
echo " ./restore_yczhdb.sh yczhdb_20250519_1414.backup"
exit 1
fi
HOST_BACKUP_PATH="${BACKUP_DIR}/${BACKUP_FILE}"
CONTAINER_BACKUP_PATH="/tmp/${BACKUP_FILE}"
# === check file is exist===
if [ ! -f "$HOST_BACKUP_PATH" ]; then
echo "cannot find file：$HOST_BACKUP_PATH"
exit 1
fi
# === copy to docker ===
echo "copy to docker..."
docker cp "$HOST_BACKUP_PATH" "${CONTAINER_NAME}:${CONTAINER_BACKUP_PATH}"
echo "restore db ${DB_NAME}..."
docker exec -i "$CONTAINER_NAME" pg_restore -U "$DB_USER" -d "$DB_NAME" --clean --verbose "$CONTAINER_BACKUP_PATH"
if [ $? -eq 0 ]; then
echo "database ${DB_NAME} restore from ${BACKUP_FILE} successfly"
else
echo "restore fail"
exit 1
fi
给脚本赋权 chmod +x /polar_backup/db_restore.sh