postgresql报错汇总（持续更新）

1、启动pg报错（1）

[postgres@fang bin]$ ./pg_ctl -D /usr/local/pgsql/data/ -l logfile start
The program "postgres" is needed by pg_ctl but was not found in the
same directory as "/usr/local/pgsql/bin/pg_ctl".
Check your installation.

该错误是由于pg bin/目录下的postgres二进制文件丢失导致，找个同样的文件复制过来即可。

2、启动pg报错（2）

[postgres@fang bin]$ ./pg_ctl -D /usr/local/pgsql/data/ -l logfile restart
pg_ctl: old server process (PID: 29465) seems to be gone
starting server anyway
waiting for server to start.... stopped waiting
pg_ctl: could not start server
Examine the log output.
[postgres@fang bin]$ ./pg_ctl -D /usr/local/pgsql/data/ -l logfile stop
pg_ctl: could not send stop signal (PID: 29465): No such process
[postgres@fang bin]$ ./pg_ctl -D /usr/local/pgsql/data/ -l logfile start
pg_ctl: another server might be running; trying to start server anyway
The program "postgres" is needed by pg_ctl but was not found in the
same directory as "/usr/local/pgsql/bin/pg_ctl".
Check your installation.

查看logfile日志文件

/bin/sh: /usr/local/pgsql/bin/postgres: No such file or directory

处理方法与问题1相同

继续启动报错查看logfile文件

2024-07-16 09:45:38.227 CST [23144] FATAL:  data directory "/usr/local/pgsql/data" has invalid permissions
2024-07-16 09:45:38.227 CST [23144] DETAIL:  Permissions should be u=rwx (0700) or u=rwx,g=rx (0750).

处理方法：切换到root

# 设置权限为0700（仅所有者有读写执行权限）
sudo chmod 0700 /usr/local/pgsql/data

# 或者，如果你希望组用户也能读取和执行（但不写入），则设置为0750
# 确保组设置正确，这取决于你的系统配置和安全需求
sudo chmod 0750 /usr/local/pgsql/data
sudo chown postgres:postgres /usr/local/pgsql/data  # 确保所有者是postgres用户，并且所属组也是正确的

3、创建用户报错

[postgres@fang bin]$ psql -U postgres
postgres=# CREATE USER flink login replication encrypted password '123456';
ERROR: must be superuser to create replication users

没有超级管理员权限，给postgres赋权超管权限：

#停止pg
[postgres@fang bin]$ ./pg_ctl stop
#执行以下命令,进入single模式
[postgres@fang bin]$ /usr/local/pgsql/bin/postgres --single -D /usr/local/pgsql/data/
PostgreSQL stand-alone backend 9.6.24
#修改账户为超管
backend> alter user postgres with superuser;
修改账户密码
backend> alter user postgres with password '123456';

#启动pg
[postgres@fang bin]$ ./pg_ctl -D /usr/local/pgsql/data/ -l logfile start

4、Caused by: org.postgresql.util.PSQLException: ERROR: replication slot "pg_cdc

--查看现有复制槽
SELECT * FROM pg_replication_slots;
--删除现有复制槽
SELECT pg_drop_replication_slot('pg_cdc');