本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。 # 解決 RDS for PostgreSQL 中可識別的清空封鎖程式 自動清空會執行積極的清空，並將交易 IDs 的存留期降至低於 RDS 執行個體 `autovacuum_freeze_max_age` 參數指定的閾值。您可以使用 Amazon CloudWatch 指標來追蹤此存留期。`MaximumUsedTransactionIDs` 若要尋找 Amazon RDS 執行個體的 `autovacuum_freeze_max_age` 設定 (預設為 2 億筆交易 ID)，您可以使用下列查詢： ``` SELECT TO_CHAR(setting::bigint, 'FM9,999,999,999') autovacuum_freeze_max_age FROM pg_settings WHERE name = 'autovacuum_freeze_max_age'; ``` 請注意，只有在存留期超過 Amazon RDS 的 5 億筆交易 ID 的[彈性自動清空](Appendix.PostgreSQL.CommonDBATasks.Autovacuum.md#Appendix.PostgreSQL.CommonDBATasks.Autovacuum.AdaptiveAutoVacuuming)閾值時，`postgres_get_av_diag()` 才會檢查積極的清空封鎖程式。若要讓 `postgres_get_av_diag()` 偵測封鎖程式，封鎖程式必須至少有 5 億筆舊的交易。 `postgres_get_av_diag()` 函數會識別下列類型的封鎖程式： **Topics** + [作用中陳述式](#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Active_statement) + [交易閒置](#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Idle_in_transaction) + [備妥交易](#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Prepared_transaction) + [邏輯複寫槽](#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Logical_replication_slot) + [僅供讀取複本](#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Read_replicas) + [暫時資料表](#Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Temporary_tables) ## 作用中陳述式 在 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`、`ROLLBACK` 或 `END` (這會導致隱含 `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()` 權限的使用者。 **警告** 終止的工作階段將復原 (`ROLLBACK`) 其所做的變更。根據您的需求，您可能想要重新執行陳述式。不過，建議只在自動清空程序完成其積極的清空操作之後才執行此操作。 ## 備妥交易 PostgreSQL 允許屬於兩個階段遞交策略的交易，稱為[備妥交易](https://www.postgresql.org/docs/current/sql-prepare-transaction.html)。這些是透過將 `max_prepared_transactions` 參數設定為非零值來啟用。備妥交易旨在確保交易耐用性，即使在資料庫當機、重新啟動或用戶端中斷連線後仍然可用。與一般交易一樣，它們會獲指派交易 ID，並可能影響自動清空。如果保持備妥狀態，則自動清空無法執行凍結，並可能導致交易 ID 包圍。 當交易無限期準備而不由交易管理員解決時，它們會變成孤立的備妥交易。修正此問題的唯一方法是分別使用 `COMMIT PREPARED` 或 `ROLLBACK 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 的[邏輯複寫](https://www.postgresql.org/docs/current/logical-replication.html)。 邏輯複寫槽有兩種類型。 **非作用中邏輯複寫槽** 複寫終止時，無法移除未使用的交易日誌，且複寫槽會變成非作用中。雖然訂閱者目前未使用非作用中的邏輯複寫槽，但其會保留在伺服器上，導致 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 資料庫問題？](https://repost.aws/knowledge-center/rds-incompatible-network) + 由於下列原因，資料庫或資料庫執行個體無法使用： + 複本執行個體的用盡儲存體 - 檢閱 [Amazon RDS 資料庫執行個體用盡儲存體](https://repost.aws/knowledge-center/rds-out-of-storage)，以取得新增儲存體的相關資訊。 + 不相容參數 - 檢閱[如何修正停滯在不相容參數狀態的 Amazon RDS 資料庫執行個體？](https://repost.aws/knowledge-center/rds-incompatible-parameters)以取得如何解決問題的詳細資訊。 如果您的執行個體不在 AWS 網路或 AWS EC2 上，請洽詢您的管理員，了解如何解決可用性或基礎設施相關問題。 **捨棄非作用中插槽** **警告** 注意：捨棄複寫槽之前，請仔細確認它沒有進行中的複寫、處於非作用中狀態，而且處於無法復原的狀態。提早捨棄插槽可能會中斷複寫或導致資料遺失。 確認不再需要複寫槽後，請將其捨棄以允許自動清空繼續。條件 `active = 'f'` 可確保只會捨棄非作用中的插槽。 ``` SELECT pg_drop_replication_slot('slot1') WHERE active ='f' ``` ## 僅供讀取複本 為 [Amazon RDS 僅供讀取複本](USER_PostgreSQL.Replication.ReadReplicas.md)啟用 `hot_standby_feedback` 設定時，可防止主要資料庫上的自動清空移除在讀取複本上執行的查詢仍可能需要的無效資料列。這會影響所有類型的實體讀取複本，包括具有或沒有複寫插槽的複本。此行為是必要的，因為在待命複本上執行的查詢需要這些資料列在主要 上保持可用，以防止[查詢衝突](https://www.postgresql.org/docs/current/hot-standby.html#HOT-STANDBY-CONFLICT)和取消。 **僅供讀取複本和實體複寫槽** 具有實體複寫槽的僅供讀取複本可大幅增強 RDS for PostgreSQL 中複寫的可靠性和穩定性。這些槽可確保主要資料庫會保留必要的預先寫入日誌檔案，直到複本處理它們為止，即使在網路中斷期間也能維持資料一致性。 從 RDS for PostgreSQL 第 14 版開始，所有複本都會使用複寫插槽。在舊版中，只有跨區域複本使用複寫槽。 當找到具有實體複寫槽作為封鎖程式的讀取複本時，`postgres_get_av_diag()` 函數會顯示類似以下的輸出。 ``` blocker | Read replica with physical replication slot database | blocker_identifier | rds_us_west_2_db_xxxxxxxxxxxxxxxxxxxxx wait_event | Not applicable autovacuum_lagging_by | 554,080,689 suggestion | Run the following query on the replica "rds_us_west_2_db_xxxxxxxxxxxxxxxxxxxx" to find the long running query: | SELECT * FROM pg_catalog.pg_stat_activity WHERE backend_xmin::text::bigint = 757989377; | Review carefully and you may consdier terminating the query on read replica using suggested_action. For more information, see Working with PostgreSQL autovacuum in the Amazon RDS User Guide. + | suggested_action | {"SELECT pg_terminate_backend(pid) FROM pg_catalog.pg_stat_activity WHERE backend_xmin::text::bigint = 757989377;"," + | [OR] + | ","Disable hot_standby_feedback"," + | [OR] + | ","Delete the read replica if not needed"} ``` **具有串流複寫的僅供讀取複本** Amazon RDS 允許在舊版中設定沒有實體複寫槽的僅供讀取複本，直到 13 版為止。這種方法允許主要資料庫更積極地回收 WAL 檔案以減少額外負荷，這在磁碟空間有限的環境中是有利的，並且可以容忍偶爾的 ReplicaLag。不過，如果沒有插槽，則待命必須保持同步，以避免缺少 WAL 檔案。Amazon RDS 使用封存的 WAL 檔案，協助複本在落後時趕上進度，但此程序需要仔細監控，而且速度可能很慢。 當 `postgres_get_av_diag()` 函數找到串流僅供讀取複本作為封鎖程式時，會顯示類似以下的輸出。 ``` blocker | Read replica with streaming replication slot database | Not applicable blocker_identifier | xx.x.x.xxx/xx wait_event | Not applicable autovacuum_lagging_by | 610,146,760 suggestion | Run the following query on the replica "xx.x.x.xxx" to find the long running query: + | SELECT * FROM pg_catalog.pg_stat_activity WHERE backend_xmin::text::bigint = 348319343; + | Review carefully and you may consdier terminating the query on read replica using suggested_action. For more information, see Working with PostgreSQL autovacuum in the Amazon RDS User Guide. + | suggested_action | {"SELECT pg_terminate_backend(pid) FROM pg_catalog.pg_stat_activity WHERE backend_xmin::text::bigint = 348319343;"," + | [OR] + | ","Disable hot_standby_feedback"," + | [OR] + | ","Delete the read replica if not needed"} ``` **建議動作** 如 `suggested_action` 欄中的建議，請仔細檢閱這些選項以解除封鎖自動清空。 + **終止查詢** – 根據建議欄中的指引，您可以連線到僅限讀取複本，如 suggested\$1action 欄中所指定，建議您仔細檢閱終止工作階段的選項。如果終止被視為安全，您可以使用 `pg_terminate_backend()` 函數來終止工作階段。此動作可由管理員 (例如 RDS 主帳戶） 或具有必要 pg\$1terminate\$1backend() 權限的使用者執行。 您可以在僅限讀取複本上執行下列 SQL 命令，以終止導致主要資料庫上的清空無法清除舊資料列的查詢。`backend_xmin` 的值會在函數的輸出中報告： ``` SELECT pg_terminate_backend(pid) FROM pg_catalog.pg_stat_activity WHERE backend_xmin::text::bigint = backend_xmin; ``` + **停用熱待命回饋** – 如果參數造成嚴重的清空延遲，請考慮停用 `hot_standby_feedback` 參數。 `hot_standby_feedback` 參數允許僅供讀取複本通知主要資料庫其查詢活動，防止主要資料庫清空待命資料庫上使用的資料表或資料列。雖然這可確保待命資料庫上的查詢穩定性，但可能會大幅延遲主要資料庫上的清空。停用此功能可讓主要資料庫繼續進行清空，而無需等待待命資料庫趕上。不過，如果嘗試存取已由主要資料庫清空的資料列，這可能會導致查詢取消或待命資料庫失敗。 + **如果不需要，請刪除僅供讀取複本** – 如果不再需要僅供讀取複本，您可以將其刪除。這將移除相關聯的複寫額外負荷，並允許主要資料庫回收交易日誌，而不會被複本保留。 ## 暫時資料表 使用 `TEMPORARY` 關鍵字建立的[暫時資料表](https://www.postgresql.org/docs/current/sql-createtable.html)位於臨時結構描述中，例如 pg\$1temp\$1xxx，並且只能由建立它們的工作階段存取。暫時資料表會在工作階段結束時捨棄。不過，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; ```