13.x, stream replication and locale(?) issues - Mailing list 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.