复制远程的PostgreSQL数据库到第二个远程服务器。

huangapple
go评论111阅读模式
英文:

Copy remote postgres database to second remote server

问题

我目前有一个生产环境和测试环境的数据库，它们位于两个Azure Postgres服务器上。我想每晚备份生产数据库到测试数据库，以便每天早上两者都是相同的。我的数据表具有约束和键，因此我不能只复制数据本身，还必须复制模式，所以简单的pandas df.to_sql无法解决问题。

我的当前计划是运行一个每晚的Azure Functions Python脚本来执行复制操作。我尝试过使用SQLAlchemy，但在正确复制元数据方面遇到了重大问题。
现在我正在尝试使用Postgres的pg_dump和pg_restore/psql命令通过子进程执行以下代码：

  1. def backup_database(location, database, password, username, backup_file):
  2.     # 使用pg_dump命令创建指定数据库的备份
  3.     cmd = [
  4.         'pg_dump',
  5.         '-Fc',
  6.         '-f', backup_file,
  7.         '-h', location,
  8.         '-d', database,
  9.         '-U', username,
  10.         '-p', '5432',
  11.         '-W',
  12.     ]
  13.     subprocess.run(cmd, check=True, input=password.encode())
  15. def clear_database(engine, metadata):
  16.     # 删除数据库中的所有表
  17.     metadata.drop_all(bind=engine, checkfirst=False)
  19. def restore_database(location, database, password, username, backup_file):
  20.     # 使用pg_restore命令将备份还原到数据库
  21.     # cmd = ['pg_restore', '-Fc', '-d', engine.url.database, backup_file]
  22.     cmd = [
  23.         'pg_restore',
  24.         '-Fc',
  25.         '-C',
  26.         '-f', backup_file,
  27.         '-h', location,
  28.         # '-d', database,
  29.         '-U', username,
  30.         '-p', '5432',
  31.         '-W',
  32.     ]
  34.     try:
  35.         subprocess.run(cmd, check=True, capture_output=True, text=True)
  36.         print("备份已还原到测试服务器。")
  37.     except subprocess.CalledProcessError as e:
  38.         print("在还原备份时发生错误：")
  39.         print(e.stdout)  # 打印命令的输出
  40.         print(e.stderr)  # 打印错误消息（如果可用）
  42. # 定义备份文件路径
  43. backup_file = '/pathtofile/backup_file.dump'  # 使用所需的备份文件路径进行更新
  44. backup_file2 = 'backup_file.dump'  # 使用所需的备份文件路径进行更新
  46. # 备份生产数据库
  47. backup_database(input_host, input_database, input_password, input_user, backup_file)
  48. print("已创建生产数据库的备份。")
  50. # 为测试服务器创建元数据对象
  51. output_metadata = MetaData(bind=output_engine)
  53. clear_database(output_engine, output_metadata)
  54. print("已清除测试服务器。")
  56. restore_database(output_host, output_database, output_password, output_user, backup_file2)
  57. print("备份已还原到测试服务器。")

这段代码似乎正在创建一个倒转文件，但无法成功还原到测试数据库。
如果我让这段代码工作，如何在Azure Functions中指定文件路径？这是否是从Azure Functions运行的合适解决方案？
如果不是，请问如何使用SQLAlchemy成功清除测试数据/元数据，然后每晚从生产环境复制数据？

英文:

I currently have a prod and test database that live on 2 servers azure postgres servers. I want to do a nightly backup of the prod database onto test, such that every morning the two are identical. My datatables have contraints and keys, so I can't just copy over the data itself but also the schemas, so a simple pandas df.to_sql won't cover it.

My current plan is to run a nightly Azure Functions python script that does the copying over. I tried sqlalchemy but had significant issues copying over metadata correctly.
Now I am trying to use postgres' pg_dump and pg_restore/psql commands via a subprocess with the following code:

  1. def backup_database(location, database, password, username, backup_file):
  2.     # Use pg_dump command to create a backup of the specified database
  3.     cmd = [
  4.         'pg_dump',
  5.         '-Fc',
  6.         '-f', backup_file,
  7.         '-h', location,
  8.         '-d', database,
  9.         '-U', username,
  10.         '-p', '5432',
  11.         '-W',
  12.     ]
  13.     subprocess.run(cmd, check=True, input=password.encode())
  15. def clear_database(engine, metadata):
  16.     # Drop all tables in the database
  17.     metadata.drop_all(bind=engine, checkfirst=False)
  19. def restore_database(location, database, password, username, backup_file):
  20.     # Use pg_restore command to restore the backup onto the database
  21.     # cmd = ['pg_restore', '-Fc', '-d', engine.url.database, backup_file]
  22.     cmd = [
  23.         'pg_restore',
  24.         '-Fc',
  25.         '-C',
  26.         '-f', backup_file,
  27.         '-h', location,
  28.         #'-d', database,
  29.         '-U', username,
  30.         '-p', '5432',
  31.         '-W',
  32.     ]
  34.     try:
  35.         subprocess.run(cmd, check=True, capture_output=True, text=True)
  36.         print("Backup restored onto the test server.")
  37.     except subprocess.CalledProcessError as e:
  38.         print("Error occurred while restoring the backup:")
  39.         print(e.stdout)  # Print the output from the command
  40.         print(e.stderr)  # Print the error message, if available
  43. # Define backup file path
  44. backup_file = '/pathtofile/backup_file.dump'  # Update with the desired backup file path
  45. backup_file2 = 'backup_file.dump'  # Update with the desired backup file path
  47. # Backup the production database
  48. backup_database(input_host, input_database, input_password, input_user, backup_file)
  49. print("Backup of the production database created.")
  51. # Create metadata object for test server
  52. output_metadata = MetaData(bind=output_engine)
  54. clear_database(output_engine, output_metadata)
  55. print("Test server cleared.")
  57. restore_database(output_host, output_datebase, output_password, output_user, backup_file2)
  58. print("Backup restored onto the test server.")

This code appears to be creating a dump file, but it is not successfully restoring to the test database.
If I get this code to work, how do I specify file paths within Azure Functions, is this a suitable solution to run from Azure Functions?
If not, how to get sqlalchemy to successfully clear test data/metadata, then copy over data from prod every night?

答案1

得分: 0

>我已经参考了MSDOC Psycopg和PostgreSQL。

  1. import psycopg2
  2. src_conn_string = "源连接字符串"
  3. dst_conn_string = "目标连接字符串"
  4. try:
  5.     src_conn = psycopg2.connect(src_conn_string)
  6.     src_cursor = src_conn.cursor()
  7.     print("已连接到源数据库。")
  8.     try:
  9.         dst_conn = psycopg2.connect(dst_conn_string)
  10.         dst_cursor = dst_conn.cursor()
  11.         print("已连接到目标数据库。")
  12.         try:
  13.             src_cursor.execute(
  14.                 "SELECT table_name FROM information_schema.tables WHERE table_schema='public' AND table_type='BASE TABLE'"
  15.             )
  16.             tables = src_cursor.fetchall()
  17.             for table in tables:
  18.                 src_cursor.execute("SELECT * FROM {0}".format(table[0]))
  19.                 rows = src_cursor.fetchall()
  20.                 for row in rows:
  21.                     dst_cursor.execute("INSERT INTO {0} VALUES {1}".format(table[0], row))
  22.                 print("数据成功传输。")
  23.         except psycopg2.Error as e:
  24.             print("传输数据时出错：", e)
  25.         finally:
  26.             dst_conn.commit()
  27.             dst_cursor.close()
  28.             dst_conn.close()
  29.             print("目标数据库连接已关闭。")
  30.     except psycopg2.Error as e:
  31.         print("连接到目标数据库时出错：", e)
  32.     finally:
  33.         src_cursor.close()
  34.         src_conn.close()
  35.         print("源数据库连接已关闭。")
  36. except psycopg2.Error as e:
  37.     print("连接到源数据库时出错：", e)

输出：

复制远程的PostgreSQL数据库到第二个远程服务器。

在Azure中：

源：

复制远程的PostgreSQL数据库到第二个远程服务器。

目标：

复制远程的PostgreSQL数据库到第二个远程服务器。

英文:

>I have referred MSDOC Psycopg and PostgreSQL.

  1. import  psycopg2
  2. src_conn_string = "SourceConnectionString"
  3. dst_conn_string = "DStConnectionString"
  4. try:
  5. src_conn = psycopg2.connect(src_conn_string)
  6. src_cursor = src_conn.cursor()
  7. print("Connected to source database.")
  8. try:
  9. dst_conn = psycopg2.connect(dst_conn_string)
  10. dst_cursor = dst_conn.cursor()
  11. print("Connected to destination database.")
  12. try:
  13. src_cursor.execute(
  14. "SELECT table_name FROM information_schema.tables WHERE table_schema='public' AND table_type='BASE TABLE'"
  15. )
  16. tables = src_cursor.fetchall()
  17. for  table  in  tables:
  18. src_cursor.execute("SELECT * FROM {0}".format(table[0]))
  20. rows = src_cursor.fetchall()
  22. for  row  in  rows:
  23. dst_cursor.execute("INSERT INTO {0} VALUES {1}".format(table[0],  row))
  24. print("Data transferred successfully.")
  25. except  psycopg2.Error  as  e:
  26. print("Error transferring data: ",  e)
  27. finally:
  28. dst_conn.commit()
  29. dst_cursor.close()
  30. dst_conn.close()
  31. print("Destination database connection closed.")
  32. except  psycopg2.Error  as  e:
  33. print("Error connecting to destination database: ",  e)
  34. finally:
  35. src_cursor.close()
  36. src_conn.close()
  37. print("Source database connection closed.")
  38. except  psycopg2.Error  as  e:
  39. print("Error connecting to source database: ",  e)

Output:

复制远程的PostgreSQL数据库到第二个远程服务器。

In Azure:

Source:

复制远程的PostgreSQL数据库到第二个远程服务器。

Destination:

复制远程的PostgreSQL数据库到第二个远程服务器。

huangapple
  • 本文由 发表于 2023年6月1日 21:09:30
  • 转载请务必保留本文链接：https://go.coder-hub.com/76382241.html
  • azure-functions
  • pg-dump
  • pgadmin
  • postgresql
  • python
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定