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

概述在初始化PostgreSQL的流复制时，记下参数不对发生的两个问题及处理办法。 环境： DB:PostgreSQL 9.1 VMWARE:6 前期准备略(包含热机和备机的安装及参数设置)。 问题1 参数配好启动备机时，日志显示 FATAL:  could not connect to the primary server: fe_sendauth: no password supplied FAT 在初始化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 supplIEdFATAL:  Could not connect to the primary server: fe_sendauth: no password supplIEdFATAL:  Could not connect to the primary server: fe_sendauth: no password supplIEdFATAL:  Could not connect to the primary server: fe_sendauth: no password supplIEdFATAL:  Could not connect to the primary server: fe_sendauth: no password supplIEdFATAL:  Could not connect to the primary server: fe_sendauth: no password supplIEdFATAL:  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 walsenderFATAL:  Could not connect to the primary server: FATAL:  must be replication role to start walsenderFATAL:  Could not connect to the primary server: FATAL:  must be replication role to start walsenderFATAL:  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 walsenderFATAL:  must be replication role to start walsenderFATAL:  must be replication role to start walsenderFATAL:  must be replication role to start walsenderFATAL:  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 RolEpostgres=# \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 RolEpostgres=# \du                             List of roles Role name |                   Attributes                   | Member of -----------+------------------------------------------------+----------- postgres  | Superuser,Replication | {} repuser   | Replication                                   +| {}           | 3 connections                                  |  t_kenyon  | Superuser,Replication                         | {}

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

以上是内存溢出为你收集整理的PostgreSQL流复制初始化异常处理一例全部内容，希望文章能够帮你解决PostgreSQL流复制初始化异常处理一例所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。