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

已于 2023-03-04 22:31:55 修改
阅读量673 收藏 2
点赞数
分类专栏： Python 数据库 文章标签： 数据库 Python 备份 恢复
于 2023-03-04 22:30:51 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/forward_huan/article/details/129337825
版权

备份数据库

使用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
    评论
专栏目录
PostgreSQL数据库备份脚本
11-26
PostgreSQL数据库备份脚本，结合系统计划任务可以实现数据库的自动化备份。密码直接设置于脚本当中。
PostgreSQL的备份与恢复
shiyq的技术小白博客
02-27 522
PostgreSQL 是一种流行的关系型数据库管理系统。在以下情况下，备份和恢复数据库特别重要：出于安全原因，你需要将数据保持备份，以避免数据丢失。你需要在不同的环境中迁移数据库。在生产环境中需要对数据库进行升级或冗余以进行故障转移。下面是备份和恢复数据库的一些常见方法。
Windows下PostgreSQL数据库的备份与恢复
最新发布
weixin_66484088的博客
05-21 942
示例：pg_dump -hlocalhost -U postgres -p 5432 -d mdx -f “E:/mdx_bak.dmp”示例：psql -h localhost -U postgres -d test -f “E:\mdx_bak.dmp”如果想备份数据库，PostgreSQL自带的执行程序pg_dump.exe.在文件目录下，输入cmd，回车。如果要导出大对象需要加上“-b”在文件目录下，输入cmd，回车。-U:这里的“U”，用户名。-f:文件输出的目录和名称。
PostgreSql pg_dump用法
热门推荐
脑子进水养啥鱼？的博客
01-29 1万+
pg_dump [connection-option...] [option...] [dbname] pg_dump 连接选项参数如下: -h host 或 --host=host：指定运行服务器的主机名。 -p port 或 --port=port：监听端口号，默认端口为5432。 -U username 或 --username=username：指定要连接的用户名。 -w 或 --no-password：从不提示密码，该选项常用于后台脚本中。 -W 或 --password：强制 pg_dump
pg_dump实例详解
xrdlqy的专栏
04-02 3201
本文转自：http://blog.chinaunix.net/uid-354915-id-3504632.html 一、pg_dump的用法： 数据库的导入导出是最常用的功能之一，每种数据库都提供有这方面的工具，例如Oracle的exp/imp，Informix的dbexp/dbimp，MySQL的mysqldump，而PostgreSQL提供的对应工具为pg_dump和pg_restore。
PostgreSQL逻辑备份pg_dump使用及其原理解析
Baiyi_destroyer的博客
08-14 3664
PostgreSQL逻辑备份pg_dump使用及其原理解析
pg_dump,pg_dumpall,pg_restore备份、备份恢复工具的相关应用
weixin_73350116的博客
06-11 1830
pg_dump,pg_dumpall在postgresql数据库中的备份方式详解。
PostgreSQL数据库导出命令pg_dump详解
fangguozi的专栏
11-28 1万+
PostgreSQL数据库导出命令pg_dump详解 http://huangro.javaeye.com/blog/366038 pg_dump 是一个将 PostgreSQL 数据库保存到一个脚本或者归档文件中的工具．这个脚本文件的格式是纯文本，它包含许多 SQL 命令， 这些 SQL 命令可以用于重建该数据库并将之恢复到保存成脚本的时候的状态．要恢复这些脚本，使用 psql。 它们甚至可
postgres数据库用户手册
07-19
使用`pg_dump`和`pg_restore`工具进行数据库备份和恢复，支持完整备份、增量备份和差异备份。可以结合`pg_basebackup`进行物理备份。 七、复制与高可用 Postgres支持多种复制模式，如异步复制、流复制和并行复制，...
PG-PROBACKUP 本地+异地备份恢复部署文档
10-05
**PG-PROBACKUP 本地+异地备份恢复部署详解** `PG-PROBACKUP` 是一个功能强大的 PostgreSQL 备份工具，尤其适用于大型数据库的高效备份与恢复。它支持本地和异地备份，提供了多种备份模式，包括全备、增量备份，并...
PostgresDiaries:一组简单SQL脚本，旨在理解使用Postgres数据库的查询
03-18
10. **备份与恢复**：了解如何备份数据库并进行恢复是任何数据库管理员的必备技能。你可能还会发现有关pg_dump和pg_restore命令的示例，这些是Postgres的主要备份工具。 通过PostgresDiaries，你可以逐步学习...
postgresql之pg_dump命令详解 - wling_89的专栏 - 博客频道 - CSDN_NET.mht
05-13
PG导入导出命令详解
mysql语句转postgres的工具
03-04
5. **备份与恢复计划**：在进行迁移之前，务必做好源数据库的备份，并了解如何在PostgreSQL中恢复数据。 综上所述，`MysqlToPostgresUtil`是一个实用的工具，它简化了MySQL到PostgreSQL的迁移过程，通过自动转换DDL...
Postgres_2017象行中国杭州第一期_张文杰（卓刀）_Greenplum备份恢复浅析
03-25
在本讲座"Postgres_2017象行中国杭州第一期_张文杰（卓刀）_Greenplum备份恢复浅析"中，张文杰（卓刀）深入探讨了Greenplum数据库的备份与恢复策略，这对于任何运行大型数据仓库或分析系统的组织来说都是至关重要的...
Windows下PostgreSql数据导出和恢复
似水流年
07-17 1577
在windows下导出数据库的数据 语法：pg_dump [connection-option...] [option...] [dbname] Pg_dump.exe执行程序在PostgreSql数据库安装目录“Bin”目录下，这是在windows下的文件名。 执行过程如下： 1、打开windows下的命令窗口：开始->cmd->安装数据库的目录->进入bin目录； 2、导出命令：pg_dump –hlocalhost –U postgres –p 5432 –d po...
PostgreSQL备份命令pg_dump使用
Tiano__的博客
01-12 2516
是 PostgreSQL 数据库中用于备份数据库的命令行工具。它允许将一个 PostgreSQL 数据库或者其中某些表、模式等导出到单个文件中，并且可以指定不同的导出选项。这将在/test 目录下创建一个名为。这将在当前目录下创建一个名为。这将在当前目录下创建一个名为。中所有的数据和架构信息。的文件，包含了数据库。的文件，包含了数据库。的文件，包含了数据库。
解决PostgreSQL数据库backup和restore报错：’pg_dump.exe file not found‘
weixin_43894266的博客
06-27 5698
PostgreSQL 13数据库建库建表导入数据等操作都运行成功，在使用pgAdmin4中backup和restore等命令时报错： ‘C:\PostgreSQL\13\pgAdmin 4\runtime\pg_dump.exe’ file not found. Please correct the Binary Path in the Preferences dialog 打开File菜单中的Preference，发现PostgreSQL Binary Path一栏中 填入其正确的路径然后保存（为啥程序
pg 备份恢复（四）—— 逻辑备份（pg_dump与copy导入导出）
Hehuyi_In的博客
10-23 6258
常用方法 pg_dumpall：适合导出cluster中所有业务库 pg_dump：适合单个库、schema级、表级导出 copy：适合单表或带条件sql结果导出（可导出为csv或txt格式） 一、pg_dumpall pg_dumpall可以转储cluster里的所有数据库（postgres、template0、template1除外）到一个sql文件，通过对cluster里的每个数据...
【PostgreSQL】Windows下PostgreSQL导入（psql）导出（pg_dump）单表数据-支持大批量
tttzzzqqq2018的博客
10-31 1385
【代码】Windows下PostgreSQL导入（psql）导出（pg_dump）单表数据-支持大批量。
postgresql数据库备份与恢复
05-09
与pg_dump工具类似，需要先创建一个新的数据库，然后使用导入工具恢复备份文件到指定数据库中。 总结 Postgres数据库备份和恢复是保证数据安全、保障系统稳定的重要手段。无论是物理备份还是逻辑备份，都有各自的...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值