使用 Python 备份 Postgresql 数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23732900/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgresql Database Backup Using Python
提问by sharipha
I would like to backup database using Python code. I want to backup some tables of related data. How to backup and how to choose desired tables using "SELECT" statement?
我想使用 Python 代码备份数据库。我想备份一些相关数据的表。如何使用“SELECT”语句备份以及如何选择所需的表？
e.g.
例如
I want to get data from 2014-05-01 to 2014-05-10 of some tables and output this result as .sql extension file
我想获取一些表的 2014-05-01 到 2014-05-10 的数据，并将这个结果输出为 .sql 扩展文件
How can I get this format using python code? If you don't mind, please explain. Thanks.
如何使用 python 代码获取这种格式？如果你不介意，请解释一下。谢谢。
采纳答案by d1ll1nger
Use psycopg2 to establish the data connection. There are quite a few examples in the documentation:
使用 psycopg2 建立数据连接。文档中有不少例子：
Once you have your data source configured, iterate through the results of your "SELECT" statement building a "INSERT INTO" statement via printing the result set to a file. Basically some reverse logic.
配置数据源后，通过将结果集打印到文件来迭代构建“INSERT INTO”语句的“SELECT”语句的结果。基本上是一些反向逻辑。
That way, if the time comes and you need to use your backup file, you simple run the SQL file which inserts the data back in...
这样，如果时间到了并且您需要使用备份文件，您只需运行 SQL 文件，将数据插入回...
Example:
例子：
import psycopg2
import sys
con = None
try:
con = psycopg2.connect(database='local', user='local', password='local',port='1970')
cur = con.cursor()
cur.execute('SELECT x FROM t')
f = open('test.sql', 'w')
for row in cur:
f.write("insert into t values (" + str(row) + ");")
except psycopg2.DatabaseError, e:
print 'Error %s' % e
sys.exit(1)
finally:
if con:
con.close()
Then to restore:
然后恢复：
psql <dbname> <username> < test.sql
Cheers,
干杯，
回答by Mehmet nuri
If your OS is Linux, you can use the code below.
First, you should run apt-get install postgresql.
如果您的操作系统是 Linux，您可以使用下面的代码。首先，您应该运行apt-get install postgresql.
def create_essentials():
yaml_file = open("settings.yaml", 'r')
settings = yaml.load(yaml_file)
db_name = settings["db_name"]
db_user = settings["db_user"]
db_password = settings["db_password"]
db_host = settings["db_host"]
db_port = settings["db_port"]
backup_path = settings["backup_path"]
filename = settings["filename"]
filename = filename + "-" + time.strftime("%Y%m%d") + ".backup"
command_str = str(db_host)+" -p "+str(db_port)+" -d "+db_name+" -U "+db_user
return command_str, backup_path, filename
def backup_database(table_names=None):
command_str,backup_path,filename = create_essentials()
command_str = "pg_dump -h "+command_str
if table_names is not None:
for x in table_names:
command_str = command_str +" -t "+x
command_str = command_str + " -F c -b -v -f '"+backup_path+"/"+filename+"'"
try:
os.system(command_str)
print "Backup completed"
except Exception as e:
print "!!Problem occured!!"
print e
def restore_database(table_names=None):
command_str,backup_path,filename = create_essentials()
command_str = "pg_restore -h "+command_str
if table_names is not None:
for x in table_names:
command_str = command_str +" -t "+x
command_str = command_str + " -v '"+backup_path+"/"+filename+"'"
try:
os.system(command_str)
print "Restore completed"
except Exception as e:
print "!!Problem occured!!"
print e
回答by furins
The first idea that comes to my mind is to dump your tables calling pg_dumpcommand, similar to the approach presented here(but google is plenty of alternatives).
我想到的第一个想法是调用pg_dump命令转储您的表，类似于此处介绍的方法（但 google 有很多替代方法）。
However, since your backup strategy requires you to select precise dates and not only tables, you will probably have to rely on a sequence of queries, and then my advise is to use a library like Psycopg.
但是，由于您的备份策略要求您选择精确的日期而不仅仅是表，您可能不得不依赖于一系列查询，然后我的建议是使用像Psycopg这样的库。
EDIT:
编辑：
I cannot provide a complete example since I don't know:
我无法提供完整的示例，因为我不知道：
- which tables do you want to dump
- what is the precise backup strategy for each table (i.e. the SELECT statement)
- how you want to restore them. By deleting the table and then re-creating it, by overwriting db rows basing on an ID attribute, ...
- 你想转储哪些表
- 每个表的精确备份策略是什么（即SELECT语句）
- 您希望如何恢复它们。通过删除表然后重新创建它，通过覆盖基于 ID 属性的数据库行，...
the following example generates a file that stores the result of a single query.
以下示例生成一个文件，用于存储单个查询的结果。
import psycopg
conn = psycopg2.connect("dbname=test user=postgres") # change this according to your RDBMS configuration
cursor = conn.cursor()
table_name='YOUR_TABLE_HERE' # place your table name here
with open("table_dump.sql") as f:
cursor.execute("SELECT * FROM %s" % (table_name)) # change the query according to your needs
column_names = []
columns_descr = cursor.description
for c in columns_descr:
column_names.append(c[0])
insert_prefix = 'INSERT INTO %s (%s) VALUES ' % (table_name, ', '.join(column_names))
rows = cursor.fetchall()
for row in rows:
row_data = []
for rd in row:
if rd is None:
row_data.append('NULL')
elif isinstance(rd, datetime.datetime):
row_data.append("'%s'" % (rd.strftime('%Y-%m-%d %H:%M:%S') ))
else:
row_data.append(repr(rd))
f.write('%s (%s);\n' % (insert_prefix, ', '.join(row_data))) # this is the text that will be put in the SQL file. You can change it if you wish.
