CODEWITHSUNDEEP
databasepostgresqlindexingpostgresql-9.3php-pgsql
charak
charak

Reputation: 187

ERROR: right sibling's left-link doesn't match: block 19 links to 346956 instead of expected 346955 in index "pg_depend_reference_index"

I have a DB in postgres. The DB is big with total size over 4TB and over 500,000 tables and many indexes. The DB is over 4 yr old. Recently, the Pgsql DB server was not starting up, so I did the following to get it started again:

/usr/pgsql-9.3/bin/pg_resetxlog -f  /var/lib/pgsql/9.3/data
/usr/pgsql-9.3/bin/pg_ctl -D /var/lib/pgsql/9.3/data stop
/usr/pgsql-9.3/bin/pg_ctl -D /var/lib/pgsql/9.3/data start
/usr/pgsql-9.3/bin/pg_ctl -D /var/lib/pgsql/9.3/data stop
systemctl restart postgresql-9.3

Since then I am getting the following error whenever I try to create a new table in the DB:

mps_schools=> create table test_test(hello int);
ERROR:  right sibling's left-link doesn't match: block 19 links to 346956 instead of expected 346955 in index "pg_depend_reference_index"

I have tried re-indexing the DB, but it doesnt work. What more can I do?

Upvotes: 3

Views: 5741

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246848

pg_resetxlog destroyed your database, which is something that can easily happen, which is why you don't call it just because you don't get the database started. It's something of a last ditch effort to get a corrupted database up.

What can you do?

  1. Best solution: restore from a backup from before you ran pg_resetxlog.

  2. Perform an offline backup of your database.

    Then start the database in single user mode:

    postgres --single -P -D /your/database/directory yourdbname

    Then try to reindex pg_depend:

    REINDEX TABLE pg_catalog.pg_depend;

    Exit the single user session, restart the database, run pg_dumpall to dump the database (and hope that it works), create a new database cluster with initdb and import the dump.

    Don't continue using the cluster where you ran pg_resetxlog.

Upvotes: 4

Related Questions