使用 Python 备份 Postgresql 数据库

Question

我想使用 Python 代码备份数据库。我想备份一些相关数据的表。如何备份以及如何使用“SELECT”语句选择所需的表？

例如

我想获取一些表的2014-05-01到2014-05-10的数据，并将这个结果输出为.sql扩展文件

如何使用 python 代码获取此格式？如果你不介意，请解释一下。谢谢。

原文由 sharipha 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

使用 psycopg2 建立数据连接。文档中有很多示例：

http://initd.org/psycopg/

配置数据源后，通过将结果集打印到文件来迭代构建 INSERT INTO 语句的“SELECT”语句的结果。基本上是一些反向逻辑。

这样，如果时间到了并且您需要使用备份文件，您只需运行将数据插入回…

例子：

 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()

然后恢复：

 psql <dbname> <username> < test.sql

原文由 d1ll1nger 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 2

如果你的操作系统是 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

原文由 Mehmet nuri 发布，翻译遵循 CC BY-SA 4.0 许可协议