postgresql and collation problem when importing

Question

I seem to be stuck trying to import a database that has been created on a Linux system to my OSX. Some of the table definitions seem to expect a specific collation type, and I for the life of me cannot figure out what is wrong.

When I do my import, I get bunch of errors, but the first relevant that then causes other errors seems to be this one:

pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 207; 1259 16585 TABLE drinks <<removed>>
pg_restore: error: could not execute query: ERROR:  collation "pg_catalog.ja_JP.utf8" for encoding "UTF8" does not exist
LINE 14:     "phonetic_name" character varying COLLATE "pg_catalog"."...
                                               ^
Command was: CREATE TABLE "public"."drinks" (
    <<removed>>
    "phonetic_name" character varying COLLATE "pg_catalog"."ja_JP.utf8"
);

As I understood this, I needed pg_catalog.ja_JP.utf8, and with Laurenz Albe's answer below I was able to create it - If I look at my pg_collation table now, I see:

jlumme=# select * from pg_collation where collname like 'ja%';
  oid  |  collname   | collnamespace | collowner | collprovider | collisdeterministic | collencoding | collcollate |  collctype  | collversion
-------+-------------+---------------+-----------+--------------+---------------------+--------------+-------------+-------------+-------------
 12419 | ja_JP       |            11 |        10 | c            | t                   |            6 | ja_JP       | ja_JP       |
 12482 | ja_JP.UTF-8 |            11 |        10 | c            | t                   |            6 | ja_JP.UTF-8 | ja_JP.UTF-8 |
 12500 | ja_JP.eucJP |            11 |        10 | c            | t                   |            1 | ja_JP.eucJP | ja_JP.eucJP |
 12566 | ja_JP       |            11 |        10 | c            | t                   |            1 | ja_JP.eucJP | ja_JP.eucJP |
 13011 | ja-x-icu    |            11 |        10 | i            | t                   |           -1 | ja          | ja          | 153.14.39
 13012 | ja-JP-x-icu |            11 |        10 | i            | t                   |           -1 | ja-JP       | ja-JP       | 153.14.39
 17898 | ja_JP.utf8  |            11 |        10 | c            | t                   |            6 | ja_JP.UTF-8 | ja_JP.UTF-8 |

But my pg_restore still fails with same error.

Collation settings on server:

<<removed>>= # select * from pg_collation where collname like 'ja%';
   collname   | collnamespace | collowner | collprovider | collencoding | collcollate  |  collctype   | collversion
--------------+---------------+-----------+--------------+--------------+--------------+--------------+-------------
 ja_JP        |            11 |        10 | c            |            1 | ja_JP        | ja_JP        |
 ja_JP.eucjp  |            11 |        10 | c            |            1 | ja_JP.eucjp  | ja_JP.eucjp  |
 ja_JP.ujis   |            11 |        10 | c            |            1 | ja_JP.ujis   | ja_JP.ujis   |
 ja_JP.utf8   |            11 |        10 | c            |            6 | ja_JP.utf8   | ja_JP.utf8   |
 japanese     |            11 |        10 | c            |            1 | japanese     | japanese     |
 japanese.euc |            11 |        10 | c            |            1 | japanese.euc | japanese.euc |
 ja_JP        |            11 |        10 | c            |            6 | ja_JP.utf8   | ja_JP.utf8   |
 ja-x-icu     |            11 |        10 | i            |           -1 | ja           | ja           | 58.0.0.50
 ja-JP-x-icu  |            11 |        10 | i            |           -1 | ja_JP        | ja_JP        | 58.0.0.50

The differences of locales between the two machines (locale -a):

Linux       |  OSX
ja_JP        | ja_JP.SJIS
ja_JP.eucjp  | ja_JP
ja_JP.ujis   | ja_JP.UTF-8
ja_JP.utf8   | ja_JP.eucJP
japanese     |
japanese.euc |

I am on postgresql 13.3 version, if that matters.

Answer 1

The dump was probably generated on a system with a different C library version.

You can create the missing collation like the existing one:

CREATE COLLATION pg_catalog."ja_JP.utf8" (
   PROVIDER = libc,
   LC_COLLATE = "ja_JP.UTF-8",
   LC_CTYPE = "ja_JP.UTF-8"
);

Then your dump should import just fine.