PG backup

要從一個 Docker Compose 中的 PostgreSQL 容器備份資料並遷移到另一個 Docker Compose 中的 PostgreSQL 容器，可以按照以下步驟進行：

---

### **步驟 1：備份原始資料庫**
1. **進入原始 PostgreSQL 容器**：
執行以下命令進入正在運行的 PostgreSQL 容器：

   docker exec -it <postgres_container_name> bash

例如：

   docker exec -it postgres-dev bash

2. **使用 `pg_dump` 備份資料庫**：
在容器內執行 `pg_dump` 命令，將資料庫備份為一個 SQL 文件：

   pg_dump -U <username> -d <database_name> > backup.sql

例如：

   pg_dump -U daodao -d daodao > backup.sql

3. **將備份文件複製到主機**：
退出容器（按 `Ctrl+D` 或輸入 `exit`），然後將備份文件從容器複製到主機：

   docker cp <postgres_container_name>:/backup.sql ./backup.sql

例如：

   docker cp pg-dev:/backup.sql ./backup.sql

---

### **步驟 2：將備份文件複製到目標主機**
1. 將 `backup.sql` 文件從原始主機複製到目標主機。可以使用 `scp` 或其他文件傳輸工具：

   scp ./backup.sql user@target_host:/path/to/backup.sql

---

### **步驟 3：恢復資料到目標 PostgreSQL 容器**
1. **將備份文件複製到目標容器**：
在目標主機上，將 `backup.sql` 文件複製到目標 PostgreSQL 容器中：

   docker cp ./backup.sql <target_postgres_container_name>:/backup.sql

例如：

   docker cp ./backup.sql postgres-prod:/backup.sql

2. **進入目標 PostgreSQL 容器**：
執行以下命令進入目標 PostgreSQL 容器：

   docker exec -it <target_postgres_container_name> bash

例如：

   docker exec -it postgres-prod bash

3. **使用 `psql` 恢復資料庫**：
在容器內執行以下命令，將備份文件恢復到目標資料庫：

   psql -U <username> -d <database_name> -f /backup.sql

例如：

   psql -U daodao -d daodao -f /backup.sql

docker exec -i storage-dev psql -U daodao -d daodao -c "DROP SCHEMA public CASCADE; CREATE SCHEMA public;"
docker exec -i storage-dev psql -U daodao -d daodao < ./full_backup.sql

---

### **步驟 4：驗證資料遷移**
1. 進入目標 PostgreSQL 容器，檢查資料是否正確恢復：

   psql -U daodao -d daodao

然後執行一些查詢來驗證資料，例如：

   SELECT * FROM your_table_name;

2. 如果資料正確，則遷移完成。

---

### **其他注意事項**
- **確保目標資料庫已存在**：在恢復資料之前，確保目標 PostgreSQL 容器中的資料庫已經創建（與備份的資料庫名稱相同）。
- **版本兼容性**：確保源和目標 PostgreSQL 的版本兼容，以避免因版本差異導致的問題。
- **備份文件大小**：如果備份文件較大，可以考慮使用壓縮工具（如 `gzip`）來減少傳輸時間。

---

### **自動化腳本（可選）**
如果你需要頻繁遷移資料，可以將上述步驟寫成腳本，例如：

# 備份
docker exec <postgres_container_name> pg_dump -U <username> -d <database_name> > backup.sql

# 複製到目標主機
scp ./backup.sql user@target_host:/path/to/backup.sql

# 恢復
ssh user@target_host "docker cp /path/to/backup.sql <target_postgres_container_name>:/backup.sql"
ssh user@target_host "docker exec <target_postgres_container_name> psql -U <username> -d <database_name> -f /backup.sql"

這樣可以簡化遷移過程。

docker cp ./backup.sql postgres-dev:/backup.sql

psql -U daodao -d daodao -c "DROP SCHEMA public CASCADE; CREATE SCHEMA public;"

## 針對四張資料表共約 800 筆資料的情況，建議使用第一種方法（pg_dump 和 psql 方法），這是最直接且可靠的選擇。這個數量級的資料量不算大，不需要特別的優化處理。以下是詳細的操作步驟：

## 使用 pg_dump 和 psql 遷移 800 筆資料的最佳方案

### 1. 從本地資料庫進行完整備份

# 備份特定的四張資料表（使用您的實際表名替換）
pg_dump -U 本地使用者名稱 -d 本地資料庫名稱 -t 表名1 -t 表名2 -t 表名3 -t 表名4 > tables_backup.sql
pg_dump -U daodao -d daodao daodao -t categories -t resources -t resource_review -t tags -t entity_tags > tables_backup.sql

# 或者，如果您希望包含所有相關的序列、函數等
pg_dump -U 本地使用者名稱 -d 本地資料庫名稱 -t 表名1 -t 表名2 -t 表名3 -t 表名4 --clean --if-exists > tables_backup.sql

`--clean --if-exists` 選項會在還原前先刪除雲端中已存在的同名表，避免衝突。

### 2. 將備份檔案傳輸到雲端伺服器

# 使用 scp 安全地複製檔案
scp tables_backup.sql 使用者名稱@雲端伺服器IP:/目標路徑/
scp tables_backup.sql root@172.234.84:~/daodao-server/
docker cp postgres-daodao:/tables_backup.sql ./tables_backup.sql

# 或使用 rsync（如果檔案較大且網路不穩定）
rsync -avz tables_backup.sql 使用者名稱@雲端伺服器IP:/目標路徑/

### 3. 將備份檔案複製到 Docker 容器並還原

# 複製到容器
docker cp /目標路徑/tables_backup.sql 容器名稱:/tmp/

docker cp ./tables_backup.sql postgres-dev:/tables_backup.sql
docker cp ./tables_backup.sql postgres-prod:/tables_backup.sql

# 還原資料
docker exec -it 容器名稱 psql -U 使用者名稱 -d 資料庫名稱 -f /tmp/tables_backup.sql

psql -U daodao -d daodao -f /tables_backup.sql

### 4. 驗證資料完整性

# 連接到容器的 PostgreSQL
docker exec -it 容器名稱 psql -U 使用者名稱 -d 資料庫名稱

# 在 psql 中執行查詢檢查每張表的記錄數
SELECT COUNT(*) FROM 表名1;
SELECT COUNT(*) FROM 表名2;
SELECT COUNT(*) FROM 表名3;
SELECT COUNT(*) FROM 表名4;

## 為何這是最佳選擇

1. **簡單直接**：對於 800 筆資料，這個方法既簡單又高效
2. **保留完整結構**：自動處理資料表結構、索引、約束和觸發器等
3. **維持資料一致性**：正確處理表間的外鍵關係和依賴
4. **單一檔案管理**：整個遷移過程只需處理一個 SQL 檔案
5. **可重複執行**：使用 `--clean --if-exists` 選項使腳本可重複執行

## 可能需要的額外操作

1. **權限設定**：如果雲端資料庫使用者與本地不同，可能需要修改擁有者

   # 在還原後執行
   ALTER TABLE 表名1 OWNER TO 新擁有者;
   ALTER TABLE 表名2 OWNER TO 新擁有者;
   # 以此類推

2. **序列重置**：如果您遷移的表使用序列（如 SERIAL 主鍵），確保序列正確更新

   # 對每個序列執行
   SELECT setval('表名_id_seq', (SELECT MAX(id) FROM 表名), true);

3. **處理特定的雲端環境設定**：某些雲端提供商可能有特定的配置需求，如連接限制或資源分配

由於資料量不大（800 筆），整個遷移過程應該很快就能完成，而且不需要特別考慮分批處理或性能優化。這個方法也最不容易出錯，是處理這種規模資料的最佳選擇。