3

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?

charak
  • 187
  • 3
  • 15

1 Answers1

4

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • I dont have a backup of the DB, so cannot do number 1. Will try and do number 2. Will this solution work ?https://lxadm.com/Repairing_broken_PostgreSQL_databases_/_tables – charak Feb 19 '18 at 06:48
  • There are a few shortcomings in that procedure: `\d` will only list the tables on the `search_path`, so you might miss some tables. Also, if your system catalogs are damaged, `\d` might not work. If there is one message you should take home from this, it should be: backups are important. – Laurenz Albe Feb 19 '18 at 08:08
  • I got fixed with your procedure 2. Thanks!! – charak Feb 24 '18 at 13:28