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.
