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.

  • Yes (both queries), returns same row count on master and replica. – arzonus Dec 4 at 17:32
  • 2
    Plausible explanation: your name column is indexed and your replica has a different sort order than your master for the same collation. See if you're concerned by recent glibc changes in this area: postgresql.verite.pro/blog/2018/08/27/glibc-upgrade.html – Daniel Vérité Dec 4 at 18:23
  • @DanielVérité Yes, the column is indexed. I see replica has a different sort order on same queries. Thank you! – arzonus Dec 5 at 6:29

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

New contributor
dullgiulio is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.