解决PostgreSQL数据迁移时数据丢失问题：.env配置排查与数据库连接管理

本文旨在帮助开发者解决在使用python和psycopg3进行postgresql数据库迁移时遇到的数据丢失问题。通过分析代码结构和问题描述，重点排查了.env配置文件和数据库连接管理，并提供详细的检查步骤和潜在解决方案，确保数据迁移的稳定性和可靠性。

在进行数据库迁移时，数据丢失是一个严重的问题。根据您提供的信息，当您在新的数据库 “zno_db_norm” 中创建表时，原始数据库 “zno_db” 中的数据会被删除。这通常不是预期行为，并且可能由多种原因引起。最常见的原因之一是配置错误，导致您的代码意外地对错误的数据库执行了删除操作。

诊断与排查

根据您提供的代码，问题很可能出在数据库连接配置上。以下是一些需要仔细检查的关键点：

1. 检查 .env 配置文件

这是最可能导致问题的地方。请务必仔细检查以下两个 .env 文件：

• config/zno.env: 用于连接原始数据库 “zno_db”。
• config/zno_norm.env: 用于连接新的数据库 “zno_db_norm”。

确认这两个文件中的以下变量是否正确且指向不同的数据库：

• DB_USERNAME: 数据库用户名
• DB_PASSWORD: 数据库密码
• DB_DATABASE: 数据库名称 – 确保 zno.env 指向 “zno_db”，zno_norm.env 指向 “zno_db_norm”。
• DB_HOST: 数据库主机地址
• DB_PORT: 数据库端口

特别注意： 即使文件名不同，也要确保 DB_DATABASE 变量的值确实指向不同的数据库。这是最容易出错的地方。

2. connect.py 中的连接逻辑

您的 connect.py 文件中的 get_conn 函数负责建立数据库连接。请确保该函数能够正确地从 .env 文件中读取配置，并且连接参数被正确传递给 psycopg.connect 函数。

import psycopg import time from dotenv import load_dotenv import os   def get_conn(envpath="config/zno.env"):     load_dotenv(envpath)      username = os.getenv("DB_USERNAME")     password = os.getenv("DB_PASSWORD")     database = os.getenv("DB_DATABASE")     host = os.getenv("DB_HOST")     port = os.getenv("DB_PORT")      try:         conn = psycopg.connect(user=username, password=password, dbname=database, host=host, port=port)         return conn     except Exception as e:  # 捕获更广泛的异常         print(f"连接失败: {e}")  # 打印异常信息         print("Reconnecting...")         time.sleep(5)         return get_conn() # 递归调用，确保最终返回连接 

改进建议：

• 在 try…except 块中捕获更广泛的异常 Exception，并打印异常信息，以便更好地诊断连接问题。
• 确保 get_conn() 函数在连接失败时最终返回一个连接对象，避免程序无限循环。

3. table.py 中的 SQL 执行

table.py 文件中的 table 函数负责读取 SQL 文件并执行。请确保传递给 get_conn 函数的 envpath 参数是正确的，以便连接到正确的数据库。

from connect import get_conn  def table(filepath, envpath, message):     try:         conn = get_conn(envpath)         cur = conn.cursor()         print(filepath, envpath)         #cur.execute()         with open(filepath, "r") as sql_file:             sql_code = sql_file.read()             cur.execute(sql_code)           conn.commit()         cur.close()         conn.close()         print(message)     except Exception as e: # 捕获更广泛的异常         print(f"执行SQL失败: {e}")  # 打印异常信息         table(filepath, envpath, message)

改进建议：

• 在 try…except 块中捕获更广泛的异常 Exception，并打印异常信息，以便更好地诊断 SQL 执行问题。

4. main.py 中的调用顺序和参数

在 main.py 文件中，您按照特定的顺序调用 table 函数。请确保每次调用都传递了正确的 envpath 参数，以便连接到正确的数据库。

from table import table from insert import insert_data, migrate_data from result import get_result    if __name__ == "__main__":     table("sql/1_CREATE_TABLE.sql", "config/zno.env", "Create table znon")     insert_data()     get_result()      table("sql/2_NORMAL_TABLE.sql", "config/zno.env", "Norming tablesn")     table("sql/3_MIGRATION.sql", "config/zno.env", "Migrationsn")     table("sql/2_NORMAL_TABLE.sql", "config/zno_norm.env", "Creating second bdn")

重点： 请特别注意最后一行 table(“sql/2_NORMAL_TABLE.sql”, “config/zno_norm.env”, “Creating second bdn”)，确保它确实使用了 zno_norm.env 文件。

5. SQL 脚本中的 DROP TABLE 语句

2_NORMAL_TABLE.sql 脚本中包含 DROP TABLE IF EXISTS 语句。这可能是导致数据丢失的原因之一，特别是如果连接到了错误的数据库。

DROP TABLE IF EXISTS educate_organisation, participants, loc, test, participants_eo;  CREATE TABLE loc (     locid SERIAL PRIMARY KEY,     locname varchar,     locregname varchar,     locareaname varchar,     loctername varchar,     UNIQUE(locname, locregname, loctername) );  -- ... 其他 CREATE TABLE 语句 ...

重要： 确保此脚本仅在目标数据库 “zno_db_norm” 中执行，并且在执行之前已经正确连接到该数据库。

解决方案

1. 仔细检查 .env 文件： 这是最重要的一步。确保 zno.env 和 zno_norm.env 文件中的 DB_DATABASE 变量指向不同的数据库。
2. 添加日志记录： 在 connect.py 和 table.py 文件中添加日志记录，以便跟踪连接和 SQL 执行过程。例如，在 get_conn 函数中打印连接参数，在 table 函数中打印 SQL 代码。
3. 逐步执行代码： 使用调试器逐步执行 main.py 文件，观察每次 table 函数调用时连接到的数据库。
4. 备份数据： 在执行任何可能导致数据丢失的操作之前，务必备份您的数据。
5. 避免在生产环境中使用 DROP TABLE： 在生产环境中，应尽量避免使用 DROP TABLE 语句。如果需要清理表，可以考虑使用 TRUNCATE TABLE 语句，该语句会删除表中的所有数据，但保留表结构。

总结

数据库迁移是一个复杂的过程，需要仔细的规划和执行。通过仔细检查配置文件、连接逻辑和 SQL 脚本，您可以避免数据丢失和其他潜在问题。在进行任何更改之前，务必备份您的数据，并使用调试器逐步执行代码，以便更好地了解代码的行为。希望本文能够帮助您解决问题，并成功完成数据库迁移。