0

Background: PostgreSQL service faced some corruption after the server power outage. And I used the pg_resetwal command to fix that issue. As suggested here

After the service successfully starts I'm facing this weird issue.

When I query with the id column, it doesn't result anything even the queried data is there and column type matches

# SELECT id, email FROM users WHERE id=1;
 id | email
----+-------
(0 rows)

But if I query with other columns (in this example, email column), it results

# SELECT id, email FROM users WHERE email='john@gmail.com';
 id |          email
----+--------------------------
 1 | john@gmail.com
(1 row)

Any suggestions?

Postgresql version: 12.7

Odko
  • 151
  • 1
  • 1
  • 8
  • 1
    Is this id column numeric or varchar? Can it contain spaces? You could try something like SELECT id, email FROM users WHERE id LIKE '%1%' to check this. If this doesn't help, could you please provide the create table command of this table? – Jonas Metzler May 10 '22 at 12:18
  • 1
    Did you try to recreate the indexes? `reindex users;` –  May 10 '22 at 12:33
  • @JonasMetzler, the id column is an integer type. So the LIKE operator is not working. – Odko May 11 '22 at 08:37

1 Answers1

1

OK, so after you have managed to get your PostgreSQL instance running what you should have done is:

  1. Take an immediate backup of all your databases
  2. Audit them + check for any damage
  3. Drop the existing dbs and restore from the audited backups
  4. Identify the mis-configuration in your server that resulted in the data corruption.

I assume you haven't done these things and have a corrupted index.

Your hardware is lying to PostgreSQL about persisting data to disk. It isn't safe to trust the existing data - anything that was being updated (not just directly updated by you, but vacuum processess too) is suspect.

Richard Huxton
  • 21,516
  • 3
  • 39
  • 51