解決 Aurora PostgreSQL 中可識別的清空封鎖程式

自動清空會執行積極的清空，並將交易 IDs 的存留期降至低於 RDS 執行個體 autovacuum_freeze_max_age 參數指定的閾值。您可以使用 Amazon CloudWatch 指標來追蹤此存留期MaximumUsedTransactionIDs。

若要尋找 Amazon RDS 執行個體的設定 autovacuum_freeze_max_age（預設為 2 億筆交易 IDs)，您可以使用下列查詢：


SELECT
    TO_CHAR(setting::bigint, 'FM9,999,999,999') autovacuum_freeze_max_age
FROM
    pg_settings
WHERE
    name = 'autovacuum_freeze_max_age';

請注意，postgres_get_av_diag()只有在存留期超過 Amazon RDS 5 億筆交易 IDs 的自適應自動清空閾值時，才會檢查積極的清空封鎖程式。若要postgres_get_av_diag()讓偵測封鎖程式，封鎖程式必須至少有 5 億筆舊交易。

postgres_get_av_diag() 函數會識別下列類型的封鎖程式：

作用中陳述式

在 PostgreSQL 中，作用中陳述式是目前正在由資料庫執行的 SQL 陳述式。這包括查詢、交易或任何進行中的操作。透過監控時pg_stat_activity，狀態欄會指出具有對應 PID 的程序處於作用中狀態。

當識別為作用中陳述式的陳述式時， postgres_get_av_diag()函數會顯示類似以下的輸出。


blocker               | Active statement
database              | my_database
blocker_identifier    | SELECT pg_sleep(20000);
wait_event            | Timeout:PgSleep
autovacuum_lagging_by | 568,600,871
suggestion            | Connect to database "my_database", review carefully and you may consider terminating the process using suggested_action. For more information, see Working with PostgreSQL autovacuum in the Amazon RDS User Guide.
suggested_action      | {"SELECT pg_terminate_backend (29621);"}

建議的動作

遵循 suggestion欄中的指引，使用者可以連線到存在作用中陳述式的資料庫，並且如 suggested_action欄中所指定，建議您仔細檢閱終止工作階段的選項。如果終止是安全的，您可以使用 pg_terminate_backend()函數來終止工作階段。此動作可由管理員（例如 RDS 主帳戶）或具有必要pg_terminate_backend()權限的使用者執行。

警告

終止的工作階段將復原其所做的 (ROLLBACK) 變更。根據您的需求，您可能想要重新執行陳述式。不過，建議您只在自動清空程序完成其積極的清空操作之後，才這麼做。

交易閒置

交易陳述式的閒置是指已開啟明確交易（例如發出BEGIN陳述式）、已執行一些工作，且現在正在等待用戶端透過發出 COMMIT、或 END（這會導致隱含 ) 傳遞更多工作ROLLBACK或發出交易結束訊號的任何工作階段COMMIT。

當將 idle in transaction陳述式識別為封鎖程式時， postgres_get_av_diag()函數會顯示類似以下的輸出。


blocker               | idle in transaction
database              | my_database
blocker_identifier    | INSERT INTO tt SELECT * FROM tt;
wait_event            | Client:ClientRead
autovacuum_lagging_by | 1,237,201,759
suggestion            | Connect to database "my_database", review carefully and you may consider terminating the process using suggested_action. For more information, see Working with PostgreSQL autovacuum in the Amazon RDS User Guide.
suggested_action      | {"SELECT pg_terminate_backend (28438);"}

建議的動作

如 suggestion欄中所示，您可以連線至交易工作階段中存在閒置的資料庫，並使用 pg_terminate_backend()函數終止工作階段。使用者可以是您的管理員 (RDS 主帳戶）使用者或具有 pg_terminate_backend()權限的使用者。

警告

已準備的交易

PostgreSQL 允許屬於兩個階段遞交策略的交易，稱為預備交易。這些是透過將 max_prepared_transactions 參數設定為非零值來啟用。準備的交易旨在確保交易耐久，即使在資料庫當機、重新啟動或用戶端中斷連線後，仍然可用。與一般交易一樣，它們會獲指派交易 ID，並可能影響自動清空。如果保持預備狀態，則自動清空無法執行凍結，並可能導致交易 ID 包圍。

當交易無限期地準備而不由交易管理員解決時，它們會變成孤立的準備交易。修正此問題的唯一方法是分別使用或 ROLLBACK PREPARED命令遞交COMMIT PREPARED或轉返交易。

注意

請注意，在預備交易期間進行的備份在還原後仍會包含該交易。請參閱下列有關如何尋找和關閉此類交易的資訊。

該postgres_get_av_diag()函數會在識別為預備交易的封鎖程式時顯示下列輸出。


blocker               | Prepared transaction
database              | my_database
blocker_identifier    | myptx
wait_event            | Not applicable
autovacuum_lagging_by | 1,805,802,632
suggestion            | Connect to database "my_database" and consider either COMMIT or ROLLBACK the prepared transaction using suggested_action. For more information, see Working with PostgreSQL autovacuum in the Amazon RDS User Guide.
suggested_action      | {"COMMIT PREPARED 'myptx';",[OR],"ROLLBACK PREPARED 'myptx';"}

建議的動作

如建議欄中所述，連線至預備交易所在的資料庫。根據 suggested_action資料欄，仔細檢閱是否執行 COMMIT或 ROLLBACK，以及相應動作。

若要監控一般預備交易，PostgreSQL 提供稱為的目錄檢視pg_prepared_xacts。您可以使用下列查詢來尋找準備好的交易。


SELECT
    gid,
    prepared,
    owner,
    database,
    transaction AS oldest_xmin
FROM
    pg_prepared_xacts
ORDER BY
    age(transaction) DESC;

邏輯複寫槽

複寫槽的目的是保留未使用的變更，直到這些變更複寫到目標伺服器為止。如需詳細資訊，請參閱 PostgreSQL 的邏輯複寫。

邏輯複寫槽有兩種類型。

非作用中邏輯複寫槽

複寫終止時，無法移除未使用的交易日誌，且複寫槽會變成非作用中。雖然訂閱者目前未使用非作用中的邏輯複寫槽，但其會保留在伺服器上，導致 WAL 檔案的保留，並防止移除舊的交易日誌。這可能會增加磁碟使用量，並特別封鎖自動清空清除內部目錄資料表，因為系統必須保留 LSN 資訊以免遭到覆寫。如果未解決，這可能會導致目錄膨脹、效能降低，以及包裝清空的風險增加，進而可能導致交易停機。

作用中但緩慢的邏輯複寫槽

有時候，由於邏輯複寫的效能降低，移除目錄的無效元組會延遲。此複寫延遲會減緩更新的速度，catalog_xmin並可能導致目錄膨脹和包裝清空。

當 postgres_get_av_diag()函數找到邏輯複寫插槽做為封鎖程式時，會顯示類似下列的輸出。


blocker               | Logical replication slot
database              | my_database
blocker_identifier    | slot1
wait_event            | Not applicable
autovacuum_lagging_by | 1,940,103,068
suggestion            | Ensure replication is active and resolve any lag for the slot if active. If inactive, consider dropping it using the command in suggested_action. For more information, see Working with PostgreSQL autovacuum in the Amazon RDS User Guide.
suggested_action      | {"SELECT pg_drop_replication_slot('slot1') FROM pg_replication_slots WHERE active = 'f';"}

建議的動作

若要解決此問題，請檢查複寫組態是否有可能終止套用程序之目標結構描述或資料的問題。最常見的原因如下：

缺少資料欄
不相容的資料類型
資料不符
缺少資料表

如果問題與基礎設施問題有關：

網路問題 - 如何解決網路狀態不相容的 Amazon RDS 資料庫問題？
由於下列原因，資料庫或資料庫執行個體無法使用：
- 複本執行個體的儲存體不足 - 檢閱 Amazon RDS 資料庫執行個體的儲存體不足，以取得新增儲存體的相關資訊。
- 不相容參數 - 檢閱如何修正停滯在不相容參數狀態的 Amazon RDS 資料庫執行個體？以取得如何解決問題的詳細資訊。

如果您的執行個體位於 AWS 網路或 on AWS EC2 之外，請洽詢您的管理員，了解如何解決可用性或基礎設施相關問題。

捨棄非作用中的插槽

警告

注意：捨棄複寫槽之前，請仔細確認它沒有進行中的複寫、處於非作用中狀態，而且處於無法復原的狀態。過早捨棄插槽可能會中斷複寫或導致資料遺失。

確認不再需要複寫槽後，請將其捨棄以允許自動清空繼續。條件active = 'f'可確保只會捨棄非作用中的插槽。


SELECT pg_drop_replication_slot('slot1') WHERE active ='f'

讀取器執行個體

啟用 hot_standby_feedback 設定時，可防止寫入器執行個體上的自動清空移除讀取器執行個體上執行的查詢仍可能需要的無效資料列。此行為是必要的，因為在讀取器執行個體上執行的查詢（也適用於 Aurora Global Database 中的讀取器執行個體）需要這些資料列在寫入器執行個體上保持可用，以防止查詢衝突和取消。

注意

hot_standby_feedback 預設會在 Aurora PostgreSQL 中啟用且無法修改。

當 postgres_get_av_diag()函數找到具有實體複寫槽做為封鎖程式的僅供讀取複本時，會顯示類似以下的輸出。


blocker               | Oldest query running on aurora reader
database              | Not applicable
blocker_identifier    | my-aurora-reader-2
wait_event            | Not applicable
autovacuum_lagging_by | 540,122,859
suggestion            | Run the following query on the reader "my-aurora-reader-2" to find the long running query:                                                                                                 
                      | SELECT * FROM pg_catalog.pg_stat_activity WHERE backend_xmin::text::bigint = 523476310;                                                                                                    
                      | Review carefully and you may consider terminating the query on reader using suggested_action.                                                      
suggested_action      | {"SELECT pg_terminate_backend(pid) FROM pg_catalog.pg_stat_activity WHERE backend_xmin::text::bigint = 523476310;","                                                                       
                      | [OR]                                                                                                                                                                                    
                      | ","Delete the reader if not needed"}

如 suggested_action欄中的建議，請仔細檢閱這些選項以解除封鎖自動清空。

終止查詢 – 根據建議欄中的指引，您可以連線到僅供讀取複本，如 suggested_action 欄中所指定，建議您仔細檢閱終止工作階段的選項。如果終止被視為安全，您可以使用 pg_terminate_backend()函數來終止工作階段。此動作可由管理員（例如 RDS 主帳戶）或具有必要 pg_terminate_backend() 權限的使用者執行。

您可以在僅供讀取複本上執行下列 SQL 命令，以終止導致主要上的清空無法清除舊資料列的查詢。的值backend_xmin會在函數的輸出中報告：
```
SELECT
    pg_terminate_backend(pid)
FROM
    pg_catalog.pg_stat_activity
WHERE
    backend_xmin::text::bigint = backend_xmin;
```
如果不需要，請刪除讀取器執行個體 – 如果不再需要讀取器執行個體，您可以將其刪除。這將移除相關聯的複寫額外負荷，並允許主要回收交易日誌，而不會被執行個體保留。

暫時資料表

使用 TEMPORARY關鍵字建立的暫時資料表位於臨時結構描述中，例如 pg_temp_xxx，並且只能存取建立它們的工作階段。暫時資料表會在工作階段結束時捨棄。不過，PostgreSQL 的自動清空程序看不到這些資料表，而且必須由建立它們的工作階段手動清空。嘗試從另一個工作階段清空暫存資料表沒有效果。

在異常情況下，暫時資料表存在，而沒有作用中的工作階段擁有它。如果擁有工作階段由於嚴重損毀、網路問題或類似事件意外結束，則可能無法清除暫存資料表，將其保留為「孤立」資料表。當 PostgreSQL 自動清空程序偵測到孤立的暫存資料表時，它會記錄下列訊息：


LOG: autovacuum: found orphan temp table \"%s\".\"%s\" in database \"%s\"

postgres_get_av_diag() 將暫存資料表識別為封鎖程式時，函數會顯示類似下列的輸出。為了讓函數正確顯示與暫時資料表相關的輸出，它需要在存在這些資料表的相同資料庫中執行。


blocker               | Temporary table
database              | my_database
blocker_identifier    | pg_temp_14.ttemp
wait_event            | Not applicable
autovacuum_lagging_by | 1,805,802,632
suggestion            | Connect to database "my_database". Review carefully, you may consider dropping temporary table using command in suggested_action. For more information, see Working with PostgreSQL autovacuum in the Amazon RDS User Guide.
suggested_action      | {"DROP TABLE ttemp;"}

建議的動作

遵循輸出資料suggestion欄中提供的指示，以識別和移除防止自動清空執行的暫存資料表。使用以下命令捨棄報告的暫存資料表postgres_get_av_diag()。根據postgres_get_av_diag()函數提供的輸出取代資料表名稱。


DROP TABLE my_temp_schema.my_temp_table;

下列查詢可用來識別暫時資料表：


SELECT
    oid,
    relname,
    relnamespace::regnamespace,
    age(relfrozenxid)
FROM
    pg_class
WHERE
relpersistence = 't'
ORDER BY
    age(relfrozenxid) DESC;

您的瀏覽器已停用或無法使用 Javascript。

您必須啟用 Javascript，才能使用 AWS 文件。請參閱您的瀏覽器說明頁以取得說明。

文件慣用形式

postgres_get_av_diag() 的函數

解決無法識別的清空封鎖程式