Postgres Pro
Downloads
Home   >   mailing lists

Re: Linux replication to FreeBSD problem - Mailing list pgsql-general

From Jov
Subject Re: Linux replication to FreeBSD problem
Date
Msg-id CADyrUxNmoLYML3YhHSy8ff831XQqcX7DmUeYt3jsmJo0JRHeBA@mail.gmail.com
Whole thread Raw
In response to Re: Linux replication to FreeBSD problem  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-general
Tree view
Yes,it is locale problem.
I do some more testing,and find that in my DB locale which is zh_CN.UTF-8,the indexes on FreeBSD slave can works if the indexed data is lower case ascii,it can't find data contain upper case.

Explicit set the column collate to "C" can solve the problem.
I will recreate all the index with collate "C".

Thanks very much!



2014-08-20 23:36 GMT+08:00 Tom Lane <tgl@sss.pgh.pa.us>:
Adrian Klaver <adrian.klaver@aklaver.com> writes:
> On 08/20/2014 07:53 AM, Jov wrote:
>> I setup a PG 9.3.5 master on CentOS 6 x86_64,and 2 screaming replicaton
>> slaves,one on CentOS6 x86_64,the other on FreeBSD 10 amd64.
>> The replication work fine for a week,But today I find a problem on sql
>> running on FreeBSD:simple sql use index do not return result.If I
>> disable the index ,use seqscan,then I can get correct result.

> I would say FreeBSD is not similar enough to Linux(CentOS) to make this
> work reliably with binary replication.

The most likely theory as to the source of the problem is that the locale
names used by the CentOS machine are not recognized by the FreeBSD OS,
and/or imply slightly different sort orderings.  So a text index that's
correctly sorted according to the CentOS machine is not correctly sorted
according to FreeBSD, leading to search failures.

You could probably make this case work reliably if you used C locale on
both systems; the behavior of that is pretty portable.

                        regards, tom lane


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

pgsql-general by date:

Previous
From: David G Johnston
Date:
Subject: Re: Query planner question
Next
From: Patrick Dung
Date:
Subject: Re: Use of 'now' constant datatype in view to take advantage of partitioned table