[pgpool-general: 760] Re: Problem with pgpool when using SSL for client/pgpool communication

Jeff Frost jeff at pgexperts.com
Thu Jul 19 10:31:54 JST 2012


Yes, we'll give this a test and report back!

On Jul 18, 2012, at 6:13 PM, Tatsuo Ishii wrote:

> Recently we have fixed pgpool code which might be related to SSL
> handling.
> 
> http://git.postgresql.org/gitweb/?p=pgpool2.git;a=commit;h=8ffb7f63664ff9c7d4682ac58842abf187f0892d
> 
> Can you try out this or grab pgpool-II-3.1-STABLE head?
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese: http://www.sraoss.co.jp
> 
>> We have a client using pgpool-II 3.1.3 which is exhibiting prepared statements
>> stuck in BIND when using SSL between the client rails 3.2.6 application and
>> the pgpool server, but not otherwise. Here's the info:
>> 
>> Pgpool is being used for load balancing with streaming replication.
>> 
>> When we turn on ssl, queries (mostly prepared insert statements, but also
>> updates and I think that I saw a select once too) get stuck in postgres and
>> just hang forever. The only way to get rid of these stuck queries is to
>> restart pgpool. The only way to prevent them from coming back is to prevent
>> the client from using ssl to connect to the pgpool server, OR to make the
>> client go direct to the db and not use pgpool.
>> 
>> We've tried to reproduce this by turning on full query logging and then using
>> pgreplay to redo a section of queries that caused things to lock up, but that
>> didn't work (even at 4x speed), and we've tried to look at the logs to see if
>> there are any errors or anything, and we weren't able to find anything odd
>> either. We can get our app to do it every time within a minute of us turning
>> it on, but we can't do it without the app, unfortunately, so we don't have a
>> repro case that will work outside of our environment.
>> 
>> It looks like there is an issue with the SSL code, since the db is waiting for
>> data from pgpool (it is in BIND state, and is doing an ssl_read_n()), and
>> pgpool thinks that it's sent all it's data (it thinks it's idle in transaction
>> and is doing a select()).
>> 
>> Supporting info:
>> 
>> [root at devops-staging-pgpool1 pgpool-II]# pgpool -v
>> pgpool-II version 3.1.3 (hatsuiboshi)
>> 
>> [root at devops-staging-db11 ~]# /usr/pgsql-9.1/bin/postgres --version
>> 
>> postgres (PostgreSQL) 9.1.4
>> 
>>> From the DB perspective:
>> 
>> companyname=# select * from pg_stat_activity where procpid = 22890;
>>  datid   |   datname    | procpid | usesysid |   usename    |
>> application_name |  client_addr  | client_hostname | client_port |
>> backend_start         |          xact_start           |         query_start
>>       | waiting |
>> 
>>     current_query
>> 
>> 
>> ----------+--------------+---------+----------+--------------+------------------+---------------+-----------------+-------------+-------------------------------+-------------------------------+------------------------------+---------+-------------------
>> -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>> ----------------------------
>> 15216748 | companyname |   22890 |    16394 | companyname |
>> | 10.11.22.23 |                 |       52847 | 2012-07-17 21:21:37.955751+00
>> | 2012-07-17 21:21:39.604965+00 | 2012-07-17 21:21:44.15175+00 | f       |
>> INSERT INTO "agent
>> _commands" ("agent_instance_id", "created_at", "customer_id",
>> "daemon_completed_at", "daemon_started_at", "last_error", "payload",
>> "priority", "requested_by_id", "status", "type", "updated_at", "uuid") VALUES
>> ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $1
>> 1, $12, $13) RETURNING "id"
>> (1 row)
>> 
>> You can find the locks here:
>> 
>> http://www.hungry.com/~tspencer/pgpoolBugFromDB.txt
>> 
>>> From the DB System perspective:
>> 
>> [root at postgresdb ~]# ps gaxuwww | grep 22890
>> root      7527  0.0  0.0  61196   792 pts/2    S+   23:00   0:00 grep 22890
>> postgres 22890  0.0  0.3 2256480 28408 ?       Ss   21:21   0:00 postgres:
>> companyname companyname 10.11.22.23(52847) BIND
>> 
>> 
>> [root at postgresdb ~]# strace -fvp 22890
>> Process 22890 attached - interrupt to quit
>> recvfrom(9,  <unfinished ...>
>> Process 22890 detached
>> 
>> 
>> [root at postgresdb ~]# gdb /usr/pgsql-9.1/bin/postgres GNU gdb (GDB) CentOS
>> (7.0.1-42.el5.centos)
>> Copyright (C) 2009 Free Software Foundation, Inc.
>> License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html>
>> This is free software: you are free to change and redistribute it.
>> There is NO WARRANTY, to the extent permitted by law.  Type "show copying"
>> and "show warranty" for details.
>> This GDB was configured as "x86_64-redhat-linux-gnu".
>> For bug reporting instructions, please see:
>> <http://www.gnu.org/software/gdb/bugs/>...
>> Reading symbols from /usr/pgsql-9.1/bin/postgres...Reading symbols from
>> /usr/lib/debug/usr/pgsql-9.1/bin/postgres.debug...done.
>> done.
>> 
>> 
>> (gdb) attach 22890
>> Attaching to program: /usr/pgsql-9.1/bin/postgres, process 22890
>> warning: .dynamic section for "/lib64/libcom_err.so.2" is not at the expected
>> address (wrong library or version mismatch?)
>> Reading symbols from /usr/lib64/libxslt.so.1...(no debugging symbols
>> found)...done.
>> Loaded symbols for /usr/lib64/libxslt.so.1
>> Reading symbols from /usr/lib64/libxml2.so.2...(no debugging symbols
>> found)...done.
>> Loaded symbols for /usr/lib64/libxml2.so.2
>> Reading symbols from /lib64/libpam.so.0...(no debugging symbols found)...done.
>> Loaded symbols for /lib64/libpam.so.0
>> Reading symbols from /lib64/libssl.so.6...Reading symbols from
>> /usr/lib/debug/lib64/libssl.so.0.9.8e.debug...done.
>> done.
>> Loaded symbols for /lib64/libssl.so.6
>> Reading symbols from /lib64/libcrypto.so.6...Reading symbols from
>> /usr/lib/debug/lib64/libcrypto.so.0.9.8e.debug...done.
>> done.
>> Loaded symbols for /lib64/libcrypto.so.6
>> Reading symbols from /usr/lib64/libgssapi_krb5.so.2...(no debugging symbols
>> found)...done.
>> Loaded symbols for /usr/lib64/libgssapi_krb5.so.2
>> Reading symbols from /lib64/libcrypt.so.1...Reading symbols from
>> /usr/lib/debug/lib64/libcrypt-2.5.so.debug...done.
>> done.
>> Loaded symbols for /lib64/libcrypt.so.1
>> Reading symbols from /lib64/libdl.so.2...Reading symbols from
>> /usr/lib/debug/lib64/libdl-2.5.so.debug...done.
>> done.
>> Loaded symbols for /lib64/libdl.so.2
>> Reading symbols from /lib64/libm.so.6...Reading symbols from
>> /usr/lib/debug/lib64/libm-2.5.so.debug...done.
>> done.
>> Loaded symbols for /lib64/libm.so.6
>> Reading symbols from /usr/lib64/libldap-2.3.so.0...(no debugging symbols
>> found)...done.
>> Loaded symbols for /usr/lib64/libldap-2.3.so.0
>> Reading symbols from /lib64/libc.so.6...Reading symbols from
>> /usr/lib/debug/lib64/libc-2.5.so.debug...(no debugging symbols found)...done.
>> (no debugging symbols found)...done.
>> Loaded symbols for /lib64/libc.so.6
>> Reading symbols from /usr/lib64/libkrb5.so.3...(no debugging symbols
>> found)...done.
>> Loaded symbols for /usr/lib64/libkrb5.so.3
>> Reading symbols from /lib64/libcom_err.so.2...(no debugging symbols found)...done.
>> Loaded symbols for /lib64/libcom_err.so.2
>> Reading symbols from /usr/lib64/libz.so.1...(no debugging symbols found)...done.
>> Loaded symbols for /usr/lib64/libz.so.1
>> Reading symbols from /lib64/libaudit.so.0...(no debugging symbols found)...done.
>> Loaded symbols for /lib64/libaudit.so.0
>> Reading symbols from /usr/lib64/libk5crypto.so.3...(no debugging symbols
>> found)...done.
>> Loaded symbols for /usr/lib64/libk5crypto.so.3
>> Reading symbols from /usr/lib64/libkrb5support.so.0...(no debugging symbols
>> found)...done.
>> Loaded symbols for /usr/lib64/libkrb5support.so.0
>> Reading symbols from /lib64/libkeyutils.so.1...(no debugging symbols
>> found)...done.
>> Loaded symbols for /lib64/libkeyutils.so.1
>> Reading symbols from /lib64/libresolv.so.2...Reading symbols from
>> /usr/lib/debug/lib64/libresolv-2.5.so.debug...done.
>> done.
>> Loaded symbols for /lib64/libresolv.so.2
>> Reading symbols from /lib64/ld-linux-x86-64.so.2...Reading symbols from
>> /usr/lib/debug/lib64/ld-2.5.so.debug...done.
>> done.
>> Loaded symbols for /lib64/ld-linux-x86-64.so.2
>> Reading symbols from /usr/lib64/liblber-2.3.so.0...(no debugging symbols
>> found)...done.
>> Loaded symbols for /usr/lib64/liblber-2.3.so.0
>> Reading symbols from /usr/lib64/libsasl2.so.2...(no debugging symbols
>> found)...done.
>> Loaded symbols for /usr/lib64/libsasl2.so.2
>> Reading symbols from /lib64/libselinux.so.1...(no debugging symbols found)...done.
>> Loaded symbols for /lib64/libselinux.so.1
>> Reading symbols from /lib64/libsepol.so.1...(no debugging symbols found)...done.
>> Loaded symbols for /lib64/libsepol.so.1
>> Reading symbols from /lib64/libnss_files.so.2...Reading symbols from
>> /usr/lib/debug/lib64/libnss_files-2.5.so.debug...done.
>> done.
>> Loaded symbols for /lib64/libnss_files.so.2
>> Reading symbols from /usr/pgsql-9.1/lib/pgpool-regclass.so...done.
>> Loaded symbols for /usr/pgsql-9.1/lib/pgpool-regclass.so
>> warning: no loadable sections found in added symbol-file system-supplied DSO
>> at 0x7fffb09fd000
>> 0x00002b89b7b05c95 in __libc_recv (fd=9, buf=0xa389af0, n=5, flags=0) at
>> ../sysdeps/unix/sysv/linux/x86_64/recv.c:30
>> 30	    return INLINE_SYSCALL (recvfrom, 6, fd, buf, n, flags, NULL, NULL);
>> 
>> 
>> (gdb) bt
>> #0  0x00002b89b7b05c95 in __libc_recv (fd=9, buf=0xa389af0, n=5, flags=0) at
>> ../sysdeps/unix/sysv/linux/x86_64/recv.c:30
>> #1  0x000000000058017d in recv (h=0xa37aee0,     buf=0xa389af0
>> "\027\003\001\003\020\324]\335o\233\060\020\337\237\261G\304^\332-MiJB\232\267i\232\246\252Z7\355\363\241\215\"c`\261\232\002\262\241[4\345\177\237\017;\201h&\020[Q\303K\022.\234u\376\331\230\373\360\235[\256\266\a\265\206\275\201\241VW\202
>> \317\030S\342p\373\376\333\317O_n\256o?Ì®?\377\030\035\065 J\355\256Ñ
>> ,\362\316\353\346T\253\320s\206|\363\071)\355\352\365\276\r\365Ӝ?\"Vg\224:\203vA烎\201\253T\001\232b\037h\t\373O\352\060l\nA\350\263.\b\325\016\024\021\032
>> M\326_\351\243&\347<eLW\340yJ\022]^\234\247\354LÌ…3\261\227Ë !]"..., size=5)
>> at /usr/include/bits/socket2.h:35
>> #2  my_sock_read (h=0xa37aee0,     buf=0xa389af0
>> "\027\003\001\003\020\324]\335o\233\060\020\337\237\261G\304^\332-MiJB\232\267i\232\246\252Z7\355\363\241\215\"c`\261\232\002\262\241[4\345\177\237\017;\201h&\020[Q\303K\022.\234u\376\331\230\373\360\235[\256\266\a\265\206\275\201\241VW\202
>> \317\030S\342p\373\376\333\317O_n\256o?Ì®?\377\030\035\065 J\355\256Ñ
>> ,\362\316\353\346T\253\320s\206|\363\071)\355\352\365\276\r\365Ӝ?\"Vg\224:\203vA烎\201\253T\001\232b\037h\t\373O\352\060l\nA\350\263.\b\325\016\024\021\032
>> M\326_\351\243&\347<eLW\340yJ\022]^\234\247\354LÌ…3\261\227Ë !]"..., size=5)
>> at be-secure.c:429
>> #3  0x00002b89b6c2e81f in BIO_read (b=0xa37aee0, out=0xa389af0, outl=5) at
>> bio_lib.c:212
>> #4  0x00002b89b698d10d in ssl3_read_n (s=0xa362060, n=5, max=<value optimized
>> out>, extend=<value optimized out>) at s3_pkt.c:198
>> #5  0x00002b89b698d5dd in ssl3_get_record (s=0xa362060, type=23,
>> buf=0xad0b80
>> "no\\\\\\\",\\\\\\\"key_value_delimiter\\\\\\\":\\\\\\\"=\\\\\\\",\\\\\\\"comment_character\\\\\\\":\\\\\\\"#\\\\\\\",\\\\\\\"config_section\\\\\\\":\\\\\\\"\\\\\\\",\\\\\\\"check_id\\\\\\\":78726},{\\\\\\\"type\\\\\\\":\\\\\\\"configuration\\\\\\\",\\\\\\\"target\\\\\\\":\\\\\\\"/etc/sys"...,
>> len=8192, peek=0) at s3_pkt.c:266
>> #6  ssl3_read_bytes (s=0xa362060, type=23,     buf=0xad0b80
>> "no\\\\\\\",\\\\\\\"key_value_delimiter\\\\\\\":\\\\\\\"=\\\\\\\",\\\\\\\"comment_character\\\\\\\":\\\\\\\"#\\\\\\\",\\\\\\\"config_section\\\\\\\":\\\\\\\"\\\\\\\",\\\\\\\"check_id\\\\\\\":78726},{\\\\\\\"type\\\\\\\":\\\\\\\"configuration\\\\\\\",\\\\\\\"target\\\\\\\":\\\\\\\"/etc/sys"...,
>> len=8192, peek=0) at s3_pkt.c:854
>> #7  0x00002b89b698a751 in ssl3_read_internal (s=0xa362060, buf=0xad0b80,
>> len=8192, peek=0) at s3_lib.c:2428
>> #8  0x00000000005812af in secure_read (port=0xa361a90, ptr=0xad0b80, len=8192)
>> at be-secure.c:251
>> #9  0x000000000058a771 in pq_recvbuf () at pqcomm.c:816
>> #10 0x000000000058ab77 in pq_getbyte () at pqcomm.c:857
>> #11 0x0000000000623530 in ReadCommand (argc=<value optimized out>, argv=0x1,
>> username=0x48 <Address 0x48 out of bounds>) at postgres.c:345
>> #12 PostgresMain (argc=<value optimized out>, argv=0x1, username=0x48 <Address
>> 0x48 out of bounds>) at postgres.c:3932
>> #13 0x00000000005e7ae4 in ServerLoop () at postmaster.c:3611
>> #14 0x00000000005e8861 in PostmasterMain (argc=5, argv=0xa344970) at
>> postmaster.c:1121
>> #15 0x000000000058c5be in main (argc=5, argv=<value optimized out>) at main.c:199
>> (gdb) quit
>> A debugging session is active.
>> 
>> 	Inferior 1 [process 22890] will be detached.
>> 
>> Quit anyway? (y or n) y
>> Detaching from program: /usr/pgsql-9.1/bin/postgres, process 22890
>> [root at postgresdb ~]#
>>> From the pgpool system:
>> 
>> [root at pgpoolhost pgpool-II]# /usr/sbin/lsof | grep TCP | grep 52847
>> pgpool     4160     root    8u     IPv4           28483861                 TCP
>> pgpoolhost.localdomain:52847->postgresdb:postgres (ESTABLISHED)
>> 
>> [root at pgpoolhost pgpool-II]# ps gaxuwww | grep 4160
>> root      1991  0.0  0.0  61200   784 pts/1    R+   23:00   0:00 grep 4160
>> root      4160  0.0  0.5  68996  5516 ?        S    21:15   0:00 pgpool:
>> companyname companyname 10.11.12.13(51372) idle in transaction
>> 
>> 
>> [root at pgpoolhost pgpool-II]# gdb /usr/bin/pgpool
>> GNU gdb (GDB) Red Hat Enterprise Linux (7.0.1-37.el5_7.1)
>> Copyright (C) 2009 Free Software Foundation, Inc.
>> License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html>
>> This is free software: you are free to change and redistribute it.
>> There is NO WARRANTY, to the extent permitted by law.  Type "show copying"
>> and "show warranty" for details.
>> This GDB was configured as "x86_64-redhat-linux-gnu".
>> For bug reporting instructions, please see:
>> <http://www.gnu.org/software/gdb/bugs/>...
>> Reading symbols from /usr/bin/pgpool...Reading symbols from
>> /usr/lib/debug/usr/bin/pgpool.debug...done.
>> done.
>> 
>> 
>> (gdb) attach 4160
>> Attaching to program: /usr/bin/pgpool, process 4160
>> Reading symbols from /usr/pgsql-9.1/lib/libpq.so.5...(no debugging symbols
>> found)...done.
>> Loaded symbols for /usr/pgsql-9.1/lib/libpq.so.5
>> Reading symbols from /usr/lib64/libpcp.so.0...Reading symbols from
>> /usr/lib/debug/usr/lib64/libpcp.so.0.0.0.debug...done.
>> done.
>> Loaded symbols for /usr/lib64/libpcp.so.0
>> Reading symbols from /lib64/libpam.so.0...(no debugging symbols found)...done.
>> Loaded symbols for /lib64/libpam.so.0
>> Reading symbols from /lib64/libssl.so.6...warning: the debug information found
>> in "/usr/lib/debug//lib64/libssl.so.0.9.8e.debug" does not match
>> "/lib64/libssl.so.6" (CRC mismatch).
>> 
>> warning: the debug information found in
>> "/usr/lib/debug/lib64/libssl.so.0.9.8e.debug" does not match
>> "/lib64/libssl.so.6" (CRC mismatch).
>> 
>> (no debugging symbols found)...done.
>> Loaded symbols for /lib64/libssl.so.6
>> Reading symbols from /lib64/libcrypto.so.6...warning: the debug information
>> found in "/usr/lib/debug//lib64/libcrypto.so.0.9.8e.debug" does not match
>> "/lib64/libcrypto.so.6" (CRC mismatch).
>> 
>> warning: the debug information found in
>> "/usr/lib/debug/lib64/libcrypto.so.0.9.8e.debug" does not match
>> "/lib64/libcrypto.so.6" (CRC mismatch).
>> 
>> (no debugging symbols found)...done.
>> Loaded symbols for /lib64/libcrypto.so.6
>> Reading symbols from /lib64/libcrypt.so.1...warning: the debug information
>> found in "/usr/lib/debug//lib64/libcrypt-2.5.so.debug" does not match
>> "/lib64/libcrypt.so.1" (CRC mismatch).
>> 
>> warning: the debug information found in
>> "/usr/lib/debug/lib64/libcrypt-2.5.so.debug" does not match
>> "/lib64/libcrypt.so.1" (CRC mismatch).
>> 
>> (no debugging symbols found)...done.
>> Loaded symbols for /lib64/libcrypt.so.1
>> Reading symbols from /lib64/libresolv.so.2...warning: the debug information
>> found in "/usr/lib/debug//lib64/libresolv-2.5.so.debug" does not match
>> "/lib64/libresolv.so.2" (CRC mismatch).
>> 
>> warning: the debug information found in
>> "/usr/lib/debug/lib64/libresolv-2.5.so.debug" does not match
>> "/lib64/libresolv.so.2" (CRC mismatch).
>> 
>> (no debugging symbols found)...done.
>> Loaded symbols for /lib64/libresolv.so.2
>> Reading symbols from /lib64/libnsl.so.1...warning: the debug information found
>> in "/usr/lib/debug//lib64/libnsl-2.5.so.debug" does not match
>> "/lib64/libnsl.so.1" (CRC mismatch).
>> 
>> warning: the debug information found in
>> "/usr/lib/debug/lib64/libnsl-2.5.so.debug" does not match "/lib64/libnsl.so.1"
>> (CRC mismatch).
>> 
>> (no debugging symbols found)...done.
>> Loaded symbols for /lib64/libnsl.so.1
>> Reading symbols from /lib64/libm.so.6...warning: the debug information found
>> in "/usr/lib/debug//lib64/libm-2.5.so.debug" does not match "/lib64/libm.so.6"
>> (CRC mismatch).
>> 
>> warning: the debug information found in
>> "/usr/lib/debug/lib64/libm-2.5.so.debug" does not match "/lib64/libm.so.6"
>> (CRC mismatch).
>> 
>> (no debugging symbols found)...done.
>> Loaded symbols for /lib64/libm.so.6
>> Reading symbols from /lib64/libc.so.6...warning: the debug information found
>> in "/usr/lib/debug//lib64/libc-2.5.so.debug" does not match "/lib64/libc.so.6"
>> (CRC mismatch).
>> 
>> warning: the debug information found in
>> "/usr/lib/debug/lib64/libc-2.5.so.debug" does not match "/lib64/libc.so.6"
>> (CRC mismatch).
>> 
>> (no debugging symbols found)...done.
>> Loaded symbols for /lib64/libc.so.6
>> Reading symbols from /usr/lib64/libgssapi_krb5.so.2...(no debugging symbols
>> found)...done.
>> Loaded symbols for /usr/lib64/libgssapi_krb5.so.2
>> Reading symbols from /usr/lib64/libldap_r-2.3.so.0...(no debugging symbols
>> found)...done.
>> Loaded symbols for /usr/lib64/libldap_r-2.3.so.0
>> Reading symbols from /lib64/libpthread.so.0...warning: the debug information
>> found in "/usr/lib/debug//lib64/libpthread-2.5.so.debug" does not match
>> "/lib64/libpthread.so.0" (CRC mismatch).
>> 
>> warning: the debug information found in
>> "/usr/lib/debug/lib64/libpthread-2.5.so.debug" does not match
>> "/lib64/libpthread.so.0" (CRC mismatch).
>> 
>> (no debugging symbols found)...done.
>> [Thread debugging using libthread_db enabled]
>> Loaded symbols for /lib64/libpthread.so.0
>> Reading symbols from /lib64/libdl.so.2...warning: the debug information found
>> in "/usr/lib/debug//lib64/libdl-2.5.so.debug" does not match
>> "/lib64/libdl.so.2" (CRC mismatch).
>> 
>> warning: the debug information found in
>> "/usr/lib/debug/lib64/libdl-2.5.so.debug" does not match "/lib64/libdl.so.2"
>> (CRC mismatch).
>> 
>> (no debugging symbols found)...done.
>> Loaded symbols for /lib64/libdl.so.2
>> Reading symbols from /lib64/libaudit.so.0...(no debugging symbols found)...done.
>> Loaded symbols for /lib64/libaudit.so.0
>> Reading symbols from /usr/lib64/libkrb5.so.3...(no debugging symbols
>> found)...done.
>> Loaded symbols for /usr/lib64/libkrb5.so.3
>> Reading symbols from /lib64/libcom_err.so.2...(no debugging symbols found)...done.
>> Loaded symbols for /lib64/libcom_err.so.2
>> Reading symbols from /usr/lib64/libk5crypto.so.3...(no debugging symbols
>> found)...done.
>> Loaded symbols for /usr/lib64/libk5crypto.so.3
>> Reading symbols from /usr/lib64/libz.so.1...(no debugging symbols found)...done.
>> Loaded symbols for /usr/lib64/libz.so.1
>> Reading symbols from /lib64/ld-linux-x86-64.so.2...warning: the debug
>> information found in "/usr/lib/debug//lib64/ld-2.5.so.debug" does not match
>> "/lib64/ld-linux-x86-64.so.2" (CRC mismatch).
>> 
>> warning: the debug information found in "/usr/lib/debug/lib64/ld-2.5.so.debug"
>> does not match "/lib64/ld-linux-x86-64.so.2" (CRC mismatch).
>> 
>> (no debugging symbols found)...done.
>> Loaded symbols for /lib64/ld-linux-x86-64.so.2
>> Reading symbols from /usr/lib64/libkrb5support.so.0...(no debugging symbols
>> found)...done.
>> Loaded symbols for /usr/lib64/libkrb5support.so.0
>> Reading symbols from /lib64/libkeyutils.so.1...(no debugging symbols
>> found)...done.
>> Loaded symbols for /lib64/libkeyutils.so.1
>> Reading symbols from /usr/lib64/liblber-2.3.so.0...(no debugging symbols
>> found)...done.
>> Loaded symbols for /usr/lib64/liblber-2.3.so.0
>> Reading symbols from /usr/lib64/libsasl2.so.2...(no debugging symbols
>> found)...done.
>> Loaded symbols for /usr/lib64/libsasl2.so.2
>> Reading symbols from /lib64/libselinux.so.1...(no debugging symbols found)...done.
>> Loaded symbols for /lib64/libselinux.so.1
>> Reading symbols from /lib64/libsepol.so.1...(no debugging symbols found)...done.
>> Loaded symbols for /lib64/libsepol.so.1
>> warning: no loadable sections found in added symbol-file system-supplied DSO
>> at 0x7fff307fd000
>> 0x00002b47147dd763 in __select_nocancel () from /lib64/libc.so.6
>> 
>> 
>> (gdb) bt
>> #0  0x00002b47147dd763 in __select_nocancel () from /lib64/libc.so.6
>> #1  0x0000000000415a61 in pool_check_fd (cp=<value optimized out>) at
>> pool_process_query.c:1062
>> #2  0x000000000041a244 in pool_read (cp=0x405c0d0, buf=0x7fff307a7e7f, len=1)
>> at pool_stream.c:138
>> #3  0x00000000004190cd in read_kind_from_backend (frontend=0x4046d50,
>> backend=0x4059f30, decided_kind=0x7fff307a8087 "2\001")
>>    at pool_process_query.c:3502
>> #4  0x0000000000443af2 in ProcessBackendResponse (frontend=0x4046d50,
>> backend=0x4059f30, state=0x7fff307a8278,     num_fields=0x7fff307a827c) at
>> pool_proto_modules.c:2191
>> #5  0x0000000000417be6 in pool_process_query (frontend=0x4046d50,
>> backend=0x4059f30, reset_request=0) at pool_process_query.c:428
>> #6  0x000000000040a67a in do_child (unix_fd=5, inet_fd=6) at child.c:354
>> #7  0x0000000000404a05 in fork_a_child (unix_fd=5, inet_fd=6, id=193) at
>> main.c:1083
>> #8  0x0000000000404d07 in reaper () at main.c:2168
>> #9  0x0000000000407cf5 in main (argc=<value optimized out>, argv=<value
>> optimized out>) at main.c:596
>> (gdb) quit
>> A debugging session is active.
>> 
>> 	Inferior 1 [process 4160] will be detached.
>> 
>> Quit anyway? (y or n) y
>> Detaching from program: /usr/bin/pgpool, process 4160
>> [root at pgpoolhost pgpool-II]# strace -fvp 4160
>> Process 4160 attached - interrupt to quit
>> select(9, [8], NULL, [8], NULL <unfinished ...>
>> Process 4160 detached
>> [root at pgpoolhost pgpool-II]#
>> Finally, the pgpool.conf:
>> 
>> #
>> # pgpool-II configuration file sample for Stream replication/Hot standby.
>> # $Header:
>> /cvsroot/pgpool/pgpool-web/contrib_docs/simple_sr_setting2/pgpool.conf,v 1.1
>> 2011/03/11 05:29:13 t-ishii Exp $
>> 
>> # Host name or IP address to listen on: '*' for all, '' for no TCP/IP
>> # connections
>> listen_addresses = '*'
>> 
>> # Port number for pgpool
>> port = 5432
>> 
>> # Port number for pgpool communication manager
>> pcp_port = 9898
>> 
>> # Unix domain socket path. (The Debian package defaults to
>> # /var/run/postgresql.)
>> socket_dir = '/tmp'
>> 
>> # Unix domain socket path for pgpool communication manager.
>> # (Debian package defaults to /var/run/postgresql)
>> pcp_socket_dir = '/tmp'
>> 
>> # Unix domain socket path for the backend. Debian package defaults to
>> /var/run/postgresql!
>> #backend_socket_dir = '/tmp'
>> 
>> # pgpool communication manager timeout. 0 means no timeout. This parameter is
>> ignored now.
>> pcp_timeout = 10
>> 
>> # number of pre-forked child process
>> num_init_children = 200
>> 
>> # Number of connection pools allowed for a child process
>> max_pool = 2
>> 
>> # If idle for this many seconds, child exits. 0 means no timeout.
>> child_life_time = 300
>> 
>> # If idle for this many seconds, connection to PostgreSQL closes.
>> # 0 means no timeout.
>> connection_life_time = 0
>> 
>> # If child_max_connections connections were received, child exits.
>> # 0 means no exit.
>> child_max_connections = 0
>> 
>> # If client_idle_limit is n (n > 0), the client is forced to be
>> # disconnected whenever after n seconds idle (even inside an explicit
>> # transactions!)
>> # 0 means no disconnect.
>> #client_idle_limit = 3600
>> client_idle_limit = 0
>> 
>> # Maximum time in seconds to complete client authentication.
>> # 0 means no timeout.
>> authentication_timeout = 60
>> 
>> # Logging directory (not really, is actually just state dir)
>> logdir = '/tmp'
>> 
>> # where logs actually go
>> log_destination = 'syslog'
>> 
>> # pid file name
>> pid_file_name = '/var/run/pgpool/pgpool.pid'
>> 
>> # Replication mode
>> replication_mode = false
>> 
>> # Load balancing mode, i.e., all SELECTs are load balanced.
>> load_balance_mode = true
>> 
>> # If there's a disagreement with the packet kind sent from backend,
>> # then degenrate the node which is most likely "minority". If false,
>> # just force to exit this session.
>> replication_stop_on_mismatch = false
>> 
>> # If there's a disagreement with the number of affected tuples in
>> # UPDATE/DELETE, then degenrate the node which is most likely
>> # "minority".
>> # If false, just abort the transaction to keep the consistency.
>> failover_if_affected_tuples_mismatch = false
>> 
>> # If true, replicate SELECT statement when replication_mode or parallel_mode
>> is enabled.
>> # A priority of replicate_select is higher than load_balance_mode.
>> replicate_select = false
>> 
>> # Semicolon separated list of queries to be issued at the end of a
>> # session
>> reset_query_list = 'ABORT; DISCARD ALL'
>> # for 8.2 or older this should be as follows.
>> #reset_query_list = 'ABORT; RESET ALL; SET SESSION AUTHORIZATION DEFAULT'
>> 
>> # white_function_list is a comma separated list of function names
>> # those do not write to database. Any functions not listed here
>> # are regarded to write to database and SELECTs including such
>> # writer-functions will be executed on master(primary) in master/slave
>> # mode, or executed on all DB nodes in replication mode.
>> #
>> # black_function_list is a comma separated list of function names
>> # those write to database. Any functions not listed here
>> # are regarded not to write to database and SELECTs including such
>> # read-only-functions will be executed on any DB nodes.
>> #
>> # You cannot make full both white_function_list and
>> # black_function_list at the same time. If you specify something in
>> # one of them, you should make empty other.
>> #
>> # Pre 3.0 pgpool-II recognizes nextval and setval in hard coded
>> # way. Following setting will do the same as the previous version.
>> # white_function_list = ''
>> # black_function_list = 'nextval,setval'
>> white_function_list = ''
>> black_function_list = 'nextval,setval'
>> 
>> # If true print timestamp on each log line.
>> print_timestamp = false
>> 
>> # If true, operate in master/slave mode.
>> master_slave_mode = true
>> 
>> # Master/slave sub mode. either 'slony' or 'stream'. Default is 'slony'.
>> master_slave_sub_mode = 'stream'
>> 
>> # If the standby server delays more than delay_threshold,
>> # any query goes to the primary only. The unit is in bytes.
>> # 0 disables the check. Default is 0.
>> # Note that health_check_period required to be greater than 0
>> # to enable the functionality.
>> delay_threshold = 100000
>> 
>> # 'always' logs the standby delay whenever health check runs.
>> # 'if_over_threshold' logs only if the delay exceeds delay_threshold.
>> # 'none' disables the delay log.
>> log_standby_delay = 'if_over_threshold'
>> 
>> # If true, cache connection pool.
>> connection_cache = true
>> 
>> # Health check timeout. 0 means no timeout.
>> health_check_timeout = 10
>> 
>> # Health check period. 0 means no health check.
>> health_check_period = 20
>> 
>> # Health check user
>> health_check_user = 'health_check'
>> 
>> sr_check_user = 'health_check'
>> sr_check_period = 5
>> 
>> # Execute command by failover.
>> # special values: %d = node id
>> # %h = host name
>> # %p = port number
>> # %D = database cluster path
>> # %m = new master node id
>> # %H = hostname of the new master node
>> # %M = old master node id
>> # %P = old primary node id
>> # %% = '%' character
>> #
>> failover_command = '/usr/local/etc/failover.sh %d "%h" %p %D %m %M "%H" %P'
>> 
>> # Execute command by failback.
>> # special values: %d = node id
>> # %h = host name
>> # %p = port number
>> # %D = database cluster path
>> # %m = new master node id
>> # %H = hostname of the new master node
>> # %M = old master node id
>> # %P = old primary node id
>> # %% = '%' character
>> #
>> failback_command = ''
>> 
>> # If true, trigger fail over when writing to the backend communication
>> # socket fails. This is the same behavior of pgpool-II 2.2.x or
>> # earlier. If set to false, pgpool will report an error and disconnect
>> # the session.
>> fail_over_on_backend_error = true
>> 
>> # If true, automatically locks a table with INSERT statements to keep
>> # SERIAL data consistency. If the data does not have SERIAL data
>> # type, no lock will be issued. An /*INSERT LOCK*/ comment has the
>> # same effect. A /*NO INSERT LOCK*/ comment disables the effect.
>> insert_lock = false
>> 
>> # If true, ignore leading white spaces of each query while pgpool judges
>> # whether the query is a SELECT so that it can be load balanced. This
>> # is useful for certain APIs such as DBI/DBD which is known to adding an
>> # extra leading white space.
>> ignore_leading_white_space = true
>> 
>> # If true, print all statements to the log. Like the log_statement option
>> # to PostgreSQL, this allows for observing queries without engaging in full
>> # debugging.
>> log_statement = false
>> 
>> # If true, print all statements to the log. Similar to log_statement except
>> # that prints DB node id and backend process id info.
>> log_per_node_statement = false
>> 
>> # If true, incoming connections will be printed to the log.
>> log_connections = true
>> 
>> # If true, hostname will be shown in ps status. Also shown in
>> # connection log if log_connections = true.
>> # Be warned that this feature will add overhead to look up hostname.
>> log_hostname = false
>> 
>> # if non 0, run in parallel query mode
>> parallel_mode = false
>> 
>> # if non 0, use query cache
>> enable_query_cache = false
>> 
>> #set pgpool2 hostname
>> pgpool2_hostname = ''
>> 
>> # system DB info
>> system_db_hostname = 'localhost'
>> system_db_port = 5432
>> system_db_dbname = 'pgpool'
>> system_db_schema = 'pgpool_catalog'
>> system_db_user = 'pgpool'
>> system_db_password = ''
>> 
>> 
>> # - HBA -
>> 
>> # If true, use pool_hba.conf for client authentication.
>> enable_pool_hba = false
>> 
>> # - online recovery -
>> # online recovery user
>> recovery_user = 'postgres'
>> 
>> # online recovery password
>> recovery_password = 'PWORD'
>> 
>> # execute a command in first stage.
>> recovery_1st_stage_command = 'basebackup.sh'
>> 
>> # execute a command in second stage.
>> recovery_2nd_stage_command = ''
>> 
>> # maximum time in seconds to wait for the recovering node's postmaster
>> # start-up. 0 means no wait.
>> # this is also used as a timer waiting for clients disconnected before
>> # starting 2nd stage
>> recovery_timeout = 90
>> 
>> # If client_idle_limit_in_recovery is n (n > 0), the client is forced
>> # to be disconnected whenever after n seconds idle (even inside an
>> # explicit transactions!) in the second stage of online recovery.
>> # n = -1 forces clients to be disconnected immediately.
>> # 0 disables this functionality(wait forever).
>> # This parameter only takes effect in recovery 2nd stage.
>> client_idle_limit_in_recovery = 0
>> 
>> # Specify table name to lock. This is used when rewriting lo_creat
>> # command in replication mode. The table must exist and has writable
>> # permission to public. If the table name is '', no rewriting occurs.
>> lobj_lock_table = ''
>> 
>> # If true, enable SSL support for both frontend and backend connections.
>> # note that you must also set ssl_key and ssl_cert for SSL to work in
>> # the frontend connections.
>> ssl = true
>> #ssl = false
>> # path to the SSL private key file
>> ssl_key = '/etc/pgpool-II/server.key'
>> # path to the SSL public certificate file
>> ssl_cert = '/etc/pgpool-II/server.crt'
>> 
>> # If either ssl_ca_cert or ssl_ca_cert_dir is set, then certificate
>> # verification will be performed to establish the authenticity of the
>> # certificate. If neither is set to a nonempty string then no such
>> # verification takes place. ssl_ca_cert should be a path to a single
>> # PEM format file containing CA root certificate(s), whereas ssl_ca_cert_dir
>> # should be a directory containing such files. These are analagous to the
>> # -CAfile and -CApath options to openssl verify(1), respectively.
>> #ssl_ca_cert = ''
>> #ssl_ca_cert = '/etc/pgpool-II/root.crt'
>> #ssl_ca_cert_dir = ''
>> 
>> # Debug message verbosity level. 0: no message, 1 <= : more verbose
>> debug_level = 1
>> 
>> # Noe info goes here!
>> # backend_hostname, backend_port, backend_weight
>> # here are examples
>> backend_hostname0 = '10.11.22.23'
>> backend_port0 = 5432
>> backend_weight0 = 1
>> backend_data_directory0 = '/var/lib/pgsql/9.1/data'
>> 
>> backend_hostname1 = '10.11.32.33'
>> backend_port1 = 5432
>> backend_weight1 = 1
>> backend_data_directory1 = '/var/lib/pgsql/9.1/data'
>> 
>> 
>> 
>> 
>> _______________________________________________
>> pgpool-general mailing list
>> pgpool-general at pgpool.net
>> http://www.pgpool.net/mailman/listinfo/pgpool-general

---
Jeff Frost <jeff at pgexperts.com>
CTO, PostgreSQL Experts, Inc.
Phone: 1-888-PG-EXPRT x506
FAX: 415-762-5122
http://www.pgexperts.com/ 








More information about the pgpool-general mailing list