# 错误信息
FATAL: sorry, too many clients already
DETAIL: There are already 300 clients connected.
HINT: You might need to increase max_connections.

# 问题分析
$ psql -U fgedu -d fgedudb -c ”
SELECT
setting::int as max_connections,
(SELECT count(*) FROM pg_stat_activity) as current_connections
FROM pg_settings
WHERE name = ‘max_connections’;
”

max_connections | current_connections
—————–+———————
300 | 300
(1 row)

# 解决方案1：释放空闲连接
$ psql -U fgedu -d fgedudb -c ”
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = ‘idle’
AND now() – state_change > interval ’10 minutes’
AND usename NOT IN (‘fgedu’, ‘pgsql’);
”

pg_terminate_backend
———————-
t
t
(2 rows)

# 解决方案2：增加最大连接数
$ psql -U fgedu -d fgedudb -c “ALTER SYSTEM SET max_connections = 400;”

# 重启数据库生效
$ pg_ctl -D /postgresql/fgdata restart

# 解决方案3：使用连接池
# 安装PgBouncer
$ yum install -y pgbouncer

# 配置PgBouncer
$ vi /etc/pgbouncer/pgbouncer.ini
[databases]
fgedudb = host=127.0.0.1 port=5432 dbname=fgedudb

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = session
max_client_conn = 1000
default_pool_size = 25

# 启动PgBouncer
$ systemctl start pgbouncer

# 错误信息
FATAL: password authentication failed for user “fgedu”
DETAIL: Role “fgedu” does not exist.
Connection matched pg_hba.conf line 95: “host all all 192.168.1.0/24 scram-sha-256”

# 问题分析
# 1. 检查用户是否存在
$ psql -U pgsql -d postgres -c “\du fgedu”

List of roles
Role name | Attributes | Member of
———–+————————————————————+———–
fgedu | Superuser, Create role, Create DB | {}

# 2. 检查pg_hba.conf配置
$ cat /postgresql/fgdata/pg_hba.conf | grep -v “^#” | grep -v “^$”

local all all trust
host all all 127.0.0.1/32 scram-sha-256
host all all 192.168.1.0/24 scram-sha-256
host replication all 192.168.1.0/24 scram-sha-256

# 解决方案1：重置密码
$ psql -U pgsql -d postgres -c “ALTER USER fgedu WITH PASSWORD ‘Fgedu@New2026’;”

ALTER ROLE

# 解决方案2：修改认证方式
$ vi /postgresql/fgdata/pg_hba.conf

# 将scram-sha-256改为md5或trust（不推荐生产环境）
host all all 192.168.1.0/24 md5

# 重载配置
$ pg_ctl -D /postgresql/fgdata reload

# 解决方案3：检查密码加密方式
$ psql -U fgedu -d fgedudb -c ”
SELECT rolname, rolpassword
FROM pg_authid
WHERE rolname = ‘fgedu’;
”

rolname | rolpassword
———+—————————————-
fgedu | SCRAM-SHA-256$4096:…
(1 row)

# 错误信息
ERROR: permission denied for table fgedu_orders
STATEMENT: SELECT * FROM fgedu_orders;

# 问题分析
$ psql -U fgedu -d fgedudb -c ”
SELECT
grantee,
table_schema,
table_name,
privilege_type
FROM information_schema.table_privileges
WHERE table_name = ‘fgedu_orders’
AND grantee = ‘fgapp_usr’;
”

grantee | table_schema | table_name | privilege_type
———+————–+—————-+—————-
fgapp_usr | public | fgedu_orders | SELECT
(1 row)

# 解决方案1：授予SELECT权限
$ psql -U fgedu -d fgedudb -c “GRANT SELECT ON fgedu_orders TO fgapp_usr;”

GRANT

# 解决方案2：授予所有权限
$ psql -U fgedu -d fgedudb -c “GRANT ALL PRIVILEGES ON fgedu_orders TO fgapp_usr;”

GRANT

# 解决方案3：授予模式权限
$ psql -U fgedu -d fgedudb -c “GRANT USAGE ON SCHEMA public TO fgapp_usr;”

GRANT

# 解决方案4：授予默认权限
$ psql -U fgedu -d fgedudb -c ”
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO fgapp_usr;
”

ALTER DEFAULT PRIVILEGES

# 错误信息
ERROR: schema “fgedu_schema” does not exist
LINE 1: SELECT * FROM fgedu_schema.fgedu_table;
^

# 解决方案1：创建模式
$ psql -U fgedu -d fgedudb -c “CREATE SCHEMA fgedu_schema;”

CREATE SCHEMA

# 解决方案2：检查搜索路径
$ psql -U fgedu -d fgedudb -c “SHOW search_path;”

search_path
—————–
“$user”, public
(1 row)

# 解决方案3：设置搜索路径
$ psql -U fgedu -d fgedudb -c “SET search_path TO fgedu_schema, public;”

SET

# 解决方案4：永久设置搜索路径
$ psql -U fgedu -d fgedudb -c “ALTER USER fgapp_usr SET search_path TO fgedu_schema, public;”

ALTER ROLE

# 错误信息
ERROR: could not extend file “base/16384/12345″: No space left on device
HINT: Check free disk space.

# 问题分析
$ df -h /postgresql

Filesystem Size Used Avail Use% Mounted on
/dev/sdb1 2.0T 1.9T 50G 98% /postgresql

# 解决方案1：清理历史数据
$ psql -U fgedu -d fgedudb -c ”
— 删除旧数据
DELETE FROM fgedu_logs WHERE created_at < now() - interval '30 days'; -- 执行VACUUM VACUUM FULL fgedu_logs; " DELETE 10000000 VACUUM # 解决方案2：清理WAL日志 $ psql -U fgedu -d fgedudb -c " -- 检查WAL文件数量 SELECT count(*) FROM pg_ls_waldir(); " count ------- 50 (1 row) -- 调整WAL保留 ALTER SYSTEM SET max_wal_size = '2GB'; ALTER SYSTEM SET wal_keep_size = '1GB'; # 解决方案3：清理临时文件 $ rm -rf /postgresql/fgdata/base/pgsql_tmp/* # 解决方案4：扩展存储 # 添加新磁盘 $ fdisk /dev/sdc $ mkfs.xfs /dev/sdc1 $ mount /dev/sdc1 /postgresql/new_storage # 创建新表空间 $ psql -U fgedu -d fgedudb -c " CREATE TABLESPACE fgedutbs_new LOCATION '/postgresql/new_storage'; " # 移动表到新表空间 $ psql -U fgedu -d fgedudb -c " ALTER TABLE fgedu_large_table SET TABLESPACE fgedutbs_new; "

# 错误信息
ERROR: could not create file “pg_tblspc/12345/PG_18_202307071/16384/12345″: No space left on device

# 问题分析
$ psql -U fgedu -d fgedudb -c ”
SELECT
spcname,
pg_size_pretty(pg_tablespace_size(oid)) as size,
pg_tablespace_location(oid) as location
FROM pg_tablespace;
”

spcname | size | location
—————+——–+——————————–
pg_default | 150 GB |
pg_global | 500 MB |
fgedutbs | 50 GB | /postgresql/fgedutbs
fgedutbs_data | 100 GB | /postgresql/fgedutbs_data

# 检查表空间所在磁盘
$ df -h /postgresql/fgedutbs_data

Filesystem Size Used Avail Use% Mounted on
/dev/sdc1 100G 95G 5G 95% /postgresql/fgedutbs_data

# 解决方案1：清理表空间
$ psql -U fgedu -d fgedudb -c ”
— 查看表空间中的表
SELECT
schemaname || ‘.’ || relname as table_name,
pg_size_pretty(pg_total_relation_size(schemaname || ‘.’ || relname)) as size
FROM pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
JOIN pg_tablespace t ON c.reltablespace = t.oid
WHERE t.spcname = ‘fgedutbs_data’
ORDER BY pg_total_relation_size(schemaname || ‘.’ || relname) DESC;
”

# 解决方案2：扩展表空间
# 扩展底层存储或添加新位置
$ psql -U fgedu -d fgedudb -c ”
— 创建新表空间
CREATE TABLESPACE fgedutbs_new LOCATION ‘/postgresql/new_storage’;

— 移动表
ALTER TABLE fgedu_large_table SET TABLESPACE fgedutbs_new;
”