Johnny Tan | 22 Aug 20:34 2011
Picon

pgpool WITH pgbouncer?

A year ago in these archives, Josh Berkus was looking into fronting
pgpool with pgbouncer, to get the benefit of pgbouncer's transaction
pooling along with pgpool's other features (failover, loadbalancing,
etc.).

Josh, did you (or anyone else on the list), ever try this out?

johnny
Picon

Validating and killing idle connections

Dear all,

I have a problem with a firewall which sits between my java web apps and postgresql. That firewall kills idle postgresql connections each hour, so if my web app is inactive for an hour, when the app tries to reconnect, it receives an exception, because the jdbc application does not know that the connection have been killed.

The problem is well known and has been documented. There are solutions for ir for some java application pools (Example for DBCP: http://sacharya.com/grails-dbcp-stale-connections/). The basis of the solution is that the connection pool itself closes idle connections after 30 minutes, and validates connections before using them.

However, we have an app which uses one non standard connection pool, which will be very hard to change.

So I'm evaluating pgpool-ii, to act as a proxy between the java apps and postgresql. The configuration will be like this:

Java apps (localhost) -> pgpool-ii (localhost) -> Only 1 Remote posgresql Server

My question is how pgpool-ii works with the remote posgresql server. Are there some settings for closing and validating connections between pgpool-ii and postgresql? Or is pgpool-ii capable to retry queries when the connection is dropped, and without making the java apps failing?

Thank you, best regards,
____________________________________________________________________
Angel Palazón Sánchez
Ant's Way of Working


_______________________________________________
Pgpool-general mailing list
Pgpool-general@...
http://pgfoundry.org/mailman/listinfo/pgpool-general
Johnny Tan | 18 Aug 23:54 2011
Picon

connections not pooling

I setup pgpool-3.0.4 (config attached) in our staging environment. In
staging, we have 5 servers which make a total of 35 connections
(multiple rails thin webservers on each).

In pgpool, I see 35 connections from the 5 servers. In the backend db,
I expected to see less than 35, but I don't. I see all 35 connections.

Is this because the thin server makes a persistent connection? If so,
then pgpool won't help me in terms of pooling connections?

johnny

/etc/pgpool-II-90/pgpool.conf:

listen_addresses = '*'
port = 9999
pcp_port = 9898
socket_dir = '/tmp'
pcp_socket_dir = '/tmp'
backend_socket_dir = '/var/run/pgpool-II-90'
pcp_timeout = 10
num_init_children = 40
max_pool = 6
child_life_time = 300
connection_life_time = 0
child_max_connections = 0
client_idle_limit = 0
authentication_timeout = 60
logdir = '/var/log/pgpool-II'
pid_file_name = '/var/run/pgpool-II-90/pgpool.pid'
replication_mode = false
load_balance_mode = false
replication_stop_on_mismatch = false
failover_if_affected_tuples_mismatch = false
replicate_select = false
reset_query_list = 'ABORT; DISCARD ALL'
white_function_list = ''
black_function_list = 'nextval,setval'
print_timestamp = true
master_slave_mode = false
master_slave_sub_mode = 'slony'
delay_threshold = 0
log_standby_delay = 'none'
connection_cache = true
health_check_timeout = 20
health_check_period = 0
health_check_user = 'nobody'
failover_command = ''
failback_command = ''
fail_over_on_backend_error = true
insert_lock = true
ignore_leading_white_space = true
log_statement = false
log_per_node_statement = false
log_connections = false
log_hostname = false
parallel_mode = false
enable_query_cache = false
pgpool2_hostname = ''
system_db_hostname = 'localhost'
system_db_port = 5432
system_db_dbname = 'pgpool'
system_db_schema = 'pgpool_catalog'
system_db_user = 'pgpool'
system_db_password = ''
backend_hostname0 = 'db-staging9'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/var/db/postgresql/data'
enable_pool_hba = false
recovery_user = 'nobody'
recovery_password = ''
recovery_1st_stage_command = ''
recovery_2nd_stage_command = ''
recovery_timeout = 90
client_idle_limit_in_recovery = 0
lobj_lock_table = ''
ssl = false
debug_level = 1
Fazool | 18 Aug 21:02 2011
Picon

Partitioning mode not working

Hi,

I am trying to do create a partitioned database by following the
instructions given at
http://pgpool.projects.postgresql.org/pgpool-II/doc/pgpool-en.html and
http://pgpool.projects.postgresql.org/pgpool-II/doc/tutorial-en.html#parallel

I have created the distribution rule, to partition the accounts table
w.r.t aid (as in the example in the tutorial).
The problem is that when I perform an insert (on the accounts table),
the system replicates the row to all data nodes, instead of only
sending it to one data node. I confirmed this by doing a select * on
all data nodes. I have set replication_mode = false in pgpool.conf.

Any ideas what I might be doing wrong? Shouldn't the insert appear on
only the node responsible to store it according to the distribution
rule?

Thanks
Picon

Re: [ADMIN] Using Postgresql as application server

Scott Marlowe пишет:
> 2011/8/16 sad <at> bestmx.ru<sad <at> bestmx.ru>:
>> Scott Marlowe пишет:
>>> 2011/8/16 sad <at> bestmx.ru<sad <at> bestmx.ru>:
>>>> Scott Marlowe пишет:
>>>>> On Mon, Aug 15, 2011 at 11:33 AM, sad <at> bestmx.ru<sad <at> bestmx.ru>      wrote:
>>>>>> Scott Marlowe пишет:
>>>>>>> On Sat, Aug 13, 2011 at 9:57 AM, c k<shreeseva.learning <at> gmail.com>
>>>>>>>   wrote:
>>>>>>>> Dear Postgres users,
>>>>>>>> from last few months I am reading and searching for can postgresql
>>>>>>>> used
>>>>>>>> as
>>>>>>>> application server? As postgresql supports many languages like
>>>>>>>> pl/perl,
>>>>>>> Besides the previously mentioned nginx module there's apache's mod
>>>>>>> libpq http://asmith.id.au/mod_libpq.html
>>>>>>>
>>>>>>> But I'd stick to a language to wrap stuff in like php etc.
>>>>>> BTW, string concatenation in postgresql (plpgsql) is FASTER than in PHP
>>>>> But I can throw 1,000 cores at a large load with php.  Much harder to
>>>>> do with plpgsql.
>>>> and?
>>>> all of them would inevitably connect the same postgresql
>>> And they'd each need postgresql to do a concat?  I'd hope nobody was
>>> dumb enough to program the app layer to do something like that.  PG
>>> might make a decent app server, but there's no way you could scale it
>>> to millions of users like you could a farm of app servers.
>> compare concat to index scan
> Lemme see.  Hit memcached, get data cat it on one of 1,000 cores in a
> farm. OR hit the pg server for every single request, watch my 48 core
> db server collapse in sheets of flame.  Yeah, I can see why the pg
> solution makes so much sense there.

oh! it looks like you have no need in DBMS at all
that's good for you.
_______________________________________________
Pgpool-general mailing list
Pgpool-general <at> pgfoundry.org
http://pgfoundry.org/mailman/listinfo/pgpool-general
Olivier NOEL | 16 Aug 11:15 2011
Picon

Error with streaming/balancing / pgpool 3.0.4

 Hello,

I have an error with pgpool 3.0.4 in a streaming/balancing
configuration with 1 master and 2 slaves :

pgpool: 2011-08-16 10:51:58 ERROR: pid 1239: s_do_auth: unknown
response "E" before processing BackendKeyData
pgpool: 2011-08-16 10:51:58 ERROR: pid 1239: s_do_auth: unknown response "
pgpool: 2011-08-16 10:51:58 ERROR: pid 1239: s_do_auth: unknown response "
pgpool: 2011-08-16 10:51:58 ERROR: pid 1239: s_do_auth: unknown response "
pgpool: 2011-08-16 10:51:58 ERROR: pid 1239: s_do_auth: unknown
response "]" before processing BackendKeyData
pgpool: 2011-08-16 10:51:58 ERROR: pid 1239: pool_read2: failed to realloc

I followed the documentation on the website for the configuration of
the postgresql servers and pgpool.

DB servers are FreeBSD with latest Postgresql server, configured in
streaming mode (1 master and 2 slaves), replication/failover is
functional.

The pgpool host is a Debian Wheezy with pgpooladmin. Pgpool has been
install from packages. I have the same error without pgpooladmin.

I tested installing pgpool from sources and I have the same error.

I also tested pgpool on a FreeBSD box from ports and from sources, and
same error.

here is the config file :

listen_addresses = '*'

port = 5432

pcp_port = 9898

socket_dir = '/tmp'

pcp_socket_dir = '/tmp'

backend_socket_dir = '/tmp'

pcp_timeout = 10

num_init_children = 32

max_pool = 4

child_life_time = 300

connection_life_time = 0

child_max_connections = 0

client_idle_limit = 0

authentication_timeout = 60

logdir = '/tmp'

pid_file_name = '/tmp/pgpool.pid'

replication_mode = false

load_balance_mode = true

replication_stop_on_mismatch = false

failover_if_affected_tuples_mismatch = false

replicate_select = false

reset_query_list = 'ABORT; DISCARD ALL'

white_function_list = ''
black_function_list = 'nextval,setval'

print_timestamp = true

master_slave_mode = true

master_slave_sub_mode = 'stream'

delay_threshold = 10000000

log_standby_delay = 'if_over_threshold'

connection_cache = true

health_check_timeout = 10

health_check_period = 20

health_check_user = 'postgres'

failover_command = '/usr/local/etc/failover.sh %d "%h" %p %D %m %M "%H" %P'

failback_command = ''

fail_over_on_backend_error = true

insert_lock = false

ignore_leading_white_space = true

log_statement = false

log_per_node_statement = false

log_connections = false

log_hostname = false

parallel_mode = false

enable_query_cache = false

pgpool2_hostname = 'pglb01'

system_db_hostname = 'fpg01'
system_db_port = 5432
system_db_dbname = 'pgpool'
system_db_schema = 'pgpool_catalog'
system_db_user = 'pgpool'
system_db_password = 'password'

nable_pool_hba = false

recovery_user = 'postgres'

recovery_password = ''

recovery_1st_stage_command = 'basebackup.sh'

recovery_2nd_stage_command = ''

recovery_timeout = 90

client_idle_limit_in_recovery = 0

lobj_lock_table = ''

ssl = false

debug_level = 0
replication_timeout = 5000
backend_hostname0 = 'fpg01'
backend_port0 = 5432
backend_data_directory0 = '/usr/local/pgsql/data'
backend_weight0 = 1
backend_hostname1 = 'fpg02'
backend_port1 = 5432
backend_data_directory1 = '/usr/local/pgsql/data'
backend_weight1 = 2
backend_hostname2 = 'fpg03'
backend_port2 = 5432
backend_data_directory2 = '/usr/local/pgsql/data'
backend_weight2 = 2

--

-- 
Olivier
Toshihiro Kitagawa | 12 Aug 20:08 2011
Picon

pgpool-II 3.1.0 beta1 released

Pgpool Global Development Group is pleased to announce the
availability of pgpool-II 3.1.0 beta1.

Users are encouraged to take part in our beta test program.
pgpool-II 3.1.0 beta1 can be down loaded here:

http://pgfoundry.org/frs/download.php/3105/pgpool-II-3.1.0-beta1.tar.gz

Please help us in testing and making pgpool-II 3.1 better!
Major changes from alpha3 to beta1 are:

New features
============

- Add sr_check_period, sr_check_user and sr_check_password directives.
  These are used for streaming replication delay checking and determining
  primary node(Tatsuo)

- Add Simplified Chinese version of tutorial document(Huang Bambo)

Bug fixes
=========

- Fix bug which cannot use the cursors of JDBC driver on standby node.
  The transaction commands come to be sent to all nodes by this fix in
  master/slave mode(Kitagawa)

- Fix bug with the handling of empty queries. The empty queries come
  to be handled the same as SELECT queries. This fix allows load-balance
  after the empty query(Kitagawa)

- Fix insert_lock so that it works correctly even if the column
  definition such as "DEFAULT nextval(('"x_seq"'::text)::regclass)"
  (Kitagawa)

- Fix pcp_attach_node command so that it emits error message while
  doing failover(Kitagawa)

- Fix log message which is emitted when pgpool-II cannot parse the
  query in the extended query protocol so that it shows the query
  (Kitagawa)

- Fix description about backend_weight inpgpool-II manual. It can be
  changed by reloading pgpool.conf(Tatsuo)

- Fix and enhance wording in English tutorial document. Fix suggested
  by Huang Bambo(Tatsuo)

Enhancements
============

- Allow to compile pgpool-regclass against PostgreSQL 9.1(Tatsuo)

- Update and sync pgpool-II manuals of English version and Japanese
  version(Tatsuo)
Tatsuo Ishii | 12 Aug 07:17 2011
Picon

Re: [Pgpool-hackers] Two primary servers

As far as I know, PostgreSQL does not provide capability. So I think
it's impossible.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

> Hi Tatsuo san, is it possible to have the old primary return as a standby. If so what are the steps required.
> Thank you
> 
>> Date: Thu, 11 Aug 2011 10:07:34 +0900
>> To: rick_segno@...
>> CC: pgpool-general@...; pgpool-hackers@...
>> Subject: Re: [Pgpool-general] Two primary servers
>> From: ishii@...
>> 
>> > Hi pgpool experts, I am just experiencing with the 3.1alpha and having this case where I am not sure if it
is the expected behavior.
>> > I set up pgpool and a postgres 9.0.2 on one box and another postgres 9.0.2 on another box. I have also
configured the pgpooladmin to work with them. On initial start, pgpool chooses the one with smaller id as
primary and the other as standby. If I disconnect the primary via the PHP page, failover kicks in and makes
the second box primary. But in the status, for the old primary, it still says "Down Running as primary
server". Now try to bring the the connection back by clicking the 'Return' button, I get two primary
servers. Please note that the old primary wasn't actually shutdown physically. Is this an expected case?
>> 
>> Yeah, I noticed the behavior. Having two primary nodes is not good of
>> course (called "split brain"). One idea is modifying pcp_attache_node
>> (called from pgpoolAdmin when you click 'Return' button) to check:
>> 
>> 1) Check if a primary server already exists.
>> 2) If it does, check if the target node is another primary server or not.
>> 3) If the target node is another primary server, throw an error.
>> 
>> Better idea, comments?
>> --
>> Tatsuo Ishii
>> SRA OSS, Inc. Japan
>> English: http://www.sraoss.co.jp/index_en.php
>> Japanese: http://www.sraoss.co.jp
>  		 	   		  
Picon

replication mode

Hi.
my application is very sensitive to a data loss.
so
i want a pgpool in _replication_ mode
and
i want it to interrupt service in case of any backend down.
to
make sure every request (reported "OK" to a client) has been performed 
on each backend (otherwise client should be notified about a falure)

is it possible to configure pgpool that manner?
Jeno | 11 Aug 17:10 2011
Picon

pgpool master or slave goes down pgpool access error vie java

hi pgpool Expert
my architecture as follows:
Master/Slave with Streaming Replication and pgpool-II
version of pgpool-II is pgpool-II.3.0.4
version of PostgreSQL is 9.0.2
I am using pgpool works as master/slave sub mode stream
and pgpool key configuration is:
=====================================================================================================
num_init_children=100
max_pool=4
child_life_time=60
connection_life_time=0
child_max_connections=0
client_idle_limit=0
connection_cache=true
=====================================================================================================
and java jdbc connection test code as fllows:
=====================================================================================================
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestPgpool {
    public static void main(String[] args) {
        for(;;){
           Thread th = new Thread(new TestThread());
           th.start();
           try {
               Thread.sleep(10);
           } catch (InterruptedException e) {
               System.out.println("1--------------");
               e.printStackTrace();
               System.out.println("1--------------");
           }
       }
   }
   static class TestThread implements Runnable{
       public void run() {
           Connection con = null;
           Statement stmt = null;
           try {
               Class.forName("org.postgresql.Driver");
               con = DriverManager.getConnection(
                    "jdbc:postgresql://192.168.1.116:9999/spring250_20100630_705",
                    "postgres","postgres");
               stmt = con.createStatement();
               String sql = "SELECT * FROM bb_member limit 1";
               ResultSet rs = stmt.executeQuery(sql);
               System.out.print("OK(");
               while(rs.next()){
                   System.out.print(rs.getInt(1) + "="
                             + rs.getString(2) + " ");
               }
               System.out.println(")");
               stmt.close();
               con.close();
           } catch (SQLException e) {
               System.out.println("2--------------");
               e.printStackTrace();
               System.out.println("2--------------");
           } catch (ClassNotFoundException e) {
               e.printStackTrace();
           }
       }
   }
}
=====================================================================================================
question:
 I do some tests
 1)the test code run always connect pgpool,
 2)test master or slave go down
 but when mster/slave go down ,java code throws exception :
 
 org.postgresql.util.PSQLException: An I/O error occurred while sending to the backend
 org.postgresql.util.PSQLException: The connection attempt failed.
 
 the error happened once for little time,then goes normal.
what should I do to solve this problem?
thanks for any help
 
jeno
 
2011-8-10
_______________________________________________
Pgpool-general mailing list
Pgpool-general@...
http://pgfoundry.org/mailman/listinfo/pgpool-general
Daniel.Crespo | 10 Aug 18:13 2011

pgpool configuration file question

http://pgpool.projects.postgresql.org/pgpool-II/doc/pgpool-en.html states the following:

 

backend_weight

... However, *values cannot be updated so you must restart pgpool-II in that case*.

 

In pgpool-II 2.2.6/2.3 *or later, you can change this value by re-loading the configuration file*. This will take effect only for new established client sessions. This is useful if you want to prevent any query sent to slaves to perform some administrative work in master/slave mode.

 

Which of the text enclosed with asterisks above is correct?

 

Thanks,

-Daniel

 

_______________________________________________
Pgpool-general mailing list
Pgpool-general@...
http://pgfoundry.org/mailman/listinfo/pgpool-general

Gmane