pg_update and encoding

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