Linux replication to FreeBSD problem
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.
psql (9.3.5)
Type "help" for help. ^
xx=> explain analyze verbose select order_id from orders where order_id =
'AAGJfwAAAAJYUzD3';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on public.orders (cost=4.47..31.06 rows=7 width=17)
(actual time=0.317..0.317 rows=0 loops=1)
Output: order_id
Recheck Cond: ((orders.order_id)::text = 'AAGJfwAAAAJYUzD3'::text)
-> Bitmap Index Scan on orders_order_id_idx (cost=0.00..4.47 rows=7
width=0) (actual time=0.252..0.252 rows=0 loops=1)
Index Cond: ((orders.order_id)::text = 'AAGJfwAAAAJYUzD3'::text)
Total runtime: 0.753 ms
(6 rows)
xx=> set enable_indexonlyscan to f;
SET
xx=> set enable_indexscan to f;
SET
xx=> set enable_bitmapscan to f;
SET
forex=> explain analyze verbose select order_id from orders where order_id
= 'AAGJfwAAAAJYUzD3';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Seq Scan on public.orders (cost=0.00..2185.69 rows=7 width=17) (actual
time=70.003..71.238 rows=1 loops=1)
Output: order_id
Filter: ((orders.order_id)::text = 'AAGJfwAAAAJYUzD3'::text)
Rows Removed by Filter: 63481
Total runtime: 71.379 ms
(5 rows)
So,Is this a un-support replication method?
Jov
blog: http:amutu.com/blog <http://amutu.com/blog>
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.
So,Is this a un-support replication method?
https://wiki.postgresql.org/wiki/Binary_Replication_Tutorial#5_Minutes_to_Simple_Replication
You must have the right setup to make this work:
2 servers with similar operating systems (e.g both Linux 64-bit).
I would say FreeBSD is not similar enough to Linux(CentOS) to make this
work reliably with binary replication.
Jov
blog: http:amutu.com/blog <http://amutu.com/blog>
--
Adrian Klaver
adrian.klaver@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
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!
Jov
blog: http:amutu.com/blog <http://amutu.com/blog>
2014-08-20 23:36 GMT+08:00 Tom Lane <tgl@sss.pgh.pa.us>:
Show quoted text
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