如何使用 psql 連線 AWS RDS PostgreSQL 並在容器與 Pod 中操作

# macOS (使用 Homebrew)
brew install postgresql

# Ubuntu/Debian
sudo apt-get update
sudo apt-get install postgresql-client

# RHEL/CentOS/Amazon Linux
sudo yum install postgresql

# Windows (使用 Chocolatey)
choco install postgresql

# 驗證安裝
psql --version
# 應該顯示：psql (PostgreSQL) 15.x

psql \
  -h yourdb.xxx.ap-east-1.rds.amazonaws.com \
  -p 5432 \
  -U postgres \
  -d postgres

# 參數說明：
# -h: RDS Endpoint (host)
# -p: 埠號 (port)
# -U: 使用者名稱 (username)
# -d: 資料庫名稱 (database)

# 設定環境變數
export PGHOST=yourdb.xxx.ap-east-1.rds.amazonaws.com
export PGPORT=5432
export PGUSER=postgres
export PGDATABASE=postgres
export PGPASSWORD=your-password  # ⚠️ 不安全，見下方說明

# 現在可以直接執行 psql
psql

# 建立 ~/.pgpass 檔案
echo "yourdb.xxx.ap-east-1.rds.amazonaws.com:5432:postgres:postgres:your-password" > ~/.pgpass

# 設定權限（必須是 0600，否則 psql 會拒絕使用）
chmod 0600 ~/.pgpass

# 現在 psql 會自動讀取密碼
psql -h yourdb.xxx.ap-east-1.rds.amazonaws.com -U postgres -d postgres

ssh -i ~/.ssh/bastion.pem ec2-user@BASTION_PUBLIC_IP

# Amazon Linux 使用 ec2-user
# Ubuntu 使用 ubuntu

# 在 Bastion Host 上安裝 psql
sudo yum install postgresql -y  # Amazon Linux
# 或
sudo apt-get install postgresql-client -y  # Ubuntu

# 測試連線 RDS
psql -h yourdb.xxx.ap-east-1.rds.amazonaws.com -U postgres -d postgres

# 如果成功，代表 Bastion → RDS 的網路路徑是通的
# 輸入 \q 離開 psql
# 輸入 exit 離開 Bastion

ssh -i ~/.ssh/bastion.pem \
  -L 5433:yourdb.xxx.ap-east-1.rds.amazonaws.com:5432 \
  -N \
  ec2-user@BASTION_PUBLIC_IP

psql -h localhost -p 5433 -U postgres -d postgres

# 或使用 127.0.0.1
psql -h 127.0.0.1 -p 5433 -U postgres -d postgres

# 方法 1: 使用 -f 參數
ssh -f -i ~/.ssh/bastion.pem \
  -L 5433:yourdb.xxx.ap-east-1.rds.amazonaws.com:5432 \
  -N \
  ec2-user@BASTION_PUBLIC_IP

# -f: 在背景執行

# 方法 2: 使用 nohup
nohup ssh -i ~/.ssh/bastion.pem \
  -L 5433:yourdb.xxx.ap-east-1.rds.amazonaws.com:5432 \
  -N \
  ec2-user@BASTION_PUBLIC_IP &

# 檢查隧道是否運作
lsof -i :5433
# 應該會看到 ssh 程序正在監聽 5433 埠號

# 關閉隧道
kill $(lsof -t -i :5433)

# Bastion Host
Host bastion
    HostName BASTION_PUBLIC_IP
    User ec2-user
    IdentityFile ~/.ssh/bastion.pem
    ServerAliveInterval 60
    ServerAliveCountMax 3

# RDS Tunnel
Host rds-tunnel
    HostName localhost
    User postgres
    LocalForward 5433 yourdb.xxx.ap-east-1.rds.amazonaws.com:5432
    ProxyJump bastion

# 建立隧道
ssh -N rds-tunnel

# 在另一個終端機連線
psql -h localhost -p 5433 -U postgres -d postgres

# 列出所有運行中的容器
docker ps

# 輸出範例：
# CONTAINER ID   IMAGE          COMMAND       CREATED        STATUS        NAMES
# abc123def456   node:18        "node app"    2 hours ago    Up 2 hours    app-server

# 進入容器（使用 bash）
docker exec -it app-server bash

# 或使用 sh（如果是 Alpine Linux）
docker exec -it app-server sh

# 在容器內安裝 psql（如果尚未安裝）
# Debian/Ubuntu 基底
apt-get update && apt-get install -y postgresql-client

# Alpine Linux 基底
apk add --no-cache postgresql-client

# 連線到 RDS
psql -h yourdb.xxx.ap-east-1.rds.amazonaws.com -U postgres -d postgres

# 方法 1: 互動模式（推薦用於臨時查詢）
docker run -it --rm \
  --name postgres-client \
  -e PGPASSWORD=your-password \
  postgres:15 \
  psql -h yourdb.xxx.ap-east-1.rds.amazonaws.com \
       -U postgres \
       -d postgres

# 參數說明：
# -it: 互動模式
# --rm: 容器結束後自動刪除
# --name: 容器名稱
# -e PGPASSWORD: 設定密碼環境變數（避免每次輸入）
# postgres:15: 使用 PostgreSQL 15 映像檔
# psql ...: 容器啟動後執行的命令

# 方法 2: 進入容器 shell
docker run -it --rm postgres:15 bash

# 在容器內執行
psql -h yourdb.xxx.ap-east-1.rds.amazonaws.com -U postgres -d postgres

version: '3.8'

services:
  # 你的應用服務
  app:
    image: your-app:latest
    environment:
      - DATABASE_URL=postgresql://postgres:password@yourdb.xxx.ap-east-1.rds.amazonaws.com:5432/postgres
    networks:
      - app-network

  # psql 客戶端（用於管理）
  db-client:
    image: postgres:15
    environment:
      - PGHOST=yourdb.xxx.ap-east-1.rds.amazonaws.com
      - PGPORT=5432
      - PGUSER=postgres
      - PGPASSWORD=your-password
      - PGDATABASE=postgres
    networks:
      - app-network
    # 保持容器運行
    command: tail -f /dev/null
    # 或使用 stdin_open 和 tty
    stdin_open: true
    tty: true

networks:
  app-network:
    driver: bridge

# 啟動所有服務
docker-compose up -d

# 進入 db-client 容器
docker-compose exec db-client bash

# 在容器內直接執行 psql（環境變數已設定）
psql

# 或從容器外直接執行 SQL
docker-compose exec db-client psql -c "SELECT version();"

# 停止服務
docker-compose down

# 列出所有 Pod
kubectl get pods -n default

# 輸出範例：
# NAME                       READY   STATUS    RESTARTS   AGE
# app-deployment-abc123      1/1     Running   0          2h
# redis-xyz789               1/1     Running   0          5h

# 進入 Pod（執行 bash）
kubectl exec -it app-deployment-abc123 -n default -- bash

# 或使用 sh
kubectl exec -it app-deployment-abc123 -n default -- sh

# 在 Pod 內安裝 psql（如果尚未安裝）
apt-get update && apt-get install -y postgresql-client

# 連線到 RDS
psql -h yourdb.xxx.ap-east-1.rds.amazonaws.com -U postgres -d postgres

# 離開 Pod
exit

# 執行單一 SQL 查詢
kubectl exec -it app-deployment-abc123 -n default -- \
  psql -h yourdb.xxx.ap-east-1.rds.amazonaws.com \
       -U postgres \
       -d postgres \
       -c "SELECT version();"

# 執行多個命令
kubectl exec -it app-deployment-abc123 -n default -- \
  psql -h yourdb.xxx.ap-east-1.rds.amazonaws.com \
       -U postgres \
       -d postgres \
       -c "SELECT * FROM users LIMIT 5;"

kubectl run psql-client \
  --rm -i --tty \
  --image=postgres:15 \
  --namespace=default \
  --env="PGHOST=yourdb.xxx.ap-east-1.rds.amazonaws.com" \
  --env="PGPORT=5432" \
  --env="PGUSER=postgres" \
  --env="PGPASSWORD=your-password" \
  --env="PGDATABASE=postgres" \
  --restart=Never \
  --command -- psql

# 參數說明：
# --rm: Pod 結束後自動刪除
# -i --tty: 互動模式
# --image: 使用的容器映像
# --env: 設定環境變數
# --restart=Never: 不自動重啟
# --command -- psql: 執行 psql 命令

# psql-client-deployment.yaml
apiVersion: v1
kind: Secret
metadata:
  name: rds-credentials
  namespace: default
type: Opaque
stringData:
  PGHOST: "yourdb.xxx.ap-east-1.rds.amazonaws.com"
  PGPORT: "5432"
  PGUSER: "postgres"
  PGPASSWORD: "your-password"
  PGDATABASE: "postgres"
---
apiVersion: apps/v1
kind: Deployment
metadata:
  name: psql-client
  namespace: default
spec:
  replicas: 1
  selector:
    matchLabels:
      app: psql-client
  template:
    metadata:
      labels:
        app: psql-client
    spec:
      containers:
      - name: postgres-client
        image: postgres:15
        envFrom:
        - secretRef:
            name: rds-credentials
        command:
        - sh
        - -c
        - |
          # 安裝額外工具
          apt-get update && apt-get install -y vim curl

          # 保持容器運行
          tail -f /dev/null
        resources:
          requests:
            memory: "128Mi"
            cpu: "100m"
          limits:
            memory: "256Mi"
            cpu: "200m"

# 部署
kubectl apply -f psql-client-deployment.yaml

# 等待 Pod 就緒
kubectl wait --for=condition=ready pod -l app=psql-client --timeout=60s

# 進入 Pod
kubectl exec -it deployment/psql-client -- bash

# 在 Pod 內執行 psql（環境變數已設定）
psql

# 或從外部直接執行查詢
kubectl exec deployment/psql-client -- psql -c "SELECT current_database();"

# 刪除部署
kubectl delete -f psql-client-deployment.yaml

# db-migration-job.yaml
apiVersion: batch/v1
kind: Job
metadata:
  name: db-migration
  namespace: default
spec:
  template:
    spec:
      restartPolicy: Never
      containers:
      - name: migration
        image: postgres:15
        env:
        - name: PGHOST
          valueFrom:
            secretKeyRef:
              name: rds-credentials
              key: PGHOST
        - name: PGUSER
          valueFrom:
            secretKeyRef:
              name: rds-credentials
              key: PGUSER
        - name: PGPASSWORD
          valueFrom:
            secretKeyRef:
              name: rds-credentials
              key: PGPASSWORD
        - name: PGDATABASE
          valueFrom:
            secretKeyRef:
              name: rds-credentials
              key: PGDATABASE
        command:
        - sh
        - -c
        - |
          # 執行資料遷移 SQL
          psql <<EOF
          CREATE TABLE IF NOT EXISTS migrations (
            id SERIAL PRIMARY KEY,
            version VARCHAR(50) NOT NULL,
            applied_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
          );

          INSERT INTO migrations (version) VALUES ('v1.0.0');
          EOF

          echo "Migration completed successfully"
  backoffLimit: 3

# 建立 Job
kubectl apply -f db-migration-job.yaml

# 查看 Job 狀態
kubectl get jobs

# 查看 Job 日誌
kubectl logs job/db-migration

# 刪除 Job
kubectl delete job db-migration

-- 列出所有資料庫
postgres=> \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 my_db     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

-- 切換資料庫
postgres=> \c my_db
You are now connected to database "my_db" as user "postgres".
my_db=>

-- 顯示連線資訊
my_db=> \conninfo
You are connected to database "my_db" as user "postgres" on host "yourdb.xxx.rds.amazonaws.com" at port "5432".
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)

-- 列出資料表
my_db=> \dt
          List of relations
 Schema |     Name      | Type  |  Owner
--------+---------------+-------+----------
 public | users         | table | postgres
 public | posts         | table | postgres
 public | comments      | table | postgres
(3 rows)

-- 描述資料表結構
my_db=> \d users
                                        Table "public.users"
   Column   |            Type             | Collation | Nullable |              Default
------------+-----------------------------+-----------+----------+-----------------------------------
 id         | integer                     |           | not null | nextval('users_id_seq'::regclass)
 email      | character varying(255)      |           | not null |
 name       | character varying(100)      |           |          |
 created_at | timestamp without time zone |           |          | CURRENT_TIMESTAMP
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
    "users_email_key" UNIQUE CONSTRAINT, btree (email)
Referenced by:
    TABLE "posts" CONSTRAINT "posts_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id)

-- 預設水平顯示
my_db=> SELECT * FROM users LIMIT 1;
 id |      email       |  name  |     created_at
----+------------------+--------+--------------------
  1 | user@example.com | John   | 2024-01-01 10:00:00
(1 row)

-- 開啟擴展模式（垂直顯示）
my_db=> \x
Expanded display is on.

my_db=> SELECT * FROM users LIMIT 1;
-[ RECORD 1 ]------------------------
id         | 1
email      | user@example.com
name       | John
created_at | 2024-01-01 10:00:00

-- 關閉擴展模式
my_db=> \x
Expanded display is off.

-- 開啟查詢時間顯示
my_db=> \timing
Timing is on.

my_db=> SELECT COUNT(*) FROM users;
 count
-------
  1000
(1 row)

Time: 5.234 ms

-- 查看資料庫版本
SELECT version();

-- 查看當前資料庫
SELECT current_database();

-- 查看當前使用者
SELECT current_user;

-- 查看所有資料表（SQL 方式）
SELECT tablename FROM pg_tables WHERE schemaname = 'public';

-- 查詢前 N 筆資料
SELECT * FROM users LIMIT 5;

-- 查詢特定欄位
SELECT id, email, name FROM users;

-- 條件查詢
SELECT * FROM users WHERE email = 'user@example.com';

-- 模糊查詢
SELECT * FROM users WHERE name LIKE 'John%';

-- 排序
SELECT * FROM users ORDER BY created_at DESC;

-- 計數
SELECT COUNT(*) FROM users;

-- 分組統計
SELECT DATE(created_at), COUNT(*)
FROM users
GROUP BY DATE(created_at)
ORDER BY DATE(created_at) DESC;

-- 查詢不重複值
SELECT DISTINCT email FROM users;

-- JOIN 查詢
SELECT u.name, p.title
FROM users u
INNER JOIN posts p ON u.id = p.user_id
LIMIT 10;

-- 更新單一記錄
UPDATE users
SET name = 'John Doe'
WHERE id = 1;

-- 更新多筆記錄
UPDATE users
SET email = LOWER(email)
WHERE email LIKE '%@EXAMPLE.COM';

-- 批次更新
UPDATE users
SET updated_at = CURRENT_TIMESTAMP
WHERE created_at < '2024-01-01';

-- 刪除記錄
DELETE FROM users WHERE id = 999;

-- 刪除符合條件的記錄
DELETE FROM users WHERE created_at < '2020-01-01';

-- 清空資料表（保留結構）
TRUNCATE TABLE users;

-- 插入記錄
INSERT INTO users (email, name)
VALUES ('new@example.com', 'New User');

-- 插入多筆記錄
INSERT INTO users (email, name) VALUES
  ('user1@example.com', 'User 1'),
  ('user2@example.com', 'User 2'),
  ('user3@example.com', 'User 3');

-- 插入並回傳結果
INSERT INTO users (email, name)
VALUES ('test@example.com', 'Test User')
RETURNING id, email;

-- 開始交易
BEGIN;

-- 執行多個操作
UPDATE users SET name = 'Updated' WHERE id = 1;
INSERT INTO audit_log (action, user_id) VALUES ('update', 1);

-- 提交交易
COMMIT;

-- 或回滾交易
ROLLBACK;

-- 設定儲存點
BEGIN;
UPDATE users SET name = 'First Update' WHERE id = 1;
SAVEPOINT sp1;
UPDATE users SET name = 'Second Update' WHERE id = 2;
-- 回滾到儲存點
ROLLBACK TO SAVEPOINT sp1;
-- 只有第一個更新會生效
COMMIT;

-- 查看資料表大小
SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- 查看資料庫大小
SELECT
    datname,
    pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

-- 查看正在執行的查詢
SELECT
    pid,
    usename,
    application_name,
    client_addr,
    state,
    query,
    query_start
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY query_start;

-- 終止慢查詢
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'active'
  AND query_start < now() - interval '5 minutes';

-- 查看索引使用情況
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

-- 查看未使用的索引
SELECT
    schemaname,
    tablename,
    indexname
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexname NOT LIKE '%_pkey';

-- 匯出查詢結果到 CSV
\copy (SELECT * FROM users) TO '/tmp/users.csv' WITH CSV HEADER;

-- 從 CSV 匯入資料
\copy users FROM '/tmp/users.csv' WITH CSV HEADER;

-- 匯出整個資料表
\copy users TO '/tmp/users_backup.csv' WITH CSV HEADER;

# 啟用 RDS IAM 認證
# AWS Console > RDS > Modify > Database authentication > Enable IAM authentication

# 在 EC2/EKS 上使用 IAM Role 產生臨時 token
TOKEN=$(aws rds generate-db-auth-token \
  --hostname yourdb.xxx.ap-east-1.rds.amazonaws.com \
  --port 5432 \
  --username iamuser \
  --region ap-east-1)

# 使用 token 連線（15 分鐘有效）
psql "host=yourdb.xxx.ap-east-1.rds.amazonaws.com \
      port=5432 \
      dbname=postgres \
      user=iamuser \
      password=$TOKEN \
      sslmode=require"

-- 檢查是否使用 SSL
SELECT ssl_is_used();

-- 查看 SSL 版本
\conninfo

-- 強制要求 SSL（在 psql 連線字串中）
psql "host=yourdb.xxx.rds.amazonaws.com \
      port=5432 \
      dbname=postgres \
      user=postgres \
      sslmode=require"

-- sslmode 選項：
-- disable: 不使用 SSL
-- allow: 嘗試 SSL，失敗則降級
-- prefer: 優先使用 SSL（預設）
-- require: 強制 SSL，不驗證憑證
-- verify-ca: 強制 SSL，驗證憑證
-- verify-full: 強制 SSL，驗證憑證和主機名

-- 建立唯讀使用者
CREATE ROLE readonly WITH LOGIN PASSWORD 'secure-password';

-- 授予連線權限
GRANT CONNECT ON DATABASE my_db TO readonly;

-- 授予 schema 使用權限
GRANT USAGE ON SCHEMA public TO readonly;

-- 授予所有資料表的 SELECT 權限
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

-- 對未來建立的資料表也生效
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO readonly;

-- 建立應用程式使用者（讀寫權限，但不能刪除資料表）
CREATE ROLE appuser WITH LOGIN PASSWORD 'secure-password';
GRANT CONNECT ON DATABASE my_db TO appuser;
GRANT USAGE ON SCHEMA public TO appuser;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO appuser;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO appuser;

-- 啟用查詢日誌（AWS RDS Parameter Group）
-- log_statement = 'all'  -- 記錄所有語句（效能影響大）
-- log_statement = 'ddl'  -- 只記錄 DDL（CREATE, ALTER, DROP）
-- log_statement = 'mod'  -- 記錄資料修改（INSERT, UPDATE, DELETE）

-- 查看最近的連線
SELECT
    usename,
    application_name,
    client_addr,
    backend_start,
    state
FROM pg_stat_activity
ORDER BY backend_start DESC;

-- 查看失敗的連線嘗試（需啟用 log_connections）
-- 在 CloudWatch Logs 中查看：
-- /aws/rds/instance/your-db/postgresql

# 1. 測試網路連通性
# 從本機測試
telnet yourdb.xxx.ap-east-1.rds.amazonaws.com 5432
# 或使用 nc
nc -zv yourdb.xxx.ap-east-1.rds.amazonaws.com 5432

# 成功會顯示：
# Connection to yourdb.xxx.ap-east-1.rds.amazonaws.com 5432 port [tcp/postgresql] succeeded!

# 2. DNS 解析測試
nslookup yourdb.xxx.ap-east-1.rds.amazonaws.com
dig yourdb.xxx.ap-east-1.rds.amazonaws.com

# 3. 路由追蹤
traceroute yourdb.xxx.ap-east-1.rds.amazonaws.com

# 4. 檢查本機防火牆（macOS）
sudo pfctl -sr | grep 5432

# 5. 從 Bastion Host 測試
ssh -i ~/.ssh/bastion.pem ec2-user@BASTION_IP
telnet yourdb.xxx.ap-east-1.rds.amazonaws.com 5432

# 1. 密碼錯誤
# 從 AWS Secrets Manager 取得正確密碼
aws secretsmanager get-secret-value \
  --secret-id rds-db-credentials \
  --query SecretString \
  --output text | jq -r .password

# 2. 使用者不存在
# 連線為 master user，建立新使用者
psql -h ... -U postgres -c "CREATE USER myuser WITH PASSWORD 'mypassword';"

# 3. 資料庫不存在
# 列出所有資料庫
psql -h ... -U postgres -l

# 建立資料庫
psql -h ... -U postgres -c "CREATE DATABASE my_db;"

# 4. IP 位址被拒絕（pg_hba.conf）
# RDS 的 pg_hba.conf 由 AWS 管理，但可以透過 Parameter Group 調整
# 檢查連線是否使用 SSL
psql "host=... sslmode=require user=postgres dbname=postgres"

# 1. 下載 RDS SSL 憑證
wget https://truststore.pki.rds.amazonaws.com/global/global-bundle.pem

# 2. 使用憑證連線
psql "host=yourdb.xxx.rds.amazonaws.com \
      port=5432 \
      dbname=postgres \
      user=postgres \
      sslmode=verify-full \
      sslrootcert=global-bundle.pem"

# 3. 或降低 SSL 要求（不建議用於生產環境）
psql "host=yourdb.xxx.rds.amazonaws.com \
      port=5432 \
      dbname=postgres \
      user=postgres \
      sslmode=require"

# 1. 檢查 Pod 是否可以解析 RDS Endpoint
kubectl exec -it pod-name -- nslookup yourdb.xxx.ap-east-1.rds.amazonaws.com

# 2. 檢查 Pod 是否可以連通 RDS 埠號
kubectl exec -it pod-name -- nc -zv yourdb.xxx.ap-east-1.rds.amazonaws.com 5432

# 3. 檢查 Node Security Group
# AWS Console > EC2 > Security Groups
# 找到 EKS Node 的 SG，記下 SG ID (例如 sg-0123456789abcdef0)

# 4. 確認 RDS Security Group 允許 Node SG
# AWS Console > RDS > Databases > Your DB > Connectivity & security
# VPC security groups > Edit inbound rules
# 新增規則：
# Type: PostgreSQL
# Source: sg-0123456789abcdef0 (Node SG)

# 5. 檢查環境變數是否正確設定
kubectl exec -it pod-name -- env | grep PG

-- 查看當前連線數
SELECT COUNT(*) FROM pg_stat_activity;

-- 查看最大連線數限制
SHOW max_connections;

-- 查看各使用者的連線數
SELECT
    usename,
    COUNT(*) as connection_count
FROM pg_stat_activity
GROUP BY usename
ORDER BY connection_count DESC;

-- 終止閒置連線
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle'
  AND state_change < now() - interval '10 minutes';

-- 增加最大連線數（需修改 RDS Parameter Group）
-- AWS Console > RDS > Parameter groups > Edit parameters
-- 找到 max_connections，修改為更大的值（例如 200）
-- 套用後需要重啟 RDS 實例

# 安裝 PgBouncer（在應用伺服器或 Bastion Host）
sudo apt-get install pgbouncer

# 設定 /etc/pgbouncer/pgbouncer.ini
[databases]
my_db = host=yourdb.xxx.rds.amazonaws.com port=5432 dbname=my_db

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20

# 應用程式連到 PgBouncer，而非直接連 RDS
psql -h pgbouncer-host -p 6432 -U postgres -d my_db

-- 使用 EXPLAIN ANALYZE 分析查詢
EXPLAIN ANALYZE
SELECT u.name, p.title
FROM users u
JOIN posts p ON u.id = p.user_id
WHERE u.email = 'user@example.com';

-- 建立索引加速查詢
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_posts_user_id ON posts(user_id);

-- 檢視慢查詢（需啟用 log_min_duration_statement）
-- 在 RDS Parameter Group 設定：
-- log_min_duration_statement = 1000  （記錄超過 1 秒的查詢）

-- 在 CloudWatch Logs 中查看慢查詢日誌