- Yuhan's Blog

0001-01-01 约 1862 字预计阅读 4 分钟

通过SQL文件备份和恢复大型数据库：PostgreSQL

主要通过使用 pg_dump 来备份，不使用增量备份，原因是本次实操的应用场景的数据库在很长一段时间内都不会有任何变化。

当然了，直接复制data文件夹里的数据也是一种备份方法，也是最简单的。

PostgreSQL提供了pg_dump实用程序来简化备份单个数据库的过程。必须以对要备份的数据库具有读取权限的用户身份运行此命令。

备份 PostgreSQL 数据库生成的文件可以有两种，一种是 SQL 文件，一种是二进制文件，二进制文件只能使用 pg_restore 进行恢复。

命令

su - postgres
####### 小数据库的备份
# 备份小数据库可以这么做，但是大数据库，单个文件可能超出操作系统允许操作的单个文件的大小最大值，并且如果想上传到云盘，大文件损坏的风险比较高，我100G的单个文件的数据库的备份数据就损坏了用不了
pg_dump mydb > db.sql
# 恢复
psql -d newdb -f db.sql
####### 大数据库的备份
# 利用操作系统提供的工具，将导出的文件分割成多个小文件
pg_dump mydb | split -b 1G - cluster
# 恢复
cat cluster* | psql newdb
####### pg_dump 关于远程备份的有用的参数
-h host
-p port
-U username

实操

我的PostgreSQL安装在Windows 11上，可以使用PowerShell，但操作系统没有提供split命令，因此我选择使用Windows 11自带的Linux 子系统，为此安装了Ubuntu 20.04。

使用Linux子系统的好处很多，其中一点是自动挂载了Windows系统下的所有硬盘，进行数据操作非常方便，简直是无缝衔接。

因此，需要从Ubuntu连接Windows来进行PostgreSQL的备份。

在Windows子系统的Ubuntu上安装PostgreSQL 13

# Create the file repository configuration:
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

# Import the repository signing key:
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

# Update the package lists:
sudo apt-get update

# Install the latest version of PostgreSQL.
# If you want a specific version, use 'postgresql-12' or similar instead of 'postgresql':
sudo apt-get -y install postgresql-13

通过 pg_dump 备份 ebooks 数据库到多个文件

# 我们已经 cd /mnt/d/backup 文件夹了
pg_dump -h 172.24.48.1 -U yuhan ebooks | split -b 1G - ebooks
##### split 命令解释：分割成1G大小的名为ebooksxx的多个文件
# split [OPTION]... [FILE [PREFIX]]
# Output pieces of FILE to PREFIXaa, PREFIXab, ...; default size is 1000 lines, and default PREFIX is 'x'.
# With no FILE, or when FILE is -, read standard input.
# -b, --bytes=SIZE：put SIZE bytes per output file
# --additional-suffix=SUFFIX：append an additional SUFFIX to file names

# 如果你想把子文件的扩展名更改成.sql的话，即分割成1G大小的名为ebooks-xx.sql的多个文件，可以这样：
pg_dump -h 172.24.48.1 -U yuhan ebooks | split -b 1G --additional-suffix .sql - ebooks-
# 后文为简单起见，运行的是上一条命令，没有增加后缀，请知悉。

关于从 Ubuntu 中访问Windows的方法，微软官方文档有解释，也就是查看cat /etc/resolv.conf 中的nameserver，当然还可以在Windows上使用ipconfig查看能被Ubuntu访问的对应的IP。

关于Ubuntu访问Windows获取其IP，可以参考微软官方文档，也就是在Ubuntu中查看cat /etc/resolv.conf中的nameserver即可。当然，也可以在Windows上查看其与子系统共享的网络的IP地址。

从Windows对应的路径即D盘的backup文件夹中，也可以看到文件正在生成：

如果你运行的命令中有增加后缀，即运行的是这条命令pg_dump -h 172.24.48.1 -U yuhan ebooks | split -b 1G --additional-suffix .sql - ebooks-，那么将生成这样的文件：

这样生成的文件总的加起来可能很大，建议使用 360压缩的批量压缩，可以直接把这一个个 sql 文件压缩成对应的 zip 文件，只需要选中所有 sql 文件，然后右键 360压缩 -> 添加到压缩文件 -> 自定义 -> 压缩每个文件到单独的压缩包 。

在 macOS/Window/Linux 上通过生成的文件进行备份的恢复

首先将生成的所有备份文件复制到主机上面，然后在主机上的PostgreSQL中创建一个新数据库名为ebooks，然后进行恢复：

cat ebooks* | psql -U yuhan -d ebooks

2022.4.29 重新从阿里云盘下载所有压缩包并解压出sql文件，通过这种方式在Windows上成功还原！Windows上的 PowerShell 也可以使用 cat 命令，没有任何毛病，只需要把 psql对应的二进制文件路径加入环境变量。

目前，我采用的备份和恢复方法是直接复制data数据，因为通过移动硬盘数据的传输速率更快。（我当然可以在macOS上运行备份命令进行远程备份，但是我并不想这么做，生成接近300G的文件真的是灾难，我的硬盘才500G）

{
  "version_info": [
    {
      "name": "PostgreSQL",
      "version": "13.4"
    },
    {
      "name": "Ubuntu",
      "version": "20.04"
    },
    {
      "name": "macOS",
      "version": "12.2.1"
    }
  ],
  "related_work": [
    {
      "name": "pg_dump 官方文档",
      "type": "official",
      "url": "https://www.postgresql.org/docs/10/app-pgdump.html"
    },
    {
      "name": "《PostgreSQL 开发指南》第 08 篇 备份与恢复",
      "type": "blog",
      "url": "https://www.modb.pro/db/329670",
      "remark": "关于 PostgreSQL 的备份与恢复，讲解的比较系统"
    },
    {
      "name": "Linux downloads (Ubuntu)  | PostgreSQL APt Repository",
      "type": "official",
      "url": "https://www.postgresql.org/download/linux/ubuntu/"
    },
    {
      "name": "split(1) — Linux manual page",
      "type": "official",
      "url": "https://man7.org/linux/man-pages/man1/split.1.html",
      "remark": "RTM 读一读手册吧，任何时候官方文档都是最好的资料"
    }
  ]
}

目录

命令

实操

在Windows子系统的Ubuntu上安装PostgreSQL 13

通过 pg_dump 备份 ebooks 数据库到多个文件

在 macOS/Window/Linux 上通过生成的文件进行备份的恢复