作者
digoal
日期
2020-11-27
标签
PostgreSQL , slot , 逻辑订阅 , 位点
背景
创建完slot, 是否立即就开始保留wal? 需不需要等消费者上线一笔后才开始保留wal?
答案: 立即保留, 不需要等待消费者来消费第一笔. 它不像事务控制begin; begin语句执行后不会立即申请新事务号, 要等有写请求时才会申请新事务号.
创建逻辑slot时, 需等数据库实例(任何database中有未结束事务都会堵塞创建slot)中所有事务结束, 事务结束后, slot打点(此时所有未结束事务已结束), 所以slot创建时的wal位点是一致性的wal位点.
创建完slot, 返回wal位点, 这个位点开始的wal日志都将被保留. (PG 13 引入max_slot_wal_keep_size参数, 可以控制是否需要永久保留wal)
同时, 逻辑slot与database挂钩, 需要逻辑复制的数据库都需要创建一个slot. (并不是一个集群创建一个slot即可). 物理slot则只需要1个.
```
postgres=# select pg_create_logical_replication_slot('db1','pgoutput');
pg_create_logical_replication_slot
(db1,5/DC828370)
(1 row)
```
返回的就是wal位点.
postgres=# select * from pg_replication_slots ;
-[ RECORD 1 ]-------+-----------
slot_name | db1
plugin | pgoutput
slot_type | logical
datoid | 14174
database | postgres
temporary | f
active | f
active_pid |
xmin |
catalog_xmin | 4472
restart_lsn | 5/DC828338
confirmed_flush_lsn | 5/DC828370
wal_status | reserved
safe_wal_size |
pg_replication_slot含义:
PG13支持了逻辑slot的保留策略功能(max_slot_wal_keep_size 参数), 配置保留策略可以防止某些订阅异常或订阅长期中断导致的wal堆积.
https://www.postgresql.org/docs/13/view-pg-replication-slots.html
The pg_replication_slots view provides a listing of all replication slots that currently exist on the database cluster, along with their current state.
For more on replication slots, see Section 26.2.6 and Chapter 48.
Table 51.81. pg_replication_slots Columns
Column Type
Description
slot_name name
A unique, cluster-wide identifier for the replication slot
plugin name
The base name of the shared object containing the output plugin this logical slot is using, or null for physical slots.
slot_type text
The slot type: physical or logical
datoid oid (references pg_database.oid)
The OID of the database this slot is associated with, or null. Only logical slots have an associated database.
database name (references pg_database.datname)
The name of the database this slot is associated with, or null. Only logical slots have an associated database.
temporary bool
True if this is a temporary replication slot. Temporary slots are not saved to disk and are automatically dropped on error or when the session has finished.
active bool
True if this slot is currently actively being used
active_pid int4
The process ID of the session using this slot if the slot is currently actively being used. NULL if inactive.
xmin xid
The oldest transaction that this slot needs the database to retain. VACUUM cannot remove tuples deleted by any later transaction.
catalog_xmin xid
The oldest transaction affecting the system catalogs that this slot needs the database to retain. VACUUM cannot remove catalog tuples deleted by any later transaction.
restart_lsn pg_lsn
The address (LSN) of oldest WAL which still might be required by the consumer of this slot and thus won't be automatically removed during checkpoints unless this LSN gets behind more than max_slot_wal_keep_size from the current LSN. NULL if the LSN of this slot has never been reserved.
confirmed_flush_lsn pg_lsn
The address (LSN) up to which the logical slot's consumer has confirmed receiving data. Data older than this is not available anymore. NULL for physical slots.
wal_status text
Availability of WAL files claimed by this slot. Possible values are:
reserved means that the claimed files are within max_wal_size.
extended means that max_wal_size is exceeded but the files are still retained, either by the replication slot or by wal_keep_size.
unreserved means that the slot no longer retains the required WAL files and some of them are to be removed at the next checkpoint. This state can return to reserved or extended.
lost means that some required WAL files have been removed and this slot is no longer usable.
The last two states are seen only when max_slot_wal_keep_size is non-negative. If restart_lsn is NULL, this field is null.
safe_wal_size int8
The number of bytes that can be written to WAL such that this slot is not in danger of getting in state "lost". It is NULL for lost slots, as well as if max_slot_wal_keep_size is -1.
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
