upgrade postgres cluster with changing collation & encoding
0
votes
2
answers
227
views
we face a problem when trying to
pg_upgrade
pg12 > pg14.
our source instance has en_US.UTF-8
which it should not have had to begin with, and the new pg14 cluster is being initiated with de_DE.UTF-8
.
pg_upgrade
throws an error on
lc_collate values for database "postgres" do not match: old "en_US.UTF-8", new "de_DE.UTF-8"
not being experienced in such at all, we came up with this solution and I'd like to hear some feedback whether this is sane, or not
pg14/initdb -D new_data_dir
pg14/pg_ctl -D new_data_dir start
pg_dumpall_12 | sed -i "/en_US.UTF-8/de_DE.UTF-8/" | psql_14 -p 6543 |& tee some.out
this leaves Ctype still with en_US.UTF-8
so we add the below per database
psql -c "UPDATE pg_database SET datctype = 'de_DE.UTF-8' WHERE datname = ;"
Asked by vrms
(269 rep)
Dec 18, 2024, 09:31 AM
Last activity: Dec 18, 2024, 02:21 PM
Last activity: Dec 18, 2024, 02:21 PM