Postgresql – Why is searching by one varchar column not working correctly on replica

postgresqlpostgresql-9.6replicationselect

PostgreSQL: 9.6, master, replica.
Async replication, wal archiving.

We detected that if we execute a select query on a varchar column against a replica, then the results return no rows, but if we execute against a master then the results will be correct.

SELECT id FROM platform WHERE name = 'test'
  • Master: 10
  • Replica: now rows
SELECT name FROM platform WHERE id = 10
  • Master: test
  • Replica: test
SELECT id FROM platform WHERE name in ('test', 'test1')
  • Master: 10, 11
  • Replica: 11
SELECT name FROM platform WHERE id in (10, 11)
  • Master: test, test1
  • Replica: test, test1

I didn't find any information about such issues and I don't know how to solve it.

Best Answer

As you say the column is indexed, try dropping and creating the index again. It might have gotten corrupted during replication.