PostgreSQL的常见错误和解决方法

转载说明：如果您喜欢这篇文章并打算转载它，请私信作者取得授权。感谢您喜爱本文，请文明转载，谢谢。

在学习新的东西时，会犯很多的错误，会遇到很多坑。我们在填坑与犯错中不断进步成长。
以下是在学习pgsql中，遇到的和给自己挖的坑。

1. 登录鉴权失败

报错日志：

INTERNAL SERVER ERROR: Unable to connect to server:connection to server at "10.0.0.66",port 5432 failed:FATAL:password authentication failed for user "portgres"

问题原因：

postgres用户权限验证失败，检查用户、密码是否输入错误或是否未给postgres设置密码

2. 远程连接白名单未开，登录失败

报错日志：

INTERNAL SERVER ERROR: Unable to connect to server:connection to server at "10.0.0.66",port 5432 failed:FATAL:no pg_hba.conf entry for host "192.168.0.8",user "postgres",database "portgres",no encryption

问题原因：

PostgreSQL数据库为了安全，它不会监听除本地以外的所有连接请求，当用户通过JDBC访问时，会报类似“FATAL:no pg_hba.conf entry for host”这样的异常。
在postgresql数据目录下的配置文件 pg_hba.conf中,找到“# IPv4 local connections:”在其下加上请求连接的机器IP，然后重启pgsql。
如：

host all all  0.0.0.0/0  md5   #这里是测试，配置的0.0.0.0/0，生产环境按需配置；md5表示密码验证方法，可以改为trust

3. 端口不通，远程登录失败

报错日志：

INTERNAL SERVER ERROR: Unable to connect to server:connection to server at "10.0.0.66",port 5432 failed:Connection refused(0x0000274D/10061) is the server running on that host and accepting TCP/IP connections?

问题原因1：

pgsql的5432端口默认只监听127.0.0.1，报这个错，是因为5432端口没有监听pgsql的IP 10.0.0.66，因此通过这个IP去连pgsql会报如上错误。

在pgsql的data目录下找到配置文件postgresql.conf，在大约第60行“#listen_addresses = ‘localhost’”后边，添加对服务器内网IP的监听，然后重启pgsql。
如：

listen_addresses = '10.0.0.66'  或者 listen_addresses = '*'

问题原因2：

如监听了IP，则检查是否开了防火墙。关闭防火墙或防火墙放行5432端口即可。

4. psql命令无法执行

报错日志：

命令行执行psql，报错

psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such file or directory
        Is the server running locally and accepting connections on that socket?

问题原因：

检查pgsql应用和端口是否正确，如pgsql运行和5432端口运行OK，则检查环境变量是否设置错误

5. 用户错误，pgsql无法启动

报错日志：

启动pgsql报错：

[root@test66 system]# /home/finance/App/postgresql-14/bin/pg_ctl -D /home/finance/Data/pgsql -l logfile stop
pg_ctl: cannot be run as root
Please log in (using, e.g., "su") as the (unprivileged) user that will
own the server process.

问题原因：

如报错提示，pgsql不能使用root用户启动，su postgres 用postgres用户启动即可。

6. 用户删除失败

报错日志：

删除用户sre失败：

postgres=# drop user sre;
ERROR:  role "sre" cannot be dropped because some objects depend on it
DETAIL:  privileges for database sre
postgres=#

问题原因：

因为有一个sre库关联到了sre用户，而sre库在使用中。在删除用户之前，先确保其未关联任何database。

7. 用户授权失败

报错日志：

给用户授权失败：

postgres=# alter roles sre with Create DB;
ERROR:  syntax error at or near "roles"
LINE 1: alter roles sre with Create DB;
              ^
postgres=#

问题原因：

虽然执行\du，看到的用户role有“Create DB”这个角色，但实际在给用户授权时，正确的拼写是“CREATEDB”，而不是“Create DB”。

postgres=# alter role sre with CREATEDB;
ALTER ROLE
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 sre       | Create DB                                                  | {}
postgres=#

8. 数据导入失败

导入数据操作：

$ psql -U postgres -h 10.0.0.66 -p 5432 -d sonar -f /tmp/sonar.sql

报错日志1：

psql: error: connection to server at "10.0.0.66", port 5432 failed: FATAL:  database "sonar" does not exist

问题原因：

如报错，导入数据之前没有先创建database。

报错日志2：

psql:/tmp/sonar.sql:36: ERROR:  role "sonar" does not exist

问题原因：

因在原有的pg数据库里面，database sonar库是赋权给sonar用户的，而在新的pgsql里面，sonar库是用postgres用户创建的。没有sonar用户，也没有授权给sonar用户。

这种情况导入数据的正确步骤：

1）先创建用户和database，并把database授权给用户

postgres=# create database sonar;
postgres=# create user sonar with password 'sonar';
postgres=# GRANT ALL PRIVILEGES ON DATABASE sonar to sonar;

2）导入数据

$ psql -U postgres -h 10.0.0.66 -p 5432 -d sonar -f /tmp/sonar.sql