[pgpool-general: 3825] Re: Connection Failures Through pgpool Only

Tatsuo Ishii ishii at postgresql.org
Sat Jun 20 18:27:08 JST 2015


> Hi pgpoolers,
> 
> After a good unit test, I am having fundamental problems implementing
> pgpool into integration-test where the real apps are. My system is not
> connected to the internet in any way and I cannot cut and paste any
> information, but I will try to fill in the gap by hand.Please help!
> 
> Problem 1Applications frequently get error 08006 (connection_failure)
> and 08003 (connection_does_not_exist); they
>                              occasionally get messages about threads no longer connected to
>                              database.None of these transactions get thru.

It is possible due to this in pgpool.conf (which is default).

child_life_time = 300

You need to fix this or your application to reconect to pgpool-II.

> Problem 2pgpool instances sometimes lockup/crash and cannot be
> accessed from psql or pgp commands

Too vague problem report. Please show us what's in the pgpool log file
when the issue occurs.

Another possiblity for "lockup" problem is misconfiguration of
num_init_children vs. max_connections in postgresql.conf (you did not
show us postgresql.conf and this is just my guess). See manual for
proper configuration.

> Problem 3The standby nodes show up mostly as status 1 and 3, and only
> occasionally as 2; the primaries are generally at 2,
>                              except for early startup which is 1.

"3" means pgpool-II detects that either 3rd PostgreSQL is down for
pgpool-II cannot reach to the server. Showing the pgpool log may
reveals the cause.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

> Generally the applications all work fine when connecting directly to
> the primary PG DB servers at 5432, and all databases can be accessed
> using psql thru the db servers at port 5432, and thru the
> pgppol/tomcat servers at 9999._The errors occur only when I change the
> application data sources to go thru the pgpool servers at
> 9999. _Nothing in the PG or pgpool logs show obvious authentications
> errors.
> 
> Background/details:
> 
> Systems Each test environment consists of 4 rhel6 HP blades, 8GB of
> RAM each
> 
> PgpoolTwo instances of v3.4 running independently on 2 severs shared
> w/ Tomcat (2GB dedicated; no watchdog; installed w/ RPMs
> 
> Potgresv9.3 on dedicated servers using PG streaming synchronous
> replication; installed w/ tar files
> 
> My exp. lev.3m pgpool, 6m PG, 20+ yrs DBA; set up 9 sets of 4 servers
> so far; 2 sets to go for the $!
> 
> Key settings:
> 
> Pg_hba.conf (as open as I can get it!):
> 
> 
> # TYPEDATABASEUSERADDRESSMETHOD
> 
> localallalltrust
> 
> hostallall0.0.0.0/0trust
> 
> hostallpostgresql0.0.0.0/0trust
> 
> hostreplicationallxx.xx.xx.xx/32trust --> (only b/c PG insisted)
> 
> pgpool.conf:
> 
> 
> listen_addresses = '*'
> 
> port = 9999
> 
> pcp_port = 9898
> 
> backend_hostname0 = 'abcdefgh'
> 
> backend_port1 = 5432
> 
> backend_weight0 = 1
> 
> backend_data_directory0 = '/data/ABCDFF1'
> 
> backend_flag0 = 'ALLOW_TO_FAILOVER'
> 
> backend_hostname1 = 'abcdefgh'
> 
> backend_port1 = 5432
> 
> backend_weight1 = 1
> 
> backend_data_directory1 = '/data/ABCDEF1'
> 
> backend_flag1 = 'ALLOW_TO_FAILOVER'
> 
> enable_pool_hba = off
> 
> replication_mode = off
> 
> load_balance_mode = on
> 
> master_slave_mode = on
> 
> master_slave_sub_mode = 'stream' (warning msg in log says system is
> not streaming)
> 
> parallel_mode = off
> 
> Loaded these into db01:
> 
> psql -h localhost -U postgres -W -f pgpool-recovery.sql dbname
> 
> psql -h localhost -U postgres -W -f pgpool-regclass.sql dbname
> 
> Thanks.
> 
> Mike Hill
> Lockheed Martin Corp.
> Herndon, VA
> michael.e.hill at lmco.com
> 
> 
> ---
> This email has been checked for viruses by Avast antivirus software.
> https://www.avast.com/antivirus


More information about the pgpool-general mailing list