13.x, stream replication and locale(?) issues

From: "Eugene M(dot) Zheganin" <eugene(at)zhegan(dot)in>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: 13.x, stream replication and locale(?) issues
Date: 2023-02-28 11:08:47
Message-ID: 2020842e-1841-4a9b-63fd-58a3628ede68@zhegan.in
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello.

I have a 13.4 pgsql instance on Linux which has a bunch of databases
with UTF-8/ru_RU.utf8 encoding/collation set.

I've stream replicated it to the 13.10 instance on FreeBSD (may be this
is the part where it all has gone wrong way, but at this moment I
believe streaming replication should work since both run one major
version). And the funny things started to happen.

First, the instance said

sql: error: FATAL:  database locale is incompatible with operating system
DETAIL:  The database was initialized with LC_COLLATE "ru_RU.utf8",
 which is not recognized by setlocale().
HINT:  Recreate the database with another locale or install the missing
locale.

I've decided to go the easy way and just symlinked the ru_RU.UTF-8
locale to ru_RU.utf8, because from my understanding it's the same
locale, just cased differently (seems like I was totally wrong).

The database was running fine for quite some time and then I've got
tonnes of complains about weird SQL queries behavior. I'll just
illustrate the difference:

*Master instance:*

tpark-rbac=# select id, service_name from micro_service where
service_name='profile';
id | service_name
----+--------------
17 | profile
(1 row)

tpark-rbac=# select operation_id, micro_service_id from
micro_service_operation where operation_id='admin.member-request.list';
      operation_id        | micro_service_id
---------------------------+------------------
admin.member-request.list |               17
(1 row)

tpark-rbac=# SELECT ms.id FROM micro_service_operation mso,
micro_service ms where mso.micro_service_id=ms.id and
ms.service_name='profile' AND mso.operation_id='admin.member-request.list';
id
----
17
(1 row)

*Standby instance:*

tpark-rbac=# select id, service_name from micro_service where
service_name='profile';
id | service_name
----+--------------
17 | profile
(1 row)

tpark-rbac=# select operation_id, micro_service_id from
micro_service_operation where operation_id='admin.member-request.list';
      operation_id        | micro_service_id
---------------------------+------------------
admin.member-request.list |               17
(1 row)

tpark-rbac=# SELECT ms.id FROM micro_service_operation mso,
micro_service ms where mso.micro_service_id=ms.id and
ms.service_name='profile' AND mso.operation_id='admin.member-request.list';
id
----
(0 rows)

The thing is, as it seems, that the value "admin.member-request.list"
isn't quite "admin.member-request.list" on a standby:

tpark-rbac=# SELECT ms.id, mso.operation_id, ms.service_name,
length(mso.operation_id) as msolength FROM micro_service_operation mso,
micro_service ms where mso.micro_service_id=ms.id and
ms.service_name='profile' and mso.operation_id like
'admin.member-request.list%';
id |       operation_id        | service_name | msolength
----+---------------------------+--------------+-----------
17 | admin.member-request.list | profile      |        25
(1 row)

tpark-rbac=# SELECT ms.id, mso.operation_id, ms.service_name,
length(mso.operation_id) as msolength FROM micro_service_operation mso,
micro_service ms where mso.micro_service_id=ms.id and
ms.service_name='profile' and mso.operation_id like
'admin.member-request.list';
id | operation_id | service_name | msolength
----+--------------+--------------+-----------
(0 rows)

And I suppose this is because of the locale hack.

Now a bunch of stupid questions:

1) why the utf-8 locales behave differently when working with what
appears to be clearly latin1 characters ? From my understanding latin1
characters shouldn't be affected at all.

2) why does the query where I just ask for equality of the value to the
"admin.member-request.list" work perfectly when FROM clause contains one
table, but fails as soon as FROM starts to contain multiple tables ?

3) how do I fix it ? Should I take locale sources for ru_RU.utf8 on
Linux and compile it on FreeBSD  - will it help ?

4) the most disappointing thing is that I have long-term relationship
with pgsql replication and to this day I was able to do any kinds of
juggling - replicating from Linux to Solaris, from Solaris to FreeBSD
and vice-versa, all possible combinations as long as UTF-8 was the
encoding - what changed now ?

5) will the downgrading to 13.4 on the standby help me ?

Thanks.

Eugene.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ian Lawrence Barwick 2023-02-28 13:41:42 Re: Quit currently running query
Previous Message Albert Cornelius 2023-02-28 10:53:33 Quit currently running query