PostgreSQL 10.0 preview 功能增强 - 逻辑复制支持并行COPY初始化数据-阿里云开发者社区

开发者社区> 阿里云数据库> 正文

PostgreSQL 10.0 preview 功能增强 - 逻辑复制支持并行COPY初始化数据

简介:

标签

PostgreSQL , 10.0 , 逻辑复制 , 初始数据COPY


背景

PostgreSQL 已支持逻辑复制,同时对逻辑复制增加了一个初始同步的增强功能,支持通过wal receiver协议跑COPY命令(已封装在逻辑复制的内核代码中),支持多表并行。

也就是说,你可以使用PostgreSQL的逻辑复制,快速的(流式、并行)将一个实例迁移到另一个实例。

Logical replication support for initial data copy  
  
Add functionality for a new subscription to copy the initial data in the  
tables and then sync with the ongoing apply process.  
  
For the copying, add a new internal COPY option to have the COPY source  
data provided by a callback function.  The initial data copy works on  
the subscriber by receiving COPY data from the publisher and then  
providing it locally into a COPY that writes to the destination table.  
  
A WAL receiver can now execute full SQL commands.  This is used here to  
obtain information about tables and publications.  
  
Several new options were added to CREATE and ALTER SUBSCRIPTION to  
control whether and when initial table syncing happens.  
  
Change pg_dump option --no-create-subscription-slots to  
--no-subscription-connect and use the new CREATE SUBSCRIPTION  
... NOCONNECT option for that.  
  
Author: Petr Jelinek <petr.jelinek@2ndquadrant.com>  
Tested-by: Erik Rijkers <er@xs4all.nl>  

逻辑复制包含的初始化COPY的流程如下

主库开启事务快照(快照支持在多个会话间共享, 这也是PostgreSQL的独门秘籍之一), COPY数据, COPY结束后释放快照, 从快照对应的WAL LSN开始接收增量.

/*-------------------------------------------------------------------------  
   2  * tablesync.c  
   3  *    PostgreSQL logical replication  
   4  *  
   5  * Copyright (c) 2012-2016, PostgreSQL Global Development Group  
   6  *  
   7  * IDENTIFICATION  
   8  *    src/backend/replication/logical/tablesync.c  
   9  *  
  10  * NOTES  
  11  *    This file contains code for initial table data synchronization for  
  12  *    logical replication.  
  13  *  
  14  *    The initial data synchronization is done separately for each table,  
  15  *    in separate apply worker that only fetches the initial snapshot data  
  16  *    from the publisher and then synchronizes the position in stream with  
  17  *    the main apply worker.  
  18  *  
  19  *    The are several reasons for doing the synchronization this way:  
  20  *     - It allows us to parallelize the initial data synchronization  
  21  *       which lowers the time needed for it to happen.  
  22  *     - The initial synchronization does not have to hold the xid and LSN  
  23  *       for the time it takes to copy data of all tables, causing less  
  24  *       bloat and lower disk consumption compared to doing the  
  25  *       synchronization in single process for whole database.  
  26  *     - It allows us to synchronize the tables added after the initial  
  27  *       synchronization has finished.  
  28  *  
  29  *    The stream position synchronization works in multiple steps.  
  30  *     - Sync finishes copy and sets table state as SYNCWAIT and waits  
  31  *       for state to change in a loop.  
  32  *     - Apply periodically checks tables that are synchronizing for SYNCWAIT.  
  33  *       When the desired state appears it will compare its position in the  
  34  *       stream with the SYNCWAIT position and based on that changes the  
  35  *       state to based on following rules:  
  36  *        - if the apply is in front of the sync in the wal stream the new  
  37  *          state is set to CATCHUP and apply loops until the sync process  
  38  *          catches up to the same LSN as apply  
  39  *        - if the sync is in front of the apply in the wal stream the new  
  40  *          state is set to SYNCDONE  
  41  *        - if both apply and sync are at the same position in the wal stream  
  42  *          the state of the table is set to READY  
  43  *     - If the state was set to CATCHUP sync will read the stream and  
  44  *       apply changes until it catches up to the specified stream  
  45  *       position and then sets state to READY and signals apply that it  
  46  *       can stop waiting and exits, if the state was set to something  
  47  *       else than CATCHUP the sync process will simply end.  
  48  *     - If the state was set to SYNCDONE by apply, the apply will  
  49  *       continue tracking the table until it reaches the SYNCDONE stream  
  50  *       position at which point it sets state to READY and stops tracking.  
  51  *  
  52  *    The catalog pg_subscription_rel is used to keep information about  
  53  *    subscribed tables and their state and some transient state during  
  54  *    data synchronization is kept in shared memory.  
  55  *  
  56  *    Example flows look like this:  
  57  *     - Apply is in front:  
  58  *        sync:8  
  59  *          -> set SYNCWAIT  
  60  *        apply:10  
  61  *          -> set CATCHUP  
  62  *          -> enter wait-loop  
  63  *        sync:10  
  64  *          -> set READY  
  65  *          -> exit  
  66  *        apply:10  
  67  *          -> exit wait-loop  
  68  *          -> continue rep  
  69  *     - Sync in front:  
  70  *        sync:10  
  71  *          -> set SYNCWAIT  
  72  *        apply:8  
  73  *          -> set SYNCDONE  
  74  *          -> continue per-table filtering  
  75  *        sync:10  
  76  *          -> exit  
  77  *        apply:10  
  78  *          -> set READY  
  79  *          -> stop per-table filtering  
  80  *          -> continue rep  
  81  *-------------------------------------------------------------------------  
  82  */  
  83   

这个patch的讨论,详见邮件组,本文末尾URL。

PostgreSQL社区的作风非常严谨,一个patch可能在邮件组中讨论几个月甚至几年,根据大家的意见反复的修正,patch合并到master已经非常成熟,所以PostgreSQL的稳定性也是远近闻名的。

参考

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=7c4f52409a8c7d85ed169bbbc1f6092274d03920

版权声明:本文中所有内容均属于阿里云开发者社区所有,任何媒体、网站或个人未经阿里云开发者社区协议授权不得转载、链接、转贴或以其他方式复制发布/发表。申请授权请邮件developerteam@list.alibaba-inc.com,已获得阿里云开发者社区协议授权的媒体、网站,在转载使用时必须注明"稿件来源:阿里云开发者社区,原文作者姓名",违者本社区将依法追究责任。 如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件至:developer2020@service.aliyun.com 进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
阿里云数据库
使用钉钉扫一扫加入圈子
+ 订阅

帮用户承担一切数据库风险,给您何止是安心!

官方博客
链接