DB備份還原

pgsql 備份

全備份全回朔

資料備份

“`bash~
sudo mkdir -p /opt/backup/idempiere
sudo chown postgres:postgres /opt/backup/idempiere
sudo nano /opt/backup/backup_idempiere.sh
sudo chmod +x /opt/backup/backup_idempiere.sh
sudo chown postgres:postgres /opt/backup/backup_idempiere.sh

sudo -u postgres nano /var/lib/postgresql/.pgpass
sudo chown postgres:postgres /var/lib/postgresql/.pgpass
sudo chmod 600 /var/lib/postgresql/.pgpass
sudo -u postgres /opt/backup/backup_idempiere.sh //測試
sudo -u postgres crontab -e

sudo nano /opt/backup/backup_idempiere.sh
```bash!
#!/bin/bash

cd /opt/backup
# 設定參數
DB_NAME="idempiere"
DB_USER="adempiere"
BACKUP_DIR="/opt/backup/idempiere"
DATE=$(date +\\%Y\\%m\\%d_\\%H\\%M\\%S)
BACKUP_FILE="$BACKUP_DIR/idempiere_$DATE.sql.gz"
LOG_FILE="$BACKUP_DIR/backup.log"

# 建立備份
echo "[$(date)] 開始備份..." >> $LOG_FILE
PGPASSWORD=adempiere pg_dump -h localhost -U $DB_USER $DB_NAME | gzip > $BACKUP_FILE
pg_dump -U $DB_USER $DB_NAME | gzip > $BACKUP_FILE
pg_dumpall -U postgres | gzip > $BACKUP_FILE
# 記錄結果
if [ $? -eq 0 ]; then
    echo "[$(date)] 備份成功: $BACKUP_FILE" >> $LOG_FILE
else
    echo "[$(date)] 備份失敗" >> $LOG_FILE
fi

# 清除 30 天前的備份
find $BACKUP_DIR -name "*.sql.gz" -mtime +30 -exec rm {} \\; >> $LOG_FILE

exit 0

#!/bin/bash

cd /opt/backup
# 設定參數
DB_NAME="idempiere"
DB_USER="adempiere"
BACKUP_DIR="/opt/backup/idempiere"
DATE=$(date +\\%Y\\%m\\%d_\\%H\\%M\\%S)
BACKUP_FILE="$BACKUP_DIR/idempiere_$DATE.sql.gz"
LOG_FILE="$BACKUP_DIR/backup.log"

# 建立備份
echo "[$(date)] 開始備份..." >> $LOG_FILE
pg_dumpall -U postgres | gzip > $BACKUP_FILE

# 記錄結果
if [ $? -eq 0 ]; then
    echo "[$(date)] 備份成功: $BACKUP_FILE" >> $LOG_FILE
else
    echo "[$(date)] 備份失敗" >> $LOG_FILE
fi

# 清除 30 天前的備份
find $BACKUP_DIR -name "*.sql.gz" -mtime +30 -exec rm {} \\; >> $LOG_FILE

exit 0

sudo -u postgres nano /var/lib/postgresql/.pgpass
“`bash!
localhost:5432:idempiere:adempiere:adempiere

sudo -u postgres crontab -e
```bash!
0 2 * * * /opt/backup/backup_idempiere.sh

搬移檔案
其他機器來拉

sudo scp [email protected]:/opt/backup/idempiere/*.sql.gz /mnt/backup/

推出去

#!/bin/bash
# 檔名: /opt/backup/sync_to_backup_server.sh

SRC="/opt/backup/idempiere/"
DST="[email protected]:/home/tom/idempiere/"

rsync -avz  $SRC $DST
~

還原

機器準備
“`bash!
sudo apt update
sudo apt install postgresql postgresql-contrib -y

sudo -u postgres psql
CREATE USER adempiere WITH PASSWORD \’your_password\’;
CREATE DATABASE idempiere OWNER adempiere;
CREATE EXTENSION IF NOT EXISTS “uuid-ossp”;
\q

拉備份資料
```bash!
scp /opt/backup/idempiere_20250628_020000.sql.gz youruser@newhost:/tmp/


cd /tmp
gunzip idempiere_20250628_020000.sql.gz

跑PGSQL
~~sudo -u postgres psql -U adempiere -d idempiere -f /tmp/idempiere_20250628_020000.sql~~
“`bash!

psql -h localhost -U adempiere -d idempiere -f idempiere_20250629_020001.sql

驗收
```bash!
sudo -u postgres psql -d idempiere
select * from ad_client limit 5;
\\q

\\dx
\\dt

雲上的話
要走ＲＳＡ驗正
先跑
ssh-keygen -t rsa -b 4096 -C “tomting” 產對應角色的ＫＥＹ
pub 貼在ＶＭ裡
ＳＣＰ指令戴上
scp -i ~/.ssh/gcp “tomting@host:/opt/backup/idempiere/*.sql.gz” ~/Downloads/