資料庫索引不一致的原因有很多,包括軟體瑕疵、硬體問題,或排序順序變更等行為的基礎異動。
PostgreSQL 社群已建構相關工具,可找出並修正這類問題。這包括 amcheck 等工具,PostgreSQL 社群建議使用這類工具找出一致性問題,包括先前版本的 PostgreSQL 14 曾出現的問題。
如果 Cloud SQL 使用者遇到這些一致性問題,可以參考這份劇本。這份教戰手冊提供相關資訊,協助 PostgreSQL 使用者找出並修正不一致的索引。
如要解決索引不一致的問題,請按照下列步驟操作:
事前準備。
開始重新建立索引前,請先備份資料庫、設定正確的權限、確認
psql
用戶端版本,並下載amcheck
擴充功能。-
其中一個陳述式會找出違反唯一和主鍵的項目,另一個陳述式則會偵測各種其他不一致的項目。
-
重新建立索引可修正所有不一致的問題。您可能需要調整執行個體的記憶體設定,才能提升效能。
-
建議您監控重新建立索引作業的進度,確保作業順利進行,不會遭到封鎖。
-
成功重新建立索引後,建議您確認索引是否含有任何不一致之處。
事前準備
備份資料庫
為確保重新建立索引期間不會遺失資料,建議您備份資料庫。詳情請參閱建立按照需求執行的備份。
設定 cloudsqlsuperuser
權限
如要完成這個頁面的步驟,您必須具備cloudsqlsuperuser
權限。詳情請參閱 session_replication_role。
確認 psql
用戶端版本為 9.6 以上
如要完成本頁面的步驟,請務必確認您的 psql
用戶端版本為 9.6 以上。執行 psql --version
指令,驗證目前的 psql
用戶端版本。
安裝 amcheck 擴充功能
如要檢查索引不一致情形,請啟用「amcheck
」擴充功能。
PostgreSQL 9.6
如要安裝 PostgreSQL 9.6 的 amcheck
,請執行下列陳述式:
CREATE EXTENSION amcheck_next;
如果收到 `Could not open extension control file...` 錯誤訊息,請確認您執行的目標維護版本正確無誤 (POSTGRES_9_6_24.R20220710.01_12)。
PostgreSQL 10 以上版本
如要安裝 PostgreSQL 10 以上版本的 amcheck
,請執行下列陳述式:
CREATE EXTENSION amcheck;
檢查索引是否不一致
以下各節說明如何檢查索引不一致的情形,包括檢查索引的不一致性,以及違反唯一鍵和主鍵的情形。
檢查不一致之處
在每個資料庫中,執行下列陳述式來檢查不一致情形:
程式碼範例
DO $$ DECLARE r RECORD; version varchar(100); BEGIN RAISE NOTICE 'Started relhasindex validation on database: %', current_database(); FOR r IN SELECT indexrelid::regclass relname FROM pg_index WHERE indrelid NOT IN (SELECT oid FROM pg_class WHERE relhasindex) LOOP RAISE LOG 'Failed to check index %: %', r.relname, 'relhasindex is false, want true'; RAISE WARNING 'Failed to check index %: %', r.relname, 'relhasindex is false, want true'; END LOOP; RAISE NOTICE 'Finished relhasindex validation on database: %', current_database(); RAISE NOTICE 'Started b-tree amcheck on database: %', current_database(); SHOW server_version into version; SELECT split_part(version, '.', 1) into version; FOR r IN SELECT c.oid, c.oid::regclass relname, i.indisunique FROM pg_index i JOIN pg_opclass op ON i.indclass[0] = op.oid JOIN pg_am am ON op.opcmethod = am.oid JOIN pg_class c ON i.indexrelid = c.oid JOIN pg_namespace n ON c.relnamespace = n.oid WHERE am.amname = 'btree' AND c.relpersistence != 't' AND c.relkind = 'i' AND i.indisready AND i.indisvalid LOOP BEGIN RAISE NOTICE 'Checking index %:', r.relname; IF version = '10' THEN PERFORM bt_index_check(index => r.oid); ELSE PERFORM bt_index_check(index => r.oid, heapallindexed => r.indisunique); END IF; EXCEPTION WHEN undefined_function THEN RAISE EXCEPTION 'Failed to find the amcheck extension'; WHEN OTHERS THEN RAISE LOG 'Failed to check index %: %', r.relname, sqlerrm; RAISE WARNING 'Failed to check index %: %', r.relname, sqlerrm; END; END LOOP; RAISE NOTICE 'Finished b-tree amcheck on database: %', current_database(); END $$;
您會看到類似下方的輸出內容:
輸出
WARNING: Failed to check index t_i_key: relhasindex is false, want true NOTICE: Checking index t_pkey: NOTICE: Checking index t_i_key: WARNING: Failed to check index t_i_key: item order invariant violated for index "t_i_key" NOTICE: Checking index t_j_key: WARNING: Failed to check index t_j_key: item order invariant violated for index "t_j_key" NOTICE: Checking index ij: WARNING: Failed to check index ij: item order invariant violated for index "ij"
如要進一步瞭解如何查看 PostgreSQL 記錄,請參閱「查看執行個體記錄」。
找出並修正違反不重複和主鍵限制的問題
本節說明如何檢查索引是否有違反唯一和主鍵的行為,以及如何修正這類問題。
找出違反唯一鍵的行為
您必須先修正違反唯一鍵的項目,才能重新建立索引。如要檢查所有違反唯一鍵的行為,請在每個資料庫中執行下列指令:
程式碼範例
WITH q AS ( /* this gets info for all UNIQUE indexes */ SELECT indexrelid::regclass as idxname, indrelid::regclass as tblname, indcollation, pg_get_indexdef(indexrelid), format('(%s)',(select string_agg(quote_ident(attname), ', ') from pg_attribute a join unnest(indkey) ia(nr) on ia.nr = a.attnum where attrelid = indrelid)) as idxfields, COALESCE(substring(pg_get_indexdef(indexrelid) FROM '[)] (WHERE .*)$'), '') as whereclause FROM pg_index WHERE indisunique /* next line excludes indexes not affected by collation changes */ AND trim(replace(indcollation::text, '0', '')) != '' ) SELECT /* the format constructs the query to execute for each index */ format( $sql$ DO $$ BEGIN RAISE NOTICE 'checking index=%3$I on table=%1$I key_columns=%2$I '; END;$$; SELECT this, prev, /* we detect both reversed ordering or just not unique */ (CASE WHEN this = prev THEN 'DUPLICATE' ELSE 'BACKWARDS' END) as violation_type FROM (SELECT %2$s AS this, lag(%2$s) OVER (ORDER BY %2$s) AS prev FROM %1$s %4$s ) s WHERE this <= prev and this IS NOT NULL and prev IS NOT NULL; /* change to just '<' if looking for reverse order in index */ $sql$, tblname, idxfields, idxname, whereclause ) FROM q -- LIMIT 20 /* may use limit for testing */ -- the next line tells psql to executes this query and then execute each returned line separately \gexec
指令碼的輸出內容會與下列內容類似:
輸出
NOTICE: checking index=users_email_key on table=users key_columns="(email)" NOTICE: checking index=games_title_key on table=games key_columns="(title)" this | prev | violation_type --------------------+--------------------+---------------- Game #16 $soccer 2 | Game #16 $soccer 2 | DUPLICATE Game #18 $soccer 2 | Game #18 $soccer 2 | DUPLICATE Game #2 $soccer 2 | Game #2 $soccer 2 | DUPLICATE Game #5 $soccer 2 | Game #5 $soccer 2 | DUPLICATE
在這個輸出內容中,表格標題 NOTICE
會顯示索引、資料欄和表格,如果輸出內容包含顯示 DUPLICATE
或 BACKWARDS
的資料列,表示索引已損毀,可能需要修正。資料列中的 BACKWARDS
表示可能隱藏重複值。如果表格中出現這兩項其中之一,請參閱「修正重複鍵違規問題」。
修正重複鍵違規問題
如果發現重複的專屬索引,或重新建立索引作業因重複鍵違規錯誤而失敗,請完成下列步驟,找出並移除重複的鍵。
從
NOTICE
表格標題中擷取key_columns
,如上述範例輸出內容所示。在以下範例中,鍵列為email
。程式碼範例
NOTICE: checking index=users_email_key on table=users key_columns="(email)"
在步驟 3 的查詢中,使用 KEY_COLUMNS 中的這些值。
找出資料表的結構定義。使用
psql
連線至資料庫,然後執行下列指令:程式碼範例
「\dt TABLE_NAME
schema
」欄中的值是您在步驟 3 的查詢中,為「SCHEMA_NAME」使用的值。舉例來說,如果查詢如下:
\dt games
輸出結果會與下列內容相似:
List of relations Schema | Name | Type | Owner --------+-------+-------+---------- public | games | table | postgres (1 row)
執行下列陳述式,強制進行完整資料表掃描,並取得重複的鍵。
程式碼範例
SET enable_indexscan = off; SET enable_bitmapscan = off; SET enable_indexonlyscan = off; SELECT KEY_COLUMNS, count(*) FROM SCHEMA_NAME.TABLE_NAME GROUP BY KEY_COLUMNS HAVING count(*) > 1;
在上述陳述式中,KEY_COLUMNS 是您要檢查的資料表中,由不重複索引或主鍵涵蓋的一或多個資料欄。您在檢查違反唯一鍵限制的項目時,會發現這些項目。這項陳述式會傳回重複的鍵,以及每個鍵的重複次數。
舉例來說,如果查詢如下:
SELECT name,count(*) FROM public.TEST_NAMES GROUP BY name HAVING count(*) > 1;
輸出結果會與下列內容相似:
name | count --------------------+------- Johnny | 2 Peter | 2 (2 rows)
在這種情況下,請繼續下一個步驟,移除重複的鍵。
如果 KEY_COLUMNS 中的任何資料欄為空值,您可以忽略這些資料欄,因為唯一限制不適用於空值資料欄。
如果沒有重複的鍵,請前往「修正不一致的索引」。
建議您執行以下非強制步驟:備份含有重複鍵的記錄。執行下列陳述式來建立備份記錄:
程式碼範例
CREATE TABLE SCHEMA_NAME.TABLE_NAME_bak AS SELECT * FROM SCHEMA_NAME.TABLE_NAME WHERE (KEY_COLUMNS) IN ((KEY_VALUES));
在這個陳述式中,KEY_VALUES 是從上一步驟結果複製的值清單。例如:
程式碼範例
CREATE TABLE public.TEST_NAMES_bak AS SELECT * FROM public.TEST_NAMES WHERE (name) IN (('Johnny'),('Peter'))
如果資料列數量龐大,建議您在
IN
陳述式中,將 ((KEY_VALUES)) 參數替換為步驟 2 中的SELECT
陳述式 (不含count
參數),例如:程式碼範例
CREATE TABLE SCHEMA_NAME.TABLE_NAME_bak AS SELECT * FROM SCHEMA_NAME.TABLE_NAME WHERE (KEY_COLUMNS) IN ( SELECT (KEY_COLUMNS) FROM SCHEMA_NAME.TABLE_NAME GROUP BY (KEY_COLUMNS) HAVING count(*) > 1);
為使用者新增複寫角色,停用觸發程序:
程式碼範例
ALTER USER CURRENT_USER with REPLICATION; SET session_replication_role = replica;
執行下列陳述式,刪除重複的鍵:
程式碼範例
BEGIN; DELETE FROM SCHEMA_NAME.TABLE_NAME a USING ( SELECT min(ctid) AS ctid, KEY_COLUMNS FROM SCHEMA_NAME.TABLE_NAME GROUP BY KEY_COLUMNS HAVING count(*) > 1 ) b WHERE a.KEY_COLUMNS = b.KEY_COLUMNS AND a.ctid <> b.ctid;
舉例來說,如果是多欄 KEY_COLUMNS:
程式碼範例
其中 day 和 rnum 是 KEY_COLUMNS。DELETE FROM public.test_random a USING ( SELECT min(ctid) AS ctid, day, rnum FROM public.test_random GROUP BY day, rnum HAVING count(*) > 1 ) b WHERE a.day=b.day and a.rnum = b.rnum AND a.ctid <> b.ctid;
執行這項陳述式後,系統會保留一列,並刪除每組重複資料列中的其他資料列。如要控管要刪除的資料列版本,請在刪除陳述式中執行下列篩選器:
程式碼範例
DELETE FROM SCHEMA_NAME.TABLE_NAME WHERE ( KEY_COLUMNS, ctid) = (KEY_VALUES, CTID_VALUE);
請完成下列步驟,確認
DELETE
指令傳回的資料列數量符合預期,且沒有任何錯誤:執行下列陳述式,找出資料表變更的資料列:
程式碼範例
SELECT schemaname, relname, n_tup_del, n_tup_upd FROM pg_stat_xact_all_tables WHERE n_tup_del+n_tup_upd > 0;
如果所有資料列都正確無誤,請提交
DELETE
交易:程式碼範例
END;
如有錯誤,請還原變更來修正錯誤:
程式碼範例
ROLLBACK;
刪除重複的鍵後,您可以重新為索引建立索引。
修正不一致的索引
下列各節說明如何修正執行個體中發現的索引不一致問題。
視資料庫的設定而定,您可能需要針對上一個步驟中識別的每個索引執行下列操作:
如果重新建立索引作業因外鍵違規而失敗,請務必找出並修正這些違規事項。
再次執行重新建立索引作業。
準備重新建立索引
找出索引大小
建立大型資料庫的索引所需時間,會比小型資料庫更長。如要加快大型資料庫的索引和重新建立索引作業,可以為這些作業分配更多記憶體和 CPU 效能。這是規劃重新建立索引作業的重要步驟。瞭解索引大小後,您可以設定重新建立索引作業使用的記憶體大小,以及設定平行工作站數量。
執行下列陳述式,找出要修正的索引大小 (以 KB 為單位):
程式碼範例
SELECT i.relname AS index_name, pg_size_pretty(pg_relation_size(x.indexrelid)) AS index_size FROM pg_index x JOIN pg_class i ON i.oid = x.indexrelid WHERE i.relname = 'INDEX_NAME';
這項陳述式會輸出類似以下的結果:
輸出
index_name | index_size ------------+------------ my_index | 16 kB (1 row)
設定用於重新建立索引的記憶體大小
根據上一節中判斷的索引大小,請務必為 maintenance_work_mem
設定適當的值。這個參數會指定要用於重新建立索引作業的記憶體量。舉例來說,如果索引大小超過 15 GB,建議您調整維護記憶體。詳情請參閱「設定資料庫標記」。
建立大型資料庫的索引所需時間,會比小型資料庫更長。為提升索引和重新建立索引作業的速度,建議您在重新建立索引作業期間,將記憶體至少 4 GB 的執行個體 maintenance_work_mem
設為執行個體記憶體的 2% 以上。
設定平行工作站數量
如要增加重新建立索引的平行工作站數量,請在資料庫中設定 max_parallel_maintenance_workers 參數 (使用 PostgreSQL 11 以上版本)。這個參數的預設值為 2,但可以設為較高的值,增加重新建立索引的工作站數量。對於具備 8 顆以上 vCPU 核心的執行個體,建議將 max_parallel_maintenance_workers
旗標值設為 4。
詳情請參閱「設定資料庫標記」。
重新建立索引
您可以使用 pg_repack
公用程式重新建立索引,不必封鎖實際運作的工作負載。這項公用程式可自動執行並簡化並行重新建立索引程序,讓您重新建立索引時不必停機,特別是對於沒有 REINDEX CONCURRENTLY
作業的 PostgreSQL 11 和更早版本。請使用 pg_repack
1.4.7 版執行此程序。
如要使用 pg_repack
重新建立索引,請完成下列步驟:
從 pg_repack 頁面下載、編譯及安裝
pg_repack
公用程式。Debian GNU/Linux 11
為方便起見,建議 Debian Linux 使用者下載並安裝這個適用於 Linux x86_64 平台的預先建構可執行二進位檔。
二進位檔的 sha256 總和檢查碼雜湊如下:
ecfee54364a625d9365d86cb27940b458bfdb0d6ff63bb88063039256fbde96f
如要確認 Linux 版本為 Debian GNU/Linux 11,請執行
hostnamectl
指令。自行編譯
從
pg_repack
頁面下載、編譯及安裝pg_repack
公用程式。建立
pg_repack
擴充功能:程式碼範例
CREATE EXTENSION pg_repack;
執行下列指令,同時重新建立索引:
程式碼範例
pg_repack -h HOSTIP -p 5432 -U USERNAME -d "DATABASE_NAME" -i "INDEX_NAME" --no-superuser-check --no-kill-backend --wait-timeout=3600
這個指令會輸出類似以下的結果:
輸出
INFO: repacking index "public.t_i_key"
如果執行
pg_repack
時發生任何錯誤,請修正錯誤並重試。修正所有不重複鍵索引和主鍵索引後,請檢查是否有違反外部鍵的行為,並修正所有發現的違規事項。
找出並修正外鍵違規
如要瞭解如何找出並修正外鍵違規事項,請參閱「找出並修正外鍵違規事項」。
監控重新建立索引作業
有時,重新建立索引作業可能會遭到其他工作階段封鎖。建議您每 4 小時檢查一次。如果重新建立索引作業遭到封鎖,您可以取消封鎖工作階段,讓重新建立索引作業完成。
請完成下列步驟,找出封鎖和等待中的工作階段,然後在 INDEX 作業中取消這些工作階段:
如要找出造成阻礙的工作階段,請執行下列查詢:
程式碼範例
SELECT pid, usename, pg_blocking_pids(pid) AS blocked_by, query AS blocked_query FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0;
如要取消工作階段,請使用先前查詢中的封鎖工作階段 PID 執行下列查詢:
程式碼範例
SELECT pg_cancel_backend(PID);
確認索引一致
您必須繼續檢查每個不一致的索引,確認索引不一致。修正所有執行個體的不一致索引和鍵違規事項後,請按照先前章節的步驟檢查是否仍有問題: