Re: Query returns no rows in pg_basebackup cluster

On 5/21/20 3:47 PM, Tom Lane wrote:
> Adrian Klaver <adrian.klaver@aklaver.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.klaver@aklaver.com