PostgreSQL: Verifying Index Corruption

Although glibc has some breaking changing with every minor version release but with v2.28, there are lots of breaking changes which will corrupt almost all Postgres indexes. In order to identify broken indexes, we can make use of amcheck tool which is available as contrib from Postgres 10 but are available as external packages for version before Postgres 10.

Tried to upgrade a Postgres 9.6 container from stretch (glibc v2.24) to buster (glibc v2.28).

1. Start Postgres server in stretch container.

❯ docker run --rm --name=postgres -v /tmp/pgdata:/var/lib/postgresql/data -e POSTGRES_PASSWORD=foobar -d postgres:9-stretch
a0705f5e06a8df3c329bc16b62828d5c2ea56dfd679ada44fe0dfd249e1053bd

2. Insert some data.

❯ docker exec -it postgres /bin/bash
root@a0705f5e06a8:/# psql -U postgres
psql (9.6.24)
Type "help" for help.
 
postgres=# CREATE TABLE foobar(name VARCHAR(10), id INT PRIMARY KEY);
CREATE TABLE
postgres=# INSERT INTO "foobar" (name, id) VALUES ('a-a', 1);
INSERT 0 1
postgres=# INSERT INTO "foobar" (name, id) VALUES ('a a', 2);
INSERT 0 1
postgres=# INSERT INTO "foobar" (name, id) VALUES ('a+a', 3);
INSERT 0 1
postgres=# INSERT INTO "foobar" (name, id) VALUES ('aa', 4);
INSERT 0 1
postgres=# SELECT * FROM foobar ORDER BY name;
 name | id
------+----
 aa   |  4
 a a  |  2
 a-a  |  1
 a+a  |  3
(4 rows)

3. Create index on name column which is of type text.

postgres=# CREATE INDEX foobar_name_idx ON foobar (name);
CREATE INDEX
postgres=# \d foobar
           Table "public.foobar"
 Column |         Type          | Modifiers
--------+-----------------------+-----------
 name   | character varying(10) |
 id     | integer               | not null
Indexes:
    "foobar_pkey" PRIMARY KEY, btree (id)
    "foobar_name_idx" btree (name)

4. Install and add amcheck extension.

# apt-get update && apt-get install -y postgresql-9.6-amcheck
....
# psql -U postgres
psql (9.6.24)
Type "help" for help.
 
postgres=# CREATE EXTENSION amcheck_next;
CREATE EXTENSION
postgres=# \dx
                           List of installed extensions
     Name     | Version |   Schema   |                Description
--------------+---------+------------+--------------------------------------------
 amcheck_next | 2       | public     | functions for verifying relation integrity
 plpgsql      | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

5. Use amcheck’s bt_index_check function to verify index corruption.

postgres=# SELECT bt_index_check(c.oid)
FROM pg_index i
JOIN pg_class c ON i.indexrelid = c.oid
WHERE c.relname IN ('foobar_name_idx');
 bt_index_check
----------------
 
(1 row)

No error means no index corruption.

6. Kill the Stretch Postgres container and start Buster Postgres container with same data directory and same Postgres version.

❯ docker rm -f postgres
postgres
❯ docker run --rm --name=postgres -v /tmp/pgdata:/var/lib/postgresql/data -e POSTGRES_PASSWORD=foobar -d postgres:9-buster
370c14d6118c4da4e17b8f46ef8124950fd243f9ca5925409e99422338558682

7. Use amcheck’s bt_index_check function to verify index corruption.

postgres=# SELECT * from foobar ORDER BY name;
 name | id
------+----
 a a  |  2
 a+a  |  3
 a-a  |  1
 aa   |  4
(4 rows)

We can see the sorting order has changed.

postgres=# SELECT bt_index_check(c.oid)
FROM pg_index i
JOIN pg_class c ON i.indexrelid = c.oid
WHERE c.relname IN ('foobar_name_idx');
ERROR:  item order invariant violated for index "foobar_name_idx"
DETAIL:  Lower index tid=(1,1) (points to heap tid=(0,4)) higher index tid=(1,2) (points to heap tid=(0,2)) page lsn=0/0.

Error means this index is most likely corrupted due to glibc version update.

8. Reindex and run amcheck’s bt_index_check function again and see if the error goes away.

postgres=# REINDEX INDEX "foobar_name_idx";
REINDEX
postgres=# SELECT bt_index_check(c.oid)
FROM pg_index i
JOIN pg_class c ON i.indexrelid = c.oid
WHERE c.relname IN ('foobar_name_idx');
 bt_index_check
----------------
 
(1 row)