我似乎一直在尝试将在Linux系统上创建的数据库导入到我的OSX。一些表定义似乎期望有一个特定的排序规则类型,而我一辈子都无法弄清楚哪里出了问题。
当我进行导入时,我会收到一堆错误,但第一个导致其他错误的相关错误似乎是:
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"
);
正如我所理解的,我需要pg_catalog.ja_JP.utf8
,通过下面Laurenz Albe的回答,我能够创建它——如果我现在查看我的pg_collation表,我会看到:
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 |
但我的pg_restore仍然失败,并出现同样的错误。
服务器上的排序规则设置:
<<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
两台机器之间的区域设置差异(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 |
如果这很重要的话,我使用的是postgresql13.3版本。
转储可能是在具有不同C库版本的系统上生成的。
您可以创建与现有排序规则类似的缺失排序规则:
CREATE COLLATION pg_catalog."ja_JP.utf8" (
PROVIDER = libc,
LC_COLLATE = "ja_JP.UTF-8",
LC_CTYPE = "ja_JP.UTF-8"
);
那么你的垃圾箱应该可以导入。