Hi!
No, what it sounds like is the OP tried to physically replicate a
database on another platform with completely different sorting rules.
The sorting rules for this locale must be the same in both platforms.
Only locale names are different. It looks like windows server does not recognize Linux locale name.
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).
Lot of queries seems working properly.
REINDEX would fix it.
REINDEX throws error
ERROR: cannot execute REINDEX during recovery
SQL state: 25006
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 is used in recovery mode to find proper recovery point and to get data from it in this point.
Locales are actually same. In windows Postgres does not recognize Linux locale name.
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.
Most queries seems to work.
Database should examined to get accidently deleted data from it.
Is making it read-write and index only solution or can it fixed in read-only database also, e-q forcing same local in postgres.conf
Andrus.
