PostgreSQL基于预写日志的复制

Love The Way You Lie 2022-08-09 03:17 120阅读 0赞

PostgreSQL是一款优秀的开源关系型数据库，从1989年至今已有22年历史，经过多次转折，如今乃是业界流行度仅次于MySQL的开源数据库系统。比起MySQL，PostgreSQL有更好的索引支持和更稳固的ACID保证，也提供了诸多非常优秀的复制(Replication)解决方案，同时以更自由的开源许可协议发布。在PostgreSQL 9.0之前，PostgreSQL就有了许多的第三方的复制方案，如下表：

<table style="border-spacing:0px; width:593px; margin-bottom:20px; border:1px solid rgb(221,221,221); color:rgb(85,85,85); font-family:'Open Sans','Helvetica Neue',Arial,Verdana,'Hiragino Sans GB',STHeiti,SimSun,sans-serif; font-size:15px; line-height:22px; text-align:justify"> 
 <thead> 
  <tr style="background-color:rgb(250,250,250)"> 
   <th style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">特性</th> 
   <th style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">共享磁盘容错</th> 
   <th style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">文件系统复制</th> 
   <th style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">基于预写日志的可读复制</th> 
   <th style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">基于触发的复制</th> 
   <th style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">基于语句的复制</th> 
   <th style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">异步多主节点复制</th> 
   <th style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">同步多节点主复制</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr style="background-color:rgb(250,250,250)"> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">常见的解决方案</td> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">NAS</td> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">DRBD</td> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">PITR</td> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">Slony</td> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">pgpool-II</td> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">Bucardo</td> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">&nbsp;</td> 
  </tr> 
  <tr> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">通信方法</td> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">shared disk</td> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">disk blocks</td> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">WAL</td> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">table rows</td> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">SQL</td> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">table rows</td> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">table rows and row locks</td> 
  </tr> 
  <tr style="background-color:rgb(250,250,250)"> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">无需特制硬件</td> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">&nbsp;</td> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">•</td> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">•</td> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">•</td> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">•</td> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">•</td> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">•</td> 
  </tr> 
  <tr> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">允许多主服务器</td> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">&nbsp;</td> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">&nbsp;</td> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">&nbsp;</td> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">&nbsp;</td> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">•</td> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">•</td> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">•</td> 
  </tr> 
  <tr style="background-color:rgb(250,250,250)"> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">不增加服务端负荷</td> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">•</td> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">&nbsp;</td> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">•</td> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">&nbsp;</td> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">•</td> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">&nbsp;</td> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">&nbsp;</td> 
  </tr> 
  <tr> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">多服务器无等待</td> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">•</td> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">&nbsp;</td> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">•</td> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">•</td> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">&nbsp;</td> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">•</td> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">&nbsp;</td> 
  </tr> 
  <tr style="background-color:rgb(250,250,250)"> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">主服务器失败时不丢失数据</td> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">•</td> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">•</td> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">&nbsp;</td> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">&nbsp;</td> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">•</td> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">&nbsp;</td> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">•</td> 
  </tr> 
  <tr> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">备用服务器支持读请求</td> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">&nbsp;</td> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">&nbsp;</td> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">Hot only</td> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">•</td> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">•</td> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">•</td> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">•</td> 
  </tr> 
  <tr style="background-color:rgb(250,250,250)"> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">表级别粒度</td> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">&nbsp;</td> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">&nbsp;</td> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">&nbsp;</td> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">•</td> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">&nbsp;</td> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">•</td> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">•</td> 
  </tr> 
  <tr> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">无冲突解析</td> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">•</td> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">•</td> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">•</td> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">•</td> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">&nbsp;</td> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">&nbsp;</td> 
   <td style="border:1px solid rgb(238,238,238); padding:4px; text-align:left">•</td> 
  </tr> 
 </tbody> 
</table>

从PostgreSQL 9.0开始，基于**预写日志**(Write Ahead Log, **WAL**)的可读复制(PITR)更是成为了官方提供的异步**主从复制**(Master-Slave Replication)解决方案，该方案拥有如下优点：

1.  使用预写日志记录数据库的改动，不额外增加服务端的其他负荷。
2.  当主服务器失败（如断电、系统崩溃、灾难）时，不会丢失任何数据。
3.  支持基于流和基于档案的两种日志传输方案。
4.  备用服务器可作为负载均衡节点提供读请求。
5.  支持多个或多级备用服务器。

### 实现原理 ###

主服务器在接受到每个事务请求时，将数据改动用预写日志(WAL)记录。具体而言，事务采用**两段提交**(Two Phase Commit)，即先将改动写入预写日志，然后再实际改动数据库。这样可以保证预写日志的时间戳永远不落后于数据库，即便是正在写入时服务器突然崩溃，重启以后也可以依据预写日志将数据恢复，因为预写日志保留了比数据库记录中更新的版本。PostgreSQL的异步复制解决方案正是利用了预写日志，将预写日志从主服务器(Master Sever)传输到备用服务器(Standby Server)，然后在备用服务器上回放(Replay)出预写日志中记录改动，从而实现主从复制。PostgreSQL使用了两种方式传输预写日志：**存档式**(archive)和**流式**(streaming)。

**存档式复制**的原理是主服务器将预写日志主动拷贝到一个安全的位置（可以直接到备用服务器，也可以是第三台服务器），同时备用服务器定期扫描这个位置，并将预写日志拷贝到备用服务器端然后再回放。这样即使主服务器崩溃了，备用服务器也可以从这个安全的位置获取到一份完整的记录，以确保任何数据不会丢失。而**流式复制**则简化了这一个步骤，由主服务器直接通过TCP协议向备用服务器传输日志，避免了两次复制的开销，有利于减小备用服务器和主服务器直接的数据延时。但当主服务器崩溃时，未被传输到备用服务器的日志则会丢失，造成数据损失。PostgreSQL支持存档式和流式两种模式的混合，当两种模式都开启时，备用服务器会定期检查是否有存档已经到达指定的位置，并回放日志。一旦检测到指定的位置没有新的日志，则会切换到流式模式试图直接从网络传输日志，接着再检查存档，不断重复这一循环。

### 基本配置步骤 ###

**1、在主服务器和从服务器上分别安装PostgreSQL 9.1以上版本，初始化数据库：**

initdb -D "/var/postgres/data"  # Unix
    initdb -D "D:/postgres/data"  # Windows

**2、修改主服务器数据目录下的配置文件pg\_hba.conf，增加备用服务器访问的权限，例如：**

host    replication    postgres_repl    59.66.134.0/24    md5

以上表示允许来自59.66.134.0/24的连接，用户名为postgres\_repl，口令采用MD5验证。 如果不需要配置流式复制，此项配置可以忽略。

**3、修改主服务器数据目录下的配置文件postgresql.conf：** 将wal\_level设为archive或hot\_standby，archive为单纯备用服务器所需的日志格式，hot\_standby为支持读请求的备用服务器所需格式； 如果要支持流式复制，修改max\_wal\_senders为一个大于零的数值，表示流式复制最大的备用服务器连接数目； 如果要支持流式复制，修改wal\_sender\_delay为一个适合的时间值，表示发送流式日志的周期。 如果要支持档案式复制，设置

archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'  # Unix
    archive_command = 'copy "%p" "X:\\server\\archivedir\\%f"'  # Windows

其中cp或copy是shell可执行命令，用于复制日志，具体应用中根据环境可以是scp，或其他拷贝方式。/mnt/server/archivedir/或X:\\server\\archivedir\\表示存储日志的安全的位置，可供备用服务器读取。

**4、修改备份服务器数据目录下的配置文件postgresql.conf：** 如果设置hot\_standby为on，则允许备用服务器支持读请求，对应主服务器上wal\_level要设置为hot\_standby；否则只作为单纯的备份服务器。

**5、在备份服务器数据目录下添加文件recovery.conf：** 设置standby\_mode为on； 如果要支持流式复制，设置primary\_conninfo，例如：

'host=59.66.134.21 port=5432 user=postgres_repl password=my_pass_word'；

如果要支持档案式复制，设置

restore_command = 'cp /path/to/archive/%f %p'
    archive_cleanup_command = 'pg_archivecleanup /path/to/archive %r'

其中/path/to/archive/为存储从主服务器复制的日志的位置，cp或copy的意义同上，可替换。

**6、分别启动主服务器和备份服务器：**

pg_ctl start -D "/var/postgres/data"  # Unix
    pg_ctl start -D "D:/postgres/data"  # Windows

在主服务器上改动数据，测试配置是否成功。