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

user_12259876 0
Viewed 1886

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
upgrade encoding postgresql database
3 Answers

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
user_17212390

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
user_64643080

@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?

user_122598760
Related