We use PostgreSQL 9.1.7 on Ubuntu Linux 12.04 on a master server and PostgreSQL 9.1.7 on FreeBSD 9.0-RELEASE on a replica server. The replica and master servers return different results on the same SQL query. A query plan shows that an index (a BTree one, we do not use hash indexes at all) is used to get the result so it looks like an index is in inconsistent or incomplete state on the replica server. The query on the master server:
db1=# select id from users where email='[email protected]'; id --------- 1698116 (1 row) db1=#
The query on the replica server:
db1=> select id from users where email='[email protected]'; id ---- (0 rows) db1=> select created_at from users where id=1698116; created_at ---------------------------- 2013-03-04 10:40:05.221214 (1 row) db1=>
As you can see the replica DB already contains a user with proper ID so the data is in place but just not indexed yet for some reason. We double checked the replica was in receiving/reapplying state so this was not a temporary outage. The user never got indexed. We also used to experience similar problems with PostgreSQL 9.0 on CentOS 5.6 so we don't think this is something FreeBSD- or PostgreSQL 9.1-specific.
We use the replica server to run lots of heavy SQL queries, can this be a root of the problem? Anyway how can we efficiently detect and prevent situations like this in the future? The replica was not down today and there was no single error line in logs so we detected this inconsistency only by occasion.