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

Tatsuo Ishii ishii at postgresql.org
Thu Jul 19 10:13:21 JST 2012


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


More information about the pgpool-general mailing list