Re: [Pgpool-general] Error with SSL Consection
Can you try like this? 1) configure num_init_children to 1. 2) restart pgpool 3) find pgpool child process pid(hint: ps status is pgpool: wait for connection request) 4) strace -p the_pid 5) connect to pgpool strace should show where pgpool tried to find ssl cert file. Make sure that it is /opt/pgpool/ssl/server.cert. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp Hello everyone in the list, I am proving the connecting SSL through with Pgpool, the pgpool configuration parameters related with SSL are: # - SSL Connections - ssl = on ssl_key = '/opt/pgpool/ssl/server.key' ssl_cert = '/opt/pgpool/ssl/server.cert' #ssl_ca_cert = '' #ssl_ca_cert_dir = '' But in pgpool.log file i see this error: 2011-11-22 06:42:02 LOG: pid 14011: connection received: host=10.13.4.201 port=40434 2011-11-22 06:42:02 DEBUG: pid 14011: Protocol Major: 1234 Minor: 5679 database: user: 2011-11-22 06:42:02 DEBUG: pid 14011: SSLRequest from client 2011-11-22 06:42:02 ERROR: pid 14011: pool_ssl: Loading SSL certificate: No such file or directory Nevertheless I can connect to postgreSQL through Pgpool. Please can anybody tell me, What could be the reason for this error? I am using Pgpool-II V_3.1_stable with PostgreSQL 9.0.5. Regards. ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] pgpool limitations
I have found that sometimes the client connections get disconnected and the new ones are established. What I do is I get the PIDs using pcp_proc_count before running pcp_attach_node and then run pcp_proc_count to check if the PIDs remain same. I found that the behaviour is random. When can this happen? From: Tatsuo Ishii is...@sraoss.co.jp To: singh.gurj...@gmail.com Cc: pgpool-general@pgfoundry.org; pgpool-hack...@pgfoundry.org Sent: Thursday, August 11, 2011 6:11 AM Subject: Re: [Pgpool-general] pgpool limitations Is there something in the works to enable this, or is this feature still in design phase? If it is already being/been developed, I wish to know if this can be back-patched to a point release of pgpool 3.0.x. It has been already in pgpool-II 3.1 alpha version. Currently there's no plan to back-patching to 3.0.x. I certainly hope we won't backpatch a new feature. That would be insane. I don't consider this a new feature. I'd say this is unexpected side-effect (a.k.a bug) of pcp_attach_node, since nowhere in the docs does is say that invoking pcp_attach_node would drop all client connections. This behavior has not been changed since pcp_attach_node was born in 2006. Moreover, the enhancement in 3.1 is only for steaming replication mode. Other modes including replication mode does not take advantage of this. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] Maintain connections if backend fails
Thank you very much Tatsuo Ishii. - Mensaje original - De: Tatsuo Ishii is...@sraoss.co.jp Para: joaquin llano joaquin.ll...@cpxextremadura.com CC: pgpool-general@pgfoundry.org Enviados: Miércoles, 23 de Noviembre 2011 8:42:06 Asunto: Re: [Pgpool-general] Maintain connections if backend fails I'm using Pgpool2 with Streaming Replication over 2 PostgreSQL nodes. I have one server with Pgpool 3.0.3 and two servers with PostgreSQL 9.1. All works with Debian Squeeze (6.0.3) and Backports Repositories. If one of the back-ends nodes fails, clients connected to Pgpool are disconnected with lost connection message. For example: I connect to Pgpool: user@client_machine$ psql -h www.xxx.yyy.zzz -p 5433 postgres postgres postgres=# \l LOG: sentencia: SELECT d.datname as Name, pg_catalog.pg_get_userbyid(d.datdba) as Owner, pg_catalog.pg_encoding_to_char(d.encoding) as Encoding, d.datcollate as Collate, d.datctype as Ctype, pg_catalog.array_to_string(d.datacl, E'\n') AS Access privileges FROM pg_catalog.pg_database d ORDER BY 1; LOG: duración: 2.247 ms Listado de base de datos Nombre | Dueño | Codificación | Collate | Ctype | Privilegios ---+--+--+-+-+--- postgres | postgres | UTF8 | es_ES.UTF-8 | es_ES.UTF-8 | prueba1 | postgres | UTF8 | es_ES.UTF-8 | es_ES.UTF-8 | prueba2 | postgres | UTF8 | es_ES.UTF-8 | es_ES.UTF-8 | template0 | postgres | UTF8 | es_ES.UTF-8 | es_ES.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | es_ES.UTF-8 | es_ES.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres (5 filas) So, if I turn off PostgreSQL daemon on one of the backends: root@postgresql2:~# /etc/init.d/postgresql stop Stopping PostgreSQL 9.1 database server: main. If I try show databases I get this lost connection error: postgres=# \l La conexión al servidor se ha perdido. Intentando reiniciar: con éxito. If I rerun the query its works: postgres=# \l LOG: sentencia: SELECT d.datname as Name, pg_catalog.pg_get_userbyid(d.datdba) as Owner, pg_catalog.pg_encoding_to_char(d.encoding) as Encoding, d.datcollate as Collate, d.datctype as Ctype, pg_catalog.array_to_string(d.datacl, E'\n') AS Access privileges FROM pg_catalog.pg_database d ORDER BY 1; LOG: duración: 1.834 ms Listado de base de datos Nombre | Dueño | Codificación | Collate | Ctype | Privilegios ---+--+--+-+-+--- postgres | postgres | UTF8 | es_ES.UTF-8 | es_ES.UTF-8 | prueba1 | postgres | UTF8 | es_ES.UTF-8 | es_ES.UTF-8 | prueba2 | postgres | UTF8 | es_ES.UTF-8 | es_ES.UTF-8 | template0 | postgres | UTF8 | es_ES.UTF-8 | es_ES.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | es_ES.UTF-8 | es_ES.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres (5 filas) Is there any way for not loosing connections when one of the back-end nodes fails? No. This is an expected behavior of pgpool-II. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] Error with SSL Consection
Tatsuo, thank you very much for your answer, the problem was solved and now I can connect through SSL: In the command window i can see that: psql -U desarrollo -h 10.13.4.201 -p -d postgres Contraseña para usuario desarrollo: psql (9.0.5) conexión SSL (cifrado: AES256-SHA, bits: 256) Digite «help» para obtener ayuda. However in the pgpool log these errors are shown, although the connection was succesful, there are some errors in pgpool log file. The log says that the connection was established without problem, but the debug message are matched to ERROR label. Would this a possible bug? 2011-11-23 04:37:25 LOG: pid 17472: connection received: host=10.13.4.201 port=47519 2011-11-23 04:37:25 DEBUG: pid 17472: Protocol Major: 1234 Minor: 5679 database: user: 2011-11-23 04:37:25 DEBUG: pid 17472: SSLRequest from client 2011-11-23 04:37:25 DEBUG: pid 17472: read_startup_packet: application_name: psql 2011-11-23 04:37:25 DEBUG: pid 17472: Protocol Major: 3 Minor: 0 database: postgres user: desarrollo 2011-11-23 04:37:25 DEBUG: pid 17472: new_connection: connecting 0 backend 2011-11-23 04:37:25 DEBUG: pid 17472: new_connection: connecting 1 backend 2011-11-23 04:37:25 DEBUG: pid 17472: pool_ssl: sending client-server SSL request 2011-11-23 04:37:25 DEBUG: pid 17472: pool_ssl: client-server SSL response: S 2011-11-23 04:37:25 DEBUG: pid 17472: pool_ssl: sending client-server SSL request 2011-11-23 04:37:25 DEBUG: pid 17472: pool_ssl: client-server SSL response: S 2011-11-23 04:37:25 DEBUG: pid 17472: pool_read_message_length: slot: 0 length: 12 2011-11-23 04:37:25 DEBUG: pid 17472: pool_read_message_length: slot: 1 length: 12 2011-11-23 04:37:25 DEBUG: pid 17472: pool_do_auth: auth kind:5 2011-11-23 04:37:25 DEBUG: pid 17472: trying md5 authentication 2011-11-23 04:37:25 ERROR: pid 17472: pool_ssl: SSL_read: no SSL error reported 2011-11-23 04:37:25 ERROR: pid 17472: pool_read: read failed (Success) Regards. -Mensaje original- De: Tatsuo Ishii [mailto:is...@sraoss.co.jp] Enviado el: miércoles, 23 de noviembre de 2011 04:21 Para: Lazaro Rubén García Martinez CC: pgpool-general@pgfoundry.org Asunto: Re: [Pgpool-general] Error with SSL Consection Can you try like this? 1) configure num_init_children to 1. 2) restart pgpool 3) find pgpool child process pid(hint: ps status is pgpool: wait for connection request) 4) strace -p the_pid 5) connect to pgpool strace should show where pgpool tried to find ssl cert file. Make sure that it is /opt/pgpool/ssl/server.cert. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp Hello everyone in the list, I am proving the connecting SSL through with Pgpool, the pgpool configuration parameters related with SSL are: # - SSL Connections - ssl = on ssl_key = '/opt/pgpool/ssl/server.key' ssl_cert = '/opt/pgpool/ssl/server.cert' #ssl_ca_cert = '' #ssl_ca_cert_dir = '' But in pgpool.log file i see this error: 2011-11-22 06:42:02 LOG: pid 14011: connection received: host=10.13.4.201 port=40434 2011-11-22 06:42:02 DEBUG: pid 14011: Protocol Major: 1234 Minor: 5679 database: user: 2011-11-22 06:42:02 DEBUG: pid 14011: SSLRequest from client 2011-11-22 06:42:02 ERROR: pid 14011: pool_ssl: Loading SSL certificate: No such file or directory Nevertheless I can connect to postgreSQL through Pgpool. Please can anybody tell me, What could be the reason for this error? I am using Pgpool-II V_3.1_stable with PostgreSQL 9.0.5. Regards. ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] Error with SSL Consection
Could you show logs after this: 2011-11-23 04:37:25 ERROR: pid 17472: pool_read: read failed (Success) -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp Tatsuo, thank you very much for your answer, the problem was solved and now I can connect through SSL: In the command window i can see that: psql -U desarrollo -h 10.13.4.201 -p -d postgres Contraseña para usuario desarrollo: psql (9.0.5) conexión SSL (cifrado: AES256-SHA, bits: 256) Digite «help» para obtener ayuda. However in the pgpool log these errors are shown, although the connection was succesful, there are some errors in pgpool log file. The log says that the connection was established without problem, but the debug message are matched to ERROR label. Would this a possible bug? 2011-11-23 04:37:25 LOG: pid 17472: connection received: host=10.13.4.201 port=47519 2011-11-23 04:37:25 DEBUG: pid 17472: Protocol Major: 1234 Minor: 5679 database: user: 2011-11-23 04:37:25 DEBUG: pid 17472: SSLRequest from client 2011-11-23 04:37:25 DEBUG: pid 17472: read_startup_packet: application_name: psql 2011-11-23 04:37:25 DEBUG: pid 17472: Protocol Major: 3 Minor: 0 database: postgres user: desarrollo 2011-11-23 04:37:25 DEBUG: pid 17472: new_connection: connecting 0 backend 2011-11-23 04:37:25 DEBUG: pid 17472: new_connection: connecting 1 backend 2011-11-23 04:37:25 DEBUG: pid 17472: pool_ssl: sending client-server SSL request 2011-11-23 04:37:25 DEBUG: pid 17472: pool_ssl: client-server SSL response: S 2011-11-23 04:37:25 DEBUG: pid 17472: pool_ssl: sending client-server SSL request 2011-11-23 04:37:25 DEBUG: pid 17472: pool_ssl: client-server SSL response: S 2011-11-23 04:37:25 DEBUG: pid 17472: pool_read_message_length: slot: 0 length: 12 2011-11-23 04:37:25 DEBUG: pid 17472: pool_read_message_length: slot: 1 length: 12 2011-11-23 04:37:25 DEBUG: pid 17472: pool_do_auth: auth kind:5 2011-11-23 04:37:25 DEBUG: pid 17472: trying md5 authentication 2011-11-23 04:37:25 ERROR: pid 17472: pool_ssl: SSL_read: no SSL error reported 2011-11-23 04:37:25 ERROR: pid 17472: pool_read: read failed (Success) Regards. -Mensaje original- De: Tatsuo Ishii [mailto:is...@sraoss.co.jp] Enviado el: miércoles, 23 de noviembre de 2011 04:21 Para: Lazaro Rubén García Martinez CC: pgpool-general@pgfoundry.org Asunto: Re: [Pgpool-general] Error with SSL Consection Can you try like this? 1) configure num_init_children to 1. 2) restart pgpool 3) find pgpool child process pid(hint: ps status is pgpool: wait for connection request) 4) strace -p the_pid 5) connect to pgpool strace should show where pgpool tried to find ssl cert file. Make sure that it is /opt/pgpool/ssl/server.cert. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp Hello everyone in the list, I am proving the connecting SSL through with Pgpool, the pgpool configuration parameters related with SSL are: # - SSL Connections - ssl = on ssl_key = '/opt/pgpool/ssl/server.key' ssl_cert = '/opt/pgpool/ssl/server.cert' #ssl_ca_cert = '' #ssl_ca_cert_dir = '' But in pgpool.log file i see this error: 2011-11-22 06:42:02 LOG: pid 14011: connection received: host=10.13.4.201 port=40434 2011-11-22 06:42:02 DEBUG: pid 14011: Protocol Major: 1234 Minor: 5679 database: user: 2011-11-22 06:42:02 DEBUG: pid 14011: SSLRequest from client 2011-11-22 06:42:02 ERROR: pid 14011: pool_ssl: Loading SSL certificate: No such file or directory Nevertheless I can connect to postgreSQL through Pgpool. Please can anybody tell me, What could be the reason for this error? I am using Pgpool-II V_3.1_stable with PostgreSQL 9.0.5. Regards. ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] pgpool limitations
One possibility is client_idle_limit. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp I have found that sometimes the client connections get disconnected and the new ones are established. What I do is I get the PIDs using pcp_proc_count before running pcp_attach_node and then run pcp_proc_count to check if the PIDs remain same. I found that the behaviour is random. When can this happen? From: Tatsuo Ishii is...@sraoss.co.jp To: singh.gurj...@gmail.com Cc: pgpool-general@pgfoundry.org; pgpool-hack...@pgfoundry.org Sent: Thursday, August 11, 2011 6:11 AM Subject: Re: [Pgpool-general] pgpool limitations Is there something in the works to enable this, or is this feature still in design phase? If it is already being/been developed, I wish to know if this can be back-patched to a point release of pgpool 3.0.x. It has been already in pgpool-II 3.1 alpha version. Currently there's no plan to back-patching to 3.0.x. I certainly hope we won't backpatch a new feature. That would be insane. I don't consider this a new feature. I'd say this is unexpected side-effect (a.k.a bug) of pcp_attach_node, since nowhere in the docs does is say that invoking pcp_attach_node would drop all client connections. This behavior has not been changed since pcp_attach_node was born in 2006. Moreover, the enhancement in 3.1 is only for steaming replication mode. Other modes including replication mode does not take advantage of this. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] seemingly hung pgpool process consuming 100% CPU
Not wanting to be impatient, but I'm very concerned about this problem, since its impossible to predict when it will occur. Is there additional information that I can provide to investigate this further? thanks On Tue, Nov 22, 2011 at 10:11 AM, Lonni J Friedman netll...@gmail.com wrote: This hadn't reproduced in a long time, but we ugpraded to pgpool-3.1 a week ago, and this morning I found a pgpool process that was consuming 100% CPU, and had been running for a week (although wasn't consuming 100% CPU the entire time). Something else weird is that it showed an active, idle connection from a client system which had only been up for the past 21 hours. Anyway, here's the backtrace from the process (gdb hung at the very bottom): [root ~]# gdb pgpool 31293 GNU gdb (GDB) Red Hat Enterprise Linux (7.1-29.el6) Copyright (C) 2010 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/sbin/pgpool...Reading symbols from /usr/lib/debug/usr/sbin/pgpool.debug...done. done. Attaching to program: /usr/sbin/pgpool, process 31293 Reading symbols from /usr/lib64/libpq.so.5...(no debugging symbols found)...done. Loaded symbols for /usr/lib64/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 /usr/lib64/libssl.so.10...(no debugging symbols found)...done. Loaded symbols for /usr/lib64/libssl.so.10 Reading symbols from /usr/lib64/libcrypto.so.10...(no debugging symbols found)...done. Loaded symbols for /usr/lib64/libcrypto.so.10 Reading symbols from /lib64/libcrypt.so.1...(no debugging symbols found)...done. Loaded symbols for /lib64/libcrypt.so.1 Reading symbols from /lib64/libresolv.so.2...(no debugging symbols found)...done. Loaded symbols for /lib64/libresolv.so.2 Reading symbols from /lib64/libnsl.so.1...(no debugging symbols found)...done. Loaded symbols for /lib64/libnsl.so.1 Reading symbols from /lib64/libm.so.6...(no debugging symbols found)...done. Loaded symbols for /lib64/libm.so.6 Reading symbols from /lib64/libc.so.6...(no debugging symbols found)...done. Loaded symbols for /lib64/libc.so.6 Reading symbols from /lib64/libgssapi_krb5.so.2...(no debugging symbols found)...done. Loaded symbols for /lib64/libgssapi_krb5.so.2 Reading symbols from /usr/lib64/libldap_r-2.4.so.2...(no debugging symbols found)...done. Loaded symbols for /usr/lib64/libldap_r-2.4.so.2 Reading symbols from /lib64/libpthread.so.0...(no debugging symbols found)...done. [Thread debugging using libthread_db enabled] Loaded symbols for /lib64/libpthread.so.0 Reading symbols from /lib64/libaudit.so.1...(no debugging symbols found)...done. Loaded symbols for /lib64/libaudit.so.1 Reading symbols from /lib64/libdl.so.2...(no debugging symbols found)...done. Loaded symbols for /lib64/libdl.so.2 Reading symbols from /lib64/libkrb5.so.3...(no debugging symbols found)...done. Loaded symbols for /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 /lib64/libk5crypto.so.3...(no debugging symbols found)...done. Loaded symbols for /lib64/libk5crypto.so.3 Reading symbols from /lib64/libz.so.1...(no debugging symbols found)...done. Loaded symbols for /lib64/libz.so.1 Reading symbols from /usr/lib64/libfreebl3.so...(no debugging symbols found)...done. Loaded symbols for /usr/lib64/libfreebl3.so Reading symbols from /lib64/ld-linux-x86-64.so.2...(no debugging symbols found)...done. Loaded symbols for /lib64/ld-linux-x86-64.so.2 Reading symbols from /lib64/libkrb5support.so.0...(no debugging symbols found)...done. Loaded symbols for /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.4.so.2...(no debugging symbols found)...done. Loaded symbols for /usr/lib64/liblber-2.4.so.2 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/libnss_files.so.2...(no debugging symbols found)...done. Loaded symbols for /lib64/libnss_files.so.2
Re: [Pgpool-general] Error with SSL Consection
This is the log: 2011-11-23 06:20:03 LOG: pid 4504: connection received: host=10.13.4.201 port=33254 2011-11-23 06:20:03 DEBUG: pid 4504: Protocol Major: 1234 Minor: 5679 database: user: 2011-11-23 06:20:03 DEBUG: pid 4504: SSLRequest from client 2011-11-23 06:20:03 DEBUG: pid 4504: read_startup_packet: application_name: psql 2011-11-23 06:20:03 DEBUG: pid 4504: Protocol Major: 3 Minor: 0 database: postgres user: postgres 2011-11-23 06:20:03 DEBUG: pid 4504: new_connection: connecting 0 backend 2011-11-23 06:20:03 DEBUG: pid 4504: new_connection: connecting 1 backend 2011-11-23 06:20:03 DEBUG: pid 4504: pool_ssl: sending client-server SSL request 2011-11-23 06:20:03 DEBUG: pid 4504: pool_ssl: client-server SSL response: S 2011-11-23 06:20:03 DEBUG: pid 4504: pool_ssl: sending client-server SSL request 2011-11-23 06:20:03 DEBUG: pid 4504: pool_ssl: client-server SSL response: S 2011-11-23 06:20:03 DEBUG: pid 4504: pool_read_message_length: slot: 0 length: 12 2011-11-23 06:20:03 DEBUG: pid 4504: pool_read_message_length: slot: 1 length: 12 2011-11-23 06:20:03 DEBUG: pid 4504: pool_do_auth: auth kind:5 2011-11-23 06:20:03 DEBUG: pid 4504: trying md5 authentication 2011-11-23 06:20:03 ERROR: pid 4504: pool_ssl: SSL_read: no SSL error reported 2011-11-23 06:20:03 ERROR: pid 4504: pool_read: read failed (Success) 2011-11-23 06:20:03 DEBUG: pid 4504: read_password_packet_password: failed to read password packet p 2011-11-23 06:20:03 DEBUG: pid 4504: do_md5: read_password_packet failed 2011-11-23 06:20:03 DEBUG: pid 4504: do_md5failed in slot 0 2011-11-23 06:20:05 DEBUG: pid 4504: I am 4504 accept fd 6 2011-11-23 06:20:05 LOG: pid 4504: connection received: host=10.13.4.201 port=33257 2011-11-23 06:20:05 DEBUG: pid 4504: Protocol Major: 1234 Minor: 5679 database: user: 2011-11-23 06:20:05 DEBUG: pid 4504: SSLRequest from client 2011-11-23 06:20:05 DEBUG: pid 4504: read_startup_packet: application_name: psql 2011-11-23 06:20:05 DEBUG: pid 4504: Protocol Major: 3 Minor: 0 database: postgres user: postgres 2011-11-23 06:20:05 DEBUG: pid 4504: new_connection: connecting 0 backend 2011-11-23 06:20:05 DEBUG: pid 4504: new_connection: connecting 1 backend 2011-11-23 06:20:05 DEBUG: pid 4504: pool_ssl: sending client-server SSL request 2011-11-23 06:20:05 DEBUG: pid 4504: pool_ssl: client-server SSL response: S 2011-11-23 06:20:05 DEBUG: pid 4504: pool_ssl: sending client-server SSL request 2011-11-23 06:20:05 DEBUG: pid 4504: pool_ssl: client-server SSL response: S 2011-11-23 06:20:05 DEBUG: pid 4504: pool_read_message_length: slot: 0 length: 12 2011-11-23 06:20:05 DEBUG: pid 4504: pool_read_message_length: slot: 1 length: 12 2011-11-23 06:20:05 DEBUG: pid 4504: pool_do_auth: auth kind:5 2011-11-23 06:20:05 DEBUG: pid 4504: trying md5 authentication 2011-11-23 06:20:05 DEBUG: pid 4504: DB node id: 0 salt: 93aed894 2011-11-23 06:20:05 DEBUG: pid 4504: trying md5 authentication 2011-11-23 06:20:05 DEBUG: pid 4504: DB node id: 1 salt: e2c21f77 2011-11-23 06:20:05 DEBUG: pid 4504: pool_read_message_length2: master slot: 0 length: 26 2011-11-23 06:20:05 DEBUG: pid 4504: pool_read_message_length2: master slot: 1 length: 26 2011-11-23 06:20:05 DEBUG: pid 4504: 0 th backend: name: application_name value: psql 2011-11-23 06:20:05 DEBUG: pid 4504: 1 th backend: name: application_name value: psql 2011-11-23 06:20:05 DEBUG: pid 4504: pool_read_message_length2: master slot: 0 length: 25 2011-11-23 06:20:05 DEBUG: pid 4504: pool_read_message_length2: master slot: 1 length: 25 2011-11-23 06:20:05 DEBUG: pid 4504: 0 th backend: name: client_encoding value: UTF8 2011-11-23 06:20:05 DEBUG: pid 4504: 1 th backend: name: client_encoding value: UTF8 2011-11-23 06:20:05 DEBUG: pid 4504: pool_read_message_length2: master slot: 0 length: 23 2011-11-23 06:20:05 DEBUG: pid 4504: pool_read_message_length2: master slot: 1 length: 23 2011-11-23 06:20:05 DEBUG: pid 4504: 0 th backend: name: DateStyle value: ISO, DMY 2011-11-23 06:20:05 DEBUG: pid 4504: 1 th backend: name: DateStyle value: ISO, DMY 2011-11-23 06:20:05 DEBUG: pid 4504: pool_read_message_length2: master slot: 0 length: 25 2011-11-23 06:20:05 DEBUG: pid 4504: pool_read_message_length2: master slot: 1 length: 25 2011-11-23 06:20:05 DEBUG: pid 4504: 0 th backend: name: integer_datetimes value: on 2011-11-23 06:20:05 DEBUG: pid 4504: 1 th backend: name: integer_datetimes value: on 2011-11-23 06:20:05 DEBUG: pid 4504: pool_read_message_length2: master slot: 0 length: 27 2011-11-23 06:20:05 DEBUG: pid 4504: pool_read_message_length2: master slot: 1 length: 27 2011-11-23 06:20:05 DEBUG: pid 4504: 0 th backend: name: IntervalStyle value: postgres 2011-11-23 06:20:05 DEBUG: pid 4504: 1 th backend: name: IntervalStyle value: postgres 2011-11-23 06:20:05 DEBUG: pid 4504: pool_read_message_length2: master slot: 0 length: 20 2011-11-23 06:20:05 DEBUG: pid 4504: pool_read_message_length2: master slot: 1 length: 20 2011-11-23 06:20:05 DEBUG: pid 4504: 0
Re: [Pgpool-general] Error with SSL Consection
Looks normal. psql first tried with trust auth. Backend requires md5 auth and pool propagated to psql. psql disconnected connection to pgpool and tried with md5 auth again. This time it was successfull. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp This is the log: 2011-11-23 06:20:03 LOG: pid 4504: connection received: host=10.13.4.201 port=33254 2011-11-23 06:20:03 DEBUG: pid 4504: Protocol Major: 1234 Minor: 5679 database: user: 2011-11-23 06:20:03 DEBUG: pid 4504: SSLRequest from client 2011-11-23 06:20:03 DEBUG: pid 4504: read_startup_packet: application_name: psql 2011-11-23 06:20:03 DEBUG: pid 4504: Protocol Major: 3 Minor: 0 database: postgres user: postgres 2011-11-23 06:20:03 DEBUG: pid 4504: new_connection: connecting 0 backend 2011-11-23 06:20:03 DEBUG: pid 4504: new_connection: connecting 1 backend 2011-11-23 06:20:03 DEBUG: pid 4504: pool_ssl: sending client-server SSL request 2011-11-23 06:20:03 DEBUG: pid 4504: pool_ssl: client-server SSL response: S 2011-11-23 06:20:03 DEBUG: pid 4504: pool_ssl: sending client-server SSL request 2011-11-23 06:20:03 DEBUG: pid 4504: pool_ssl: client-server SSL response: S 2011-11-23 06:20:03 DEBUG: pid 4504: pool_read_message_length: slot: 0 length: 12 2011-11-23 06:20:03 DEBUG: pid 4504: pool_read_message_length: slot: 1 length: 12 2011-11-23 06:20:03 DEBUG: pid 4504: pool_do_auth: auth kind:5 2011-11-23 06:20:03 DEBUG: pid 4504: trying md5 authentication 2011-11-23 06:20:03 ERROR: pid 4504: pool_ssl: SSL_read: no SSL error reported 2011-11-23 06:20:03 ERROR: pid 4504: pool_read: read failed (Success) 2011-11-23 06:20:03 DEBUG: pid 4504: read_password_packet_password: failed to read password packet p 2011-11-23 06:20:03 DEBUG: pid 4504: do_md5: read_password_packet failed 2011-11-23 06:20:03 DEBUG: pid 4504: do_md5failed in slot 0 2011-11-23 06:20:05 DEBUG: pid 4504: I am 4504 accept fd 6 2011-11-23 06:20:05 LOG: pid 4504: connection received: host=10.13.4.201 port=33257 2011-11-23 06:20:05 DEBUG: pid 4504: Protocol Major: 1234 Minor: 5679 database: user: 2011-11-23 06:20:05 DEBUG: pid 4504: SSLRequest from client 2011-11-23 06:20:05 DEBUG: pid 4504: read_startup_packet: application_name: psql 2011-11-23 06:20:05 DEBUG: pid 4504: Protocol Major: 3 Minor: 0 database: postgres user: postgres 2011-11-23 06:20:05 DEBUG: pid 4504: new_connection: connecting 0 backend 2011-11-23 06:20:05 DEBUG: pid 4504: new_connection: connecting 1 backend 2011-11-23 06:20:05 DEBUG: pid 4504: pool_ssl: sending client-server SSL request 2011-11-23 06:20:05 DEBUG: pid 4504: pool_ssl: client-server SSL response: S 2011-11-23 06:20:05 DEBUG: pid 4504: pool_ssl: sending client-server SSL request 2011-11-23 06:20:05 DEBUG: pid 4504: pool_ssl: client-server SSL response: S 2011-11-23 06:20:05 DEBUG: pid 4504: pool_read_message_length: slot: 0 length: 12 2011-11-23 06:20:05 DEBUG: pid 4504: pool_read_message_length: slot: 1 length: 12 2011-11-23 06:20:05 DEBUG: pid 4504: pool_do_auth: auth kind:5 2011-11-23 06:20:05 DEBUG: pid 4504: trying md5 authentication 2011-11-23 06:20:05 DEBUG: pid 4504: DB node id: 0 salt: 93aed894 2011-11-23 06:20:05 DEBUG: pid 4504: trying md5 authentication 2011-11-23 06:20:05 DEBUG: pid 4504: DB node id: 1 salt: e2c21f77 2011-11-23 06:20:05 DEBUG: pid 4504: pool_read_message_length2: master slot: 0 length: 26 2011-11-23 06:20:05 DEBUG: pid 4504: pool_read_message_length2: master slot: 1 length: 26 2011-11-23 06:20:05 DEBUG: pid 4504: 0 th backend: name: application_name value: psql 2011-11-23 06:20:05 DEBUG: pid 4504: 1 th backend: name: application_name value: psql 2011-11-23 06:20:05 DEBUG: pid 4504: pool_read_message_length2: master slot: 0 length: 25 2011-11-23 06:20:05 DEBUG: pid 4504: pool_read_message_length2: master slot: 1 length: 25 2011-11-23 06:20:05 DEBUG: pid 4504: 0 th backend: name: client_encoding value: UTF8 2011-11-23 06:20:05 DEBUG: pid 4504: 1 th backend: name: client_encoding value: UTF8 2011-11-23 06:20:05 DEBUG: pid 4504: pool_read_message_length2: master slot: 0 length: 23 2011-11-23 06:20:05 DEBUG: pid 4504: pool_read_message_length2: master slot: 1 length: 23 2011-11-23 06:20:05 DEBUG: pid 4504: 0 th backend: name: DateStyle value: ISO, DMY 2011-11-23 06:20:05 DEBUG: pid 4504: 1 th backend: name: DateStyle value: ISO, DMY 2011-11-23 06:20:05 DEBUG: pid 4504: pool_read_message_length2: master slot: 0 length: 25 2011-11-23 06:20:05 DEBUG: pid 4504: pool_read_message_length2: master slot: 1 length: 25 2011-11-23 06:20:05 DEBUG: pid 4504: 0 th backend: name: integer_datetimes value: on 2011-11-23 06:20:05 DEBUG: pid 4504: 1 th backend: name: integer_datetimes value: on 2011-11-23 06:20:05 DEBUG: pid 4504: pool_read_message_length2: master slot: 0 length: 27 2011-11-23 06:20:05 DEBUG: pid 4504: pool_read_message_length2: master
Re: [Pgpool-general] Error with SSL Consection
That's great, thank you very much for your help and time. Regards. -Mensaje original- De: Tatsuo Ishii [mailto:is...@sraoss.co.jp] Enviado el: miércoles, 23 de noviembre de 2011 11:03 Para: Lazaro Rubén García Martinez CC: pgpool-general@pgfoundry.org Asunto: Re: [Pgpool-general] Error with SSL Consection Looks normal. psql first tried with trust auth. Backend requires md5 auth and pool propagated to psql. psql disconnected connection to pgpool and tried with md5 auth again. This time it was successfull. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp This is the log: 2011-11-23 06:20:03 LOG: pid 4504: connection received: host=10.13.4.201 port=33254 2011-11-23 06:20:03 DEBUG: pid 4504: Protocol Major: 1234 Minor: 5679 database: user: 2011-11-23 06:20:03 DEBUG: pid 4504: SSLRequest from client 2011-11-23 06:20:03 DEBUG: pid 4504: read_startup_packet: application_name: psql 2011-11-23 06:20:03 DEBUG: pid 4504: Protocol Major: 3 Minor: 0 database: postgres user: postgres 2011-11-23 06:20:03 DEBUG: pid 4504: new_connection: connecting 0 backend 2011-11-23 06:20:03 DEBUG: pid 4504: new_connection: connecting 1 backend 2011-11-23 06:20:03 DEBUG: pid 4504: pool_ssl: sending client-server SSL request 2011-11-23 06:20:03 DEBUG: pid 4504: pool_ssl: client-server SSL response: S 2011-11-23 06:20:03 DEBUG: pid 4504: pool_ssl: sending client-server SSL request 2011-11-23 06:20:03 DEBUG: pid 4504: pool_ssl: client-server SSL response: S 2011-11-23 06:20:03 DEBUG: pid 4504: pool_read_message_length: slot: 0 length: 12 2011-11-23 06:20:03 DEBUG: pid 4504: pool_read_message_length: slot: 1 length: 12 2011-11-23 06:20:03 DEBUG: pid 4504: pool_do_auth: auth kind:5 2011-11-23 06:20:03 DEBUG: pid 4504: trying md5 authentication 2011-11-23 06:20:03 ERROR: pid 4504: pool_ssl: SSL_read: no SSL error reported 2011-11-23 06:20:03 ERROR: pid 4504: pool_read: read failed (Success) 2011-11-23 06:20:03 DEBUG: pid 4504: read_password_packet_password: failed to read password packet p 2011-11-23 06:20:03 DEBUG: pid 4504: do_md5: read_password_packet failed 2011-11-23 06:20:03 DEBUG: pid 4504: do_md5failed in slot 0 2011-11-23 06:20:05 DEBUG: pid 4504: I am 4504 accept fd 6 2011-11-23 06:20:05 LOG: pid 4504: connection received: host=10.13.4.201 port=33257 2011-11-23 06:20:05 DEBUG: pid 4504: Protocol Major: 1234 Minor: 5679 database: user: 2011-11-23 06:20:05 DEBUG: pid 4504: SSLRequest from client 2011-11-23 06:20:05 DEBUG: pid 4504: read_startup_packet: application_name: psql 2011-11-23 06:20:05 DEBUG: pid 4504: Protocol Major: 3 Minor: 0 database: postgres user: postgres 2011-11-23 06:20:05 DEBUG: pid 4504: new_connection: connecting 0 backend 2011-11-23 06:20:05 DEBUG: pid 4504: new_connection: connecting 1 backend 2011-11-23 06:20:05 DEBUG: pid 4504: pool_ssl: sending client-server SSL request 2011-11-23 06:20:05 DEBUG: pid 4504: pool_ssl: client-server SSL response: S 2011-11-23 06:20:05 DEBUG: pid 4504: pool_ssl: sending client-server SSL request 2011-11-23 06:20:05 DEBUG: pid 4504: pool_ssl: client-server SSL response: S 2011-11-23 06:20:05 DEBUG: pid 4504: pool_read_message_length: slot: 0 length: 12 2011-11-23 06:20:05 DEBUG: pid 4504: pool_read_message_length: slot: 1 length: 12 2011-11-23 06:20:05 DEBUG: pid 4504: pool_do_auth: auth kind:5 2011-11-23 06:20:05 DEBUG: pid 4504: trying md5 authentication 2011-11-23 06:20:05 DEBUG: pid 4504: DB node id: 0 salt: 93aed894 2011-11-23 06:20:05 DEBUG: pid 4504: trying md5 authentication 2011-11-23 06:20:05 DEBUG: pid 4504: DB node id: 1 salt: e2c21f77 2011-11-23 06:20:05 DEBUG: pid 4504: pool_read_message_length2: master slot: 0 length: 26 2011-11-23 06:20:05 DEBUG: pid 4504: pool_read_message_length2: master slot: 1 length: 26 2011-11-23 06:20:05 DEBUG: pid 4504: 0 th backend: name: application_name value: psql 2011-11-23 06:20:05 DEBUG: pid 4504: 1 th backend: name: application_name value: psql 2011-11-23 06:20:05 DEBUG: pid 4504: pool_read_message_length2: master slot: 0 length: 25 2011-11-23 06:20:05 DEBUG: pid 4504: pool_read_message_length2: master slot: 1 length: 25 2011-11-23 06:20:05 DEBUG: pid 4504: 0 th backend: name: client_encoding value: UTF8 2011-11-23 06:20:05 DEBUG: pid 4504: 1 th backend: name: client_encoding value: UTF8 2011-11-23 06:20:05 DEBUG: pid 4504: pool_read_message_length2: master slot: 0 length: 23 2011-11-23 06:20:05 DEBUG: pid 4504: pool_read_message_length2: master slot: 1 length: 23 2011-11-23 06:20:05 DEBUG: pid 4504: 0 th backend: name: DateStyle value: ISO, DMY 2011-11-23 06:20:05 DEBUG: pid 4504: 1 th backend: name: DateStyle value: ISO, DMY 2011-11-23 06:20:05 DEBUG: pid 4504: pool_read_message_length2: master slot: 0 length: 25 2011-11-23 06:20:05 DEBUG: pid 4504: pool_read_message_length2: master slot: 1 length: 25 2011-11-23 06:20:05
Re: [Pgpool-general] pgpool, latency, and postgres protocol
It's definitely pgpool issue with serializing postgres frontend/backend protocol messages. Tried on environment with actual (non-simulated) latency. Same work takes 5 times longer with pgpool then without it. Using pgpool 3.1. Regards, Stevo. On Mon, Nov 21, 2011 at 3:28 PM, Stevo Slavić ssla...@gmail.com wrote: Hello pgpool users, Using tc (see http://linux.die.net/man/8/tc ) I'm simulating 60ms latency between pgpool and a master backend. When executing a simple query over pgpool, backend messages seem to get serialized (see [1], notice 60ms+ between almost every backend message), while when executing same query on same backend but directly (not through pgpool), backend messages get sent concurrently (see [2]) back to the driver/client. Query is executed from a simple CLI Java application which makes use of postgresql-9.1-901.jdbc4.jar driver, with logging enabled. See [3] pgpool debug log when this query is executed through pgpool. Does anyone have any ideas why is pgpool serializing these backend messages? Is there a way to enable postgres protocol messages logging in pgpool? Would like to configure that to see if pgpool is receiving messages. Also timestamp log format seems to fixed, and doesn't include milliseconds. Any way to change that? Regards, Stevo. [1] jdbc driver log output, query execution through pgpool 23:03:22.625 (1) PostgreSQL 9.1 JDBC4 (build 901) 23:03:22.646 (1) Trying to establish a protocol version 3 connection to pgsql_srv_standby: 23:03:22.709 (1) FE= StartupPacket(user=foo, database=foo, client_encoding=UTF8, DateStyle=ISO, extra_float_digits=2) 23:03:22.835 (1) =BE AuthenticationOk 23:03:22.849 (1) =BE ParameterStatus(application_name = ) 23:03:22.849 (1) =BE ParameterStatus(client_encoding = UTF8) 23:03:22.851 (1) =BE ParameterStatus(DateStyle = ISO, MDY) 23:03:22.851 (1) =BE ParameterStatus(integer_datetimes = on) 23:03:22.851 (1) =BE ParameterStatus(IntervalStyle = postgres) 23:03:22.851 (1) =BE ParameterStatus(is_superuser = on) 23:03:22.851 (1) =BE ParameterStatus(server_encoding = UTF8) 23:03:22.851 (1) =BE ParameterStatus(server_version = 9.1.1) 23:03:22.852 (1) =BE ParameterStatus(session_authorization = foo) 23:03:22.852 (1) =BE ParameterStatus(standard_conforming_strings = on) 23:03:22.852 (1) =BE ParameterStatus(TimeZone = Europe/Amsterdam) 23:03:22.852 (1) =BE BackendKeyData(pid=24275,ckey=1498571326) 23:03:22.852 (1) =BE ReadyForQuery(I) 23:03:22.858 (1) simple execute, handler=org.postgresql.core.SetupQueryRunner$SimpleResultHandler@7f54ee6, maxRows=0, fetchSize=0, flags=23 23:03:22.864 (1) FE= Parse(stmt=null,query=SET extra_float_digits = 3,oids={}) 23:03:22.873 (1) FE= Bind(stmt=null,portal=null) 23:03:22.873 (1) FE= Execute(portal=null,limit=1) 23:03:22.874 (1) FE= Sync 23:03:22.934 (1) =BE ParseComplete [null] 23:03:22.995 (1) =BE BindComplete [null] 23:03:23.057 (1) =BE CommandStatus(SET) 23:03:23.118 (1) =BE ReadyForQuery(I) 23:03:23.118 (1) compatible = 9.1 23:03:23.118 (1) loglevel = 2 23:03:23.118 (1) prepare threshold = 5 getConnection returning driver[className=org.postgresql.Driver,org.postgresql.Driver@7f05238] 23:03:23.150 (1) simple execute, handler=org.postgresql.jdbc2.AbstractJdbc2Statement$StatementResultHandler@7f64160, maxRows=0, fetchSize=0, flags=17 23:03:23.150 (1) FE= Parse(stmt=null,query=select nextval('hibernate_sequence'),oids={}) 23:03:23.150 (1) FE= Bind(stmt=null,portal=null) 23:03:23.151 (1) FE= Describe(portal=null) 23:03:23.151 (1) FE= Execute(portal=null,limit=0) 23:03:23.151 (1) FE= Sync 23:03:23.212 (1) =BE ParseComplete [null] 23:03:23.273 (1) =BE BindComplete [null] 23:03:23.335 (1) =BE RowDescription(1) 23:03:23.396 (1) =BE DataRow 23:03:23.396 (1) =BE CommandStatus(SELECT 1) 23:03:23.456 (1) =BE ReadyForQuery(I) 23:03:23.462 (1) FE= Terminate [2] jdbc driver log output, query execution directly on backend 21:16:05.428 (1) simple execute, handler=org.postgresql.jdbc2.AbstractJdbc2Statement$StatementResultHandler@7f74161, maxRows=0, fetchSize=0, flags=17 21:16:05.428 (1) FE= Parse(stmt=null,query=select nextval('hibernate_sequence'),oids={}) 21:16:05.428 (1) FE= Bind(stmt=null,portal=null) 21:16:05.428 (1) FE= Describe(portal=null) 21:16:05.428 (1) FE= Execute(portal=null,limit=0) 21:16:05.428 (1) FE= Sync 21:16:05.489 (1) =BE ParseComplete [null] 21:16:05.489 (1) =BE BindComplete [null] 21:16:05.489 (1) =BE RowDescription(1) 21:16:05.489 (1) =BE DataRow 21:16:05.489 (1) =BE CommandStatus(SELECT 1) 21:16:05.489 (1) =BE ReadyForQuery(I) [3] pgpool debug log 2011-11-21 23:03:22 DEBUG: pid 30055: key: pid_file_name 2011-11-21 23:03:22 DEBUG: pid 30055: value: '/var/run/pgpool-II-91/pgpool.pid' kind: 4 2011-11-21 23:03:22 DEBUG: pid 30055: key: logdir 2011-11-21 23:03:22 DEBUG: pid 30055: value: '/data/postgresql_db' kind: 4 2011-11-21 23:03:22 DEBUG: pid
[Pgpool-general] Authentication when using streaming replication
Hi, I have trouble setting up pgpool2-3.0.2 (from Debian Squeeze backports) with two PostgreSQL 9.1 servers in streaming replication mode. When I set the {pool,pg}_hba.conf settings to ``trust``, everything seems to go smoothly. However, in my production environment, I don't want just anyone do anything to my database server; at least the password auth should be enforced, and passwords should be protected from spying eyes. My pgpool2 instance is running on a seperate server. From what I see, I cannot use md5 in Postgres' hba with streaming replication. So I could use SSL for the connection between pgpool and Postgres, I guess, allowing ``password`` access to Postgres over SSL. But how do I then set up access in pgpool's hba? For security reasons, I want the Postgres hba to only allow connections from the pgpool box. Sorry, if this is a bit confuse. Short version: How do I set up Postgres' and pgpool's hba files to * allow access to Postgres from pgpool only * enforce password use * not transmit passwords unencrypted Another question, maybe related: What is the purpose of the pool_passwd file? I cannot find anything in the docs, but my pgpool complains about it missing upon startup ... Thanks for your insight! Andreas. ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
[Pgpool-general] About pgpool-HA
Hello everyone, I have some questions related with pgpool-HA. 1. Can I use pgpool-HA with pgpool-II 3.1 in streaming replication mode? 2. Is there a repository for installing pgpool-HA in CentOS from yum command? 3. Is there any documentation for configuring an environment with Pgpool-II and Pgpool-HA? Regards. ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] About pgpool-HA
Hello everyone, I have some questions related with pgpool-HA. 1. Can I use pgpool-HA with pgpool-II 3.1 in streaming replication mode? Yes. Please make sure that start pgpool with -D option. 2. Is there a repository for installing pgpool-HA in CentOS from yum command? I don't know. I know Devrim is responsible for creating Redhat rpm for pgpool, but I'm not sure he has done it for pgpool-HA. 3. Is there any documentation for configuring an environment with Pgpool-II and Pgpool-HA? It's included in the source code. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] About pgpool-HA
On Thu, 2011-11-24 at 09:15 +0900, Tatsuo Ishii wrote: 2. Is there a repository for installing pgpool-HA in CentOS from yum command? I don't know. I know Devrim is responsible for creating Redhat rpm for pgpool, but I'm not sure he has done it for pgpool-HA. Well, I haven't built -ha package for a long time, because of the big list of extra build dependencies. I can give it a shot, though. I also need to update pgpool-II, too. Regards, -- Devrim GÜNDÜZ Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] Authentication when using streaming replication
I have trouble setting up pgpool2-3.0.2 (from Debian Squeeze backports) with two PostgreSQL 9.1 servers in streaming replication mode. When I set the {pool,pg}_hba.conf settings to ``trust``, everything seems to go smoothly. However, in my production environment, I don't want just anyone do anything to my database server; at least the password auth should be enforced, and passwords should be protected from spying eyes. My pgpool2 instance is running on a seperate server. From what I see, I cannot use md5 in Postgres' hba with streaming replication. So I could use SSL for the connection between pgpool and Postgres, I guess, allowing ``password`` access to Postgres over SSL. But how do I then set up access in pgpool's hba? For security reasons, I want the Postgres hba to only allow connections from the pgpool box. Sorry, if this is a bit confuse. Short version: How do I set up Postgres' and pgpool's hba files to * allow access to Postgres from pgpool only Just properly set pg_hba.conf so that it only accepts incoming connections from the host which pgpool is running on. * enforce password use * not transmit passwords unencrypted pgpool acceses PosgreSQL in 3 different ways(assuming you use streaming replication mode along with health checking): 1) Ordinaly database access. You can use md5 auth. Please look into the docs coming with pgpool-II for more details. 2) Health checking. Unfortunately pgpool-II cannot use other than trust auth. 3) Specific checking with streaming replication. You can use md5 auth. In summary, if you do not plan to use health checking, you can configure pgpool-II to use md5 auth. Another question, maybe related: What is the purpose of the pool_passwd file? I cannot find anything in the docs, but my pgpool complains about it missing upon startup ... Really? It's definitely included in pgpool-en.html coming with pgpool-II source code. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] seemingly hung pgpool process consuming 100% CPU
Not wanting to be impatient, but I'm very concerned about this problem, since its impossible to predict when it will occur. Is there additional information that I can provide to investigate this further? I really need to know where pgpool is looping. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp thanks On Tue, Nov 22, 2011 at 10:11 AM, Lonni J Friedman netll...@gmail.com wrote: This hadn't reproduced in a long time, but we ugpraded to pgpool-3.1 a week ago, and this morning I found a pgpool process that was consuming 100% CPU, and had been running for a week (although wasn't consuming 100% CPU the entire time). Something else weird is that it showed an active, idle connection from a client system which had only been up for the past 21 hours. Anyway, here's the backtrace from the process (gdb hung at the very bottom): [root ~]# gdb pgpool 31293 GNU gdb (GDB) Red Hat Enterprise Linux (7.1-29.el6) Copyright (C) 2010 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/sbin/pgpool...Reading symbols from /usr/lib/debug/usr/sbin/pgpool.debug...done. done. Attaching to program: /usr/sbin/pgpool, process 31293 Reading symbols from /usr/lib64/libpq.so.5...(no debugging symbols found)...done. Loaded symbols for /usr/lib64/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 /usr/lib64/libssl.so.10...(no debugging symbols found)...done. Loaded symbols for /usr/lib64/libssl.so.10 Reading symbols from /usr/lib64/libcrypto.so.10...(no debugging symbols found)...done. Loaded symbols for /usr/lib64/libcrypto.so.10 Reading symbols from /lib64/libcrypt.so.1...(no debugging symbols found)...done. Loaded symbols for /lib64/libcrypt.so.1 Reading symbols from /lib64/libresolv.so.2...(no debugging symbols found)...done. Loaded symbols for /lib64/libresolv.so.2 Reading symbols from /lib64/libnsl.so.1...(no debugging symbols found)...done. Loaded symbols for /lib64/libnsl.so.1 Reading symbols from /lib64/libm.so.6...(no debugging symbols found)...done. Loaded symbols for /lib64/libm.so.6 Reading symbols from /lib64/libc.so.6...(no debugging symbols found)...done. Loaded symbols for /lib64/libc.so.6 Reading symbols from /lib64/libgssapi_krb5.so.2...(no debugging symbols found)...done. Loaded symbols for /lib64/libgssapi_krb5.so.2 Reading symbols from /usr/lib64/libldap_r-2.4.so.2...(no debugging symbols found)...done. Loaded symbols for /usr/lib64/libldap_r-2.4.so.2 Reading symbols from /lib64/libpthread.so.0...(no debugging symbols found)...done. [Thread debugging using libthread_db enabled] Loaded symbols for /lib64/libpthread.so.0 Reading symbols from /lib64/libaudit.so.1...(no debugging symbols found)...done. Loaded symbols for /lib64/libaudit.so.1 Reading symbols from /lib64/libdl.so.2...(no debugging symbols found)...done. Loaded symbols for /lib64/libdl.so.2 Reading symbols from /lib64/libkrb5.so.3...(no debugging symbols found)...done. Loaded symbols for /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 /lib64/libk5crypto.so.3...(no debugging symbols found)...done. Loaded symbols for /lib64/libk5crypto.so.3 Reading symbols from /lib64/libz.so.1...(no debugging symbols found)...done. Loaded symbols for /lib64/libz.so.1 Reading symbols from /usr/lib64/libfreebl3.so...(no debugging symbols found)...done. Loaded symbols for /usr/lib64/libfreebl3.so Reading symbols from /lib64/ld-linux-x86-64.so.2...(no debugging symbols found)...done. Loaded symbols for /lib64/ld-linux-x86-64.so.2 Reading symbols from /lib64/libkrb5support.so.0...(no debugging symbols found)...done. Loaded symbols for /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.4.so.2...(no debugging symbols found)...done. Loaded symbols for /usr/lib64/liblber-2.4.so.2 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
Re: [Pgpool-general] seemingly hung pgpool process consuming 100% CPU
On Wed, Nov 23, 2011 at 10:42 PM, Tatsuo Ishii is...@sraoss.co.jp wrote: Not wanting to be impatient, but I'm very concerned about this problem, since its impossible to predict when it will occur. Is there additional information that I can provide to investigate this further? I really need to know where pgpool is looping. OK, how can I capture that information? ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] seemingly hung pgpool process consuming 100% CPU
On Wed, Nov 23, 2011 at 10:42 PM, Tatsuo Ishii is...@sraoss.co.jp wrote: Not wanting to be impatient, but I'm very concerned about this problem, since its impossible to predict when it will occur. Is there additional information that I can provide to investigate this further? I really need to know where pgpool is looping. OK, how can I capture that information? You already attached to the pgpool process. So just type n (for next) will tell you next line to execute. If pgpool really loops, n should show the same line after some repeating n. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general