# 添加官方源（獲取最新版本，Ubuntu 22.04 自帶的版本較舊）
sudo apt install -y curl ca-certificates
sudo install -d /usr/share/postgresql-common/pgdg
curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail \
  https://www.postgresql.org/media/keys/ACCC4CF8.asc
sh -c 'echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] \
  https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > \
  /etc/apt/sources.list.d/pgdg.list'
sudo apt update
sudo apt install -y postgresql-16    # 安裝 PostgreSQL 16
# 檢查狀態(tài)
sudo systemctl status postgresql

sudo -u postgres psql

-- 創(chuàng)建應(yīng)用用戶（不要直接用 postgres 超級(jí)用戶）
CREATE USER myapp WITH PASSWORD '強(qiáng)密碼在這里';

-- 創(chuàng)建數(shù)據(jù)庫
CREATE DATABASE myapp_db OWNER myapp;

-- 如果需要超級(jí)用戶權(quán)限（不推薦）
-- CREATE USER admin WITH SUPERUSER PASSWORD '密碼';

-- 退出
\q

CREATE USER readonly WITH PASSWORD '只讀用戶密碼';
GRANT CONNECT ON DATABASE myapp_db TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;

sudo nano /etc/postgresql/16/main/postgresql.conf

# 連接設(shè)置
listen_addresses = 'localhost'   # 只監(jiān)聽本地（如需遠(yuǎn)程訪問改為 '*'）
max_connections = 100            # 最大連接數(shù)（根據(jù)內(nèi)存調(diào)整）

# 內(nèi)存設(shè)置（根據(jù)服務(wù)器內(nèi)存調(diào)整）
shared_buffers = 256MB           # 建議內(nèi)存的 25%
effective_cache_size = 768MB     # 建議內(nèi)存的 75%
work_mem = 4MB                   # 每個(gè)排序操作的內(nèi)存
maintenance_work_mem = 64MB      # VACUUM、CREATE INDEX 的內(nèi)存

# 日志
log_destination = 'stderr'
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d.log'
log_min_duration_statement = 1000  # 記錄超過 1 秒的慢查詢

# 檢查點(diǎn)
checkpoint_completion_target = 0.9
wal_buffers = 16MB

# 修改 listen_addresses
sudo sed -i "s/#listen_addresses = 'localhost'/listen_addresses = '*'/" \
  /etc/postgresql/16/main/postgresql.conf

sudo nano /etc/postgresql/16/main/pg_hba.conf

# 格式：類型 數(shù)據(jù)庫 用戶 地址 認(rèn)證方式
host    myapp_db    myapp    應(yīng)用服務(wù)器IP/32    scram-sha-256

sudo systemctl restart postgresql

# 從應(yīng)用服務(wù)器測(cè)試連接
psql -h 數(shù)據(jù)庫服務(wù)器IP -U myapp -d myapp_db
# 或者用 Python
python3 -c "import psycopg2; conn = psycopg2.connect('host=數(shù)據(jù)庫IP dbname=myapp_db user=myapp password=密碼'); print('連接成功')"

-- 列出所有數(shù)據(jù)庫
\l
-- 切換數(shù)據(jù)庫
\c myapp_db
-- 列出所有表
\dt
-- 查看表結(jié)構(gòu)
\d 表名
-- 查看當(dāng)前連接
SELECT * FROM pg_stat_activity;
-- 查看表大小
SELECT pg_size_pretty(pg_total_relation_size('表名'));
-- 查看數(shù)據(jù)庫大小
SELECT pg_size_pretty(pg_database_size('myapp_db'));

# 備份單個(gè)數(shù)據(jù)庫（推薦格式：custom，支持并行恢復(fù)）
pg_dump -U postgres -Fc myapp_db > /backup/myapp-$(date +%Y%m%d).dump
# 備份所有數(shù)據(jù)庫
pg_dumpall -U postgres | gzip > /backup/all-$(date +%Y%m%d).sql.gz
# 恢復(fù)
pg_restore -U postgres -d myapp_db /backup/myapp-20260427.dump
# 自動(dòng)備份（加入 cron）
echo "0 2 * * * pg_dump -U postgres -Fc myapp_db | gzip > /backup/myapp-\$(date +\%Y\%m\%d).dump.gz" | sudo -u postgres crontab -

-- 查看正在執(zhí)行的慢查詢
SELECT pid, now() - pg_stat_activity.query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active'
  AND now() - pg_stat_activity.query_start > interval '5 seconds'
ORDER BY duration DESC;

-- 找出需要添加索引的表
SELECT schemaname, tablename, seq_scan, idx_scan,
       n_live_tup, n_dead_tup
FROM pg_stat_user_tables
ORDER BY seq_scan DESC
LIMIT 10;

# 手動(dòng)執(zhí)行 VACUUM（清理死元組，更新統(tǒng)計(jì)信息）
sudo -u postgres vacuumdb --all --analyze
# PostgreSQL 有 autovacuum 自動(dòng)維護(hù)，通常不需要手動(dòng)執(zhí)行
# 查看 autovacuum 狀態(tài)
sudo -u postgres psql -c "SELECT * FROM pg_stat_bgwriter;"