Postgres 数据库的备份与恢复记录

forward_huan

已于 2023-03-04 22:31:55 修改

阅读量666

点赞数

分类专栏： Python 数据库文章标签：数据库 Python 备份恢复

于 2023-03-04 22:30:51 首次发布

本文链接：https://blog.csdn.net/forward_huan/article/details/129337825

版权

Python 同时被 2 个专栏收录

49 篇文章 9 订阅

订阅专栏

数据库

7 篇文章 0 订阅

订阅专栏

备份数据库

使用Postgres sql自带的pg_dump.exe进行数据库备份

使用帮助

C:\Users\pc>pg_dump --help
pg_dump 把一个数据库转储为纯文本文件或者是其它格式.

用法:
  pg_dump [选项]... [数据库名字]

一般选项:
  -f, --file=FILENAME          输出文件或目录名
  -F, --format=c|d|t|p         输出文件格式 (定制, 目录, tar)
                               明文 (默认值))
  -j, --jobs=NUM               执行多个并行任务进行备份转储工作
  -v, --verbose                详细模式
  -V, --version                输出版本信息，然后退出
  -Z, --compress=0-9           被压缩格式的压缩级别
  --lock-wait-timeout=TIMEOUT  在等待表锁超时后操作失败
  --no-sync                    do not wait for changes to be written safely to disk
  -?, --help                   显示此帮助, 然后退出

控制输出内容选项:
  -a, --data-only              只转储数据,不包括模式
  -b, --blobs                  在转储中包括大对象
  -B, --no-blobs               exclude large objects in dump
  -c, --clean                  在重新创建之前，先清除（删除）数据库对象
  -C, --create                 在转储中包括命令,以便创建数据库
  -E, --encoding=ENCODING      转储以ENCODING形式编码的数据
  -n, --schema=SCHEMA          只转储指定名称的模式
  -N, --exclude-schema=SCHEMA  不转储已命名的模式
  -o, --oids                   在转储中包括 OID
  -O, --no-owner               在明文格式中, 忽略恢复对象所属者

  -s, --schema-only            只转储模式, 不包括数据
  -S, --superuser=NAME         在明文格式中使用指定的超级用户名
  -t, --table=TABLE            只转储指定名称的表
  -T, --exclude-table=TABLE    不转储指定名称的表
  -x, --no-privileges          不要转储权限 (grant/revoke)
  --binary-upgrade             只能由升级工具使用
  --column-inserts             以带有列名的INSERT命令形式转储数据
  --disable-dollar-quoting     取消美元 (符号) 引号, 使用 SQL 标准引号
  --disable-triggers           在只恢复数据的过程中禁用触发器
  --enable-row-security        启用行安全性（只转储用户能够访问的内容）
  --exclude-table-data=TABLE   不转储指定名称的表中的数据
  --if-exists              当删除对象时使用IF EXISTS
  --inserts                    以INSERT命令，而不是COPY命令的形式转储数据
  --no-publications            do not dump publications
  --no-security-labels         不转储安全标签的分配
  --no-subscriptions           do not dump subscriptions
  --no-synchronized-snapshots  在并行工作集中不使用同步快照
  --no-tablespaces             不转储表空间分配信息
  --no-unlogged-table-data     不转储没有日志的表数据
  --quote-all-identifiers      所有标识符加引号，即使不是关键字
  --section=SECTION            备份命名的节 (数据前, 数据, 及 数据后)
  --serializable-deferrable   等到备份可以无异常运行
  --snapshot=SNAPSHOT          为转储使用给定的快照
  --strict-names               要求每个表和/或schema包括模式以匹配至少一个实体
  --use-set-session-authorization
                               使用 SESSION AUTHORIZATION 命令代替
                ALTER OWNER 命令来设置所有权

联接选项:
  -d, --dbname=DBNAME       对数据库 DBNAME备份
  -h, --host=主机名        数据库服务器的主机名或套接字目录
  -p, --port=端口号        数据库服务器的端口号
  -U, --username=名字      以指定的数据库用户联接
  -w, --no-password        永远不提示输入口令
  -W, --password           强制口令提示 (自动)
  --role=ROLENAME          在转储前运行SET ROLE

如果没有提供数据库名字, 那么使用 PGDATABASE 环境变量
的数值.

报告错误至 <pgsql-bugs@postgresql.org>.

使用示例

pg_dump.exe --file "F:\\test_database.backup" --host "localhost" --port "5432" --username "postgres" --no-password --verbose --format=c --blobs "test"

在这里插入图片描述

此时发现no password supplied，这时需要配置Postgres的默认密码文件，点击这里查看如何配置.

配置完成以后再次运行，就可以看到正常开始备份了
在这里插入图片描述

Python脚本

import subprocess


def backup(save_path, host, user_name, db_name, port=5432):
    cmd = f'pg_dump.exe --file "{save_path}"  --host {host} --port {port} --username {user_name} ' \
          f"--no-password --format=c --blobs {db_name}"
    print(cmd)
    proc = subprocess.Popen(cmd, stderr=subprocess.PIPE, stdout=subprocess.PIPE, shell=False)
    proc.wait()
    result = f'{proc.stderr.read().decode("gbk")}'
    if result:
        print(result)


if __name__ == '__main__':
    backup(r"F:\\test_database.backup", "localhost", "postgres", "test")

恢复数据库

使用Postgres sql自带的pg_restore.exe进行数据库恢复

使用帮助

C:\Users\pc>pg_restore.exe --help
pg_restore 从一个归档中恢复一个由 pg_dump 创建的 PostgreSQL 数据库.

用法:
  pg_restore [选项]... [文件名]

一般选项:
  -d, --dbname=名字        连接数据库名字
  -f, --file=FILENAME      output file name (- for stdout)
  -F, --format=c|d|t       备份文件格式(应该自动进行)
  -l, --list               打印归档文件的 TOC 概述
  -v, --verbose            详细模式
  -V, --version            输出版本信息, 然后退出
  -?, --help               显示此帮助, 然后退出

恢复控制选项:
  -a, --data-only             只恢复数据, 不包括模式
  -c, --clean                  在重新创建之前，先清除（删除）数据库对象
  -C, --create                 创建目标数据库
  -e, --exit-on-error          发生错误退出, 默认为继续
  -I, --index=NAME             恢复指定名称的索引
  -j, --jobs=NUM               执行多个并行任务进行恢复工作
  -L, --use-list=FILENAME      从这个文件中使用指定的内容表排序
                               输出
  -n, --schema=NAME            在这个模式中只恢复对象
  -N, --exclude-schema=NAME    do not restore objects in this schema
  -O, --no-owner               不恢复对象所属者
  -P, --function=NAME(args)    恢复指定名字的函数
  -s, --schema-only           只恢复模式, 不包括数据
  -S, --superuser=NAME         使用指定的超级用户来禁用触发器
  -t, --table=NAME             restore named relation (table, view, etc.)
  -T, --trigger=NAME          恢复指定名字的触发器
  -x, --no-privileges          跳过处理权限的恢复 (grant/revoke)
  -1, --single-transaction     作为单个事务恢复
  --disable-triggers           在只恢复数据的过程中禁用触发器
  --enable-row-security        启用行安全性
  --if-exists              当删除对象时使用IF EXISTS
  --no-data-for-failed-tables  对那些无法创建的表不进行
                               数据恢复
  --no-publications            do not restore publications
  --no-security-labels         不恢复安全标签信息
  --no-subscriptions           do not restore subscriptions
  --no-tablespaces             不恢复表空间的分配信息
  --section=SECTION            恢复命名节 (数据前、数据及数据后)
  --strict-names               要求每个表和/或schema包括模式以匹配至少一个实体
  --use-set-session-authorization
                               使用 SESSION AUTHORIZATION 命令代替
                ALTER OWNER 命令来设置所有权

联接选项:
  -h, --host=主机名        数据库服务器的主机名或套接字目录
  -p, --port=端口号        数据库服务器的端口号
  -U, --username=名字      以指定的数据库用户联接
  -w, --no-password        永远不提示输入口令
  -W, --password           强制口令提示 (自动)
  --role=ROLENAME          在恢复前执行SET ROLE操作

The options -I, -n, -N, -P, -t, -T, and --section can be combined and specified
multiple times to select multiple objects.

如果没有提供输入文件名, 则使用标准输入.

报告错误至 <pgsql-bugs@postgresql.org>.

使用示例

pg_restore.exe --host "localhost" --port "5432" --username "postgres" --no-password --dbname "test"  "F:\\test_database.backup"

在这里插入图片描述
直接在命令行里执行上述恢复数据库命令会报错，原因是该数据库中已经存在备份文件中的数据库表，恢复时造成数据冲突。解决方法是先删除数据库中的所有表，然后再重新恢复。

# 登录数据库
psql -U postgres test
# 查看所有表
\d
# 删除所有表
drop table student;
...
# 查看剩余表，直到为空
\d
# 退出psql窗体
Ctrl C
# 重新备份
pg_restore.exe --host "localhost" --port "5432" --username "postgres" --no-password --dbname "test"  "F:\\test_database.backup"

在这里插入图片描述

Python脚本

import subprocess
import psycopg2


def delete_all_table(host, user_name, pwd, db_name, port=5432):
    db = f"host={host}  user={user_name} dbname={db_name} port={port} password={pwd}"
    try:
        conn: psycopg2._psycopg.connection = psycopg2.connect(db)
        cursor: psycopg2._psycopg.cursor = conn.cursor()
        cursor.execute(
            "select " + "table_name from information_schema.tables where table_schema='public'")
        table_names = [item[0] for item in cursor.fetchall()]
        for table_name in table_names:
            cursor.execute("drop " + f"table if exists {table_name}")
        conn.commit()
        cursor.close()
        conn.close()
    except Exception as ex:
        print(ex)


def restore(backup_path, host, user_name, db_name, port=5432):
    cmd = f'pg_restore.exe --host {host} --port {port} --username {user_name} --no-password ' \
          f'--dbname {db_name}  "{backup_path}"'
    print(cmd)
    proc = subprocess.Popen(cmd, stderr=subprocess.PIPE, stdout=subprocess.PIPE, shell=False)
    proc.wait()
    result = f'{proc.stderr.read().decode("gbk")}'
    if result:
        print(result)


if __name__ == '__main__':
    delete_all_table("localhost", "postgres", "postgres", "test")
    restore(r"F:\\test_database.backup", "localhost", "postgres", "test")

forward_huan

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
Postgres 数据库的备份与恢复记录

pg_dump.exe --file "F:\\test_database.backup" --host localhost --port 5432 --username postgres --no-password --format=c --blobs testpg_restore.exe --host localhost --port 5432 --username postgres --no-password --dbname test "F:\\test_database.backup"
复制链接

扫一扫