微信公众号“SRE成长记”可查看原文

在学习新的东西时，会犯很多的错误，会遇到很多坑。我们在填坑与犯错中不断进步成长。 以下是在学习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"

image.png

问题原因：

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

image.png

问题原因：

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

image.png

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?

image.png

问题原因1：

pgsql的5432端口默认只监听127.0.0.1，报这个错，是因为5432端口没有监听pgsql的IP 10.0.0.66，因此通过这个IP去连pgsql会报如上错误。 image.png 在pgsql的data目录下找到配置文件postgresql.conf，在大约第60行“#listen_addresses = 'localhost'”后边，添加对服务器内网IP的监听，然后重启pgsql。 如：

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

image.png

问题原因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?

image.png

问题原因：

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

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.

image.png

问题原因：

如报错提示，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=#

image.png

问题原因：

因为有一个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=#

image.png

问题原因：

虽然执行\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用户。

image.png

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

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

image.png