Re: Query returns no rows in pg_basebackup cluster

On 5/21/20 3:47 PM, Tom Lane wrote:
> Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> writes:
>> On 5/21/20 1:20 PM, Andrus wrote:
>>> In windows pg_basebackup was used to create base backup from Linux server.
>
>> Are you referring to two different instances of Postgres on Windows?
>
> No, what it sounds like is the OP tried to physically replicate a
> database on another platform with completely different sorting rules.
> Which means all his text indexes are corrupt according to the
> destination platform's sorting rules, which easily explains the
> observed misbehavior (ie, index searches not finding the expected rows).

Well what I was trying to figure out was:

"Windows server this query returns 0 rows.

In Windows server same query using like

select * from firma1.desktop where baas like '_LOGIFAI'

returns properly 16 rows. "

My suspicion is that first case is for the replicated database and
failed for the reasons you mentioned and that the second case is for a
'native' Windows instance. Just trying to get confirmation.

>
> REINDEX would fix it. But the major point here is you can't just ignore
> a collation mismatch, which in turn implies that you can't do physical
> replication from Linux to Windows, or vice versa (and most other
> cross-platform cases are just as dangerous).
>
>>> Database in Windows is in read-only (recovery) mode so it cannot changed.
>
> Then you might as well just rm -rf it (or whatever the equivalent Windows
> incantation is). On Windows, that database is broken and useless.
>
> regards, tom lane
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com