From 34981c30a78e8bbd7791131059a9210f9928b62c Mon Sep 17 00:00:00 2001
From: 刘光辉 <347230014@qq.com>
Date: 星期四, 17 九月 2026 09:24:09 +0800
Subject: [PATCH] Merge remote-tracking branch 'origin/master' into master
---
docker/postgres/migration/install.sh | 168 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 168 insertions(+), 0 deletions(-)
diff --git a/docker/postgres/migration/install.sh b/docker/postgres/migration/install.sh
new file mode 100644
index 0000000..f22904b
--- /dev/null
+++ b/docker/postgres/migration/install.sh
@@ -0,0 +1,168 @@
+#!/usr/bin/env bash
+# 鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�
+# JNPF PostgreSQL schema 鍒濆鍖栵紙鍏ㄦ柊绌哄簱涓撶敤锛�
+# 1) 寤哄洓搴� jnpf_init / jnpf_flow / jnpf_xxljob / nacos_config
+# 2) 璺戝畼鏂瑰垵濮嬪寲鑴氭湰锛堢粨鏋勶紝鏃犱笟鍔℃暟鎹級
+# 3) 璺戣嚜寤鸿〃 DDL self_built_tables_pg.sql
+# 4) 璺戞暟鎹帴鍙ｄ慨姝� data_interface_pg_fixes.sql锛堟鍒荤┖琛ㄢ啋骞傜瓑 no-op锛屾暟鎹縼绉诲悗鐢� migrate-data.sh 鍐嶆斁涓�娆★級
+# 5) 寤� Nacos 瀛樺偍搴� schema锛坣acos_config_pg.sql锛�
+#
+# 鍓嶇疆锛歝x-postgres 瀹瑰櫒宸茬敱鏍� compose 鎷夎捣锛堝畨鍏ㄥ熀绾块殢鏍� compose锛屾湰鑴氭湰涓嶅啀閮ㄧ讲瀹瑰櫒锛夛細
+# docker volume create postgres_pgdata # 鍏ㄦ柊鏈哄櫒
+# docker compose up -d cx-postgres # 鍦ㄤ粨搴撴牴 / 绂荤嚎浜や粯鍖呮牴鐩綍鎵ц
+#
+# 瀹夊叏瀹堝崼锛氱洰鏍囧簱宸插瓨鍦ㄤ笖鍚〃鏃�**鎷掔粷鎵ц**锛堝畼鏂� init 鍚� DROP TABLE IF EXISTS锛�
+# 閲嶆斁浼氭竻绌烘棦鏈夋暟鎹級锛涚‘闇�閲嶇亴璇锋樉寮� FORCE_RESEED=1銆�
+#
+# 鐢ㄦ硶锛�
+# ./install.sh # 寤哄簱 + 寤鸿〃锛�= schema锛�
+# ./install.sh schema # 鍚屼笂锛堝吋瀹规棫鍙傛暟鍐欐硶锛�
+#
+# 鍙傛暟锛堢幆澧冨彉閲忥紝榛樿=鐜板満鏍囧噯鍊硷級锛�
+# CONTAINER PG 瀹瑰櫒鍚� 榛樿 cx-postgres
+# PGUSER 瓒呯骇鐢ㄦ埛 榛樿 postgres锛坋xec psql 璧板鍣ㄦ湰鍦� trust锛屾棤闇�瀵嗙爜锛�
+# DB_INIT / DB_FLOW / DB_XXLJOB / DB_NACOS 鍥涘簱鍚嶏紙娴嬭瘯鏃跺彲鏀� *_t8 闅旂锛�
+# CREATE_EXTENSIONS 鏄惁寤哄父鐢ㄦ墿灞曪紙1/0锛夛紝榛樿 1锛堥潪鑷村懡锛屽け璐ヤ粎鍛婅锛�
+# FORCE_RESEED 鐩爣搴撻潪绌烘椂浠嶅己鍒堕噸鐏岋紙1/0锛夛紝榛樿 0
+# 鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�
+set -euo pipefail
+
+SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
+SQL_DIR="$SCRIPT_DIR/sql"
+
+CONTAINER="${CONTAINER:-cx-postgres}"
+PGUSER="${PGUSER:-postgres}"
+DB_INIT="${DB_INIT:-jnpf_init}"
+DB_FLOW="${DB_FLOW:-jnpf_flow}"
+DB_XXLJOB="${DB_XXLJOB:-jnpf_xxljob}"
+DB_NACOS="${DB_NACOS:-nacos_config}"
+CREATE_EXTENSIONS="${CREATE_EXTENSIONS:-1}"
+FORCE_RESEED="${FORCE_RESEED:-0}"
+STEP="${1:-schema}"
+
+log() { printf '\033[1;34m[install]\033[0m %s\n' "$*"; }
+warn() { printf '\033[1;33m[install][warn]\033[0m %s\n' "$*"; }
+die() { printf '\033[1;31m[install][err]\033[0m %s\n' "$*" >&2; exit 1; }
+
+# 鍦ㄥ鍣ㄥ唴璺� psql锛堟湰鍦� unix socket锛宲ostgres 瓒呯骇鐢ㄦ埛 trust锛屾棤闇�瀵嗙爜/绔彛锛�
+psql_exec() { docker exec -i "$CONTAINER" psql -U "$PGUSER" "$@"; }
+
+# 绛� PG 杩涚▼灏辩华锛堥娆� initdb 鏁板崄绉掞級銆傛闃舵瀹瑰櫒鍋ュ悍妫�鏌� unhealthy 灞為鏈熲�斺��
+# 瀹冩帰娴嬬殑涓氬姟搴撴鏄湰鑴氭湰瑕佸垱寤虹殑锛宧ealthy 鍙細鍑虹幇鍦ㄦ湰鑴氭湰鎴愬姛涔嬪悗
+for _i in $(seq 1 60); do
+ if docker exec "$CONTAINER" pg_isready -qU "$PGUSER" 2>/dev/null; then break; fi
+ [ "$_i" = 60 ] && { echo "閿欒锛氱瓑寰� PG 灏辩华瓒呮椂锛坉ocker logs $CONTAINER 鏌ョ湅锛�" >&2; exit 1; }
+ sleep 2
+done
+# 鎶婂涓� SQL 鏂囦欢鍠傜粰瀹瑰櫒鍐� psql锛岄亣閿欏嵆鍋�
+run_sql_file() {
+ local db="$1" file="$2"
+ [ -f "$file" ] || die "缂� SQL 鏂囦欢锛�${file}锛坓it 鎷夌爜璺嚎锛氬厛璺� ./assemble-sql-onsite.sh锛涚绾垮寘璺嚎锛氬寘鍐� sql/ 搴斿凡缁勮锛岃仈绯绘瀯寤烘満锛�"
+ log "鈫� [$db] 鎵ц $(basename "$file") ($(du -h "$file" | cut -f1))"
+ psql_exec -d "$db" -v ON_ERROR_STOP=1 -q -f - < "$file"
+}
+db_exists() { psql_exec -tAc "SELECT 1 FROM pg_database WHERE datname='$1'" | grep -q 1; }
+create_db() {
+ local db="$1"
+ if db_exists "$db"; then
+ warn "搴� $db 宸插瓨鍦紝璺宠繃 CREATE DATABASE锛堝箓绛夛級"
+ else
+ log "鍒涘缓搴� $db"
+ psql_exec -d postgres -v ON_ERROR_STOP=1 -c "CREATE DATABASE \"$db\" ENCODING 'UTF8'"
+ fi
+}
+# 鍙暟 BASE TABLE锛堟帓闄� pg_stat_statements 绛夋墿灞曡鍥撅紝璁℃暟涓嶅彈 CREATE_EXTENSIONS 褰卞搷锛�
+table_count() { psql_exec -tAc "SELECT count(*) FROM information_schema.tables WHERE table_schema='public' AND table_type='BASE TABLE'" -d "$1"; }
+# 鍏ㄦ柊 schema 鍩虹嚎锛氬畼鏂� 293 寮犺〃 + 鑷缓 99 寮犺〃锛堝惈 DMS V2.3 鍗佽〃鍙婃枃浠跺璞℃槧灏勮〃锛夈��
+verify_init_table_count() {
+ local init_table_count init_custom_table_count
+ init_table_count="$(table_count "$DB_INIT")"
+ init_custom_table_count=$((init_table_count - 293))
+ if [ "$init_table_count" -ne 392 ] || [ "$init_custom_table_count" -ne 99 ]; then
+ die "琛ㄦ暟鏍稿澶辫触锛�$DB_INIT 鍏� ${init_table_count} 寮� BASE TABLE锛岃嚜寤鸿〃 ${init_custom_table_count} 寮狅紱鏈熸湜鎬绘暟 392 寮犮�佽嚜寤� 99 寮�"
+ fi
+}
+# 闈炵┖瀹堝崼锛歩nit SQL 鍚� DROP TABLE IF EXISTS锛屽宸叉湁鏁版嵁鐨勫簱閲嶆斁=娓呭簱
+guard_empty() {
+ local db="$1" n
+ db_exists "$db" || return 0
+ n="$(table_count "$db")"
+ [ "$n" -eq 0 ] && return 0
+ if [ "$FORCE_RESEED" = "1" ]; then
+ warn "搴� $db 宸叉湁 $n 寮犺〃锛孎ORCE_RESEED=1 鈫� 缁х画閲嶇亴锛堟棦鏈夋暟鎹皢琚� init 鑴氭湰瑕嗙洊/娓呯┖锛�"
+ else
+ die "搴� $db 宸叉湁 $n 寮犺〃鈥斺�旀湰鑴氭湰鍙敤浜庡叏鏂扮┖搴擄紙init 鍚� DROP TABLE锛岄噸鏀句細娓呮暟鎹級銆傜‘闇�閲嶇亴锛欶ORCE_RESEED=1 ./install.sh"
+ fi
+}
+
+# 鈹�鈹� schema锛氬缓搴� + 瀹樻柟 init + 鑷缓琛� + 鏁版嵁鎺ュ彛淇 + Nacos 瀛樺偍搴� 鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�鈹�
+do_schema() {
+ docker inspect "$CONTAINER" >/dev/null 2>&1 \
+ || die "瀹瑰櫒 $CONTAINER 涓嶅湪鈥斺�斿厛鍦ㄤ粨搴撴牴/浜や粯鍖呮牴鐩綍锛歞ocker volume create postgres_pgdata && docker compose up -d cx-postgres"
+
+ log "===== 闈炵┖瀹堝崼 ====="
+ guard_empty "$DB_INIT"; guard_empty "$DB_FLOW"; guard_empty "$DB_XXLJOB"; guard_empty "$DB_NACOS"
+
+ log "===== 寤哄洓搴� ====="
+ create_db "$DB_INIT"; create_db "$DB_FLOW"; create_db "$DB_XXLJOB"; create_db "$DB_NACOS"
+
+ if [ "$CREATE_EXTENSIONS" = "1" ]; then
+ log "寤哄父鐢ㄦ墿灞曪紙闈炶嚧鍛斤級"
+ for ext in pg_stat_statements pg_trgm pgcrypto pg_bigm vector; do
+ psql_exec -d "$DB_INIT" -c "CREATE EXTENSION IF NOT EXISTS \"$ext\"" >/dev/null 2>&1 \
+ && log " ext $ext 鉁�" || warn " ext $ext 璺宠繃锛堥暅鍍忔湭鍚垨鏃犻渶锛�"
+ done
+ fi
+
+ log "===== 瀹樻柟鍒濆鍖栬剼鏈� ====="
+ run_sql_file "$DB_INIT" "$SQL_DIR/jnpf_dbnull_init.sql"
+ run_sql_file "$DB_INIT" "$SQL_DIR/Update_PostgreSQL_1127.sql"
+ run_sql_file "$DB_FLOW" "$SQL_DIR/jnpf_flow_init.sql"
+ run_sql_file "$DB_XXLJOB" "$SQL_DIR/jnpf_xxljob_init.sql"
+
+ log "===== 鑷缓琛� DDL ====="
+ run_sql_file "$DB_INIT" "$SQL_DIR/self_built_tables_pg.sql"
+
+ log "===== 鑷缓琛ㄥ閲忥紙杩佺Щ蹇収鍚庣殑鍒楃骇瀵归綈锛屽箓绛夛級====="
+ run_sql_file "$DB_INIT" "$SQL_DIR/update_lims_huanjing_20260717.sql"
+ run_sql_file "$DB_INIT" "$SQL_DIR/update_lims_sxt_wendingxing_20260717.sql"
+ run_sql_file "$DB_INIT" "$SQL_DIR/update_lims_sxt_wendingxing_20260720.sql"
+ run_sql_file "$DB_INIT" "$SQL_DIR/update_lims_sxt_flow_biz_status_20260719.sql"
+ run_sql_file "$DB_INIT" "$SQL_DIR/update_lims_sxt_quyang_zhongzhi_shenqing_biz_status_20260723.sql"
+ run_sql_file "$DB_INIT" "$SQL_DIR/update_lims_sxt_quyang_zhongzhi_shenqing_qingyandan_link_20260723.sql"
+ run_sql_file "$DB_INIT" "$SQL_DIR/update_lims_sxt_plan_status_20260720.sql"
+ run_sql_file "$DB_INIT" "$SQL_DIR/update_lims_wendingxing_biangeng_20260720.sql"
+ run_sql_file "$DB_INIT" "$SQL_DIR/create_lims_sxt_quyang_jihua_renwu_20260724.sql"
+ run_sql_file "$DB_INIT" "$SQL_DIR/alter_lims_sxt_quyang_jihua_renwu_jianyan_xiang_kuaizhao_20260724.sql"
+ run_sql_file "$DB_INIT" "$SQL_DIR/create_audit_events_ext_20260724.sql"
+ run_sql_file "$DB_INIT" "$SQL_DIR/create_audit_events_20260724.sql"
+ run_sql_file "$DB_INIT" "$SQL_DIR/update_audit_events_title_entry_20260810.sql"
+ run_sql_file "$DB_INIT" "$SQL_DIR/update_audit_events_ingest_merge_20260818.sql"
+ run_sql_file "$DB_INIT" "$SQL_DIR/repair_audit_events_integrity_20260824.sql"
+ run_sql_file "$DB_INIT" "$SQL_DIR/create_dms_permission_v2_2_20260815.sql"
+ run_sql_file "$DB_INIT" "$SQL_DIR/upgrade_dms_permission_v2_2_to_v2_3_20260817.sql"
+ run_sql_file "$DB_INIT" "$SQL_DIR/create_dms_file_objects_20260817.sql"
+ run_sql_file "$DB_INIT" "$SQL_DIR/upgrade_dms_file_properties_20260820.sql"
+ run_sql_file "$DB_INIT" "$SQL_DIR/update_audit_events_target_timeline_index_20260823.sql"
+
+ log "===== 鏁版嵁鎺ュ彛淇锛堢┖琛ㄥ箓绛夛紝鏁版嵁杩佺Щ鍚� migrate-data.sh 鍐嶆斁涓�娆★級====="
+ run_sql_file "$DB_INIT" "$SQL_DIR/data_interface_pg_fixes.sql"
+
+ log "===== Nacos 瀛樺偍搴� schema ====="
+ run_sql_file "$DB_NACOS" "$SQL_DIR/nacos_config_pg.sql"
+
+ log "===== 琛ㄦ暟鏍稿 ====="
+ verify_init_table_count
+ log " $DB_INIT BASE TABLE = $(table_count "$DB_INIT") (鏈熸湜 392锛氬畼鏂�293 + 鑷缓99锛屽惈 DMS V2.3 鍗佽〃鍜屾枃浠跺璞℃槧灏勮〃)"
+ log " $DB_FLOW BASE TABLE = $(table_count "$DB_FLOW") (鏈熸湜 70)"
+ log " $DB_XXLJOB BASE TABLE = $(table_count "$DB_XXLJOB") (鏈熸湜 11)"
+ log " $DB_NACOS BASE TABLE = $(table_count "$DB_NACOS") (鏈熸湜 12)"
+ log "schema 灏辩华銆備笅涓�姝ワ細鍋滄満绐楀彛璺� ./migrate-data.sh 杩佹暟鎹紱Nacos 閰嶇疆鍐呭鎸� docs/archive/server-pg-cutover-checklist.md SW-7 瀵煎叆銆�"
+}
+
+case "$STEP" in
+ schema) do_schema ;;
+ container|all) die "姝ラ '$STEP' 宸插簾寮冿細PG 瀹瑰櫒缁熶竴鐢辨牴 compose 鎷夎捣锛坉ocker compose up -d cx-postgres锛夛紝鏈剼鏈彧鍋� schema" ;;
+ *) die "鏈煡姝ラ '$STEP'锛堜粎鏀寔 schema锛�" ;;
+esac
+log "install.sh [$STEP] 瀹屾垚 鉁�"
--
Gitblit v1.8.0