Thread: pg_update and encoding
Preparing to use pg_update, I used initdb to create the new pgsql/data, but pg_update has exited with
encodings for database "template1" do not match: old "UTF8", new "SQL_ASCII"
Should I delete pgsql/data and re-create with initdb -E "UTF8"?
Thanks,
Graeme
On 9/12/23 04:49, Graeme wrote: > Preparing to use pg_update, I used initdb to create the new pgsql/data, > but pg_update has exited with I'm guessing that is actually pg_upgrade: https://www.postgresql.org/docs/current/pgupgrade.html > > encodings for database "template1" do not match: old "UTF8", new > "SQL_ASCII" > > Should I delete pgsql/data and re-create with initdb -E "UTF8"? Yes for two reasons: 1) The upgrade will not happen. 2) From here: https://www.postgresql.org/docs/current/multibyte.html "The SQL_ASCII setting behaves considerably differently from the other settings. When the server character set is SQL_ASCII, the server interprets byte values 0–127 according to the ASCII standard, while byte values 128–255 are taken as uninterpreted characters. No encoding conversion will be done when the setting is SQL_ASCII. Thus, this setting is not so much a declaration that a specific encoding is in use, as a declaration of ignorance about the encoding. In most cases, if you are working with any non-ASCII data, it is unwise to use the SQL_ASCII setting because PostgreSQL will be unable to help you by converting or validating non-ASCII characters." > > Thanks, > > Graeme > -- Adrian Klaver adrian.klaver@aklaver.com
On 12/09/2023 15:52, Adrian Klaver wrote: > On 9/12/23 04:49, Graeme wrote: >> Preparing to use pg_update, I used initdb to create the new >> pgsql/data, but pg_update has exited with > > I'm guessing that is actually pg_upgrade: > > https://www.postgresql.org/docs/current/pgupgrade.html > >> >> encodings for database "template1" do not match: old "UTF8", new >> "SQL_ASCII" >> >> Should I delete pgsql/data and re-create with initdb -E "UTF8"? > > Yes for two reasons: > > 1) The upgrade will not happen. > > 2) From here: > > https://www.postgresql.org/docs/current/multibyte.html > > "The SQL_ASCII setting behaves considerably differently from the other > settings. When the server character set is SQL_ASCII, the server > interprets byte values 0–127 according to the ASCII standard, while > byte values 128–255 are taken as uninterpreted characters. No encoding > conversion will be done when the setting is SQL_ASCII. Thus, this > setting is not so much a declaration that a specific encoding is in > use, as a declaration of ignorance about the encoding. In most cases, > if you are working with any non-ASCII data, it is unwise to use the > SQL_ASCII setting because PostgreSQL will be unable to help you by > converting or validating non-ASCII characters." > >> >> Thanks, >> >> Graeme >> > Thanks, done. Data now accessible Graeme