Postgres从10.4升级到11.5编码数据库"postgres“不匹配:旧的"SQL_ASCII"，新的"UTF8”

Question

目前正在检查将Postgres从10.4升级到11.5的程序。

当我使用"check“选项运行pg_upgrade时，我会收到以下消息。如果你对此有任何评论，关于你是如何解决这个问题的，我将不胜感激。

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

Answer 1

@jjane，Laurenz感谢您的评论，我第一次不明白，并且根据我试图执行以下命令的文档：#/usr/pgsql-11/bin/postgresql-11-setup initdb --locale=C -D /var/lib/pgsql/11/data

但是它正在下降，然后我按照您的建议执行了命令，并且验证已经成功：/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*

现在，我有第二个问题，可以更改我的源postgres的编码吗？如果我决定只避免这种情况，并在默认情况下继续使用SQL_ASCII，它会以何种方式影响？

Answer 2

当您登录新服务器时，需要将其设置为与旧服务器匹配。就像其中之一：

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

Answer 3

您需要使用与旧集群相同的(可恶的)编码来创建新集群：

initdb -E SQL_ASCII --locale=C /new/data/directory