[pgpool-general: 19] Re: posgres 9.1 streaming replication and pgpool-II configuration.

[Sean Patronis]

On 11/30/2011 06:12 PM, Tatsuo Ishii wrote:
>> I have looked around at the documentation on configuring postgres
>> streaming replication and pgpool, and now I am getting these errors in
>> our test scenario.
>>
>> I have started with a working postgres 9.1 streaming replication
>> configuration and just attempted to add pgpool on top of it.  I have
>> used pgpool.conf.sample-stream as the base for my config, and edited
>> where necessary.  Postgres port 5432 is accesible from the host
>> running pgpool (I have gotten basic load balancing working with pgpool
>> prior to this)
>>
>>
>> when I run pgpool i get the following errors:
>>
>> [root at dbnew etc]# pgpool -n -D
>> 2011-11-30 16:55:01 LOG: pid 37930: Backend status file
>> /tmp/pgpool_status discarded
>> 2011-11-30 16:55:01 LOG: pid 37930: pgpool-II successfully
>> started. version 3.1 (hatsuiboshi)
>> 2011-11-30 16:55:01 ERROR: pid 37930: s_do_auth: expecting R got E
>> 2011-11-30 16:55:01 ERROR: pid 37930: make_persistent_db_connection:
>> s_do_auth failed
>> 2011-11-30 16:55:01 ERROR: pid 37930: find_primary_node:
>> make_persistent_connection failed
>> 2011-11-30 16:55:01 ERROR: pid 37964: s_do_auth: expecting R got E
>> 2011-11-30 16:55:01 ERROR: pid 37964: make_persistent_db_connection:
>> s_do_auth failed
>> 2011-11-30 16:55:01 ERROR: pid 37964: check_replication_time_lag:
>> could not connect to DB node 0, check health check configuration
>>
>> clearly something is not configured correctly, what gives?
> Yes, you have problem with configuration. To check this please make
> sure that you can connect from the host pgpool is running on as user =
> sr_check_user, password = sr_check_password to database "postgres" by
> using psql or whatever client you like.
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese: http://www.sraoss.co.jp
>

pgpool is running on the master database server and the sr_check_user 
can connect to the postgres database just fine:

[root at dbnew etc]# psql -Ureplicator -W  postgres
Password for user replicator:
psql (9.1.1)
Type "help" for help.

postgres=> select tablename from pg_tables where tableowner='postgres' 
limit 5;
   tablename
--------------
  pg_statistic
  pg_type
  pg_attribute
  pg_authid
  pg_proc
(5 rows)

postgres=> \q



It can also connect to the streaming replicated postgres server on 
another host:

[root at dbnew etc]# psql -Ureplicator -W -h db2.internaltest.com postgres
Password for user replicator:
psql (9.1.1)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

postgres=> select tablename from pg_tables where tableowner='postgres' 
limit 5;
   tablename
--------------
  pg_statistic
  pg_type
  pg_attribute
  pg_authid
  pg_proc
(5 rows)

postgres=> \q


I have even enabled that user to connect without a password and I still 
get the same error.  What else can I look for to fix this error?

Thanks.