PostgreSQL流复制初始化异常处理一例

发布时间：2020-12-13 17:41:07 所属栏目：百科来源：网络整理

导读：在初始化PostgreSQL的流复制时，记下参数不对发生的两个问题及处理办法。环境： DB:PostgreSQL 9.1 VMWARE:6 前期准备略(包含热机和备机的安装及参数设置)。问题1 参数配好启动备机时，日志显示 FATAL: could not connect to the primary server: fe_sendau

在初始化PostgreSQL的流复制时，记下参数不对发生的两个问题及处理办法。

环境：
DB:PostgreSQL 9.1
VMWARE:6

前期准备略(包含热机和备机的安装及参数设置)。

问题1 参数配好启动备机时，日志显示
FATAL: could not connect to the primary server: fe_sendauth: no password supplied

FATAL:  could not connect to the primary server: fe_sendauth: no password supplied

FATAL:  could not connect to the primary server: fe_sendauth: no password supplied

FATAL:  could not connect to the primary server: fe_sendauth: no password supplied

FATAL:  could not connect to the primary server: fe_sendauth: no password supplied

FATAL:  could not connect to the primary server: fe_sendauth: no password supplied

FATAL:  could not connect to the primary server: fe_sendauth: no password supplied

FATAL:  could not connect to the primary server: fe_sendauth: no password supplied

该问题显示是密码没有提供，
但是检查.pgpass和primary_conninfo以及把primary_conninfo里面的内容拷贝出来psql一下
都是可以连接的，重新检查下参数，发现standby机子上的参数standby_mode这个参数没有调整，修改，使之standby_mode = on,再重启standby机子，结果这个问题就解决了，测试过程中发现，primary_conninfo中不设置password也会出现这个问题。

接来下却发生了下面这个问题。

问题2. standby日志显示：
FATAL: could not connect to the primary server: FATAL: must be replication role to start walsender

FATAL:  could not connect to the primary server: FATAL:  must be replication role to start walsender

FATAL:  could not connect to the primary server: FATAL:  must be replication role to start walsender

FATAL:  could not connect to the primary server: FATAL:  must be replication role to start walsender

FATAL:  could not connect to the primary server: FATAL:  must be replication role to start walsender

在master端的日志显示：
FATAL: must be replication role to start walsender

FATAL:  must be replication role to start walsender
FATAL:  must be replication role to start walsender
FATAL:  must be replication role to start walsender
FATAL:  must be replication role to start walsender
FATAL:  must be replication role to start walsender

从日志上也可以看出，walsender的角色必须是replication，回到master端查看repuser，果然是没有配replication权限，只是普通用户。改之。

postgres=# CREATE USER repuser  REPLICATION LOGIN  CONNECTION LIMIT 3   ENCRYPTED PASSWORD 'repuser';
CREATE ROLE
postgres=# du
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 postgres  | Superuser,Create role,Create DB,Replication | {}
 repuser   | Replication                                   +| {}
           | 3 connections

再次重启slave端的Postgresql，这次正常了。查看master端的视图pg_stat_replication

postgres=# select procpid,usename,application_name,client_addr,client_port,state,sync_state from pg_stat_replication;
procpid | usename | application_name | client_addr | client_port |  state  | sync_state
---------+---------+------------------+---------------+-------------+-----------+------------
 11816 | repuser | walreceiver   | 192.168.2.134 |    41205 | streaming | async
(1 row)

在本次创建user的过程发现，9.1版本的PG创建一个super用户时会默认带出replication权限。

postgres=# create user t_kenyon SUPERUSER ;
CREATE ROLE
postgres=# du
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 postgres  | Superuser,Replication | {}
 repuser   | Replication                                   +| {}
           | 3 connections                                  | 
 t_kenyon  | Superuser,Replication                         | {}

总结：
repuser在9.1后已经不需要配置super权限，在配置流复制和其他复杂配置时需要小心仔细，避免一些不必要的麻烦。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!