pg_dump一致性备份以及cache lookup failed错误的原因分析

2 minute read

背景

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。

如果系统无法避免,建议使用物理备份。

Flag Counter

digoal’s 大量PostgreSQL文章入口