RPostgres连接PostgreSQL时的区域编码乱码问题求助

问题概述 #

使用RPostgres包连接本地PostgreSQL数据库时，执行连接代码后返回乱码错误，切换系统locale后错误信息仍无法正常解析。核心矛盾是R会话编码与PostgreSQL数据库编码不匹配，同时可能存在用户/密码拼写错误。

环境信息 #

• 执行的连接代码：

con <- dbConnect(RPostgres::Postgres(),
dbname="####",
host="127.0.0.1",
port=5432,
user="postgre",
password="####")

• 初始乱码错误：

Error: ��������:  �û� "postgre" Password ��֤ʧ��

• 切换locale为English_United States.1252后的错误：

Error: <d6><c2><c3><fc><b4><ed><ce><f3>:  <d3><U+00FB><a7> "postgre" Password <c8><cf><U+05A4><U+02A7><b0><dc>

• 系统初始locale：

Sys.getlocale()
[1] "LC_COLLATE=Chinese (Simplified)_China.utf8;LC_CTYPE=Chinese (Simplified)_China.utf8;LC_MONETARY=Chinese (Simplified)_China.utf8;LC_NUMERIC=C;LC_TIME=Chinese (Simplified)_China.utf8"

• PostgreSQL数据库编码：Chinese (Simplified)_China.936（对应GBK编码）
• RStudio会话信息：

R version 4.2.0 (2022-04-22 ucrt)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows 10 x64 (build 19044)

Matrix products: default

locale:
[1] LC_COLLATE=zh-cn.UTF-8  LC_CTYPE=zh-cn.UTF-8    LC_MONETARY=zh-cn.UTF-8 LC_NUMERIC=C            LC_TIME=zh-cn.UTF-8    

attached base packages:
[1] graphics  grDevices utils     datasets  stats     methods   base     

other attached packages:
 [1] DBI_1.1.3       RPostgres_1.4.4 forcats_0.5.1   stringr_1.4.0   dplyr_1.0.9     purrr_0.3.4     readr_2.1.2    
 [8] tidyr_1.2.0     tibble_3.1.7    ggplot2_3.3.6   tidyverse_1.3.2

解决方法 #

1. 统一R会话与数据库编码 #

PostgreSQL使用GBK编码，而当前R会话采用UTF-8，编码不匹配导致错误信息乱码。将R的locale切换为GBK对应的区域：

Sys.setlocale(category = "LC_ALL", locale = "Chinese (Simplified)_China.936")

执行后重新运行连接代码，此时错误信息会正常显示，可明确判断问题根源。

2. 修正数据库用户名（常见错误） #

PostgreSQL默认超级用户是postgres，而非代码中的postgre，先修正连接代码中的user参数：

user="postgres"

3. 验证密码正确性 #

乱码提示的核心是密码认证失败，确保输入的密码与目标数据库用户的密码完全一致，注意大小写和特殊字符。

4. 强制指定客户端编码（可选） #

如果切换locale后仍有问题，在连接时通过options参数强制客户端使用GBK编码与数据库通信：

con <- dbConnect(RPostgres::Postgres(),
dbname="你的数据库名",
host="127.0.0.1",
port=5432,
user="postgres",
password="你的密码",
options="-c client_encoding=GBK")

5. 检查PostgreSQL认证配置 #

确保本地连接允许密码登录：

1. 找到PostgreSQL安装目录下的data/pg_hba.conf文件
2. 定位host all all 127.0.0.1/32对应的行，将认证方式改为md5或scram-sha-256（PostgreSQL 10及以上版本推荐）
3. 保存文件后重启PostgreSQL服务

内容的提问来源于stack exchange，提问作者Yiwei Zhu