PostgreSQL 開發者工具鏈：psql、pgAdmin、pgbench、pgTAP 完整指南 | PostgreSQL

-- Schema 探索
\dt+                    -- 列出所有 tables（含大小）
\d+ orders              -- 顯示 table 結構（含儲存參數）
\di+ orders*            -- 列出特定 table 的 indexes
\df+ public.*           -- 列出所有 functions
\dx                     -- 列出所有 extensions
\sf my_function         -- 顯示 function 原始碼
\sv my_view             -- 顯示 view 定義
\sf+ my_function        -- 含行號的原始碼

-- 連線與資訊
\conninfo               -- 顯示連線資訊
\c otherdb              -- 切換資料庫
\l+                     -- 列出所有 databases
\dn+                    -- 列出所有 schemas
\du+                    -- 列出所有 roles

-- 輸出控制
\x auto                 -- 自動切換擴展顯示（推薦）
\timing on              -- 顯示查詢執行時間
\pset null '(NULL)'     -- NULL 值明確顯示
\pset format csv        -- CSV 格式輸出

-- 每 2 秒監控活動連線
SELECT pid, usename, state, wait_event_type, left(query, 60) AS query
FROM pg_stat_activity
WHERE state = 'active' AND pid <> pg_backend_pid();
\watch 2

-- 每 5 秒監控複寫延遲
SELECT
    client_addr, state,
    pg_wal_lsn_diff(sent_lsn, replay_lsn) AS replay_lag_bytes,
    replay_lag
FROM pg_stat_replication;
\watch 5

-- 每 10 秒監控表膨脹
SELECT
    relname, n_live_tup, n_dead_tup,
    round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
    last_autovacuum::timestamp(0)
FROM pg_stat_user_tables
WHERE n_live_tup > 1000
ORDER BY dead_pct DESC NULLS LAST LIMIT 10;
\watch 10

-- PG16+：指定次數與間隔
\watch i=2 c=10   -- 每 2 秒一次，共 10 次

-- 匯出查詢結果為 CSV
\copy (SELECT * FROM orders WHERE created_at > '2026-01-01') TO '/tmp/orders.csv' WITH (FORMAT csv, HEADER true)

-- 從 CSV 匯入
\copy staging_orders FROM '/home/user/data.csv' WITH (FORMAT csv, HEADER true, NULL '')

-- \gexec：將查詢結果作為 SQL 執行（Meta Programming）
SELECT format('CREATE INDEX idx_%I_id ON %I(id);', tablename, tablename)
FROM pg_tables
WHERE schemaname = 'public' AND tablename NOT IN (
    SELECT tablename FROM pg_indexes WHERE indexname LIKE '%_id'
);
\gexec
-- 自動為所有缺少 id 索引的表建立索引

-- \crosstabview：交叉表視圖（樞紐分析）
SELECT
    extract(month FROM created_at)::int AS month,
    status,
    count(*)
FROM orders
WHERE created_at >= '2026-01-01'
GROUP BY 1, 2 ORDER BY 1, 2;
\crosstabview

-- \gdesc：顯示查詢欄位型別（不實際執行）
SELECT id, name, total_amount FROM orders;
\gdesc

-- ~/.psqlrc 範例

-- 客製化 PROMPT（顯示 DB 名稱與主機）
\set PROMPT1 '%[%033[1;31m%]%/%[%033[0m%] %n@%M:%> %x%R%# '

-- NULL 顯示為明確文字
\pset null '¤NULL¤'

-- 自動切換擴展顯示
\x auto

-- 顯示查詢執行時間
\timing on

-- 歷史紀錄設定
\set HISTSIZE 5000
\set HISTFILE ~/.psql_history-:DBNAME
\set HISTCONTROL ignoredups

-- 錯誤時自動 rollback 到 savepoint
\set ON_ERROR_ROLLBACK interactive

-- 常用查詢別名
\set activity 'SELECT pid, usename, state, wait_event_type, left(query, 80) AS query FROM pg_stat_activity WHERE state != \'idle\' AND pid <> pg_backend_pid() ORDER BY query_start;'
\set locks 'SELECT pid, locktype, relation::regclass, mode, granted FROM pg_locks WHERE NOT granted ORDER BY pid;'
\set bloat 'SELECT relname, n_live_tup, n_dead_tup, round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct FROM pg_stat_user_tables WHERE n_live_tup > 1000 ORDER BY dead_pct DESC NULLS LAST LIMIT 20;'
\set dbsize 'SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size FROM pg_database ORDER BY pg_database_size(datname) DESC;'
\set tablesize 'SELECT relname, pg_size_pretty(pg_total_relation_size(oid)) AS total_size, pg_size_pretty(pg_relation_size(oid)) AS table_size, pg_size_pretty(pg_indexes_size(oid)) AS index_size FROM pg_class WHERE relkind = \'r\' AND relnamespace = \'public\'::regnamespace ORDER BY pg_total_relation_size(oid) DESC LIMIT 20;'

\echo '=== psqlrc loaded ==='
\echo 'Type :activity :locks :bloat :dbsize :tablesize for quick queries'

# macOS
brew install --cask pgadmin4

# Docker（推薦開發環境）
docker run -d --name pgadmin4 \
  -p 5050:80 \
  -e PGADMIN_DEFAULT_EMAIL=admin@example.com \
  -e PGADMIN_DEFAULT_PASSWORD=admin \
  dpage/pgadmin4

# macOS
brew install --cask dbeaver-community

# Linux
flatpak install flathub io.dbeaver.DBeaverCommunity

pip install pgcli
pgcli -h localhost -U postgres -d mydb

# 並行 dump（僅支援 -Fd 目錄格式，-j 建議 CPU 核心數的 50-75%）
pg_dump -U postgres -Fd mydb -j 8 -f /backup/mydb_dir/

# 排除大表資料但保留結構（開發環境複製）
pg_dump -U postgres -Fc mydb \
  --exclude-table-data='audit_*' \
  --exclude-table-data='log_*' \
  --exclude-table-data='sessions' \
  > mydb_dev.dump

# 只備份特定 tables
pg_dump -U postgres -Fc mydb \
  -t 'orders' -t 'order_items' -t 'customers' \
  > mydb_core.dump

# 分離結構和資料（便於先建表再匯入）
pg_dump -U postgres --section=pre-data  -Fc mydb > mydb_pre.dump
pg_dump -U postgres --section=data      -Fc mydb > mydb_data.dump
pg_dump -U postgres --section=post-data -Fc mydb > mydb_post.dump

# 可攜式 dump（忽略 owner、privilege、tablespace）
pg_dump -U postgres -Fc mydb \
  --no-owner --no-privileges --no-tablespaces \
  > mydb_portable.dump

# PG16+：指定壓縮算法
pg_dump -U postgres -Fc --compress=lz4 mydb > mydb_lz4.dump
pg_dump -U postgres -Fc --compress=zstd:3 mydb > mydb_zstd.dump

# 並行還原（大幅縮短時間）
pg_restore -U postgres -d mydb -j 8 mydb.dump

# 選擇性還原：列出內容 → 編輯 → 還原
pg_restore --list mydb.dump > restore_list.txt
# 編輯 restore_list.txt，用 ; 註解不需要的項目
pg_restore -U postgres -d mydb -L restore_list.txt mydb.dump

# 分段還原（效能最佳化：先建表 → 匯入資料 → 再建索引）
pg_restore -U postgres -d mydb --section=pre-data mydb_pre.dump
pg_restore -U postgres -d mydb --section=data mydb_data.dump
pg_restore -U postgres -d mydb --section=post-data mydb_post.dump

-- 匯出查詢結果
COPY (
    SELECT o.id, c.name, o.total_amount
    FROM orders o JOIN customers c ON o.customer_id = c.id
    WHERE o.created_at >= '2026-01-01'
) TO '/tmp/order_summary.csv' WITH (FORMAT csv, HEADER true);

-- 匯入到臨時表（常見 ETL 模式）
CREATE TEMP TABLE staging_orders (LIKE orders INCLUDING ALL);
COPY staging_orders FROM '/tmp/new_orders.csv' WITH (FORMAT csv, HEADER true);

-- 合併到正式表（UPSERT）
INSERT INTO orders SELECT * FROM staging_orders
ON CONFLICT (id) DO UPDATE SET
    total_amount = EXCLUDED.total_amount,
    updated_at = now();

-- PG17+：ON_ERROR 參數（跳過格式錯誤的行）
COPY orders FROM '/tmp/messy_data.csv' WITH (
    FORMAT csv, HEADER true,
    ON_ERROR 'ignore'       -- 跳過錯誤行而非中止
);

-- COPY FREEZE：匯入同時凍結 tuple，避免後續 VACUUM
BEGIN;
TRUNCATE orders;
COPY orders FROM '/data/orders.csv' WITH (FORMAT csv, HEADER true, FREEZE true);
COMMIT;

-- 1. 暫時停用 indexes
-- 2. 暫時停用 triggers
ALTER TABLE orders DISABLE TRIGGER ALL;
-- 3. 暫時停用 foreign key checks
SET session_replication_role = replica;
-- 4. 增大 maintenance_work_mem
SET maintenance_work_mem = '1GB';
-- 5. 執行 COPY
COPY orders FROM '/data/large_import.csv' WITH (FORMAT csv, HEADER true);
-- 6. 重建 indexes
-- 7. 恢復 triggers
ALTER TABLE orders ENABLE TRIGGER ALL;
SET session_replication_role = DEFAULT;
-- 8. 更新統計
ANALYZE orders;

# 從壓縮檔直接匯入
gunzip -c orders.csv.gz | psql -U postgres -d mydb \
  -c "COPY orders FROM STDIN WITH (FORMAT csv, HEADER true)"

# 跨伺服器資料搬遷
psql -h source -c "COPY orders TO STDOUT WITH (FORMAT csv, HEADER true)" | \
psql -h target -c "COPY orders FROM STDIN WITH (FORMAT csv, HEADER true)"

# 搭配 pv 監控傳輸進度
psql -h source -c "COPY orders TO STDOUT WITH (FORMAT binary)" | \
  pv -pterb | \
  psql -h target -c "COPY orders FROM STDIN WITH (FORMAT binary)"

# Ubuntu/Debian
sudo apt install postgresql-17-pgtap

# macOS
brew install pgtap

CREATE EXTENSION pgtap;

BEGIN;
SELECT plan(5);    -- 宣告共 5 個測試

-- 結構測試
SELECT has_table('orders', 'orders 表應存在');
SELECT has_column('orders', 'total_amount', '應有 total_amount 欄位');
SELECT col_type_is('orders', 'total_amount', 'numeric', '型別應為 numeric');
SELECT has_pk('orders', '應有 primary key');
SELECT has_fk('order_items', '應有 foreign key');

SELECT * FROM finish();
ROLLBACK;    -- rollback 確保測試不污染資料

-- 欄位約束測試
SELECT col_not_null('orders', 'customer_id', 'customer_id 不可為 NULL');
SELECT col_has_default('orders', 'created_at', 'created_at 應有預設值');
SELECT col_default_is('orders', 'status', 'pending', 'status 預設為 pending');

-- 外鍵測試
SELECT fk_ok('order_items', 'order_id', 'orders', 'id',
             'order_items.order_id 應參照 orders.id');

-- 索引測試
SELECT has_index('orders', 'idx_orders_customer_id', '索引應存在');
SELECT index_is_type('orders', 'idx_orders_customer_id', 'btree', '應為 btree');

-- Function 測試
SELECT has_function('calculate_total', ARRAY['integer'], '函數應存在');
SELECT function_returns('calculate_total', ARRAY['integer'], 'numeric', '應回傳 numeric');

-- 資料測試（結果集比較）
SELECT results_eq(
    'SELECT count(*)::integer FROM orders WHERE status = ''completed''',
    ARRAY[42],
    '已完成訂單應有 42 筆'
);

-- 例外測試
SELECT throws_ok(
    'INSERT INTO orders (id) VALUES (NULL)',
    '23502',
    NULL,
    '插入 NULL id 應報錯'
);

BEGIN;
SELECT plan(8);

SELECT has_table('user_preferences', 'migration 應建立 user_preferences 表');
SELECT has_column('user_preferences', 'user_id', '應有 user_id 欄位');
SELECT has_column('user_preferences', 'theme', '應有 theme 欄位');
SELECT col_type_is('user_preferences', 'theme', 'text', '應為 text');
SELECT col_default_is('user_preferences', 'theme', 'light', '預設為 light');
SELECT fk_ok('user_preferences', 'user_id', 'users', 'id');
SELECT has_index('user_preferences', 'idx_user_preferences_user_id');
SELECT hasnt_column('users', 'preferences_json', '舊欄位應已移除');

SELECT * FROM finish();
ROLLBACK;

# 執行測試目錄下所有 .sql 測試檔
pg_prove -U postgres -d testdb tests/*.sql

# 詳細輸出
pg_prove -U postgres -d testdb -v tests/*.sql

# CI/CD 整合（GitHub Actions）
- name: Run database tests
  run: pg_prove -U postgres -d testdb --recurse tests/

# 初始化測試資料（-s 100 → 約 1.6GB，1000 萬筆帳戶）
pgbench -U postgres -i -s 100 mydb

# 基礎壓測：10 個客戶端，每個 100 transactions
pgbench -U postgres -c 10 -t 100 mydb

# 指定持續時間 + 多執行緒 + 進度報告
pgbench -U postgres -c 32 -j 8 -T 120 -P 5 mydb

# 只讀測試
pgbench -U postgres -c 32 -j 8 -T 60 -S mydb

# 使用 Prepared Statements（更貼近實際）
pgbench -U postgres -c 16 -j 4 -T 120 -M prepared mydb

# 建立自訂腳本
cat > /tmp/custom_bench.sql << 'EOF'
\set customer_id random(1, 100000)
\set amount random(100, 10000)
BEGIN;
SELECT * FROM customers WHERE id = :customer_id;
INSERT INTO orders (customer_id, total_amount, status)
VALUES (:customer_id, :amount, 'pending');
UPDATE customers SET last_order_at = now() WHERE id = :customer_id;
COMMIT;
EOF

pgbench -U postgres -c 16 -j 4 -T 120 -f /tmp/custom_bench.sql mydb

# 多腳本混合測試（讀寫比 80:20）
pgbench -U postgres -c 16 -j 4 -T 120 \
  -f /tmp/read_bench.sql@80 \
  -f /tmp/custom_bench.sql@20 \
  mydb

# Zipfian 分佈（少數 ID 被頻繁存取，貼近真實場景）
cat > /tmp/zipf_bench.sql << 'EOF'
\set aid random_zipfian(1, 100000, 1.5)
SELECT * FROM pgbench_accounts WHERE aid = :aid;
EOF

pgbench -U postgres -c 16 -T 120 -f /tmp/zipf_bench.sql mydb

-- 維度一：總耗時 Top 20（對整體效能影響最大）
SELECT
    queryid,
    round(total_exec_time::numeric / 1000, 2) AS total_secs,
    calls,
    round(mean_exec_time::numeric, 2) AS avg_ms,
    rows,
    round(100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0), 1) AS hit_pct,
    left(query, 120) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 20;

-- 維度二：平均耗時 Top 20（單次最慢）
SELECT queryid, calls,
    round(mean_exec_time::numeric, 2) AS avg_ms,
    round(max_exec_time::numeric, 2) AS max_ms,
    left(query, 120) AS query
FROM pg_stat_statements
WHERE calls >= 10
ORDER BY mean_exec_time DESC LIMIT 20;

-- 維度三：I/O 密集 Top 20（需要索引優化）
SELECT queryid, calls,
    shared_blks_read AS disk_reads,
    round(100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0), 1) AS hit_pct,
    left(query, 120) AS query
FROM pg_stat_statements
ORDER BY shared_blks_read DESC LIMIT 20;

-- 維度四：執行時間不穩定（CV 值高）
SELECT queryid, calls,
    round(mean_exec_time::numeric, 2) AS avg_ms,
    round(100.0 * stddev_exec_time / nullif(mean_exec_time, 0), 1) AS cv_pct,
    left(query, 120) AS query
FROM pg_stat_statements
WHERE calls >= 50 AND mean_exec_time > 10
ORDER BY cv_pct DESC LIMIT 20;