import os import subprocess import logging import time from pathlib import Path import shutil import json # 配置日志 logging.basicConfig( level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s', handlers=[ logging.StreamHandler(), logging.FileHandler('postgres_service_manager.log') ] ) logger = logging.getLogger(__name__) def get_postgres_service_name(): """获取 PostgreSQL 服务名称""" # #region agent log try: with open(r'd:\code\vue\greater_wms\.cursor\debug.log', 'a', encoding='utf-8') as f: f.write(json.dumps({"sessionId":"debug-session","runId":"run1","hypothesisId":"A","location":"postgresql.py:19","message":"get_postgres_service_name entry","data":{},"timestamp":int(time.time()*1000)})+'\n') except: pass # #endregion service_names = [ "postgresql-x64-16", "postgresql-x64-15", "postgresql-x64-14", "postgresql-x64-13", "postgresql-x64-12", "postgresql" ] # 检查服务状态 for service in service_names: try: # #region agent log try: with open(r'd:\code\vue\greater_wms\.cursor\debug.log', 'a', encoding='utf-8') as f: f.write(json.dumps({"sessionId":"debug-session","runId":"run1","hypothesisId":"A","location":"postgresql.py:35","message":"checking service","data":{"service":service},"timestamp":int(time.time()*1000)})+'\n') except: pass # #endregion result = subprocess.run( ["sc", "query", service], capture_output=True, text=True, encoding='utf-8', errors='ignore' ) # #region agent log try: with open(r'd:\code\vue\greater_wms\.cursor\debug.log', 'a', encoding='utf-8') as f: f.write(json.dumps({"sessionId":"debug-session","runId":"run1","hypothesisId":"A","location":"postgresql.py:44","message":"sc query result","data":{"service":service,"returncode":result.returncode,"stdout":result.stdout[:200],"stderr":result.stderr[:200] if result.stderr else ""},"timestamp":int(time.time()*1000)})+'\n') except: pass # #endregion if "RUNNING" in result.stdout or "STOPPED" in result.stdout: # #region agent log try: with open(r'd:\code\vue\greater_wms\.cursor\debug.log', 'a', encoding='utf-8') as f: f.write(json.dumps({"sessionId":"debug-session","runId":"run1","hypothesisId":"A","location":"postgresql.py:46","message":"service found","data":{"service":service},"timestamp":int(time.time()*1000)})+'\n') except: pass # #endregion return service except Exception as e: # #region agent log try: with open(r'd:\code\vue\greater_wms\.cursor\debug.log', 'a', encoding='utf-8') as f: f.write(json.dumps({"sessionId":"debug-session","runId":"run1","hypothesisId":"A","location":"postgresql.py:52","message":"exception checking service","data":{"service":service,"error":str(e)},"timestamp":int(time.time()*1000)})+'\n') except: pass # #endregion continue default_service = service_names[0] if service_names else "postgresql" # #region agent log try: with open(r'd:\code\vue\greater_wms\.cursor\debug.log', 'a', encoding='utf-8') as f: f.write(json.dumps({"sessionId":"debug-session","runId":"run1","hypothesisId":"A","location":"postgresql.py:58","message":"returning default service","data":{"default_service":default_service},"timestamp":int(time.time()*1000)})+'\n') except: pass # #endregion return default_service def is_service_running(service_name=None): """检查服务是否正在运行""" # #region agent log try: with open(r'd:\code\vue\greater_wms\.cursor\debug.log', 'a', encoding='utf-8') as f: f.write(json.dumps({"sessionId":"debug-session","runId":"run1","hypothesisId":"D","location":"postgresql.py:47","message":"is_service_running entry","data":{"service_name":service_name},"timestamp":int(time.time()*1000)})+'\n') except: pass # #endregion if not service_name: service_name = get_postgres_service_name() try: result = subprocess.run( ["sc", "query", service_name], capture_output=True, text=True, encoding='utf-8', errors='ignore' ) # #region agent log try: with open(r'd:\code\vue\greater_wms\.cursor\debug.log', 'a', encoding='utf-8') as f: f.write(json.dumps({"sessionId":"debug-session","runId":"run1","hypothesisId":"D","location":"postgresql.py:60","message":"service status check result","data":{"service_name":service_name,"returncode":result.returncode,"stdout":result.stdout[:300],"is_running":"RUNNING" in result.stdout},"timestamp":int(time.time()*1000)})+'\n') except: pass # #endregion return "RUNNING" in result.stdout except Exception as e: # #region agent log try: with open(r'd:\code\vue\greater_wms\.cursor\debug.log', 'a', encoding='utf-8') as f: f.write(json.dumps({"sessionId":"debug-session","runId":"run1","hypothesisId":"D","location":"postgresql.py:66","message":"exception checking service status","data":{"error":str(e)},"timestamp":int(time.time()*1000)})+'\n') except: pass # #endregion logger.error(f"检查服务状态失败: {str(e)}") return False def stop_postgres_service(): """停止 PostgreSQL 服务""" try: service_name = get_postgres_service_name() logger.info(f"尝试停止 PostgreSQL 服务: {service_name}") # 使用 net stop 停止服务 result = subprocess.run( ["net", "stop", service_name], capture_output=True, text=True, encoding='utf-8', errors='ignore' ) if result.returncode == 0: logger.info(f"PostgreSQL 服务 '{service_name}' 已停止") return True # 如果 net stop 失败，尝试 sc stop result = subprocess.run( ["sc", "stop", service_name], capture_output=True, text=True, encoding='utf-8', errors='ignore' ) if result.returncode == 0: logger.info(f"PostgreSQL 服务 '{service_name}' 已停止") return True # 如果服务未停止，尝试强制终止进程 logger.warning("服务未正常停止，尝试终止进程") subprocess.run( ["taskkill", "/F", "/IM", "postgres.exe"], capture_output=True, encoding='utf-8', errors='ignore' ) logger.info("PostgreSQL 进程已终止") return True except Exception as e: logger.error(f"停止 PostgreSQL 服务失败: {str(e)}") return False def start_postgres_service(): """启动 PostgreSQL 服务""" # #region agent log try: with open(r'd:\code\vue\greater_wms\.cursor\debug.log', 'a', encoding='utf-8') as f: f.write(json.dumps({"sessionId":"debug-session","runId":"run1","hypothesisId":"B,C","location":"postgresql.py:112","message":"start_postgres_service entry","data":{},"timestamp":int(time.time()*1000)})+'\n') except: pass # #endregion try: service_name = get_postgres_service_name() # #region agent log try: with open(r'd:\code\vue\greater_wms\.cursor\debug.log', 'a', encoding='utf-8') as f: f.write(json.dumps({"sessionId":"debug-session","runId":"run1","hypothesisId":"A,B","location":"postgresql.py:118","message":"got service name","data":{"service_name":service_name},"timestamp":int(time.time()*1000)})+'\n') except: pass # #endregion logger.info(f"尝试启动 PostgreSQL 服务: {service_name}") # 使用 net start 启动服务 # #region agent log try: with open(r'd:\code\vue\greater_wms\.cursor\debug.log', 'a', encoding='utf-8') as f: f.write(json.dumps({"sessionId":"debug-session","runId":"run1","hypothesisId":"B,C","location":"postgresql.py:123","message":"before net start","data":{"service_name":service_name},"timestamp":int(time.time()*1000)})+'\n') except: pass # #endregion result = subprocess.run( ["net", "start", service_name], capture_output=True, text=True, encoding='utf-8', errors='ignore' ) # #region agent log try: with open(r'd:\code\vue\greater_wms\.cursor\debug.log', 'a', encoding='utf-8') as f: f.write(json.dumps({"sessionId":"debug-session","runId":"run1","hypothesisId":"B,C","location":"postgresql.py:132","message":"net start result","data":{"returncode":result.returncode,"stdout":result.stdout[:500],"stderr":result.stderr[:500] if result.stderr else ""},"timestamp":int(time.time()*1000)})+'\n') except: pass # #endregion if result.returncode == 0: logger.info(f"PostgreSQL 服务 '{service_name}' 已启动") # #region agent log try: with open(r'd:\code\vue\greater_wms\.cursor\debug.log', 'a', encoding='utf-8') as f: f.write(json.dumps({"sessionId":"debug-session","runId":"run1","hypothesisId":"B","location":"postgresql.py:137","message":"net start succeeded","data":{"service_name":service_name},"timestamp":int(time.time()*1000)})+'\n') except: pass # #endregion return True # 如果 net start 失败，尝试 sc start # #region agent log try: with open(r'd:\code\vue\greater_wms\.cursor\debug.log', 'a', encoding='utf-8') as f: f.write(json.dumps({"sessionId":"debug-session","runId":"run1","hypothesisId":"B,C","location":"postgresql.py:142","message":"net start failed, trying sc start","data":{"service_name":service_name},"timestamp":int(time.time()*1000)})+'\n') except: pass # #endregion result = subprocess.run( ["sc", "start", service_name], capture_output=True, text=True, encoding='utf-8', errors='ignore' ) # #region agent log try: with open(r'd:\code\vue\greater_wms\.cursor\debug.log', 'a', encoding='utf-8') as f: f.write(json.dumps({"sessionId":"debug-session","runId":"run1","hypothesisId":"B,C","location":"postgresql.py:150","message":"sc start result","data":{"returncode":result.returncode,"stdout":result.stdout[:500],"stderr":result.stderr[:500] if result.stderr else ""},"timestamp":int(time.time()*1000)})+'\n') except: pass # #endregion if result.returncode == 0: logger.info(f"PostgreSQL 服务 '{service_name}' 启动命令执行成功") # #region agent log try: with open(r'd:\code\vue\greater_wms\.cursor\debug.log', 'a', encoding='utf-8') as f: f.write(json.dumps({"sessionId":"debug-session","runId":"run1","hypothesisId":"B","location":"postgresql.py:238","message":"sc start command succeeded, waiting to verify","data":{"service_name":service_name},"timestamp":int(time.time()*1000)})+'\n') except: pass # #endregion # 等待服务启动完成 time.sleep(5) # 验证服务是否真的在运行 # #region agent log try: with open(r'd:\code\vue\greater_wms\.cursor\debug.log', 'a', encoding='utf-8') as f: f.write(json.dumps({"sessionId":"debug-session","runId":"run1","hypothesisId":"B,E","location":"postgresql.py:246","message":"checking service status after start","data":{"service_name":service_name},"timestamp":int(time.time()*1000)})+'\n') except: pass # #endregion status_result = subprocess.run( ["sc", "query", service_name], capture_output=True, text=True, encoding='utf-8', errors='ignore' ) # #region agent log try: with open(r'd:\code\vue\greater_wms\.cursor\debug.log', 'a', encoding='utf-8') as f: f.write(json.dumps({"sessionId":"debug-session","runId":"run1","hypothesisId":"B,E","location":"postgresql.py:256","message":"service status after start","data":{"service_name":service_name,"returncode":status_result.returncode,"stdout":status_result.stdout[:500],"is_running":"RUNNING" in status_result.stdout},"timestamp":int(time.time()*1000)})+'\n') except: pass # #endregion if "RUNNING" in status_result.stdout: logger.info(f"PostgreSQL 服务 '{service_name}' 已启动") return True else: # 服务启动失败，检查错误日志 logger.warning(f"PostgreSQL 服务 '{service_name}' 启动后立即停止") # #region agent log try: data_dir = None try: data_dir = get_postgres_data_dir() except: pass with open(r'd:\code\vue\greater_wms\.cursor\debug.log', 'a', encoding='utf-8') as f: f.write(json.dumps({"sessionId":"debug-session","runId":"run1","hypothesisId":"E","location":"postgresql.py:268","message":"service stopped after start, checking logs","data":{"service_name":service_name,"data_dir":str(data_dir) if data_dir else None,"status_output":status_result.stdout[:500]},"timestamp":int(time.time()*1000)})+'\n') except: pass # #endregion # 检查数据目录有效性 try: data_dir = get_postgres_data_dir() pg_version_file = data_dir / "PG_VERSION" # #region agent log try: with open(r'd:\code\vue\greater_wms\.cursor\debug.log', 'a', encoding='utf-8') as f: f.write(json.dumps({"sessionId":"debug-session","runId":"run1","hypothesisId":"E","location":"postgresql.py:287","message":"checking data directory validity","data":{"data_dir":str(data_dir),"pg_version_exists":pg_version_file.exists()},"timestamp":int(time.time()*1000)})+'\n') except: pass # #endregion if not pg_version_file.exists(): logger.error(f"数据目录无效：缺少 PG_VERSION 文件。数据目录: {data_dir}") # #region agent log try: with open(r'd:\code\vue\greater_wms\.cursor\debug.log', 'a', encoding='utf-8') as f: f.write(json.dumps({"sessionId":"debug-session","runId":"run1","hypothesisId":"E","location":"postgresql.py:293","message":"PG_VERSION file missing","data":{"data_dir":str(data_dir)},"timestamp":int(time.time()*1000)})+'\n') except: pass # #endregion except Exception as e: # #region agent log try: with open(r'd:\code\vue\greater_wms\.cursor\debug.log', 'a', encoding='utf-8') as f: f.write(json.dumps({"sessionId":"debug-session","runId":"run1","hypothesisId":"E","location":"postgresql.py:299","message":"exception checking data directory","data":{"error":str(e)},"timestamp":int(time.time()*1000)})+'\n') except: pass # #endregion # 尝试读取 Windows 事件日志 try: # #region agent log try: with open(r'd:\code\vue\greater_wms\.cursor\debug.log', 'a', encoding='utf-8') as f: f.write(json.dumps({"sessionId":"debug-session","runId":"run1","hypothesisId":"E","location":"postgresql.py:307","message":"reading windows event log","data":{},"timestamp":int(time.time()*1000)})+'\n') except: pass # #endregion event_log_result = subprocess.run( ["powershell", "-Command", "Get-EventLog -LogName Application -Source 'postgresql*' -Newest 3 -ErrorAction SilentlyContinue | Select-Object -ExpandProperty Message | Out-String"], capture_output=True, text=True, encoding='utf-8', errors='ignore' ) if event_log_result.returncode == 0 and event_log_result.stdout: logger.error(f"Windows 事件日志错误信息:\n{event_log_result.stdout}") # #region agent log try: with open(r'd:\code\vue\greater_wms\.cursor\debug.log', 'a', encoding='utf-8') as f: f.write(json.dumps({"sessionId":"debug-session","runId":"run1","hypothesisId":"E","location":"postgresql.py:318","message":"windows event log retrieved","data":{"event_log":event_log_result.stdout[:2000]},"timestamp":int(time.time()*1000)})+'\n') except: pass # #endregion except Exception as e: # #region agent log try: with open(r'd:\code\vue\greater_wms\.cursor\debug.log', 'a', encoding='utf-8') as f: f.write(json.dumps({"sessionId":"debug-session","runId":"run1","hypothesisId":"E","location":"postgresql.py:324","message":"exception reading event log","data":{"error":str(e)},"timestamp":int(time.time()*1000)})+'\n') except: pass # #endregion return False # #region agent log try: with open(r'd:\code\vue\greater_wms\.cursor\debug.log', 'a', encoding='utf-8') as f: f.write(json.dumps({"sessionId":"debug-session","runId":"run1","hypothesisId":"B,C","location":"postgresql.py:160","message":"both start methods failed","data":{"service_name":service_name,"net_stdout":result.stdout[:500],"net_stderr":result.stderr[:500] if result.stderr else ""},"timestamp":int(time.time()*1000)})+'\n') except: pass # #endregion raise RuntimeError("启动 PostgreSQL 服务失败") except Exception as e: # #region agent log try: with open(r'd:\code\vue\greater_wms\.cursor\debug.log', 'a', encoding='utf-8') as f: f.write(json.dumps({"sessionId":"debug-session","runId":"run1","hypothesisId":"B,C","location":"postgresql.py:165","message":"exception in start_postgres_service","data":{"error":str(e),"error_type":type(e).__name__},"timestamp":int(time.time()*1000)})+'\n') except: pass # #endregion logger.error(f"启动 PostgreSQL 服务失败: {str(e)}") return False def restart_postgres_service(): """重启 PostgreSQL 服务""" try: if stop_postgres_service(): time.sleep(3) # 等待服务完全停止 return start_postgres_service() except Exception as e: logger.error(f"重启 PostgreSQL 服务失败: {str(e)}") return False def get_postgres_data_dir(): """获取 PostgreSQL 数据目录""" # #region agent log try: with open(r'd:\code\vue\greater_wms\.cursor\debug.log', 'a', encoding='utf-8') as f: f.write(json.dumps({"sessionId":"debug-session","runId":"run1","hypothesisId":"E","location":"postgresql.py:160","message":"get_postgres_data_dir entry","data":{},"timestamp":int(time.time()*1000)})+'\n') except: pass # #endregion possible_paths = [ r"D:/app/postgresql/data", r"C:/Program Files/PostgreSQL/15/data", r"C:/Program Files/PostgreSQL/14/data", r"C:/Program Files/PostgreSQL/13/data", r"C:/Program Files/PostgreSQL/12/data", ] env_path = os.environ.get('PGDATA') # #region agent log try: with open(r'd:\code\vue\greater_wms\.cursor\debug.log', 'a', encoding='utf-8') as f: f.write(json.dumps({"sessionId":"debug-session","runId":"run1","hypothesisId":"E","location":"postgresql.py:175","message":"checking PGDATA env","data":{"env_path":env_path,"exists":Path(env_path).exists() if env_path else False},"timestamp":int(time.time()*1000)})+'\n') except: pass # #endregion if env_path and Path(env_path).exists(): return Path(env_path) for path in possible_paths: # #region agent log try: with open(r'd:\code\vue\greater_wms\.cursor\debug.log', 'a', encoding='utf-8') as f: f.write(json.dumps({"sessionId":"debug-session","runId":"run1","hypothesisId":"E","location":"postgresql.py:182","message":"checking data path","data":{"path":path,"exists":Path(path).exists()},"timestamp":int(time.time()*1000)})+'\n') except: pass # #endregion if Path(path).exists(): return Path(path) # #region agent log try: with open(r'd:\code\vue\greater_wms\.cursor\debug.log', 'a', encoding='utf-8') as f: f.write(json.dumps({"sessionId":"debug-session","runId":"run1","hypothesisId":"E","location":"postgresql.py:186","message":"data directory not found","data":{},"timestamp":int(time.time()*1000)})+'\n') except: pass # #endregion raise FileNotFoundError("无法找到 PostgreSQL 数据目录") def fix_postgres_service(): """修复 PostgreSQL 服务启动问题""" try: logger.info("开始修复 PostgreSQL 服务启动问题...") # 1. 停止服务 if not stop_postgres_service(): logger.warning("无法停止服务，尝试强制修复") # 2. 检查数据目录 try: data_dir = get_postgres_data_dir() logger.info(f"找到 PostgreSQL 数据目录: {data_dir}") # 2.1 检查数据目录有效性（关键文件 PG_VERSION） pg_version_file = data_dir / "PG_VERSION" if not pg_version_file.exists(): logger.error(f"数据目录无效：缺少关键文件 PG_VERSION") logger.error(f"数据目录: {data_dir}") logger.error("这通常意味着数据目录被损坏或删除。") logger.error("解决方案：") logger.error("1. 如果您有数据备份，请恢复备份") logger.error("2. 如果没有备份，需要重新初始化数据库（会丢失所有数据）") logger.error(" 重新初始化命令: initdb -D \"{}\"".format(data_dir)) logger.error(" 或者使用 PostgreSQL 安装程序重新配置数据目录") return False # 3. 检查并处理恢复配置文件 recovery_signal = data_dir / "recovery.signal" recovery_done = data_dir / "recovery.done" auto_conf = data_dir / "postgresql.auto.conf" if recovery_signal.exists(): logger.info("发现 recovery.signal 文件，尝试完成恢复过程") if recovery_done.exists(): recovery_done.unlink() recovery_signal.rename(recovery_done) logger.info("已将 recovery.signal 重命名为 recovery.done") # 4. 删除可能引起问题的自动配置文件 if auto_conf.exists(): backup_auto_conf = auto_conf.with_suffix(".auto.conf.bak") shutil.copy(auto_conf, backup_auto_conf) auto_conf.unlink() logger.info("已备份并删除 postgresql.auto.conf 文件") # 5. 检查 postgresql.conf 文件 conf_file = data_dir / "postgresql.conf" if conf_file.exists(): logger.info("检查 postgresql.conf 文件完整性") # 这里可以添加更多的配置文件检查逻辑 except FileNotFoundError as e: logger.warning(f"无法找到数据目录: {str(e)}") # 6. 确保所有 PostgreSQL 进程已终止 subprocess.run( ["taskkill", "/F", "/IM", "postgres.exe"], capture_output=True, encoding='utf-8', errors='ignore' ) # 7. 尝试启动服务 time.sleep(2) if start_postgres_service(): logger.info("PostgreSQL 服务修复成功") return True else: logger.error("修复后仍无法启动服务") return False except Exception as e: logger.error(f"修复过程中发生错误: {str(e)}") return False def get_postgres_bin_dir(): """获取 PostgreSQL bin 目录""" possible_paths = [ r"C:\Program Files\PostgreSQL\16\bin", r"C:\Program Files\PostgreSQL\15\bin", r"C:\Program Files\PostgreSQL\14\bin", r"C:\Program Files\PostgreSQL\13\bin", r"C:\Program Files\PostgreSQL\12\bin", r"D:\app\postgresql\bin", ] for path in possible_paths: if Path(path).exists() and (Path(path) / "initdb.exe").exists(): return Path(path) # 尝试从环境变量获取 pg_bin = os.environ.get('PG_BIN') if pg_bin and Path(pg_bin).exists(): return Path(pg_bin) raise FileNotFoundError("无法找到 PostgreSQL bin 目录") def init_postgres_database(): """初始化 PostgreSQL 数据库""" # #region agent log try: with open(r'd:\code\vue\greater_wms\.cursor\debug.log', 'a', encoding='utf-8') as f: f.write(json.dumps({"sessionId":"debug-session","runId":"run1","hypothesisId":"E","location":"postgresql.py:479","message":"init_postgres_database entry","data":{},"timestamp":int(time.time()*1000)})+'\n') except: pass # #endregion try: # 1. 停止服务 logger.info("正在停止 PostgreSQL 服务...") stop_postgres_service() time.sleep(2) # 2. 获取数据目录 data_dir = get_postgres_data_dir() logger.info(f"数据目录: {data_dir}") # 3. 检查数据目录是否已存在且有效 pg_version_file = data_dir / "PG_VERSION" if pg_version_file.exists(): logger.warning(f"数据目录已存在且包含 PG_VERSION 文件") response = input("数据目录已存在，是否要重新初始化？这将删除所有现有数据！(yes/no): ").strip().lower() if response != 'yes': logger.info("用户取消初始化操作") return False # 备份现有数据目录 backup_dir = data_dir.parent / f"{data_dir.name}_backup_{int(time.time())}" logger.info(f"备份现有数据目录到: {backup_dir}") if data_dir.exists(): try: shutil.move(str(data_dir), str(backup_dir)) logger.info("数据目录已备份") except Exception as e: logger.error(f"备份数据目录失败: {str(e)}") return False # 4. 获取 PostgreSQL bin 目录 try: bin_dir = get_postgres_bin_dir() logger.info(f"PostgreSQL bin 目录: {bin_dir}") except FileNotFoundError as e: logger.error(f"无法找到 PostgreSQL bin 目录: {str(e)}") logger.error("请确保 PostgreSQL 已正确安装") return False # 5. 确保数据目录存在 data_dir.parent.mkdir(parents=True, exist_ok=True) # 6. 执行 initdb initdb_exe = bin_dir / "initdb.exe" logger.info(f"正在初始化数据库，这可能需要几分钟...") logger.info(f"执行命令: {initdb_exe} -D \"{data_dir}\" -U postgres -A trust -E UTF8 --locale=C") # #region agent log try: with open(r'd:\code\vue\greater_wms\.cursor\debug.log', 'a', encoding='utf-8') as f: f.write(json.dumps({"sessionId":"debug-session","runId":"run1","hypothesisId":"E","location":"postgresql.py:530","message":"before initdb","data":{"initdb_exe":str(initdb_exe),"data_dir":str(data_dir)},"timestamp":int(time.time()*1000)})+'\n') except: pass # #endregion result = subprocess.run( [str(initdb_exe), "-D", str(data_dir), "-U", "postgres", "-A", "trust", "-E", "UTF8", "--locale=C"], capture_output=True, text=True, encoding='utf-8', errors='ignore' ) # #region agent log try: with open(r'd:\code\vue\greater_wms\.cursor\debug.log', 'a', encoding='utf-8') as f: f.write(json.dumps({"sessionId":"debug-session","runId":"run1","hypothesisId":"E","location":"postgresql.py:540","message":"initdb result","data":{"returncode":result.returncode,"stdout":result.stdout[:1000],"stderr":result.stderr[:1000] if result.stderr else ""},"timestamp":int(time.time()*1000)})+'\n') except: pass # #endregion if result.returncode == 0: logger.info("数据库初始化成功！") logger.info(result.stdout) # 7. 验证 PG_VERSION 文件 if pg_version_file.exists(): logger.info(f"验证成功：PG_VERSION 文件已创建") # 读取版本号 try: with open(pg_version_file, 'r') as f: version = f.read().strip() logger.info(f"PostgreSQL 版本: {version}") except: pass else: logger.warning("警告：PG_VERSION 文件未找到，但 initdb 返回成功") # 8. 尝试启动服务 logger.info("正在启动 PostgreSQL 服务...") time.sleep(2) if start_postgres_service(): logger.info("PostgreSQL 服务启动成功！") return True else: logger.warning("数据库初始化成功，但服务启动失败") return False else: logger.error("数据库初始化失败") logger.error(f"返回码: {result.returncode}") if result.stderr: logger.error(f"错误信息: {result.stderr}") if result.stdout: logger.error(f"输出信息: {result.stdout}") return False except Exception as e: # #region agent log try: with open(r'd:\code\vue\greater_wms\.cursor\debug.log', 'a', encoding='utf-8') as f: f.write(json.dumps({"sessionId":"debug-session","runId":"run1","hypothesisId":"E","location":"postgresql.py:575","message":"exception in init_postgres_database","data":{"error":str(e),"error_type":type(e).__name__},"timestamp":int(time.time()*1000)})+'\n') except: pass # #endregion logger.error(f"初始化数据库时发生错误: {str(e)}") return False def restore_from_backup(backup_path): """从备份恢复 PostgreSQL 数据库 Args: backup_path: 备份目录路径（包含backup_label的目录） Returns: bool: 恢复是否成功 """ # #region agent log try: with open(r'd:\code\vue\greater_wms\.cursor\debug.log', 'a', encoding='utf-8') as f: f.write(json.dumps({"sessionId":"debug-session","runId":"run1","hypothesisId":"RESTORE","location":"postgresql.py:636","message":"restore_from_backup entry","data":{"backup_path":backup_path},"timestamp":int(time.time()*1000)})+'\n') except: pass # #endregion try: backup_dir = Path(backup_path) # 验证备份目录 if not backup_dir.exists(): logger.error(f"备份目录不存在: {backup_path}") # #region agent log try: with open(r'd:\code\vue\greater_wms\.cursor\debug.log', 'a', encoding='utf-8') as f: f.write(json.dumps({"sessionId":"debug-session","runId":"run1","hypothesisId":"RESTORE","location":"postgresql.py:649","message":"backup directory not exists","data":{"backup_path":backup_path},"timestamp":int(time.time()*1000)})+'\n') except: pass # #endregion return False backup_label_file = backup_dir / "backup_label" if not backup_label_file.exists(): logger.error(f"备份目录无效：缺少 backup_label 文件") logger.error(f"请确保这是有效的 PostgreSQL base backup") # #region agent log try: with open(r'd:\code\vue\greater_wms\.cursor\debug.log', 'a', encoding='utf-8') as f: f.write(json.dumps({"sessionId":"debug-session","runId":"run1","hypothesisId":"RESTORE","location":"postgresql.py:654","message":"backup_label missing","data":{"backup_path":backup_path},"timestamp":int(time.time()*1000)})+'\n') except: pass # #endregion return False # 检查PG_VERSION pg_version_file = backup_dir / "PG_VERSION" if not pg_version_file.exists(): logger.error(f"备份目录无效：缺少 PG_VERSION 文件") # #region agent log try: with open(r'd:\code\vue\greater_wms\.cursor\debug.log', 'a', encoding='utf-8') as f: f.write(json.dumps({"sessionId":"debug-session","runId":"run1","hypothesisId":"RESTORE","location":"postgresql.py:661","message":"PG_VERSION missing","data":{"backup_path":backup_path},"timestamp":int(time.time()*1000)})+'\n') except: pass # #endregion return False # 读取备份的PostgreSQL版本 try: with open(pg_version_file, 'r') as f: backup_version = f.read().strip() logger.info(f"备份的 PostgreSQL 版本: {backup_version}") # #region agent log try: with open(r'd:\code\vue\greater_wms\.cursor\debug.log', 'a', encoding='utf-8') as f: f.write(json.dumps({"sessionId":"debug-session","runId":"run1","hypothesisId":"RESTORE","location":"postgresql.py:669","message":"backup version read","data":{"backup_version":backup_version},"timestamp":int(time.time()*1000)})+'\n') except: pass # #endregion except Exception as e: logger.warning(f"无法读取备份版本: {str(e)}") # 1. 停止服务 logger.info("正在停止 PostgreSQL 服务...") # #region agent log try: with open(r'd:\code\vue\greater_wms\.cursor\debug.log', 'a', encoding='utf-8') as f: f.write(json.dumps({"sessionId":"debug-session","runId":"run1","hypothesisId":"RESTORE","location":"postgresql.py:675","message":"stopping postgres service","data":{},"timestamp":int(time.time()*1000)})+'\n') except: pass # #endregion stop_postgres_service() time.sleep(3) # 2. 获取数据目录 try: data_dir = get_postgres_data_dir() logger.info(f"PostgreSQL 数据目录: {data_dir}") # #region agent log try: with open(r'd:\code\vue\greater_wms\.cursor\debug.log', 'a', encoding='utf-8') as f: f.write(json.dumps({"sessionId":"debug-session","runId":"run1","hypothesisId":"RESTORE","location":"postgresql.py:680","message":"got data directory","data":{"data_dir":str(data_dir)},"timestamp":int(time.time()*1000)})+'\n') except: pass # #endregion except FileNotFoundError as e: logger.error(f"无法找到 PostgreSQL 数据目录: {str(e)}") logger.error("请先初始化数据库或确保PostgreSQL已正确安装") # #region agent log try: with open(r'd:\code\vue\greater_wms\.cursor\debug.log', 'a', encoding='utf-8') as f: f.write(json.dumps({"sessionId":"debug-session","runId":"run1","hypothesisId":"RESTORE","location":"postgresql.py:683","message":"data directory not found","data":{"error":str(e)},"timestamp":int(time.time()*1000)})+'\n') except: pass # #endregion return False # 3. 备份当前数据目录（如果存在且不为空） data_dir_exists = data_dir.exists() data_dir_has_content = data_dir_exists and any(data_dir.iterdir()) if data_dir_exists else False # #region agent log try: with open(r'd:\code\vue\greater_wms\.cursor\debug.log', 'a', encoding='utf-8') as f: f.write(json.dumps({"sessionId":"debug-session","runId":"run1","hypothesisId":"RESTORE","location":"postgresql.py:688","message":"checking existing data directory","data":{"data_dir":str(data_dir),"exists":data_dir_exists,"has_content":data_dir_has_content},"timestamp":int(time.time()*1000)})+'\n') except: pass # #endregion if data_dir_has_content: logger.info("检测到现有数据目录，正在备份...") timestamp = int(time.time()) current_backup_dir = data_dir.parent / f"{data_dir.name}_backup_{timestamp}" try: if data_dir.exists(): shutil.move(str(data_dir), str(current_backup_dir)) logger.info(f"当前数据目录已备份到: {current_backup_dir}") # #region agent log try: with open(r'd:\code\vue\greater_wms\.cursor\debug.log', 'a', encoding='utf-8') as f: f.write(json.dumps({"sessionId":"debug-session","runId":"run1","hypothesisId":"RESTORE","location":"postgresql.py:695","message":"current data directory backed up","data":{"backup_dir":str(current_backup_dir)},"timestamp":int(time.time()*1000)})+'\n') except: pass # #endregion except Exception as e: logger.error(f"备份当前数据目录失败: {str(e)}") logger.error("恢复操作已取消，以保护现有数据") # #region agent log try: with open(r'd:\code\vue\greater_wms\.cursor\debug.log', 'a', encoding='utf-8') as f: f.write(json.dumps({"sessionId":"debug-session","runId":"run1","hypothesisId":"RESTORE","location":"postgresql.py:697","message":"backup current data directory failed","data":{"error":str(e)},"timestamp":int(time.time()*1000)})+'\n') except: pass # #endregion return False # 4. 创建新的数据目录 data_dir.mkdir(parents=True, exist_ok=True) logger.info(f"正在从备份恢复数据到: {data_dir}") # #region agent log try: with open(r'd:\code\vue\greater_wms\.cursor\debug.log', 'a', encoding='utf-8') as f: f.write(json.dumps({"sessionId":"debug-session","runId":"run1","hypothesisId":"RESTORE","location":"postgresql.py:702","message":"starting file copy","data":{"backup_dir":str(backup_dir),"data_dir":str(data_dir)},"timestamp":int(time.time()*1000)})+'\n') except: pass # #endregion # 5. 复制备份文件到数据目录 logger.info("正在复制备份文件...") try: copied_items = [] # 复制所有文件和目录 for item in backup_dir.iterdir(): # 跳过某些不需要的文件 if item.name in ['.', '..']: continue dest = data_dir / item.name if item.is_dir(): if dest.exists(): shutil.rmtree(str(dest)) shutil.copytree(str(item), str(dest)) logger.debug(f"已复制目录: {item.name}") copied_items.append(f"dir:{item.name}") else: shutil.copy2(str(item), str(dest)) logger.debug(f"已复制文件: {item.name}") copied_items.append(f"file:{item.name}") logger.info("备份文件复制完成") # #region agent log try: with open(r'd:\code\vue\greater_wms\.cursor\debug.log', 'a', encoding='utf-8') as f: f.write(json.dumps({"sessionId":"debug-session","runId":"run1","hypothesisId":"RESTORE","location":"postgresql.py:724","message":"file copy completed","data":{"copied_count":len(copied_items),"items":copied_items[:10]},"timestamp":int(time.time()*1000)})+'\n') except: pass # #endregion except Exception as e: logger.error(f"复制备份文件失败: {str(e)}") # #region agent log try: with open(r'd:\code\vue\greater_wms\.cursor\debug.log', 'a', encoding='utf-8') as f: f.write(json.dumps({"sessionId":"debug-session","runId":"run1","hypothesisId":"RESTORE","location":"postgresql.py:726","message":"file copy failed","data":{"error":str(e)},"timestamp":int(time.time()*1000)})+'\n') except: pass # #endregion return False # 6. 验证关键文件 required_files = ['PG_VERSION', 'postgresql.conf', 'pg_hba.conf'] for file_name in required_files: file_path = data_dir / file_name if not file_path.exists(): logger.warning(f"警告：缺少关键文件 {file_name}") # 6.1 清理可能存在的postmaster.pid文件（可能导致权限错误） postmaster_pid = data_dir / "postmaster.pid" if postmaster_pid.exists(): logger.info("发现残留的 postmaster.pid 文件，正在删除...") try: postmaster_pid.unlink() logger.info("已删除 postmaster.pid 文件") # #region agent log try: with open(r'd:\code\vue\greater_wms\.cursor\debug.log', 'a', encoding='utf-8') as f: f.write(json.dumps({"sessionId":"debug-session","runId":"run1","hypothesisId":"RESTORE","location":"postgresql.py:736","message":"removed postmaster.pid","data":{},"timestamp":int(time.time()*1000)})+'\n') except: pass # #endregion except Exception as e: logger.warning(f"无法删除 postmaster.pid 文件: {str(e)}") # #region agent log try: with open(r'd:\code\vue\greater_wms\.cursor\debug.log', 'a', encoding='utf-8') as f: f.write(json.dumps({"sessionId":"debug-session","runId":"run1","hypothesisId":"RESTORE","location":"postgresql.py:740","message":"failed to remove postmaster.pid","data":{"error":str(e)},"timestamp":int(time.time()*1000)})+'\n') except: pass # #endregion # 6.2 设置数据目录权限（确保PostgreSQL服务账户有写入权限） logger.info("正在设置数据目录权限...") try: # 获取PostgreSQL服务账户 service_name = get_postgres_service_name() result = subprocess.run( ["sc", "qc", service_name], capture_output=True, text=True, encoding='utf-8', errors='ignore' ) # 尝试为常见服务账户设置权限 # 通常PostgreSQL服务以NETWORK SERVICE或Local System运行 service_accounts = [ "NT AUTHORITY\\NETWORK SERVICE", "NT AUTHORITY\\SYSTEM", "NT SERVICE\\postgresql-x64-16" ] # 使用icacls设置权限 data_dir_str = str(data_dir) for account in service_accounts: try: # 授予完全控制权限 result = subprocess.run( ["icacls", data_dir_str, "/grant", f"{account}:(OI)(CI)F", "/T"], capture_output=True, text=True, encoding='utf-8', errors='ignore' ) if result.returncode == 0: logger.info(f"已为 {account} 设置数据目录权限") # #region agent log try: with open(r'd:\code\vue\greater_wms\.cursor\debug.log', 'a', encoding='utf-8') as f: f.write(json.dumps({"sessionId":"debug-session","runId":"run1","hypothesisId":"RESTORE","location":"postgresql.py:760","message":"set permissions","data":{"account":account,"data_dir":data_dir_str},"timestamp":int(time.time()*1000)})+'\n') except: pass # #endregion break except Exception as e: logger.debug(f"无法为 {account} 设置权限: {str(e)}") # 也确保当前用户有权限（用于调试） try: import getpass current_user = getpass.getuser() result = subprocess.run( ["icacls", data_dir_str, "/grant", f"{current_user}:(OI)(CI)F", "/T"], capture_output=True, text=True, encoding='utf-8', errors='ignore' ) except: pass except Exception as e: logger.warning(f"设置数据目录权限时出错: {str(e)}") # #region agent log try: with open(r'd:\code\vue\greater_wms\.cursor\debug.log', 'a', encoding='utf-8') as f: f.write(json.dumps({"sessionId":"debug-session","runId":"run1","hypothesisId":"RESTORE","location":"postgresql.py:775","message":"failed to set permissions","data":{"error":str(e)},"timestamp":int(time.time()*1000)})+'\n') except: pass # #endregion # 7. 处理backup_label文件 # PostgreSQL在启动时会自动处理backup_label文件 # 如果存在backup_label，PostgreSQL会进入恢复模式 backup_label = data_dir / "backup_label" if backup_label.exists(): logger.info("检测到 backup_label 文件，PostgreSQL 将在启动时自动进入恢复模式") # 可以创建recovery.signal来强制恢复模式，但通常backup_label就足够了 # 8. 设置正确的文件权限（Windows上通常不需要，但为了兼容性） logger.info("正在验证数据目录完整性...") pg_version_check = data_dir / "PG_VERSION" if not pg_version_check.exists(): logger.error("恢复失败：PG_VERSION 文件未找到") return False # 9. 启动服务 logger.info("正在启动 PostgreSQL 服务...") # #region agent log try: with open(r'd:\code\vue\greater_wms\.cursor\debug.log', 'a', encoding='utf-8') as f: f.write(json.dumps({"sessionId":"debug-session","runId":"run1","hypothesisId":"RESTORE","location":"postgresql.py:752","message":"starting postgres service","data":{},"timestamp":int(time.time()*1000)})+'\n') except: pass # #endregion time.sleep(2) service_started = start_postgres_service() # #region agent log try: with open(r'd:\code\vue\greater_wms\.cursor\debug.log', 'a', encoding='utf-8') as f: f.write(json.dumps({"sessionId":"debug-session","runId":"run1","hypothesisId":"RESTORE","location":"postgresql.py:754","message":"service start result","data":{"started":service_started},"timestamp":int(time.time()*1000)})+'\n') except: pass # #endregion if service_started: logger.info("PostgreSQL 服务启动成功！") logger.info("数据库恢复完成！") # 等待服务完全启动 time.sleep(5) # 验证服务状态 is_running = is_service_running() # #region agent log try: with open(r'd:\code\vue\greater_wms\.cursor\debug.log', 'a', encoding='utf-8') as f: f.write(json.dumps({"sessionId":"debug-session","runId":"run1","hypothesisId":"RESTORE","location":"postgresql.py:762","message":"service status check","data":{"is_running":is_running},"timestamp":int(time.time()*1000)})+'\n') except: pass # #endregion if is_running: logger.info("服务验证：PostgreSQL 正在运行") # #region agent log try: with open(r'd:\code\vue\greater_wms\.cursor\debug.log', 'a', encoding='utf-8') as f: f.write(json.dumps({"sessionId":"debug-session","runId":"run1","hypothesisId":"RESTORE","location":"postgresql.py:764","message":"restore completed successfully","data":{},"timestamp":int(time.time()*1000)})+'\n') except: pass # #endregion return True else: logger.warning("服务启动后立即停止，请检查日志") # #region agent log try: with open(r'd:\code\vue\greater_wms\.cursor\debug.log', 'a', encoding='utf-8') as f: f.write(json.dumps({"sessionId":"debug-session","runId":"run1","hypothesisId":"RESTORE","location":"postgresql.py:766","message":"service stopped after start","data":{},"timestamp":int(time.time()*1000)})+'\n') except: pass # #endregion return False else: logger.error("服务启动失败") # #region agent log try: with open(r'd:\code\vue\greater_wms\.cursor\debug.log', 'a', encoding='utf-8') as f: f.write(json.dumps({"sessionId":"debug-session","runId":"run1","hypothesisId":"RESTORE","location":"postgresql.py:769","message":"service start failed","data":{},"timestamp":int(time.time()*1000)})+'\n') except: pass # #endregion return False except Exception as e: logger.error(f"恢复数据库时发生错误: {str(e)}") import traceback logger.error(traceback.format_exc()) # #region agent log try: with open(r'd:\code\vue\greater_wms\.cursor\debug.log', 'a', encoding='utf-8') as f: f.write(json.dumps({"sessionId":"debug-session","runId":"run1","hypothesisId":"RESTORE","location":"postgresql.py:772","message":"exception in restore","data":{"error":str(e),"error_type":type(e).__name__},"timestamp":int(time.time()*1000)})+'\n') except: pass # #endregion return False def list_backup_directories(backup_base_path=None): """列出可用的备份目录 Args: backup_base_path: 备份基础路径，默认为当前脚本目录下的base_backup Returns: list: 备份目录列表 """ if backup_base_path is None: # 默认使用脚本所在目录的base_backup script_dir = Path(__file__).parent backup_base_path = script_dir / "base_backup" else: backup_base_path = Path(backup_base_path) if not backup_base_path.exists(): return [] backups = [] for item in backup_base_path.iterdir(): if item.is_dir(): backup_label = item / "backup_label" pg_version = item / "PG_VERSION" if backup_label.exists() and pg_version.exists(): # 读取版本信息 try: with open(pg_version, 'r') as f: version = f.read().strip() except: version = "未知" # 读取备份时间 try: with open(backup_label, 'r') as f: label_content = f.read() # 提取时间信息 for line in label_content.split('\n'): if 'START TIME:' in line: time_str = line.split('START TIME:')[1].strip() break else: time_str = "未知时间" except: time_str = "未知时间" backups.append({ 'path': str(item), 'name': item.name, 'version': version, 'time': time_str }) return backups def check_service_status(): """检查服务状态并显示详细信息""" service_name = get_postgres_service_name() is_running = is_service_running(service_name) print(f"服务名称: {service_name}") print(f"运行状态: {'运行中' if is_running else '已停止'}") if is_running: try: # 尝试连接数据库验证服务状态 import psycopg2 try: conn = psycopg2.connect( dbname='postgres', user='postgres', password='abc@1234', host='localhost', port='5432' ) conn.close() print("数据库连接: 正常") except Exception as e: print(f"数据库连接: 异常 ({str(e)})") except ImportError: print("数据库连接: 未安装 psycopg2 库，无法测试连接") return is_running def main(): """主函数：提供命令行界面""" print("=" * 50) print("PostgreSQL 服务管理工具") print("=" * 50) while True: print("\n请选择操作:") print("1. 检查服务状态") print("2. 启动服务") print("3. 停止服务") print("4. 重启服务") print("5. 修复服务启动问题") print("6. 初始化数据库（会删除现有数据！）") print("7. 从备份恢复数据库（会删除现有数据！）") print("8. 退出") choice = input("请输入选项 (1-8): ").strip() if choice == "1": print("\n检查服务状态...") check_service_status() elif choice == "2": print("\n启动服务...") if start_postgres_service(): print("服务启动成功") else: print("服务启动失败，请尝试修复") elif choice == "3": print("\n停止服务...") if stop_postgres_service(): print("服务停止成功") else: print("服务停止失败") elif choice == "4": print("\n重启服务...") if restart_postgres_service(): print("服务重启成功") else: print("服务重启失败") elif choice == "5": print("\n修复服务启动问题...") if fix_postgres_service(): print("服务修复成功") else: print("服务修复失败，请查看日志文件") elif choice == "6": print("\n初始化数据库...") print("警告：这将删除所有现有数据！") confirm = input("确认要继续吗？(yes/no): ").strip().lower() if confirm == 'yes': if init_postgres_database(): print("数据库初始化成功！") else: print("数据库初始化失败，请查看日志文件") else: print("操作已取消") elif choice == "7": print("\n从备份恢复数据库...") print("警告：这将删除所有现有数据并恢复备份！") # 列出可用的备份 backups = list_backup_directories() if not backups: print("未找到可用的备份目录") print("请确保备份目录位于: backup/base_backup/") backup_path = input("或者手动输入备份目录路径: ").strip() if not backup_path: print("操作已取消") continue else: print("\n可用的备份:") for i, backup in enumerate(backups, 1): print(f"{i}. {backup['name']} (PostgreSQL {backup['version']}, {backup['time']})") print(f"{len(backups) + 1}. 手动输入路径") backup_choice = input(f"请选择备份 (1-{len(backups) + 1}): ").strip() try: choice_num = int(backup_choice) if 1 <= choice_num <= len(backups): backup_path = backups[choice_num - 1]['path'] elif choice_num == len(backups) + 1: backup_path = input("请输入备份目录路径: ").strip() else: print("无效选项") continue except ValueError: print("无效输入") continue if not backup_path: print("操作已取消") continue confirm = input("确认要从备份恢复吗？这将覆盖现有数据！(yes/no): ").strip().lower() if confirm == 'yes': if restore_from_backup(backup_path): print("数据库恢复成功！") else: print("数据库恢复失败，请查看日志文件") else: print("操作已取消") elif choice == "8": print("退出程序") break else: print("无效选项，请重新选择") # input("\n按回车键继续...") if __name__ == "__main__": # 直接运行测试 print("正在启动 PostgreSQL 服务管理工具...") main()