参考文章:PostgreSQL备份和恢复_没刮胡子的程序员专栏-CSDN博客_postgresql 备份

Postgresql的三种备份方式_江天水一泓的博客-CSDN博客_postgresql 备份

PostgreSQL的备份与恢复 - 知乎

pg_dumpall - PostgreSQL 中文文档 - 文江博客

一、使用postgresql命令进行备份与恢复

Postgresql的常见备份方式有以下三种:

  1. 文件系统级别的冷备份。
    1. 这种备份方式需要关闭数据库,然后拷贝数据文件的完整目录。恢复数据库时,只需将数据目录复制到原来的位置。该方式实际工作中很少使用。
  2. SQL转储。
    • 用到pg_dump和pg_dumpall两个备份工具,这两个工具不会产生文件系统级别的备份,并且不能用于连续归档方案。尽管如此,在很多需要高可靠性的情况下,它是首选的备份技术。
  3. 连续归档

该方式的策略是把一个文件系统级别的全量备份和WAL(预写式日志)级别的增量备份结合起来。当需要恢复时,先恢复文件系统级别的备份,然后重放备份的WAL文件,把系统恢复到之前的某个状态。

在任何时间,PostgreSQL在数据集簇目录的pg_wal/子目录下都保持有一个预写式日志(WAL)。这个日志存在的目的是为了保证崩溃后的安全:如果系统崩溃,可以“重放”从最后一次检查点以来的日志项来恢复数据库的一致性。

这种备份有显著的优点:

  •   不需要一个完美的一致的文件系统备份作为开始点。备份中的任何内部不一致性将通过日志重放来修正。
  • 可以结合一个无穷长的WAL文件序列用于重放,可以通过简单地归档WAL文件来达到连续备份。
  • 不需要重放WAL项一直到最后。可以在任何点停止重放,并使数据库恢复到当时的一致状态。
  • 可以连续地将一系列WAL文件输送给另一台已经载入了相同基础备份文件的机器,得到一个实时的热备份系统。

1.1 SQL转储

1.1.1 pg_dump命令

  • pg_dump是一个普通的PostgreSQL客户端应用,可以访问该数据库的远端主机上进行备份工作。
  • pg_dump每次只转储一个数据库,而且它不会转储关于角色或表空间(因为它们是集簇范围的)的信息。

a.转储

需要先登陆数据库su - postgres

# 本机
pg_dump dbname > outfile

# 远程
pg_dump -h 1.1.1.1 -p 1234 dbname > dbname.bak

# 实时自动备份数据库
   a. 登录数据库
        su - postgres
   b.创建备份目录
        mkdir -p ~/dbbackups
   c.创建定时任务
        crontab -e
    编辑文件:
        0 0 * * 0 pg_dump -U postgres laozuo> ~/dbbackups/laozuo.org.bak

b.恢复 

# infile为pg_dump命令的输出文件,命令不会创建数据库dbname,
# 在执行psql前需要先从template0创建dbname(
# 例如,用命令createdb -T template0 dbname)。
psql dbname < infile


# ON_ERROR_STOP的作用: 遇到一个SQL错误后让psql退出
psql --set ON_ERROR_STOP=on dbname < infile

c.转储恢复一体化 

# pg_dump和psql使用管道结合,直接从一个服务器转储一个数据库到另一个服务器
pg_dump -h host1 dbname | psql -h host2 dbname

1.1.2 pg_dump命令处理大型数据库

对于非常大型的数据库,可以将将split配合其他两种方法(gizip,并行度)之一进行使用。

# 处理大型数据库时,可以压缩存储
    pg_dump dbname | gzip > filename.gz
    
    # 此时的恢复:
        gunzip -c filename.gz | psql dbname
        # 或者
        cat filename.gz | gunzip | psql dbname

# 大型数据库,分割存储
    # 让每一块的大小为1兆字节
        pg_dump dbname | split -b 1m - filename
   
    # 恢复
        cat filename* | psql dbname

# 自定义转储格式
    # 如果PostgreSQL所在的系统上安装了zlib压缩库,
    # 自定义转储格式将在写出数据到输出文件时对其压缩。这
    # 将产生和使用gzip时差不多大小的转储文件,
    # 但是这种方式的一个优势是其中的表可以被有选择地恢复。
    pg_dump -Fc dbname > filename

    # 恢复
    # 自定义格式的转储不是psql的脚本,只能通过pg_restore恢复
    pg_restore -d dbname filename

# 使用并行模式转储
    # 使用-j参数控制并行度,并行转储只支持“目录”归档格式
    pg_dump -j num -F d -f out.dir dbname

#目录格式备份:
pg_dump -h localhost -p 5432 -U someuser -F d -f /somepath/a_directory mydb

# 从9.3版本开始支持并行备份选项--jobs (-j),
# 此选项只有在按目录格式进行备份时才会生效,每个写线程只负责写一个单独的文件,
# 因此一定是输出结果为多个独立的文件时才可以并行。
pg_dump -h localhost -p 5432 -U someuser -j 3 -Fd -f /somepath/a_directory mydb

 1.1.3 pg_dumpall命令

  • pg_dumpall备份一个给定集簇中的每一个数据库,并且也保留了集簇范围的数据,如角色和表空间定义。
  • pg_dump只备份数据库集群中的某个数据库的数据,它不会导出角色和表空间相关的信息,因为这些信息是整个数据库集群共用的,不属于某个单独的数据库pg_dumpall,对集簇中的每个数据库调用pg_dump来完成该工作,还会还转储对所有数据库公用的全局对象(pg_dump不保存这些对象)。
  • pg_dumpall工作时会发出命令重新创建角色、表空间和空数据库,接着为每一个数据库pg_dump。这意味着每个数据库自身是一致的,但是不同数据库的快照并不同步。

  • 集簇范围的数据可以使用pg_dumpall–globals-only选项来单独转储。

建议 :

  • 建议每天对角色和表空间定义等全局对象进行备份,但不建议每天使用pg_dumpall来备份全库数据,因为pg_dumpall仅支持导出为SQL文本格式,而使用这种庞大的SQL文本备份来进行全库级别的数据库恢复时及其耗时的,所以一般只建议使用pg_dumpall来备份全局对象而非全库数据
  • pg_dumpall可实现仅备份角色和表空间定义
    • pg_dumpall -h localhost -U postgres --port=5432 -f myglobals.sql --globals-only
  • 如果仅需备份角色定义而无需备份表空间,添加--roles-only选项:
    • pg_dumpall -h localhost -U postgres --port=5432 -f myroles.sql --roles-only 

选项 :

参考:pg_dumpall - PostgreSQL 中文文档 - 文江博客

 a.转储

需要先登陆数据库su - postgres

pg_dumpall > outfile

 b.恢复 

psql -f infile -d postgres
psql -f /opt/pgsql-9.4.4/data/all.dump -d postgres

 1.2 连续归档

Postgresql 9.6.5 文件级备份,全量备份,增量备份及恢复_风情客家__的博客-CSDN博客

二、客户端操作数据库的备份与迁移

Postgresql数据库迁移分两类:

  1. 同用户名、同schema迁移;可以使用pg数据库备份、还原功能。
  2. 不同用户名、不同schema迁移

对于第二用迁移相对比较复杂,pg数据库访问时schema时,按照“$user”,public的顺序进行。

可通过sql shell输入命令查看:

#SHOW search_path;

也可通过postgresql.config文件进行查看。文件地址:

“安装目录“/data/postgresql.conf 

查看数据库schema访问顺序

因此在数据恢复之后要进行schema的访问顺序设置,通过命令行:

#ALTER USER test(用户名) SET search_path to test(schema名称),public

具体过程:

2.1 同用户名同schema数据库恢复:

2.1.1.备份数据库

数据库右击选择备份,选择文件地址、输入文件名称,点击备份。

备份数据库

设置数据库文件地址、文件名

结果进程返回0,说明备份成功,点击完成即可。

备份成功图

2.2.2.恢复数据库

新建数据库,名字根据自己的需求命名,所有者要和备份数据库一致。

右击数据库,选择恢复。

选择备份成功的数据库文件。

点击恢复。

设置数据库名称

恢复数据库

恢复文件选择

恢复进程为0,说明恢复成功。

恢复成功图

2.2 数据库表相同不同用户不同schema恢复:

将数据库用户名为test,schema为test的数据库,转到数据库用户名为test1,schema为test1的数据库中。

2.2.1. 生成sql文件。

生成sql文件,运行成功如图下

2.2.2. 新建用户及数据库和schema

2.2.3. 修改sql文件,将文件中的schema名称test,替换成test1。

2.2.4. 恢复数据schema,选择数据库》test1,右击选择运行sql文件

2.2.5. 选择sql文件

2.2.6. 运行成功

2.2.7. 设置schema访问顺序,在查询窗口运行下面语句

#ALTER USER zjch SET search_path to test1,public
Logo

为开发者提供学习成长、分享交流、生态实践、资源工具等服务,帮助开发者快速成长。

更多推荐