📊 PostgreSQL 大型資料庫實作：批次建立 50 萬筆資料、欄位填值、備份、還原與效能基準完整指南
在進行資料庫效能測試、查詢優化、壓力模擬或災難復原演練時，建立大量測試資料是一項基本且必要的流程。本篇示範如何在 PostgreSQL 17 中建立可用的測試環境，包含資料表建立、批次插入 50 萬筆資料、填入姓名與聯絡資訊、完成備份與還原、資料一致性驗證，並提供 pgbench 效能基準測試與備份/還原耗時分析，作為完整的資料庫實驗手冊。
1️⃣ 系統環境與前置準備
- 作業系統：CentOS Stream 10
- 資料庫版本：PostgreSQL 17
- 管理工具：Adminer（PHP Web UI）
- 測試環境：4 vCPU / 8 GB RAM / NVMe SSD
📦 安裝 PostgreSQL 17
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-10-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo dnf -qy module disable postgresql
sudo dnf install -y postgresql17 postgresql17-server
sudo /usr/pgsql-17/bin/postgresql-17-setup initdb
sudo systemctl enable --now postgresql-17
🌐 啟用遠端連線
sudo sed -i "s/^#listen_addresses = .*/listen_addresses = '*'/" /var/lib/pgsql/17/data/postgresql.conf
sudo sed -i '$a host all all 192.168.1.0/24 md5' /var/lib/pgsql/17/data/pg_hba.conf
sudo systemctl restart postgresql-17
2️⃣ 安裝 Adminer（Web 管理界面）
sudo dnf install -y httpd php php-pgsql
sudo systemctl enable --now httpd
sudo mkdir -p /var/www/html/adminer
sudo wget -O /var/www/html/adminer/index.php https://www.adminer.org/latest.php
sudo chown -R apache:apache /var/www/html/adminer
sudo chmod -R 755 /var/www/html/adminer
3️⃣ 建立 50 萬筆資料表
CREATE TABLE test_table_500k (
id SERIAL PRIMARY KEY,
info TEXT,
name_zh TEXT,
phone TEXT,
email TEXT,
address TEXT,
date_value DATE,
randstr TEXT
);
4️⃣ 批次插入 50 萬筆資料
sudo -u postgres psql postgres -c \
"INSERT INTO test_table_500k (info)
SELECT 'batch500k-' || generate_series(1,500000);"
✔ 確認筆數
SELECT COUNT(*) FROM test_table_500k;
5️⃣ 批次填入欄位資料（中文姓名 / 手機 / Email / 地址 / 日期 / 亂數）
sudo -u postgres psql postgres -c "
UPDATE test_table_500k
SET
name_zh =
(ARRAY['王','李','張','劉','陳','楊','趙','黃','周','吳','徐','孫','馬','朱','胡','郭','林','何','高','羅'])[floor(random()*20)+1] ||
(ARRAY['小明','小華','雅婷','志強','冠廷','怡君','建宏','俊傑','欣怡','柏妤','雅雯','嘉宏','冠宇','佩珊','家豪','玉婷','嘉玲','詠翔','亭妤','柏翰'])[floor(random()*20)+1],
phone = '09' || lpad((floor(random()*100000000))::text, 8, '0'),
email = lower(substr(md5(random()::text), 1, 8)) || floor(random()*999)::text || '@example.com',
address =
(ARRAY['台北市','新北市','桃園市','台中市','台南市','高雄市','基隆市','新竹市','嘉義市'])[floor(random()*9)+1]
|| (ARRAY['中正區','大安區','信義區','板橋區','新莊區','桃園區','中壢區','西屯區','東區','永康區','苓雅區'])[floor(random()*11)+1]
|| floor(random()*300)::text || '號',
date_value = CURRENT_DATE - (floor(random()*730))::int,
randstr = substr(md5(random()::text), 1, 16);
"
6️⃣ Adminer 確認資料內容
登入 Adminer → 選擇資料庫 postgres → 打開 test_table_500k，即可查看完整 50 萬筆資料。
7️⃣ PostgreSQL 資料庫備份（pg_dump）
📄 備份單一資料庫
sudo -u postgres /usr/pgsql-17/bin/pg_dump postgres > /root/postgres.sql
📦 備份所有資料庫
sudo -u postgres /usr/pgsql-17/bin/pg_dumpall > /root/all_databases.sql
8️⃣ 實際備份檔案容量（SQL / gzip / tar.gz）
| 檔案格式 | 指令 | 容量 |
|---|---|---|
| SQL 原始檔 | pg_dump postgres | ≈ 92 MB |
| gzip 壓縮後 | gzip postgres.sql | ≈ 11 MB |
| tar.gz 壓縮檔 | tar -czvf | ≈ 10–12 MB |
9️⃣ 匯出內容預覽（部分資料）
--
-- PostgreSQL database dump
--
CREATE TABLE public.test_table_500k (
id integer NOT NULL,
info text,
name_zh text,
phone text,
email text,
address text,
date_value date,
randstr text
);
COPY public.test_table_500k (...)
1 batch500k-1 陳雅婷 0978453321 f3b93caa21@example.com ...
2 batch500k-2 黃志強 0987123399 c1ec92d889@example.com ...
🔟 建立還原用資料庫 testdb2
sudo -u postgres psql -c "CREATE DATABASE testdb2;"
1️⃣1️⃣ 還原資料至 testdb2
sudo -u postgres psql testdb2 < /root/postgres.sql
1️⃣2️⃣ 還原後一致性驗證（checksum）
✔ 原始資料庫 checksum
SELECT md5(string_agg(randstr, '' ORDER BY id)) FROM test_table_500k;
✔ 還原後資料庫 checksum
SELECT md5(string_agg(randstr, '' ORDER BY id)) FROM test_table_500k;
若兩者相同 → 資料完全一致。
1️⃣3️⃣ PostgreSQL pgbench 基準測試（TPS / 延遲）
📌 安裝 pgbench
sudo dnf install -y postgresql17-contrib
📌 初始化測試資料（scale=50 → 約 500 萬筆）
sudo -u postgres pgbench -i -s 50 postgres
📌 執行壓力測試（20 併發 / 4 執行緒 / 30 秒）
sudo -u postgres pgbench -c 20 -j 4 -T 30 postgres
📊 測試結果範例
latency average = 3.85 ms
tps = 5188.41 (including connections establishing)
tps = 5201.92 (excluding connections establishing)
1️⃣4️⃣ 備份與還原耗時分析（50 萬筆）
| 作業項目 | 指令 | 耗時 | 說明 |
|---|---|---|---|
| pg_dump 備份 | pg_dump postgres | 約 9.2 秒 | 文字 SQL 匯出速度快 |
| gzip 壓縮 | gzip postgres.sql | 約 3.4 秒 | 壓縮後僅 11 MB |
| tar.gz 打包 | tar -czvf | 約 3.8 秒 | 可存放多檔案 |
| psql 還原 | psql testdb2 < postgres.sql | 約 15.6 秒 | COPY 模式速度最佳 |
| checksum 比對 | string_agg + md5 | 約 2.1 秒 | 確保資料一致性 |
完整流程耗時約 30～35 秒，視 CPU / I/O 而定。
🏁 結論
本篇示範了 PostgreSQL 在大量資料情境下的完整操作流程，包括：
- 建立 50 萬筆資料表
- 批次插入 + 欄位填值（姓名、手機、Email、地址、日期、亂數）
- pg_dump 與壓縮後容量分析
- 資料庫還原與 checksum 一致性驗證
- pgbench 壓力測試與 TPS 數據
- 備份/還原耗時比較表
此流程可作為效能測試、壓力模擬、查詢優化與教育訓練的基礎範本，亦可輕鬆擴展至數百萬筆資料場景。
沒有留言: