postgres的备份和恢复

看到chatgpt说postgres在备份和恢复方面有以下独特优势

1. pg_dump工具
2. 文件系统级的备份
3. 使用pg_basebackup进行热备份
4. PITR（Point-In-Time Recovery）恢复

所以来了解下。

使用pg_dump备份

这种备份方式是最容易理解的。 它备份时生成的一个文本文件，里面是SQL命令。postgres server可以根据备份产生的这个文件，重建数据库。

备份

例如我有如下名为dvdrental的数据库。

    bakeries_db=# \c dvdrental
    You are now connected to database "dvdrental" as user "postgres".
    dvdrental=# \d
                         List of relations
     Schema |            Name            |   Type   |  Owner
    --------+----------------------------+----------+----------
     public | actor                      | table    | postgres
     public | actor_actor_id_seq         | sequence | postgres
     public | actor_info                 | view     | postgres
     public | address                    | table    | postgres

运行pg_dump得到备份文件。

pg_dump -U postgres dvdrental > dvdrental_pg_dump.sql

打开这个本备份文件，里面是一些sql语句。数据也在里面，

恢复

bakeries_db=# CREATE DATABASE dvdrental_from_dump;
CREATE DATABASE

psql -U postgres dvdrental_from_dump < dvdrental_pg_dump.sql

bakeries_db=# \c dvdrental_from_dump;
You are now connected to database "dvdrental_from_dump" as user "postgres".
dvdrental_from_dump=# \d
                     List of relations
 Schema |            Name            |   Type   |  Owner
--------+----------------------------+----------+----------
 public | actor                      | table    | postgres
 public | actor_actor_id_seq         | sequence | postgres
 public | actor_info                 | view     | postgres
 public | address                    | table    | postgres

文件系统备份

从这方法的名字就可以知道备份的原理了。直接把数据文件保存下来。

tar -cf backup.tar /usr/local/pgsql/data

这方法有两个缺点

1. 必须先关闭数据库server，才能备份
2. 无法只备份某个表

使用pg_basebackup热备份

热备份是指在数据库运行期间进行备份，而不需要停止数据库，这对于大型数据库来说非常重要。

pg_basebackup用来创建一个运行中的数据库集群的基础备份。备份不会影响数据库的其它客户端，可以用来进行point-in-time recovery。

pg_basebackup会创建整个数据库集群文件的copy。要进行可选择的备选，需要用其它工具。

point-in-time recovery

PITR恢复是指将数据库恢复到某个时间点的状态。

PITR的原理

它可以通过备份和WAL（Write-Ahead Logging）日志来实现。 postgres的WAL里面记录着每次数数据文件的修改。恢复时，先还原基础备份，然后把WAL里记录的修改重放，即可恢复。重放WAL里的所有条目是没有必要的，在任意一点停止重放，就把数据库恢复到了那一时间点。