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

Karl von Randow karl+pgpool at cactuslab.com
Thu Jul 19 11:16:44 JST 2012


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
> *********************************************



More information about the pgpool-general mailing list