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.

Laurenz Albe · Accepted Answer · 2021-06-02 10:54:05Z

4

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.

answered Jun 2, 2021 at 10:54

Laurenz Albe

257k22 gold badges314 silver badges390 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

julumme Over a year ago

Thanks for the suggestion Laurenz. The provider keyword seemed to keep tripping postgres9.6 so I upgraded the DB to 13.3 on my mac and I was able to create the collation. However, I am still in the same situation, I keep getting pg_restore: error: could not execute query: ERROR: collation "pg_catalog.ja_JP.utf8" for encoding "UTF8" does not exist. I've edited the original question to add latest information and formatting

Laurenz Albe Over a year ago

You have to create the database, connect to it and create the collation in that database. Then restore the dump into that database.

julumme Over a year ago

Oh, doh. That was the missing link

Collectives™ on Stack Overflow

postgresql and collation problem when importing

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related