作者
digoal
日期
2016-08-29
标签
PostgreSQL , pg_dump , cache lookup failed , 逻辑备份 , 一致性
背景
PostgreSQL逻辑备份，如何保证备份数据的一致性呢，例如备份的同时，数据被纂改或者有新增的数据，如何保证在全库视角，备份出来的数据是在备份开始时看到的一致数据。
一致性逻辑备份分析
可以追溯到1999年的代码，早期PostgreSQL通过serializable隔离级别来保证备份的一致性。
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=bcb5aac81dec14d892fae18b446315367563be4c
Add keywords to implement Vadim's transaction isolation
and lock syntax as fully parsed tokens.
Two keywords for isolation are non-reserved SQL92
(COMMITTED, SERIALIZABLE).
All other new keywords are non-reserved Postgres (not SQL92)
(ACCESS, EXCLUSIVE, MODE, SHARE).
Add syntax to allow CREATE [GLOBAL|LOCAL] TEMPORARY TABLE, throwing an
error if GLOBAL is specified.
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=df9e539ea2c434c73d724234e792536789fd97b8
+ /*
+ * Start serializable transaction to dump consistent data
+ */
+ {
+ PGresult *res;
+
+ res = PQexec(g_conn, "begin");
+ if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
+ {
+ fprintf(stderr, "BEGIN command failed. Explanation from backend: '%s'.\n", PQerrorMessage(g_conn));
+ exit_nicely(g_conn);
+ }
+ PQclear(res);
+ res = PQexec(g_conn, "set transaction isolation level serializable");
+ if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
+ {
+ fprintf(stderr, "SET TRANSACTION command failed. Explanation from backend: '%s'.\n", PQerrorMessage(g_conn));
+ exit_nicely(g_conn);
+ }
+ PQclear(res);
+ }
+
9.1 开始，PostgreSQL改进并支持了SSI隔离级别，比SQL92标准更加苛刻。
同时支持transaction snapshot，一致性备份不再需要serializable，使用repeatable read即可。
所以pg_dump.c改成如下
https://git.postgresql.org/gitweb/?p=postgresql.git;a=blobdiff;f=src/bin/pg_dump/pg_dump.c;h=d3eb7662880b3dda57f052d7fbbd22392a8cb562;hp=e844b5b062440ecf7233576d396ca36dafc5e8b9;hb=dafaa3efb75ce1aae2e6dbefaf6f3a889dea0d21;hpb=c18f51da17d8cf01d62218e0404e18ba246bde54
/*
* 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");
}
else if (AH->remoteVersion >= 70400)
{
/* note: comma was not accepted in SET TRANSACTION before 8.0 */
ExecuteSqlStatement(AH,
"SET TRANSACTION ISOLATION LEVEL "
"SERIALIZABLE READ ONLY");
}
else
ExecuteSqlStatement(AH,
"SET TRANSACTION ISOLATION LEVEL SERIALIZABLE");
逻辑备份cache lookup failed错误分析
除了考虑数据的一致性备份，还需要考虑结构的一致性。
某些获取数据结构的调用是snapshot now的，所以可能有很小的窗口期可能被执行DDL，从而导致relcache变化。
如果pg_dump发现relcache变化，则会爆出cache lookup failed的错误，导致备份失败。
14 * Note that pg_dump runs in a transaction-snapshot mode transaction,
15 * so it sees a consistent snapshot of the database including system
16 * catalogs. However, it relies in part on various specialized backend
17 * functions like pg_get_indexdef(), and those things tend to run on
18 * SnapshotNow time, ie they look at the currently committed state. So
19 * it is possible to get 'cache lookup failed' error if someone
20 * performs DDL changes while a dump is happening. The window for this
21 * sort of thing is from the acquisition of the transaction snapshot to
22 * getSchemaData() (when pg_dump acquires AccessShareLock on every
23 * table it intends to dump). It isn't very large, but it can happen.
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=dafaa3efb75ce1aae2e6dbefaf6f3a889dea0d21
所以应该避免在使用逻辑备份期间执行DDL。
如果系统无法避免，建议使用物理备份。
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
