Sample Header Ad - 728x90

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