Solved postgresql12-server upgrade fails

I am doing a step-by-step upgrade of the postgresql-server port (12.8 -> 13.4_1) as described in /usr/ports/UPDATING. Everything works fine. But the command under root # su -l postgres -c "/usr/local/bin/initdb --encoding=utf-8 --lc-collate=C -D /var/db/postgres/data13 -U postgres" returns error:

Code:
# su -l postgres -c "/usr/local/bin/initdb --encoding=utf-8 --lc-collate=C -D /var/db/postgres/data13 -U postgres"
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "C".
The default text search configuration will be set to "english".

Data page checksums are disabled.

creating directory /var/db/postgres/data13 ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... W-SU
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... 2021-10-31 12:17:41.051 MSK [93380] FATAL:  could not open collator for locale "und": U_FILE_ACCESS_ERROR
2021-10-31 12:17:41.051 MSK [93380] STATEMENT:  SELECT pg_import_system_collations('pg_catalog');

child process exited with exit code 1
initdb: removing data directory "/var/db/postgres/data13"

OS: FreeBSD 12.2-RELEASE-p7 GENERIC amd64

The last two upgrades of the postgresql-server port on two different hosts worked properly (10 -> 12, 11 -> 12).

How can we solve this issue?

WBR, alexxis.
 
Code:
 # locale
LANG=en_US.UTF-8
LC_CTYPE="en_US.UTF-8"
LC_COLLATE="en_US.UTF-8"
LC_TIME="en_US.UTF-8"
LC_NUMERIC="en_US.UTF-8"
LC_MONETARY="en_US.UTF-8"
LC_MESSAGES="en_US.UTF-8"
LC_ALL=

NB. Previos upgrades were successful.
 
That looks to be in order. Then I'm wondering where it gets that und from.

Do you get the same error if you use service postgresql initdb? I did an upgrade recently on 13.0 and that went without a hitch. So it might be something specific to 12.2.
 
That looks to be in order. Then I'm wondering where it gets that und from.

Do you get the same error if you use service postgresql initdb? I did an upgrade recently on 13.0 and that went without a hitch. So it might be something specific to 12.2.
I think that "und" means "undefined".

And I got the same error if I use service postgresql initdb.
 
I think that "und" means "undefined".
Sounds plausible, but it begs the question what exactly it is that's undefined.

Does locale -a show a nice proper listing?
Code:
C
C.UTF-8
POSIX
af_ZA.ISO8859-1
af_ZA.ISO8859-15
af_ZA.UTF-8
am_ET.UTF-8
ar_AE.UTF-8
ar_EG.UTF-8
ar_JO.UTF-8
ar_MA.UTF-8
ar_QA.UTF-8
ar_SA.UTF-8
be_BY.CP1131
be_BY.CP1251
be_BY.ISO8859-5
be_BY.UTF-8
bg_BG.CP1251
bg_BG.UTF-8
ca_AD.ISO8859-1
ca_AD.ISO8859-15
ca_AD.UTF-8
ca_ES.ISO8859-1
ca_ES.ISO8859-15
ca_ES.UTF-8
ca_FR.ISO8859-1
ca_FR.ISO8859-15
ca_FR.UTF-8
ca_IT.ISO8859-1
ca_IT.ISO8859-15
ca_IT.UTF-8
cs_CZ.ISO8859-2
cs_CZ.UTF-8
da_DK.ISO8859-1
da_DK.ISO8859-15
da_DK.UTF-8
de_AT.ISO8859-1
de_AT.ISO8859-15
de_AT.UTF-8
de_CH.ISO8859-1
de_CH.ISO8859-15
de_CH.UTF-8
de_DE.ISO8859-1
de_DE.ISO8859-15
de_DE.UTF-8
el_GR.ISO8859-7
el_GR.UTF-8
en_AU.ISO8859-1
en_AU.ISO8859-15
en_AU.US-ASCII
en_AU.UTF-8
en_CA.ISO8859-1
en_CA.ISO8859-15
en_CA.US-ASCII
en_CA.UTF-8
en_GB.ISO8859-1
en_GB.ISO8859-15
en_GB.US-ASCII
en_GB.UTF-8
en_HK.ISO8859-1
en_HK.UTF-8
en_IE.ISO8859-1
en_IE.ISO8859-15
en_IE.UTF-8
en_NZ.ISO8859-1
en_NZ.ISO8859-15
en_NZ.US-ASCII
en_NZ.UTF-8
en_PH.UTF-8
en_SG.ISO8859-1
en_SG.UTF-8
en_US.ISO8859-1
en_US.ISO8859-15
en_US.US-ASCII
en_US.UTF-8
en_ZA.ISO8859-1
en_ZA.ISO8859-15
en_ZA.US-ASCII
en_ZA.UTF-8
es_AR.ISO8859-1
es_AR.UTF-8
es_CR.UTF-8
es_ES.ISO8859-1
es_ES.ISO8859-15
es_ES.UTF-8
es_MX.ISO8859-1
es_MX.UTF-8
et_EE.ISO8859-1
et_EE.ISO8859-15
et_EE.UTF-8
eu_ES.ISO8859-1
eu_ES.ISO8859-15
eu_ES.UTF-8
fi_FI.ISO8859-1
fi_FI.ISO8859-15
fi_FI.UTF-8
fr_BE.ISO8859-1
fr_BE.ISO8859-15
fr_BE.UTF-8
fr_CA.ISO8859-1
fr_CA.ISO8859-15
fr_CA.UTF-8
fr_CH.ISO8859-1
fr_CH.ISO8859-15
fr_CH.UTF-8
fr_FR.ISO8859-1
fr_FR.ISO8859-15
fr_FR.UTF-8
he_IL.UTF-8
hi_IN.ISCII-DEV
hi_IN.UTF-8
hr_HR.ISO8859-2
hr_HR.UTF-8
hu_HU.ISO8859-2
hu_HU.UTF-8
hy_AM.ARMSCII-8
hy_AM.UTF-8
is_IS.ISO8859-1
is_IS.ISO8859-15
is_IS.UTF-8
it_CH.ISO8859-1
it_CH.ISO8859-15
it_CH.UTF-8
it_IT.ISO8859-1
it_IT.ISO8859-15
it_IT.UTF-8
ja_JP.SJIS
ja_JP.UTF-8
ja_JP.eucJP
kk_KZ.UTF-8
ko_KR.CP949
ko_KR.UTF-8
ko_KR.eucKR
lt_LT.ISO8859-13
lt_LT.UTF-8
lv_LV.ISO8859-13
lv_LV.UTF-8
mn_MN.UTF-8
nb_NO.ISO8859-1
nb_NO.ISO8859-15
nb_NO.UTF-8
nl_BE.ISO8859-1
nl_BE.ISO8859-15
nl_BE.UTF-8
nl_NL.ISO8859-1
nl_NL.ISO8859-15
nl_NL.UTF-8
nn_NO.ISO8859-1
nn_NO.ISO8859-15
nn_NO.UTF-8
pl_PL.ISO8859-2
pl_PL.UTF-8
pt_BR.ISO8859-1
pt_BR.UTF-8
pt_PT.ISO8859-1
pt_PT.ISO8859-15
pt_PT.UTF-8
ro_RO.ISO8859-2
ro_RO.UTF-8
ru_RU.CP1251
ru_RU.CP866
ru_RU.ISO8859-5
ru_RU.KOI8-R
ru_RU.UTF-8
se_FI.UTF-8
se_NO.UTF-8
sk_SK.ISO8859-2
sk_SK.UTF-8
sl_SI.ISO8859-2
sl_SI.UTF-8
sr_RS.ISO8859-2
sr_RS.ISO8859-5
sr_RS.UTF-8
sr_RS.UTF-8@latin
sv_FI.ISO8859-1
sv_FI.ISO8859-15
sv_FI.UTF-8
sv_SE.ISO8859-1
sv_SE.ISO8859-15
sv_SE.UTF-8
tr_TR.ISO8859-9
tr_TR.UTF-8
uk_UA.CP1251
uk_UA.ISO8859-5
uk_UA.KOI8-U
uk_UA.UTF-8
zh_CN.GB18030
zh_CN.GB2312
zh_CN.GBK
zh_CN.UTF-8
zh_CN.eucCN
zh_HK.UTF-8
zh_TW.Big5
zh_TW.UTF-8
 
The result of executing of the command locale -a.
 

Attachments

  • loc.txt
    2.7 KB · Views: 130
Did you run that locale command as postgres user or as root? You are running that upgrade command as postgres, which could have some environment variables set/unset?
 
Did you run that locale command as postgres user or as root? You are running that upgrade command as postgres, which could have some environment variables set/unset?
As root. All commands, as I see, we execute as root. Please pay attention to command which starts su -l postgres...
 
But... You can see my output as postgres:

$ locale
LANG=
LC_CTYPE="C"
LC_COLLATE="C"
LC_TIME="C"
LC_NUMERIC="C"
LC_MONETARY="C"
LC_MESSAGES="C"
LC_ALL=
 
We have a very detailed output in that case.

Code:
2021-11-01 21:23:22.250 MSK [99944] DEBUG:  building index "pg_toast_2600_index" on table "pg_toast_2600" serially
2021-11-01 21:23:22.251 MSK [99944] DEBUG:  CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 1/1/238
2021-11-01 21:23:22.255 MSK [99944] DEBUG:  shmem_exit(0): 1 before_shmem_exit callbacks to make
2021-11-01 21:23:22.255 MSK [99944] DEBUG:  shmem_exit(0): 12 on_shmem_exit callbacks to make
2021-11-01 21:23:22.255 MSK [99944] NOTICE:  shutting down
2021-11-01 21:23:22.257 MSK [99944] DEBUG:  performing replication slot checkpoint
2021-11-01 21:23:22.267 MSK [99944] DEBUG:  attempting to remove WAL segments older than log file 000000000000000000000000
2021-11-01 21:23:22.268 MSK [99944] DEBUG:  SlruScanDirectory invoking callback on pg_subtrans/0000
2021-11-01 21:23:22.268 MSK [99944] DEBUG:  cleaning up dynamic shared memory control segment with ID 2114402105
2021-11-01 21:23:22.273 MSK [99944] DEBUG:  proc_exit(0): 3 callbacks to make
2021-11-01 21:23:22.273 MSK [99944] NOTICE:  database system is shut down
2021-11-01 21:23:22.274 MSK [99944] DEBUG:  exit(0)
2021-11-01 21:23:22.274 MSK [99944] DEBUG:  shmem_exit(-1): 0 before_shmem_exit callbacks to make
2021-11-01 21:23:22.275 MSK [99944] DEBUG:  shmem_exit(-1): 0 on_shmem_exit callbacks to make
2021-11-01 21:23:22.275 MSK [99944] DEBUG:  proc_exit(-1): 0 callbacks to make
ok
performing post-bootstrap initialization ... 2021-11-01 21:23:23.980 MSK [99945] FATAL:  could not open collator for locale "und": U_FILE_ACCESS_ERROR
2021-11-01 21:23:23.980 MSK [99945] STATEMENT:  SELECT pg_import_system_collations('pg_catalog');

It's a tail of the output of the debug.
 
I had something similar and ended up with something like:
Code:
su -l postgres -c "/usr/local/bin/initdb --encoding=utf-8 --lc-collate=en_US.UTF-8 --lc-ctype=en_US.UTF-8 --lc-time=en_US.UTF-8 --lc-numeric=en_US.UTF-8 --lc-monetary=en_US.UTF-8 --lc-messages=en_US.UTF-8 --lc-all=en_US.UTF-8 -D /var/db/postgres/data13 -U postgres"
 
I think you'll need to read through this link to get a solution to your problem. The issue is not specific to FreeBSD as such as that link shows the issue on MacOS. But it should give you a head start.
 
Thank you very much all for your help! The issue is solved.

I just remove package icu and install port postgresql13-server.

That's all.

WBR, alexxis.
 
Back
Top