Well the obvious fix is to run PostgreSQL on the same OS and version of OS +
PostgreSQL. :)
1. Have you verified that the PK index was built successfully during the
restore? Or, if you used pgbasebackup, then since it is physical not logical
replication something may be mismatched. Tried re-indexing the PK on server2?
If the pk index is broken for some reason on server 2, then this could explain
it because Pg may not use indexes at all when doing LIKEs ... even with the %
at the end ...if your locale isn't appropriate. So the explain analyze may
show that the LIKEs are not using the index even if LIKE 'xyz%'.
2. Aside from a bug with Postgres on the Mac, it may possibly be due to a
locale/encoding problem so that's the second thing I'd check. Maybe the @ sign
is throwing it off for the locale? (since the problems seem to be around the
email addresses on slave 2). Not sure why LIKE seems to work though in the
case.
3. Make sure Pg is the same version on all three.
select version();
Good luck. (But I would not do streaming replication across different OSs like
this.)
Sent from my Verizon Wireless 4G LTE smartphone
-------- Original message --------
From: Musall Maik <m...@selbstdenker.ag>
Date: 05/20/2015 8:25 AM (GMT-05:00)
To: WebObjects-Dev Apple <webobjects-dev@lists.apple.com>
Subject: strange SELECT results in postgresql slave
Hi,
I know many of you work with PostgreSQL so I'll try this here.
I have a strange case where a SELECT for a primary key returns 0 rows on one
slave, while it returns the correct 1 row on another slave and on the master.
It does however return that row on all slaves when queried with LIKE and
trailing or leading wildcard.
psql version is 9.3.5, because that's what comes with Ubuntu 14.04 LTS
Master runs Ubuntu 14.04 LTS
Slave 1 runs also Ubuntu 14.04 LTS
Slave 2 runs Mac OS X 10.7, pgsql installed via homebrew
Both slaves are configured with streaming replication, and I've been using that
setup for years, starting with psql 9.1, with no problems so far. Suspecting
some weird problem, I already re-initialized slave 2 with a fresh backup and
started replication from the beginning, so the database is fresh from a master
copy, and is verified to be current.
2015-05-19 20:53:43.937 CEST LOG: entering standby mode
2015-05-19 20:53:43.974 CEST LOG: redo starts at 31/3F000028
2015-05-19 20:53:45.522 CEST LOG: consistent recovery state reached at
31/40CCE6E8
2015-05-19 20:53:45.523 CEST LOG: database system is ready to accept read only
connections
2015-05-19 20:53:45.604 CEST LOG: started streaming WAL from primary at
31/41000000 on timeline 1
So here's the query.
SELECT * FROM MyTable WHERE email = 'f...@example.com';
This returns 1 row on master and slave 1, but 0 on slave 2, while this query:
SELECT * FROM MyTable WHERE email LIKE 'f...@example.com%';
or this one
SELECT * FROM MyTable WHERE email LIKE '%f...@example.com';
returns the correct 1 row on all three systems. Note that this works with the
wildcard on either end, or also somewhere in the middle, doesn't matter. Note:
"email" is the primary key on this table.
This behaviour is the same with any address to be queried, and is also the same
on a similar second table. This does NOT occur on any other table, which all
have integer primary keys. There is also no problem when I select for other
attributes on these tables.
Does anyone have a hint?
Thanks
Maik
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (Webobjects-dev@lists.apple.com)
Help/Unsubscribe/Update your Subscription:
https://lists.apple.com/mailman/options/webobjects-dev/mcassidy%40escholar.com
This email sent to mcass...@escholar.com
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (Webobjects-dev@lists.apple.com)
Help/Unsubscribe/Update your Subscription:
https://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com
This email sent to arch...@mail-archive.com
