每天5分钟PG聊通透第27期,为什么备份会堵塞业务?
digoal约 7 分钟参考文档点击文末阅读原文打开; 推荐《最好的PostgreSQL学习镜像》;
每天5分钟PG聊通透第27期,为什么备份会堵塞业务?
备份、订阅、恢复
3、为什么逻辑备份可能和业务产生冲突?
https://www.bilibili.com/video/BV1Em4y1y7PV/
逻辑备份pg_dump备份集是一致性备份集, 如果一个实例有多个database, 一致性最大范围可包含一个库.
逻辑备份原理如下：
1 首先开启RR事务
2 然后对需要备份的对象加共享锁, 防止要备份的数据被DROP或TRUNCATE, 或者结构被变更.
过程2进行中, 和这些操作冲突, pg_dump getSchemaData() 操作被堵塞: (与DDL、vacuum full、cluser、ALTER 等操作冲突, 包括pg_repack在切换数据文件时也需要短暂的排他锁与之冲突.)
过程2结束后, 过程3结束前, 和这些操作冲突, 用户操作被堵塞: (与DDL、vacuum full、cluser、ALTER 等操作冲突, 包括pg_repack在切换数据文件时也需要短暂的排他锁与之冲突.)
3 依次备份数据, 直到完成, 释放共享锁.
由于2到3的过程取决于备份集的大小, 如果备份集很大, 在这段时间冲突概率就会比较大.
例如, 凌晨逻辑备份, 用户正好在凌晨执行vacuum full整理数据, 或者在凌晨有一些数据处理操作用到了DDL处理中间结果|表名等.
PS: 多个库不保障一致性, 因为不支持跨库import snapshot. 也许未来的版本会支持.
如果是单个库使用多个JOB并行备份时, 怎么保障多个JOB备份数据的全局一致性? 就是用的snapshot export功能.
postgres=# begin transaction isolation level repeatable read ;
BEGIN
postgres=*# select pg_export_snapshot();
pg_export_snapshot
---------------------
00000004-00000227-1
(1 row) .......
db1=# begin TRANSACTION ISOLATION LEVEL repeatable read;
BEGIN
db1=*# SET TRANSACTION SNAPSHOT '00000004-00000227-1';
ERROR: cannot import a snapshot from a different database
pg_dump.c // 开启RR 事务
/*
* Start transaction-snapshot mode transaction to dump consistent data.
*/
ExecuteSqlStatement(AH, "BEGIN");
if (AH->remoteVersion >= 90100)
{
/*
* To support the combination of serializable_deferrable with the jobs
* option we use REPEATABLE READ for the worker connections that are
* passed a snapshot. As long as the snapshot is acquired in a
* SERIALIZABLE, READ ONLY, DEFERRABLE transaction, its use within a
* REPEATABLE READ transaction provides the appropriate integrity
* guarantees. This is a kluge, but safe for back-patching.
*/
if (dopt->serializable_deferrable && AH->sync_snapshot_id == NULL)
ExecuteSqlStatement(AH,
"SET TRANSACTION ISOLATION LEVEL "
"SERIALIZABLE, READ ONLY, DEFERRABLE");
else
ExecuteSqlStatement(AH,
"SET TRANSACTION ISOLATION LEVEL "
"REPEATABLE READ, READ ONLY");
}
pg_dump.c // 对表加共享锁
/*
* getTables
* read all the tables (no indexes)
* in the system catalogs return them in the TableInfo* structure
*
* numTables is set to the number of tables read in
*/
TableInfo *
getTables(Archive *fout, int *numTables)
{
......... for (i = 0; i < ntups; i++)
{
.........
/*
* Read-lock target tables to make sure they aren't DROPPED or altered
* in schema before we get around to dumping them.
*
* Note that we don't explicitly lock parents of the target tables; we
* assume our lock on the child is enough to prevent schema
* alterations to parent tables.
*
* NOTE: it'd be kinda nice to lock other relations too, not only
* plain or partitioned tables, but the backend doesn't presently
* allow that.
*
* We only need to lock the table for certain components; see
* pg_dump.h
*/
if (tblinfo[i].dobj.dump &&
(tblinfo[i].relkind == RELKIND_RELATION ||
tblinfo[i].relkind == RELKIND_PARTITIONED_TABLE) &&
(tblinfo[i].dobj.dump & DUMP_COMPONENTS_REQUIRING_LOCK))
{
resetPQExpBuffer(query);
appendPQExpBuffer(query,
"LOCK TABLE %s IN ACCESS SHARE MODE",
fmtQualifiedDumpable(&tblinfo[i]));
ExecuteSqlStatement(fout, query->data);
}
/* Emit notice if join for owner failed */
if (strlen(tblinfo[i].rolname) == 0)
pg_log_warning("owner of table \"%s\" appears to be invalid",
tblinfo[i].dobj.name);
pg_dump.h // 判断表的哪些元数据需要加共享锁
/* component types of an object which can be selected for dumping */
typedef uint32 DumpComponents; /* a bitmask of dump object components */
#define DUMP_COMPONENT_NONE (0)
#define DUMP_COMPONENT_DEFINITION (1 << 0)
#define DUMP_COMPONENT_DATA (1 << 1)
#define DUMP_COMPONENT_COMMENT (1 << 2)
#define DUMP_COMPONENT_SECLABEL (1 << 3)
#define DUMP_COMPONENT_ACL (1 << 4)
#define DUMP_COMPONENT_POLICY (1 << 5)
#define DUMP_COMPONENT_USERMAP (1 << 6)
#define DUMP_COMPONENT_ALL (0xFFFF) /*
* component types which require us to obtain a lock on the table
*
* Note that some components only require looking at the information
* in the pg_catalog tables and, for those components, we do not need
* to lock the table. Be careful here though- some components use
* server-side functions which pull the latest information from
* SysCache and in those cases we *do* need to lock the table.
*
* We do not need locks for the COMMENT and SECLABEL components as
* those simply query their associated tables without using any
* server-side functions. We do not need locks for the ACL component
* as we pull that information from pg_class without using any
* server-side functions that use SysCache. The USERMAP component
* is only relevant for FOREIGN SERVERs and not tables, so no sense
* locking a table for that either (that can happen if we are going
* to dump "ALL" components for a table).
*
* We DO need locks for DEFINITION, due to various server-side
* functions that are used and POLICY due to pg_get_expr(). We set
* this up to grab the lock except in the cases we know to be safe.
*/
#define DUMP_COMPONENTS_REQUIRING_LOCK (\
DUMP_COMPONENT_DEFINITION |\
DUMP_COMPONENT_DATA |\
DUMP_COMPONENT_POLICY)
今日荐书
彩蛋:国产数据库周边生态
当然一款数据库要流行起来, 除了自己要强大, 还离不开生态. 用好周边生态工具, 管理水平战胜90%老司机!!! 下面简单介绍一下国产数据库周边生态.
1、管控软件
鸣嵩(前阿里云数据库总经理 / 研究员)等大佬们创业创办的云猿生, 核心产品是KubeBlocks. 他们的理念是让管理数据库和搭积木一样简单, 如果你要管理很多套并且种类(OLTP\OLAP\NoSQL\KV\TS\MQ等)很多的数据库产品, 推荐首选.
https://github.com/apecloud/kubeblocks
PG中文社区核心委员唐成老师的公司乘数开源的Clup, 专用管理PostgreSQL和PolarDB的集群管理软件, 如果你要管理很多套数据库, 推荐选择. 并且Clup还提供了企业版、自研的连接池、分布式存储、一体机、备份平台等, 是企业用户推荐之选.
https://www.csudata.com/
若航开源的pigsty, 集成了300多个PG插件的PG集群和PolarDB集群管理软件, 如果你要管理很多套PG或PolarDB数据库, 且对插件有特别多的需求, 推荐选择.
https://pigsty.cc/zh/
2、审计监控诊断优化
翟总(曾经是我背后的男人)到海信聚好看后研发的 DBdoctor, 采用ebpf技术, 在对数据库几乎没有影响的情况下实时监控数据库和服务器的各项指标, 发现和诊断问题根因非常方便.
https://www.dbdoctor.cn/
天舟老哥的核心产品Bytebase 是位于您和数据库之间的中间件。它是数据库 DevOps 的 GitLab/GitHub，专为开发人员、DBA 和平台工程师打造。
https://bytebase.cc/docs/introduction/what-is-bytebase/
PawSQL, SQL优化和诊断产品.
D-Smart, Oracle老前辈白老大出品, 专注企业级市场, 将业界顶级DBA经验的产品化作品, 产品功能包括数据库监控、诊断、优化等.
https://www.modb.pro/db/567140
3、国产数据库IDE
IDE是开发者的必备工具，例如社区有pgAdmin, 国产IDE则可以看看老程序猿达刚老师的DeskUI:
https://www.deskui.com
4、数据同步&迁移&备份恢复
NineData, 老领导出去创业做的产品, 产品涵盖了数据同步、迁移、备份、比对、devops、chatDBA等.
https://www.ninedata.cloud/home
DSG, 非常老牌的数据库同步迁移企业级产品, 支持各种数据库的异构和同构迁移, 用他们的话说, 没有dsg搞不定的迁移, 比goldengate还牛.
https://www.dsgdata.com/
公开课
如果你对PolarDB学习感兴趣可以阅读这个公开课系列:
除了PolarDB还非常值得关注的几款PG栈国产数据库:
HaloDB(基于PG兼容PostgreSQL、Oracle、MySQL. http://www.halodbtech.com/ )、 IvorySQL(基于开源PG兼容PG、Oracle. https://www.ivorysql.org/zh-cn/ )、 ProtonBase(云原生分布式数仓. https://protonbase.com/ )、 成都文武数据库（https://ww-it.cn）
参考文档点击阅读原文获得
感谢关注我的github (https://github.com/digoal/blog) 及视频号: