Thanks for the explanation! Yes, it seems this is not related to collations, because I see an integer duplicate there now, thanks for pointing to this. Maybe the restoration of the database is started twice somehow, that produces that duplicates, so I will try to restart the migration process from scratch.
On Sun, Mar 12, 2023 at 12:53 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Erik Wienhold <ewie@ewie.name> writes: > On 11/03/2023 19:23 CET Alexey Murz Korepov <murznn@gmail.com> wrote: >> Could anyone suggest to me the ways to change a database collation with >> removing all the duplicates, caused by this change?
> Collations can only affect uniqueness if they are nondeterministic or if you > have functional indexes, e.g. using lower(text) for a case-insensitive unique > index. Otherwise the collations only affect text ordering.
Yeah. I suspect that what actually happened here was a previous change in the host system's sort ordering (cf [1]), leading to text indexes becoming functionally corrupt and unable to enforce uniqueness correctly, after which you accumulated some unintentional duplicates. If you try reindex'ing on the source database you'll probably find that it fails with the same errors. I don't know of any automatic tools for fixing up such duplications, and wouldn't trust one hugely anyway --- you'll probably need manual curation of the fixes.
