0

Today I upgraded debian twice (10->11 and 11->12). The standard upgradecluster procedure went well the first time (pgsql 11->13), but it failed the second time (pgsql 13->15). Searching back the reason was:

ERROR: collation "pg_catalog.C.UTF-8" for encoding "UTF8" does not exist LINE 22: "linkedinurl" character(255) COLLATE "pg_catalog"."C.UTF...

I assume that means that there is some alternate encoding/collation used for those fields that was removed.

The actual question is twofold:

Is there a smarter workaround for that besides examining all textual fields, resetting collations to see what could be wrong?

And second, assuming I get that fixed in the pgsql 13 instance, can I again dump the 15 cluster and upgrade the 13 again using

sudo pg_dropcluster --stop 15 main  
sudo pg_upgradecluster 13 main

?

Or do I need different steps for that?

Improve this question

1 Answer 1

Reset to default
0 
DO $$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN
        SELECT table_schema, table_name, column_name
        FROM information_schema.columns
        WHERE collation_name = 'pg_catalog.C.UTF-8'
    LOOP
        EXECUTE format(
            'ALTER TABLE %I.%I ALTER COLUMN %I SET DATA TYPE character varying COLLATE "C";',
            rec.table_schema, rec.table_name, rec.column_name
        );
    END LOOP;
END $$;
Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.