[pgpool-general: 762] Re: Problem with pgpool when using SSL, for client/pgpool communication
Tatsuo Ishii
ishii at postgresql.org
Thu Jul 19 11:36:57 JST 2012
Thank you for testing!
Next time pgpool hungs, could you please send me core and pgpool
executable binary off list? I assume you are using Linux x86_64.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp
> We have been running this patch for a week and it has been very
> successful, thank you.
>
> We are still having some hung connections - they stay hung forever
> until we kill the process on the DB server, but now we get maybe 1 a
> day whereas we were getting several an hour before.
>
> So perhaps there's still a case where this breaks? We are also using
> SSL for client/pgpool and pgpool/server communication.
>
> I am happy to test patches. We have been reliably reproducing these
> hung connections so I can confirm success after a couple of days!
> Thank you _very much_ for your attention on this list, it is most
> appreciated.
>
> Best regards,
> Karl
>
> On 19/07/2012 1:32 p.m., pgpool-general-request at pgpool.net wrote:
>> Message: 1
>> Date: Wed, 18 Jul 2012 18:31:54 -0700
>> From: Jeff Frost <jeff at pgexperts.com>
>> To: Tatsuo Ishii <ishii at postgresql.org>
>> Cc: pgpool-general at pgpool.net
>> Subject: [pgpool-general: 760] Re: Problem with pgpool when using SSL
>> for client/pgpool communication
>> Message-ID: <6919E518-DA5D-4D30-A653-256E6D03BA46 at pgexperts.com>
>> Content-Type: text/plain; charset=windows-1252
>>
>> 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/
>>
>>
>>
>>
>>
>>
>>
>>
>> ------------------------------
>>
>> _______________________________________________
>> pgpool-general mailing list
>> pgpool-general at pgpool.net
>> http://www.pgpool.net/mailman/listinfo/pgpool-general
>>
>>
>> End of pgpool-general Digest, Vol 9, Issue 30
>> *********************************************
>
> _______________________________________________
> 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