Postgres upgrade from 10.4 to 11.5 encodings for database "postgres" do not match: old "SQL_ASCII", new "UTF8"
Asked Answered
C

3

6

Currently checking the procedure for upgrading Postgres from 10.4 to 11.5.

When I run pg_upgrade with the "check" option, I have the following messages. If you have any comments about this, about how you have fixed the issue, I would be grateful.

bash-4.2$ /usr/pgsql-11/bin/pg_upgrade \
> -b /usr/pgsql-10/bin \
> -B /usr/pgsql-11/bin \
> -d /var/lib/pgsql/10/data \
> -D /var/lib/pgsql/11/data \
> -c pgsql-10/ pgsql-11/

Performing Consistency Checks on Old Live Server
------------------------------------------------
Checking cluster versions                                   
ok
Checking database user is the install user                  
ok
Checking database connection settings                       
ok
Checking for prepared transactions                          
ok
Checking for reg* data types in user tables                 
ok
Checking for contrib/isn with bigint-passing mismatch       
ok

encodings for database "postgres" do not match:  old "SQL_ASCII", new "UTF8"
Failure, exiting
Cock answered 22/10, 2019 at 21:43 Comment(0)
C
1

@jjanes, Laurenz Albe Thanks by your comments I did not understand at first time and according to the documents I was trying to execute the following command: #/usr/pgsql-11/bin/postgresql-11-setup initdb --locale=C -D /var/lib/pgsql/11/data
but it was falling, then I have executed the command as you recommend and the validation has been successfully:/usr/pgsql-11/bin/initdb --locale=C -D /var/lib/pgsql/11/data

-bash-4.2$ /usr/pgsql-11/bin/pg_upgrade \
> -b /usr/pgsql-10/bin \
> -B /usr/pgsql-11/bin \
> -d /var/lib/pgsql/10/data \
> -D /var/lib/pgsql/11/data \
> -c pgsql-10/ pgsql-11/
Performing Consistency Checks on Old Live Server
------------------------------------------------
Checking cluster versions                                   ok
Checking database user is the install user                  ok
Checking database connection settings                       ok
Checking for prepared transactions                          ok
Checking for reg* data types in user tables                 ok
Checking for contrib/isn with bigint-passing mismatch       ok
Checking for presence of required libraries                 ok
Checking database user is the install user                  ok
Checking for prepared transactions                          ok

*Clusters are compatible*

Now, I have a second question is it possible to change the encoding from my source postgres DB? in which way it can affect if I decide only avoid this and continue working with SQL_ASCII by default?

Cock answered 23/10, 2019 at 22:4 Comment(0)
M
3

When you initdb the new server you need to set it to match the old one. Something like one of these:

initdb --locale C  -D /var/lib/pgsql/11/data
pg_ctl initdb -o "--locale C"  -D /var/lib/pgsql/11/data
LC_ALL=C initdb  -D /var/lib/pgsql/11/data
Metaphysic answered 22/10, 2019 at 22:10 Comment(5)
The error is because of the encoding, not the locale.Chopfallen
But setting the locale will set the default encoding. They usually go together. If you change just one, then the upgrade check will get one step further along before bombing out.Metaphysic
Ah you are right, thanks. --locale=C will choose encoding SQL_ASCII by default.Chopfallen
Hi jjanes, as you see I have only executed pg_upgrade with -c (check) option, and the process has aborted, do you think the upgrade will run without abort? if this is correct then I think I can apply your adviceCock
No. You have redo the initdb of the new cluster, with an encoding to match the old one. If you do not do that, then it will not succeed. There is no way to "fix" the new cluster so that it will succeed, you have to re-create it.Metaphysic
C
1

You need to create the new cluster with the same (abominable) encoding as the old one:

initdb -E SQL_ASCII --locale=C /new/data/directory
Chopfallen answered 23/10, 2019 at 5:45 Comment(0)
C
1

@jjanes, Laurenz Albe Thanks by your comments I did not understand at first time and according to the documents I was trying to execute the following command: #/usr/pgsql-11/bin/postgresql-11-setup initdb --locale=C -D /var/lib/pgsql/11/data
but it was falling, then I have executed the command as you recommend and the validation has been successfully:/usr/pgsql-11/bin/initdb --locale=C -D /var/lib/pgsql/11/data

-bash-4.2$ /usr/pgsql-11/bin/pg_upgrade \
> -b /usr/pgsql-10/bin \
> -B /usr/pgsql-11/bin \
> -d /var/lib/pgsql/10/data \
> -D /var/lib/pgsql/11/data \
> -c pgsql-10/ pgsql-11/
Performing Consistency Checks on Old Live Server
------------------------------------------------
Checking cluster versions                                   ok
Checking database user is the install user                  ok
Checking database connection settings                       ok
Checking for prepared transactions                          ok
Checking for reg* data types in user tables                 ok
Checking for contrib/isn with bigint-passing mismatch       ok
Checking for presence of required libraries                 ok
Checking database user is the install user                  ok
Checking for prepared transactions                          ok

*Clusters are compatible*

Now, I have a second question is it possible to change the encoding from my source postgres DB? in which way it can affect if I decide only avoid this and continue working with SQL_ASCII by default?

Cock answered 23/10, 2019 at 22:4 Comment(0)

© 2022 - 2024 — McMap. All rights reserved.