Re: [GENERAL] PGPool in Master-Master mode, is it possible?

[Tatsuo Ishii]

I am not familiar with swarm cluster nor repmgr. So I wonder why you
wouldn't be able to get to the goal you are trying to achieve without
repmgr. Can you please elaborate more?

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

> I am also interested in this set-up
> My idea is to run pgpool in a docker swarm cluster and to have two instances 
> of pgpool on different nodes (but not in watchdog mode). In this case it is 
> not possible to give the responsablity to pgpool to do the failover otherwise 
> both instances would try to do the failover (they don't know about each 
> other, no watchdog mode). So I have to set failover_command parameter to an 
> empty string and let repmgr do the automatic failover. I have tested and I 
> believe it is ok: in case of master failure repmgr does the promote, pgpool 
> will not do the failover but will constantly try to reconnect to a new master 
> and as soon as repmgr has done the promotion it is OK.
> The important think is that when pgpool starts I have to read the state of 
> the cluster in the repmgr metadata table (repl_nodes) and generate the 
> /tmp/pgpool_status file based on that. (I even have to do a promote_node in 
> the edge case that the master is down and was brought down when pgpool also 
> was down).  
> But the inconvenient is that if pgpool fails, let's say because the docker 
> node of pgpool is stopped, then docker swarm restart the service on another 
> node but this can take a few seconds. So I would prefer to have multiple 
> pgpool. I did not have the time right now to test such a scenario but I am 
> interested to know if it is possible.
> Pierre 
> 
> On Wednesday, November 15, 2017, 11:02:31 AM GMT+1, Vikas Sharma 
>  wrote:  
>  
>  Thank you Tatsuo for the Reply, I will post this in list you mentioned.
> 
> By Master-Master, I meant two pgpool servers both active accepting 
> connections from Application, If one Pgpool Server becomes unlavailable other 
> still accepting connections. At an Ideal time, both Pgpool instances on each 
> PgPool server working actively.  The database in this case will be 1 master 
> and 2 slaves, replicated by streaming replication.
> 
> Regards
> VS
> 
> On 14 November 2017 at 22:56, Tatsuo Ishii  wrote:
> 
>> Hello There,
>>
>> I need to setup two PGPool Servers in Master-Master mode, First thing I
>> want to know, is it possible?
>>
>> I know we can setup 2 PGPool servers in master-slave mode using watchdog.
>> http://www.pgpool.net/pgpool- web/contrib_docs/watchdog_ 
>> master_slave_3.3/en.html
>>
>> Could anyone please enlighten me and any workaround for this?
>>
>> Regards
>> VS
> 
>> I need to setup two PGPool Servers in Master-Master mode, First thing I
>> want to know, is it possible?
>>
>> I know we can setup 2 PGPool servers in master-slave mode using watchdog.
>> http://www.pgpool.net/pgpool- web/contrib_docs/watchdog_ 
>> master_slave_3.3/en.html
>>
>> Could anyone please enlighten me and any workaround for this?
> 
> This is not an appropriate list. You should go to the Pgpool mailing
> list:
> 
> https://www.pgpool.net/ mailman/listinfo/pgpool- general
> 
> Anyway... I am not sure what you mean by "Master-Master mode" but if
> that means you want to issue write queries by connecting to any of
> Pgpool-II port, it's already possible in the set up you have created.
> 
> Best regards,
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_ en.php
> Japanese:http://www.sraoss.co. jp
> 
> 
>   


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] PGPool in Master-Master mode, is it possible?

[Vikas Sharma]

Thank you Tatsuo for the Reply, I will post this in list you mentioned.

By Master-Master, I meant two pgpool servers both active accepting
connections from Application, If one Pgpool Server becomes unlavailable
other still accepting connections. At an Ideal time, both Pgpool instances
on each PgPool server working actively.  The database in this case will be
1 master and 2 slaves, replicated by streaming replication.

Regards
VS

On 14 November 2017 at 22:56, Tatsuo Ishii  wrote:

> > Hello There,
> >
> > I need to setup two PGPool Servers in Master-Master mode, First thing I
> > want to know, is it possible?
> >
> > I know we can setup 2 PGPool servers in master-slave mode using watchdog.
> > http://www.pgpool.net/pgpool-web/contrib_docs/watchdog_
> master_slave_3.3/en.html
> >
> > Could anyone please enlighten me and any workaround for this?
> >
> > Regards
> > VS
>
> > I need to setup two PGPool Servers in Master-Master mode, First thing I
> > want to know, is it possible?
> >
> > I know we can setup 2 PGPool servers in master-slave mode using watchdog.
> > http://www.pgpool.net/pgpool-web/contrib_docs/watchdog_
> master_slave_3.3/en.html
> >
> > Could anyone please enlighten me and any workaround for this?
>
> This is not an appropriate list. You should go to the Pgpool mailing
> list:
>
> https://www.pgpool.net/mailman/listinfo/pgpool-general
>
> Anyway... I am not sure what you mean by "Master-Master mode" but if
> that means you want to issue write queries by connecting to any of
> Pgpool-II port, it's already possible in the set up you have created.
>
> Best regards,
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese:http://www.sraoss.co.jp
>

Re: [GENERAL] PGPool in Master-Master mode, is it possible?

[Tatsuo Ishii]

> Hello There,
> 
> I need to setup two PGPool Servers in Master-Master mode, First thing I
> want to know, is it possible?
> 
> I know we can setup 2 PGPool servers in master-slave mode using watchdog.
> http://www.pgpool.net/pgpool-web/contrib_docs/watchdog_master_slave_3.3/en.html
> 
> Could anyone please enlighten me and any workaround for this?
> 
> Regards
> VS

> I need to setup two PGPool Servers in Master-Master mode, First thing I
> want to know, is it possible?
> 
> I know we can setup 2 PGPool servers in master-slave mode using watchdog.
> http://www.pgpool.net/pgpool-web/contrib_docs/watchdog_master_slave_3.3/en.html
> 
> Could anyone please enlighten me and any workaround for this?

This is not an appropriate list. You should go to the Pgpool mailing
list:

https://www.pgpool.net/mailman/listinfo/pgpool-general

Anyway... I am not sure what you mean by "Master-Master mode" but if
that means you want to issue write queries by connecting to any of
Pgpool-II port, it's already possible in the set up you have created.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] PGPool in Master-Master mode, is it possible?

[Vikas Sharma]

Hello There,

I need to setup two PGPool Servers in Master-Master mode, First thing I
want to know, is it possible?

I know we can setup 2 PGPool servers in master-slave mode using watchdog.
http://www.pgpool.net/pgpool-web/contrib_docs/watchdog_master_slave_3.3/en.html

Could anyone please enlighten me and any workaround for this?

Regards
VS

Re: [GENERAL] PGpool question

[Tatsuo Ishii]

> This may be the wrong list, but I am not sure where it needs to go.  I am
> trying to set up pgpool, and I keeping on getting this message:
> 
> NOTICE:  add node from hostname:"xxx" port:9000 pgpool_port:
> rejected.
> Jul 28 22:11:49 xx pgpool[10768]: [172-2] 2017-07-28 22:11:49: pid
> 10768: DETAIL:  verify the other watchdog node configurations
> Jul 28 22:11:49 x pgpool[10768]: [172-3] 2017-07-28 22:11:49: pid
> 10768: LOCATION:  watchdog.c:1481
> 
> I believe I have mismatched settings for the watchdog configuration, but I
> cannot find them.  I have debugging turned all the way up, but nothing is
> telling me what setting is the problem,  Is there any way I can figure out
> what specific watchdog setting its complaining about?

Muhammad Usama kindly followed the question:

The message refers to the configuration mismatch of other_pgpool_* config
parameter,
The one defined in section  5.14.9. Watchdog servers configurations
 of
the documentation.
So you need to check the other_pgpool_hostname, other_pgpool_port or
other_wd_port
config values.

If you have further questions, please post to pgpool-gene...@pgpool.net.

https://www.pgpool.net/mailman/listinfo/pgpool-general

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] PGpool question

[Andrew Kerber]

This may be the wrong list, but I am not sure where it needs to go.  I am
trying to set up pgpool, and I keeping on getting this message:

NOTICE:  add node from hostname:"xxx" port:9000 pgpool_port:
rejected.
Jul 28 22:11:49 xx pgpool[10768]: [172-2] 2017-07-28 22:11:49: pid
10768: DETAIL:  verify the other watchdog node configurations
Jul 28 22:11:49 x pgpool[10768]: [172-3] 2017-07-28 22:11:49: pid
10768: LOCATION:  watchdog.c:1481

I believe I have mismatched settings for the watchdog configuration, but I
cannot find them.  I have debugging turned all the way up, but nothing is
telling me what setting is the problem,  Is there any way I can figure out
what specific watchdog setting its complaining about?


-- 
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'

Re: [GENERAL] PgPool or alternatives

[Alvaro Aguayo Garcia-Rada]

Hi.

I had to implement something similar some time ago. Basically, a group of 
database servers (postgres) geographically distributed, with each one having a 
group of servers in each datacenter, and each server preferring the nearest 
database server, but allowing connections to a further one if the nearest is 
down.

After going through different solutions (pgpool between them), I got to 
Postgres BDR with HAProxy. Each app server connects to the local HAProxy, which 
forwards the connection to the nearest available database server(preference is 
set directly in the HAProxy configuration). That way, I get high availability 
and replication happens really fast, right after the transaction is committed.

The only drawback with Postgres BDR is it has some limitations:

- New databases are NOT replicated; but you can have any number of databases 
with no problem.
- Users & roles must be replicated manually, as BDR works at database-level.
- There are some DDL restrictions: mostly due to how BDR works internally. In 
my experience, none of them has been a real problem. Full list: 
http://bdr-project.org/docs/stable/ddl-replication-statements.html
- DDL replication may sometimes mean automatic cancellation of running 
transactions. so it must be carefully planned. However, consider TEMPORARY 
TABLES are not replicated, so DDL on them is not affected by BDR restrictions.
- Even when BDR documentations says nothing about this, it can have trouble 
replicating really large transactions. In my experience, my BDR cluster stopped 
replicating (had to rebuild it) when an app made a 8 million records update in 
a single transaction. Since that app was corrected, nothing similar has ever 
happened, and I think most apps should not have problems with this.

Alvaro Aguayo
Jefe de Operaciones
Open Comb Systems E.I.R.L.

Oficina: (+51-1) 3377813 | RPM: #034252 / (+51) 995540103  | RPC: (+51) 
954183248
Website: www.ocs.pe

- Original Message -
From: "Simon Windsor" <simon.wind...@cornfield.me.uk>
To: pgsql-general@postgresql.org
Sent: Saturday, 21 January, 2017 2:38:59 PM
Subject: [GENERAL] PgPool or alternatives

Hi

My employer wants to move from an in house Oracle solution to a cloud 
based Postgres system. The system will involve a number of data loaders 
running 24x7 feeding several Postgres Databases that will be used by 
internal applications and external customer applications.

For the record, internal and external applications make heavy use of 
Temporary tables, that are session related. This requirement means I 
cannot consider normal replication methods.

Is PgPool the only viable that will allow the system the data loaders to 
feed [n] databases that will be functional identical?

Simon

-- 
Simon Windsor

Eml: simon.wind...@cornfield.me.uk
Tel: 01454 617689
Mob: 0755 197 9733


“There is nothing in the world that some man cannot make a little worse and 
sell a little cheaper, and he who considers price only is that man's lawful 
prey.”



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] PgPool or alternatives

[Thomas Kellerer]

Simon Windsor schrieb am 21.01.2017 um 21:26:

I was wondering if there is another option that will allow me to
spool all ALTER|CREATE|DELETE|DROP|INSERT|UPDATE commands to all
nodes, and SELECTs to any of the connected nodes. The apllication can
actually handle separate READ|WRITE nodes from how it was written for
Oracle.


You could logical replication: https://2ndquadrant.com/en/resources/pglogical/




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] PgPool or alternatives

[Simon Windsor]

Hi

Thanks for the reply. We were not planning to use pgPools connection 
pool mode, but its replication mode.


Our tests with pgPool allow us to install a backup db via pgPool to each 
node, and tests loads overnight of 10+GB of inserts/updates/deletes all 
work fine, with only a slight loss of performance vs a standalone DB.


I was wondering if there is another option that will allow me to spool 
all ALTER|CREATE|DELETE|DROP|INSERT|UPDATE commands to all nodes, and 
SELECTs to any of the connected nodes. The apllication can actually 
handle separate READ|WRITE nodes from how it was written for Oracle.


Simon

On 21/01/2017 20:09, Stephen Frost wrote:

Simon,

* Simon Windsor (simon.wind...@cornfield.me.uk) wrote:

My employer wants to move from an in house Oracle solution to a
cloud based Postgres system. The system will involve a number of
data loaders running 24x7 feeding several Postgres Databases that
will be used by internal applications and external customer
applications.

For the record, internal and external applications make heavy use of
Temporary tables, that are session related. This requirement means I
cannot consider normal replication methods.

Is PgPool the only viable that will allow the system the data
loaders to feed [n] databases that will be functional identical?

I'm not sure what you mean by 'functional identical', but I wouldn't
generally consider that to be a property of pgpool (or pgbouncer, or any
other connection pooler, really).

That said, my general feeling is that pgbouncer tends to be simpler,
faster, and less likely to introduce oddities that you don't expect.
The 'session' mode might work for you, though it might be debatable if
that really helps you all that much.  'transaction' mode is what I
usually recommend as it allows idle connections to be handled by
pgbouncer (unlike 'session' mode), but there are caveats to using that
mode, of course.

I'm a bit curious where you're thinking of using the connection pooler
also though.  If you have data loaders running 24x7 feeding data
constantly to PG, do you really need a connection pooler for those?
Connection poolers make a lot of sense for environments where there's
lots of down-time on the connection, but the less down-time, the less
they make sense.

Thanks!

Stephen


--
Simon Windsor

Eml: simon.wind...@cornfield.me.uk
Tel: 01454 617689
Mob: 0755 197 9733


“There is nothing in the world that some man cannot make a little worse and 
sell a little cheaper, and he who considers price only is that man's lawful 
prey.”



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] PgPool or alternatives

[Stephen Frost]

Simon,

* Simon Windsor (simon.wind...@cornfield.me.uk) wrote:
> My employer wants to move from an in house Oracle solution to a
> cloud based Postgres system. The system will involve a number of
> data loaders running 24x7 feeding several Postgres Databases that
> will be used by internal applications and external customer
> applications.
> 
> For the record, internal and external applications make heavy use of
> Temporary tables, that are session related. This requirement means I
> cannot consider normal replication methods.
> 
> Is PgPool the only viable that will allow the system the data
> loaders to feed [n] databases that will be functional identical?

I'm not sure what you mean by 'functional identical', but I wouldn't
generally consider that to be a property of pgpool (or pgbouncer, or any
other connection pooler, really).

That said, my general feeling is that pgbouncer tends to be simpler,
faster, and less likely to introduce oddities that you don't expect.
The 'session' mode might work for you, though it might be debatable if
that really helps you all that much.  'transaction' mode is what I
usually recommend as it allows idle connections to be handled by
pgbouncer (unlike 'session' mode), but there are caveats to using that
mode, of course.

I'm a bit curious where you're thinking of using the connection pooler
also though.  If you have data loaders running 24x7 feeding data
constantly to PG, do you really need a connection pooler for those?
Connection poolers make a lot of sense for environments where there's
lots of down-time on the connection, but the less down-time, the less
they make sense.

Thanks!

Stephen


signature.asc
Description: Digital signature

[GENERAL] PgPool or alternatives

[Simon Windsor]

Hi

My employer wants to move from an in house Oracle solution to a cloud 
based Postgres system. The system will involve a number of data loaders 
running 24x7 feeding several Postgres Databases that will be used by 
internal applications and external customer applications.


For the record, internal and external applications make heavy use of 
Temporary tables, that are session related. This requirement means I 
cannot consider normal replication methods.


Is PgPool the only viable that will allow the system the data loaders to 
feed [n] databases that will be functional identical?


Simon

--
Simon Windsor

Eml: simon.wind...@cornfield.me.uk
Tel: 01454 617689
Mob: 0755 197 9733


“There is nothing in the world that some man cannot make a little worse and 
sell a little cheaper, and he who considers price only is that man's lawful 
prey.”



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pgpool fail to load balance after database restart

[Tatsuo Ishii]

Michael,

He already posted the identical question to the Pgpool-II mailing list
yesterday.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

> On Wed, Oct 12, 2016 at 10:04 AM, Dylan Luong  
> wrote:
>> I am new to pgpool.
> 
> You may want to ask directly on the mailing lists of pgpool, even if
> Tatsuo-san or anybody involved in it are looking at this mailing list:
> http://pgpool.net/mediawiki/index.php/Mailing_lists
> -- 
> Michael
> 
> 
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pgpool fail to load balance after database restart

[Michael Paquier]

On Wed, Oct 12, 2016 at 10:04 AM, Dylan Luong  wrote:
> I am new to pgpool.

You may want to ask directly on the mailing lists of pgpool, even if
Tatsuo-san or anybody involved in it are looking at this mailing list:
http://pgpool.net/mediawiki/index.php/Mailing_lists
-- 
Michael


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] pgpool fail to load balance after database restart

[Dylan Luong]

Hi,
I am new to pgpool. I have just installed pgpool and configured  Master/Slave 
mode with "stream" sub_mode. I have enabled load balance as that is the main 
feature we want test out. I have enabled Health Check. The load balance appears 
to work as I can see connections to both Master and Slave database.
pgpool is installed on a separate server. (linux Redhat 6)
ie: server1 = pgpool
server2 = Master
server3= Slave
(wal streaming replication)

I tested two scenarios and it failing:

1.  When I stop/start the slave database, pgpool stops load balancing. It 
looks like it doesn't know that it available again after the slave comes backup 
online. Even after I restart pgpool it doesn't load balance. It just sends all 
queries to the Master.

2.  I tried shutting down slave db and then shut down master db (no 
failover) and restart master db and restart slave, pgpool failed to reconnect 
to the master or the slave. I get database unavailable errors. However, if I 
restart pgpool for this scenario, its all fine.

It appears that pgpool is not doing the healthcheck correctly?? Or have I 
missed something here??

Here are my main pgpool.conf changes:

#--
# CONNECTIONS
#--

# - pgpool Connection Settings -

listen_addresses = '*'
   # Host name or IP address to listen on:
   # '*' for all, '' for no TCP/IP connections
   # (change requires restart)
#port = 
port = 5432
   # Port number
   # (change requires restart)
socket_dir = '/var/run/postgresql'
   # Unix domain socket path
   # The Debian package defaults to
   # /var/run/postgresql
   # (change requires restart)
listen_backlog_multiplier = 2
   # Set the backlog parameter of listen(2) to
   # 
num_init_children * listen_backlog_multiplier.
   # (change requires restart)
serialize_accept = off
   # whether to serialize accept() call to 
avoid thundering herd problem
   # (change requires restart)

# - pgpool Communication Manager Connection Settings -

pcp_listen_addresses = '*'
   # Host name or IP address for pcp process to 
listen on:
   # '*' for all, '' for no TCP/IP connections
   # (change requires restart)
pcp_port = 9898
   # Port number for pcp
   # (change requires restart)
pcp_socket_dir = '/var/run/postgresql'
   # Unix domain socket path for pcp
   # The Debian package defaults to
   # /var/run/postgresql
   # (change requires restart)

# - Backend Connection Settings -

backend_hostname0 = '10.69.20.11'
   # Host name or IP address to connect to for 
backend 0
backend_port0 = 5432
   # Port number for backend 0
backend_weight0 = 1
   # Weight for backend 0 (only in load 
balancing mode)
backend_data_directory0 = '/var/lib/pgsql/9.5/data'
   # Data directory for backend 0
backend_flag0 = 'ALLOW_TO_FAILOVER'
   # Controls various backend behavior
   # ALLOW_TO_FAILOVER or DISALLOW_TO_FAILOVER
backend_hostname1 = '10.68.20.11'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/var/lib/pgsql/9.5/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'


#--
# POOLS
#--

# - Concurrent session and pool size -

num_init_children = 32
   # Number of concurrent sessions allowed
   # (change requires restart)
max_pool = 4
   # Number of connection pool caches per 
connection
   # (change requires restart)

# - Life time -

child_life_time = 300
   # Pool exits after being idle for this many 
seconds
child_max_connections = 0
   # Pool exits after receiving that many 
connections
   # 0 means no exit
connection_life_time = 0
   # Connection to 

[GENERAL] pgpool question

[Rakesh Kumar]

I posted this in the pgpool mailing list and so far has not recvd any response. 
Looks like that mailing list is hardly active.
=

I am researching pgpool technology.  Let us say we want to use it as an 
alternative to streaming replication of PG.  Apparently in replication mode, 
pgpool can replicate to all participating nodes, which presumably will be in 
raw mode (non replicating mode).  

My questions:

1 - Do we have a choice of limiting number of nodes for sync writes (and 
remaining one in async), so that overall performance impact is limited.
2 - If a node is down, does pgpool has the ability to resync from the last 
known transaction that was committed. How does it do it? Does it maintain 
 a state information or save all transactions that need to be replayed 
later on.
3 - Is app-stickiness possible. By app-stickiness I mean, a given app or 
session always goes to the same node.  This helps in avoiding stale reads.
4-  Is the failover transparent to the client. Meaning, if a node goes down, do 
clients have to use a new connection string.
5 -  How about high availability  of pgpool itself.  Can we have multiple 
pgpools?

If there is a documentation to answer these questions, please let me know.

thanks.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pgpool-II: cannot use serializable mode in a hot standby

[Tatsuo Ishii]

> It should not forward transactions which are requested to be
> SERIALIZABLE to standbys.  If you just suppress the SET statement
> (or substitute REPEATABLE READ), queries in that transaction can
> return incorrect results.

Yes. Once "SET default_transaction_isolation to serializable" (or its
variants) are issued, pgpool-II will redirect all subsequent queries
to the primary server until the transaction ends.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pgpool-II: cannot use serializable mode in a hot standby

[Kevin Grittner]

On Wed, Apr 13, 2016 at 5:54 PM, Kevin Grittner  wrote:

> See this example, and imagine that
> the transaction generating the list of receipts for the closed
> batch is run on the standby before the transaction adding the last
> receipt commits.  Or test it.

https://wiki.postgresql.org/wiki/SSI#Deposit_Report

-- 
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pgpool-II: cannot use serializable mode in a hot standby

[Kevin Grittner]

On Wed, Apr 13, 2016 at 1:30 AM, Alexander Pyhalov  wrote:
> Tatsuo Ishii писал 13.04.2016 02:36:
>>>
>>> On 04/12/2016 16:50, Adrian Klaver wrote:

 On 04/12/2016 01:06 AM, Alexander Pyhalov wrote:
>>>
>>> I understand. I mean perhaps pgpool shouldn't forward these statements
>>> to slaves.

It should not forward transactions which are requested to be
SERIALIZABLE to standbys.  If you just suppress the SET statement
(or substitute REPEATABLE READ), queries in that transaction can
return incorrect results.

>> Yeah, PostgreSQL used to accept the command on standbys (at least in
>> 9.0). The restriction was added later on.

... in 9.1, for a reason.

>> It woule be nice if you send
>> a bug report to the pgpool-II bug tracker to not forget it.
>>
>> http://pgpool.net/mediawiki/index.php/Bug_tracking_system
>
> Filed http://www.pgpool.net/mantisbt/view.php?id=191

As the entry stands at the moment, the suggestions for fixes will
allow incorrect query results.  See this example, and imagine that
the transaction generating the list of receipts for the closed
batch is run on the standby before the transaction adding the last
receipt commits.  Or test it.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pgpool-II: cannot use serializable mode in a hot standby

[Alexander Pyhalov]

Tatsuo Ishii писал 13.04.2016 02:36:

On 04/12/2016 16:50, Adrian Klaver wrote:

On 04/12/2016 01:06 AM, Alexander Pyhalov wrote:

I understand. I mean perhaps pgpool shouldn't forward these statements
to slaves.


Yeah, PostgreSQL used to accept the command on standbys (at least in
9.0). The restriction was added later on. It woule be nice if you send
a bug report to the pgpool-II bug tracker to not forget it.

http://pgpool.net/mediawiki/index.php/Bug_tracking_system


Filed http://www.pgpool.net/mantisbt/view.php?id=191


---
System Administrator of Southern Federal University Computer Center



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pgpool-II: cannot use serializable mode in a hot standby

[Tatsuo Ishii]

> On 04/12/2016 16:50, Adrian Klaver wrote:
>> On 04/12/2016 01:06 AM, Alexander Pyhalov wrote:
>>> Hi.
>>>
>>> We have application which explicitly does
>>> set default_transaction_isolation to 'serializable' .
>>> It is connected to PostgreSQL master/slave cluster through pgpool-II
>>> (pgpool2 3.4.3-1.pgdg70+1). Our logs are filling with
>>>
>>>   ERROR: 0A000: cannot use serializable mode in a hot standby DETAIL:
>>> "default_transaction_isolation" is set to "serializable". HINT: You
>>> can
>>> use "SET default_transaction_isolation = 'repeatable read'" to change
>>> the default. db netstat, client 10.160.1.38, user nf2pg LOCATION:
>>> GetSerializableTransactionSnapshot, predicate.c:1588 STATEMENT:
>>> DISCARD ALL
>>>
>>> It seems pgpool sends these statements to the slave server. Is it
>>> pgpool/application misconfiguration or pgpool issue?
>>>
>>
>> I would say the above is coming from Postgres not pgpool:
> 
> I understand. I mean perhaps pgpool shouldn't forward these statements
> to slaves.

Yeah, PostgreSQL used to accept the command on standbys (at least in
9.0). The restriction was added later on. It woule be nice if you send
a bug report to the pgpool-II bug tracker to not forget it.

http://pgpool.net/mediawiki/index.php/Bug_tracking_system

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pgpool-II: cannot use serializable mode in a hot standby

[Adrian Klaver]

On 04/12/2016 07:02 AM, Alexander Pyhalov wrote:

On 04/12/2016 16:50, Adrian Klaver wrote:

On 04/12/2016 01:06 AM, Alexander Pyhalov wrote:

Hi.

We have application which explicitly does
set default_transaction_isolation to 'serializable' .
It is connected to PostgreSQL master/slave cluster through pgpool-II
(pgpool2 3.4.3-1.pgdg70+1). Our logs are filling with

  ERROR: 0A000: cannot use serializable mode in a hot standby DETAIL:
"default_transaction_isolation" is set to "serializable". HINT: You can
use "SET default_transaction_isolation = 'repeatable read'" to change
the default. db netstat, client 10.160.1.38, user nf2pg LOCATION:
GetSerializableTransactionSnapshot, predicate.c:1588 STATEMENT:
DISCARD ALL

It seems pgpool sends these statements to the slave server. Is it
pgpool/application misconfiguration or pgpool issue?



I would say the above is coming from Postgres not pgpool:


I understand. I mean perhaps pgpool shouldn't forward these statements
to slaves.



That is probably best handled here:

http://www.pgpool.net/mailman/listinfo/pgpool-general

or here:

http://pgpool.net/mediawiki/index.php/Bug_tracking_system

--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pgpool-II: cannot use serializable mode in a hot standby

[Alexander Pyhalov]

On 04/12/2016 16:50, Adrian Klaver wrote:

On 04/12/2016 01:06 AM, Alexander Pyhalov wrote:

Hi.

We have application which explicitly does
set default_transaction_isolation to 'serializable' .
It is connected to PostgreSQL master/slave cluster through pgpool-II
(pgpool2 3.4.3-1.pgdg70+1). Our logs are filling with

  ERROR: 0A000: cannot use serializable mode in a hot standby DETAIL:
"default_transaction_isolation" is set to "serializable". HINT: You can
use "SET default_transaction_isolation = 'repeatable read'" to change
the default. db netstat, client 10.160.1.38, user nf2pg LOCATION:
GetSerializableTransactionSnapshot, predicate.c:1588 STATEMENT:
DISCARD ALL

It seems pgpool sends these statements to the slave server. Is it
pgpool/application misconfiguration or pgpool issue?



I would say the above is coming from Postgres not pgpool:


I understand. I mean perhaps pgpool shouldn't forward these statements 
to slaves.


--
Best regards,
Alexander Pyhalov,
system administrator of Southern Federal University IT department


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pgpool-II: cannot use serializable mode in a hot standby

[Adrian Klaver]

On 04/12/2016 01:06 AM, Alexander Pyhalov wrote:

Hi.

We have application which explicitly does
set default_transaction_isolation to 'serializable' .
It is connected to PostgreSQL master/slave cluster through pgpool-II
(pgpool2 3.4.3-1.pgdg70+1). Our logs are filling with

  ERROR: 0A000: cannot use serializable mode in a hot standby DETAIL:
"default_transaction_isolation" is set to "serializable". HINT: You can
use "SET default_transaction_isolation = 'repeatable read'" to change
the default. db netstat, client 10.160.1.38, user nf2pg LOCATION:
GetSerializableTransactionSnapshot, predicate.c:1588 STATEMENT: DISCARD ALL

It seems pgpool sends these statements to the slave server. Is it
pgpool/application misconfiguration or pgpool issue?



I would say the above is coming from Postgres not pgpool:

http://www.postgresql.org/docs/9.5/interactive/hot-standby.html

"The Serializable transaction isolation level is not yet available in 
hot standby. (See Section 13.2.3 and Section 13.4.1 for details.) An 
attempt to set a transaction to the serializable isolation level in hot 
standby mode will generate an error."


http://www.postgresql.org/docs/9.5/interactive/applevel-consistency.html#SERIALIZABLE-CONSISTENCY

"Warning

This level of integrity protection using Serializable transactions does 
not yet extend to hot standby mode (Section 25.5). Because of that, 
those using hot standby may want to use Repeatable Read and explicit 
locking on the master."


--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] pgpool-II: cannot use serializable mode in a hot standby

[Alexander Pyhalov]

Hi.

We have application which explicitly does
set default_transaction_isolation to 'serializable' .
It is connected to PostgreSQL master/slave cluster through pgpool-II 
(pgpool2 3.4.3-1.pgdg70+1). Our logs are filling with


 ERROR: 0A000: cannot use serializable mode in a hot standby DETAIL: 
"default_transaction_isolation" is set to "serializable". HINT: You can 
use "SET default_transaction_isolation = 'repeatable read'" to change 
the default. db netstat, client 10.160.1.38, user nf2pg LOCATION: 
GetSerializableTransactionSnapshot, predicate.c:1588 STATEMENT: DISCARD ALL


It seems pgpool sends these statements to the slave server. Is it 
pgpool/application misconfiguration or pgpool issue?


--
Best regards,
Alexander Pyhalov,
system administrator of Southern Federal University IT department


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] pgpool II, streaming replication and HA

[Alexander Pyhalov]

Hi.

I'd like to setup HA PostgreSQL streaming replication cluster with two 
nodes. The main issue which I'd like to protect from is HW node failure 
or reboot. I supposed to use repmgr to control PostgreSQL cluster and 
two pgpool instances as load balancers/DB proxies.


The issue which I still don't understand how to handle is temporary 
master failure. Let's say, we have host A (master) and host B (slave). 
Now A reboots. Pgpool (or repmgr, doesn't matter) promotes B to master. 
Now A comes online (and still considers itself master). How can Pgpool 
determine that A host is "bad one" and stop routing queries to A?

--
Best regards,
Alexander Pyhalov,
system administrator of Southern Federal University IT department


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pgpool II, streaming replication and HA

[Alexander Pyhalov]

On 01/27/2016 11:53, Tatsuo Ishii wrote:


There's a file called "pool_status" which records the previous status
of the DB servers. So the file records that "A is down, B is up". In
your case, when A reboots, the old primary server on A may start as a
primary, but from the file pgpool knows that the PostgreSQL on A was
down and never automatically regards it online. So any query sent to
pgpool will be routed to the new primary running on B, never routed to
A.

To make the old primary on A usable, you need to shutdown the
PostgreSQL (if it's running) and turn it to a new standby connecting
to the current primary on B. You can do it by either "online recovery"
feature of pgpool or do it manually using pg_basebackup etc. In the
latter case, you need to "attach" 'the new standby to make it online
by using pcp_attach_node.


Thank you for clarification.


--
Best regards,
Alexander Pyhalov,
system administrator of Southern Federal University IT department


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pgpool II, streaming replication and HA

[Tatsuo Ishii]

I don't know anything about repmgr. I neglect the questions to repmgr.

> I'd like to setup HA PostgreSQL streaming replication cluster with two
> nodes. The main issue which I'd like to protect from is HW node
> failure or reboot. I supposed to use repmgr to control PostgreSQL
> cluster and two pgpool instances as load balancers/DB proxies.
> 
> The issue which I still don't understand how to handle is temporary
> master failure. Let's say, we have host A (master) and host B
> (slave). Now A reboots. Pgpool (or repmgr, doesn't matter) promotes B
> to master. Now A comes online (and still considers itself master). How
> can Pgpool determine that A host is "bad one" and stop routing queries
> to A?

There's a file called "pool_status" which records the previous status
of the DB servers. So the file records that "A is down, B is up". In
your case, when A reboots, the old primary server on A may start as a
primary, but from the file pgpool knows that the PostgreSQL on A was
down and never automatically regards it online. So any query sent to
pgpool will be routed to the new primary running on B, never routed to
A.

To make the old primary on A usable, you need to shutdown the
PostgreSQL (if it's running) and turn it to a new standby connecting
to the current primary on B. You can do it by either "online recovery"
feature of pgpool or do it manually using pg_basebackup etc. In the
latter case, you need to "attach" 'the new standby to make it online
by using pcp_attach_node.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pgpool ssl handshake failure

[Albe Laurenz]

AI Rumman wrote:
> I am using pgpool-II version 3.4.3 (tataraboshi).
> Where my database is Postgresql 8.4.
> 
> I am trying to configure ssl mode from client and between pgpool and database 
> it is non-ssl.
> I configured as document and now I am getting this in my log:
> 
>   2015-10-13 22:17:58: pid 1857: LOG:  new connection received
>   2015-10-13 22:17:58: pid 1857: DETAIL:  connecting host=10.0.0.5 
> port=65326
>   2015-10-13 22:17:58: pid 1857: LOG:  pool_ssl: "SSL_read": "ssl 
> handshake failure"
>   2015-10-13 22:17:58: pid 1857: ERROR:  unable to read data from frontend
>   2015-10-13 22:17:58: pid 1857: DETAIL:  socket read failed with an 
> error "Success"
> 
> Please let me know what wrong I am doing.

Does the problem occur immediately when you try to connect or only after some 
time?

If the latter, it may well be a renegotiation issue.
If the former, I can't guess.
WHat are the OpenSSL versions involved on both ends?

Yours,
Laurenz Albe

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] pgpool ssl handshake failure

[AI Rumman]

Hi,

I am using pgpool-II version 3.4.3 (tataraboshi).
Where my database is Postgresql 8.4.

I am trying to configure ssl mode from client and between pgpool and
database it is non-ssl.
I configured as document and now I am getting this in my log:

>
> *2015-10-13 22:17:58: pid 1857: LOG:  new connection received*
> *2015-10-13 22:17:58: pid 1857: DETAIL:  connecting host=10.0.0.5
> port=65326*
> *2015-10-13 22:17:58: pid 1857: LOG:  pool_ssl: "SSL_read": "ssl handshake
> failure"*
> *2015-10-13 22:17:58: pid 1857: ERROR:  unable to read data from 
> frontend**2015-10-13
> 22:17:58: pid 1857: DETAIL:  socket read failed with an error "Success"*

Please let me know what wrong I am doing.

Thanks & Regards.

Re: [GENERAL] pgpool ssl handshake failure

[Adrian Klaver]

On 10/15/2015 06:59 AM, AI Rumman wrote:

Hi,

I am using pgpool-II version 3.4.3 (tataraboshi).
Where my database is Postgresql 8.4.


Probably already know, but 8.4 is approximately 1.25 years beyond EOL:

http://www.postgresql.org/support/versioning/



I am trying to configure ssl mode from client and between pgpool and
database it is non-ssl.


What is non-ssl, the database or pgpool?


I configured as document and now I am getting this in my log:

/2015-10-13 22:17:58: pid 1857: LOG:  new connection received
//2015-10-13 22:17:58: pid 1857: DETAIL:  connecting host=10.0.0.5
port=65326
//2015-10-13 22:17:58: pid 1857: LOG:  pool_ssl: "SSL_read": "ssl
handshake failure"
//2015-10-13 22:17:58: pid 1857: ERROR:  unable to read data from
frontend
//2015-10-13 22:17:58: pid 1857: DETAIL:  socket read failed with an
error "Success"/

Please let me know what wrong I am doing.


Not quite sure but given the below from the 9.5 Release Notes:

"
Remove server configuration parameter ssl_renegotiation_limit, which was 
deprecated in earlier releases (Andres Freund)


While SSL renegotiation is a good idea in theory, it has caused enough 
bugs to be considered a net negative in practice, and it is due to be 
removed from future versions of the relevant standards. We have 
therefore removed support for it from PostgreSQL."


I would check to see what  ssl_renegotiation_limit is set to:

http://www.postgresql.org/docs/8.4/static/runtime-config-connection.html

and if it is not set to 0, then try that.




Thanks & Regards.




--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pgpool ssl handshake failure

[Tatsuo Ishii]

> Hi,
> 
> I am using pgpool-II version 3.4.3 (tataraboshi).
> Where my database is Postgresql 8.4.
> 
> I am trying to configure ssl mode from client and between pgpool and
> database it is non-ssl.
> I configured as document and now I am getting this in my log:
> 
>>
>> *2015-10-13 22:17:58: pid 1857: LOG:  new connection received*
>> *2015-10-13 22:17:58: pid 1857: DETAIL:  connecting host=10.0.0.5
>> port=65326*
>> *2015-10-13 22:17:58: pid 1857: LOG:  pool_ssl: "SSL_read": "ssl handshake
>> failure"*
>> *2015-10-13 22:17:58: pid 1857: ERROR:  unable to read data from 
>> frontend**2015-10-13
>> 22:17:58: pid 1857: DETAIL:  socket read failed with an error "Success"*
> 
> Please let me know what wrong I am doing.

Works for me using psql coming with PostgreSQL 9.4.5 and pgpool-II 3.4.3.
(This is Ubuntu 14.04. PostgreSQL and pgpool-II are compiled from the
source code).

$ psql -p 11000 -h localhost test
psql (9.4.5)
SSL connection (protocol: TLSv1, cipher: AES256-SHA, bits: 256, compression: 
off)
Type "help" for help.

I don't think your old PostgreSQL 8.4 server is related to your
problem because you are trying to enable SSL between client and
pgpool, not pgpool and PostgreSQL server. However psql coming with
PostgreSQL 8.4 might be related to the problem. Why don't you try
newer version of psql (more precisely, newer libpq).

I assume your SSL setting is perfect. If you are not sure, please take
a look at FAQ:

http://pgpool.net/mediawiki/index.php/FAQ#How_can_I_set_up_SSL_for_pgpool-II.3F

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pgpool ssl handshake failure

[AI Rumman]

I configured Postgresql 9.4 and still getting the same error.

Thanks.

On Thu, Oct 15, 2015 at 7:16 AM, Adrian Klaver 
wrote:

> On 10/15/2015 06:59 AM, AI Rumman wrote:
>
>> Hi,
>>
>> I am using pgpool-II version 3.4.3 (tataraboshi).
>> Where my database is Postgresql 8.4.
>>
>
> Probably already know, but 8.4 is approximately 1.25 years beyond EOL:
>
> http://www.postgresql.org/support/versioning/
>
>
>> I am trying to configure ssl mode from client and between pgpool and
>> database it is non-ssl.
>>
>
> What is non-ssl, the database or pgpool?
>
> I configured as document and now I am getting this in my log:
>>
>> /2015-10-13 22:17:58: pid 1857: LOG:  new connection received
>> //2015-10-13 22:17:58: pid 1857: DETAIL:  connecting host=10.0.0.5
>> port=65326
>> //2015-10-13 22:17:58: pid 1857: LOG:  pool_ssl: "SSL_read": "ssl
>> handshake failure"
>> //2015-10-13 22:17:58: pid 1857: ERROR:  unable to read data from
>> frontend
>> //2015-10-13 22:17:58: pid 1857: DETAIL:  socket read failed with an
>> error "Success"/
>>
>> Please let me know what wrong I am doing.
>>
>
> Not quite sure but given the below from the 9.5 Release Notes:
>
> "
> Remove server configuration parameter ssl_renegotiation_limit, which was
> deprecated in earlier releases (Andres Freund)
>
> While SSL renegotiation is a good idea in theory, it has caused enough
> bugs to be considered a net negative in practice, and it is due to be
> removed from future versions of the relevant standards. We have therefore
> removed support for it from PostgreSQL."
>
> I would check to see what  ssl_renegotiation_limit is set to:
>
> http://www.postgresql.org/docs/8.4/static/runtime-config-connection.html
>
> and if it is not set to 0, then try that.
>
>
>
>> Thanks & Regards.
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: [GENERAL] pgpool ssl handshake failure

[Adrian Klaver]

On 10/15/2015 09:36 AM, AI Rumman wrote:

I configured Postgresql 9.4 and still getting the same error.


Configured what?

Or more to the point what is ssl_renegotiation_limit set to?



Thanks.

On Thu, Oct 15, 2015 at 7:16 AM, Adrian Klaver
> wrote:

On 10/15/2015 06:59 AM, AI Rumman wrote:

Hi,

I am using pgpool-II version 3.4.3 (tataraboshi).
Where my database is Postgresql 8.4.


Probably already know, but 8.4 is approximately 1.25 years beyond EOL:

http://www.postgresql.org/support/versioning/


I am trying to configure ssl mode from client and between pgpool and
database it is non-ssl.


What is non-ssl, the database or pgpool?

I configured as document and now I am getting this in my log:

 /2015-10-13 22:17:58: pid 1857: LOG:  new connection received
 //2015-10-13 22:17:58: pid 1857: DETAIL:  connecting
host=10.0.0.5
 port=65326
 //2015-10-13 22:17:58: pid 1857: LOG:  pool_ssl:
"SSL_read": "ssl
 handshake failure"
 //2015-10-13 22:17:58: pid 1857: ERROR:  unable to read
data from
 frontend
 //2015-10-13 22:17:58: pid 1857: DETAIL:  socket read
failed with an
 error "Success"/

Please let me know what wrong I am doing.


Not quite sure but given the below from the 9.5 Release Notes:

"
Remove server configuration parameter ssl_renegotiation_limit, which
was deprecated in earlier releases (Andres Freund)

While SSL renegotiation is a good idea in theory, it has caused
enough bugs to be considered a net negative in practice, and it is
due to be removed from future versions of the relevant standards. We
have therefore removed support for it from PostgreSQL."

I would check to see what  ssl_renegotiation_limit is set to:

http://www.postgresql.org/docs/8.4/static/runtime-config-connection.html

and if it is not set to 0, then try that.



Thanks & Regards.



--
Adrian Klaver
adrian.kla...@aklaver.com 





--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] PgPool Configuration Document required

[Melvin Davidson]

google search works fine for that:


http://pgpool.net/mediawiki/index.php/Documentation

http://www.pgpool.net/mediawiki/images/pgpool-en-3.3.0.pdf


On Tue, Jun 30, 2015 at 12:05 PM, Jimit Amin jimitam...@gmail.com wrote:

 Dear Sir/Mam,

 Can I have technical documentation for configuration of PgPool?




-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

[GENERAL] PgPool Configuration Document required

[Jimit Amin]

Dear Sir/Mam,

Can I have technical documentation for configuration of PgPool?

Re: [GENERAL] PgPool Configuration Document required

[Joshua D. Drake]

On 06/30/2015 09:05 AM, Jimit Amin wrote:

Dear Sir/Mam,

Can I have technical documentation for configuration of PgPool?



http://www.pgpool.net/mediawiki/index.php/Main_Page

--
Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Announcing I'm offended is basically telling the world you can't
control your own emotions, so everyone else should do it for you.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] PgPool Configuration Document required

[Jimit Amin]

Dear Sir,

Sorry for this type of question, I have already configured PgPool but
facing 3 issue. That's why I thought that I may have done wrong setup.

Issue faced
I have streaming replication setup 1 master and 1 read-only server and
PgPool

1. I have already mentioned nextval in blacklistfunction but sometime
PgPool fires nextval on read-only server. I have log file.

2. I am not able to see modified data updated by same transaction because

Begin;
Update  ; --- master server

Select that data ... ; --- I am not able to see updated data by previous
update because this select executed on read-only server

3. From application I am executing one procedure which returns cursor..
I am getting error like unnamed portal  dose not exists, while checking
log I got to know that exec procedure done on read-only slave server and
fetch all in unnamed portal  executed on slave server.

Thanks,
Jimit Amin
On 30 Jun 2015 21:52, Joshua D. Drake j...@commandprompt.com wrote:


 On 06/30/2015 09:05 AM, Jimit Amin wrote:

 Dear Sir/Mam,

 Can I have technical documentation for configuration of PgPool?


 http://www.pgpool.net/mediawiki/index.php/Main_Page

 --
 Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
 PostgreSQL Centered full stack support, consulting and development.
 Announcing I'm offended is basically telling the world you can't
 control your own emotions, so everyone else should do it for you.

Re: [GENERAL] PgPool Configuration Document required

[Joshua D. Drake]

On 06/30/2015 09:35 AM, Jimit Amin wrote:

Dear Sir,

Sorry for this type of question, I have already configured PgPool but
facing 3 issue. That's why I thought that I may have done wrong setup.



I would suggest the pgpool mailing lists.

JD

--
Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Announcing I'm offended is basically telling the world you can't
control your own emotions, so everyone else should do it for you.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Pgpool starting problem

[Jay at Verizon]

Problem has been solved utilizing tcpdump which revealed that the standbys were 
not trying to contact the primary server, although they each were contacting 
the other standby. From 
this, we determined that a minor typographical error was present in the 
pgpool.conf file. The setting under the watchdog section for other_hostname0 
was correct, and other required 
settings for that entry were also correct, but had somehow been commented out. 
The log file was not very revealing, and finding one badly inserted # in a 
647 line file was 
problematic to say the least.

On 9/9/2014 2:42 PM, Ellen [via PostgreSQL] wrote:
 Hi Jay,
 Can you pls tell me how you resolved this issue.
 We are running pgpool-II version 3.3.3
 Thanks.
 Ellen

 
 If you reply to this email, your message will be added to the discussion 
 below:
 http://postgresql.1045698.n5.nabble.com/Pgpool-starting-problem-tp5803276p5818354.html
 To unsubscribe from Pgpool starting problem, click here 
 http://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_codenode=5803276code=amF5a25vd3N1bml4QGdtYWlsLmNvbXw1ODAzMjc2fDM1NDE5ODU4OQ==.
 NAML 
 http://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewerid=instant_html%21nabble%3Aemail.namlbase=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespacebreadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml
  






--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Pgpool-starting-problem-tp5803276p5819090.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: [GENERAL] Pgpool starting problem

[Ellen]

Hi Jay,
Can you pls tell me how you resolved this issue. 
We are running pgpool-II version 3.3.3
Thanks.
Ellen



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Pgpool-starting-problem-tp5803276p5818354.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Pgpool starting problem

[Jay at Verizon]

Ellen,

To date I have no solution. I'm currently trying to build a debug-able version 
of 3.3.4 as I think I have a network problem which is preventing pgpool from 
being a happy shareable system. Each one currently thinks it's a primary upon 
startup, if it will start at all. 

Sent from my iPad

 On Sep 9, 2014, at 2:42 PM, Ellen [via PostgreSQL] 
 ml-node+s1045698n5818354...@n5.nabble.com wrote:
 
 Hi Jay, 
 Can you pls tell me how you resolved this issue. 
 We are running pgpool-II version 3.3.3 
 Thanks. 
 Ellen 
 
 If you reply to this email, your message will be added to the discussion 
 below:
 http://postgresql.1045698.n5.nabble.com/Pgpool-starting-problem-tp5803276p5818354.html
 To unsubscribe from Pgpool starting problem, click here.
 NAML




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Pgpool-starting-problem-tp5803276p5818370.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

[GENERAL] Pgpool starting problem

[Jay at Verizon]

Hi all,

If I turn use_watchdog = off in the pgpool.conf file, then in spite of the
system being configured as master-slave, a socket file is created in /tmp,
and everything starts normally with no errors. If, however, I set
use_watchdog = on, pgpool will abort on startup with the following errors:

2014-05-08 14:40:22 ERROR: pid 32893: watchdog: wd_init failed
2014-05-08 14:40:22 ERROR: pid 32893: wd_main error
2014-05-08 14:40:22 ERROR: pid 32893: unlink(/tmp/.s.PGSQL.9898) failed: No
such file or directory

It seems pretty consistent doing this, and I can flip use_watchdog on and
off and the same behavior repeating. Any ideas?
--
Jay



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Pgpool-starting-problem-tp5803276.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Pgpool starting problem

[David G Johnston]

Jay at Verizon wrote
 Hi all,
 
 If I turn use_watchdog = off in the pgpool.conf file, then in spite of the
 system being configured as master-slave, a socket file is created in /tmp,
 and everything starts normally with no errors. If, however, I set
 use_watchdog = on, pgpool will abort on startup with the following errors:
 
 2014-05-08 14:40:22 ERROR: pid 32893: watchdog: wd_init failed
 2014-05-08 14:40:22 ERROR: pid 32893: wd_main error
 2014-05-08 14:40:22 ERROR: pid 32893: unlink(/tmp/.s.PGSQL.9898) failed:
 No such file or directory
 
 It seems pretty consistent doing this, and I can flip use_watchdog on and
 off and the same behavior repeating. Any ideas?
 --
 Jay

I'm doubting you are providing enough information for someone to actually
provide useful help.

Beyond that this is not the correct list for this question.  pgPoolII is a
standalone product - i.e., not part of PostgreSQL core which is what this
list to dedicated to.  The proper links, as well as documentation, can be
found at:

http://www.pgpool.net/mediawiki/index.php/Main_Page

I suggest posting the actual configuration file along with detailing exactly
how (and on what platform) you are running the various applications.

It is possible that O/S user permissions prevent pgPool from seeing or
interacting with the socket file.

David J.





--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Pgpool-starting-problem-tp5803276p5803279.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Pgpool starting problem

[Tatsuo Ishii]

 Hi all,
 
 If I turn use_watchdog = off in the pgpool.conf file, then in spite of the
 system being configured as master-slave, a socket file is created in /tmp,
 and everything starts normally with no errors. If, however, I set
 use_watchdog = on, pgpool will abort on startup with the following errors:
 
 2014-05-08 14:40:22 ERROR: pid 32893: watchdog: wd_init failed
 2014-05-08 14:40:22 ERROR: pid 32893: wd_main error
 2014-05-08 14:40:22 ERROR: pid 32893: unlink(/tmp/.s.PGSQL.9898) failed: No
 such file or directory
 
 It seems pretty consistent doing this, and I can flip use_watchdog on and
 off and the same behavior repeating. Any ideas?

You didn't give any pgpool version, configuration file info, I cannot
guess what's going on. There's a detailed installation document here:

http://www.pgpool.net/pgpool-web/contrib_docs/watchdog_master_slave_3.3/en.html

Despite you follow the instruction you still have problems, you should ask 
pgpool ML.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Pgpool Setup inquiry - Configuration concerns

[Burgess, Freddie]

Environment: PostgreSQL 9.3.3 on RHEL 6.4

We have already configured streaming replication in our environment. We have a 
master/primary and two standby server configuration. Our next task requires us 
to configure and install pgpool.

we want to leverage the installer:  installer-pg93-3.3.3.tar.gz

Will running this installer intuitively reason that replication is already 
configured and running on the three nodes and just perform the remaining 
required components of the setup?

thanks

Freddie

[GENERAL] pgpool-ha not found in yum postgres repository

[Mistina Michal]

Hello to everybody.

I've found there should be a package pgpool-ha in the full package list:

http://yum.postgresql.org/news-packagelist.php

 

I installed PostgreSQL yum repository by installing this RPM (red hat 6,
x86_64):

http://yum.postgresql.org/9.2/redhat/rhel-6-x86_64/pgdg-redhat92-9.2-7.noarc
h.rpm

found on the website:

http://yum.postgresql.org/repopackages.php

 

Then I issued yum search pgpool-ha. The package wasn't found.

Yum repository for postgresql is enabled and present..

cat /etc/yum.repos.d/pgdg-92-redhat.repo 

[pgdg92]

name=PostgreSQL 9.2 $releasever - $basearch

baseurl=http://yum.postgresql.org/9.2/redhat/rhel-6-$basearch

enabled=1

gpgcheck=1

gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG-92

 

[pgdg92-source]

name=PostgreSQL 9.2 $releasever - $basearch - Source

failovermethod=priority

baseurl=http://yum.postgresql.org/srpms/9.2/redhat/rhel-$releasever-$basearc
h

enabled=0

gpgcheck=1

gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG-92

 

 

Why is pgpool-ha rpm package missing in the PostgreSQL yum repository
despite it is listed in the full package list link?

 

Best regards,

Michal Mistina

 



smime.p7s
Description: S/MIME cryptographic signature

[GENERAL] Pgpool outdated spec

[Emanuel Araújo]

Hi,

I'm doing a rebuild to a rpm package postgresql-9.0 with pgpool but your
spec is outdated, there is some spec updated or have to make my changes
manually?

-- *
Emanuel Araújo*
http://eacshm.wordpress.com/
*
*
*Linux Certified
LPIC-1*

Re: [GENERAL] Pgpool outdated spec

[Tatsuo Ishii]

 I'm doing a rebuild to a rpm package postgresql-9.0 with pgpool but your
 spec is outdated, there is some spec updated or have to make my changes
 manually?

Devrim is in charge of updating the spec file. Devrim?
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Pgpool outdated spec

[Devrim GÜNDÜZ]

On Mon, 2011-09-12 at 08:07 -0300, Emanuel Araújo wrote:
 
 I'm doing a rebuild to a rpm package postgresql-9.0 with pgpool but
 your spec is outdated, there is some spec updated or have to make my
 changes manually? 

Please hold on for 2-3 days. PgPool is the first RPM that I will need to
update, however I'm waiting for 9.1.0 to be releases first. I will
release packages before Thursday.

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

[GENERAL] pgpool HA not working

[Sanjay Rao]

Hi

I am working on pgpool /postgresql/linux first time. All these three are 
new to me.


I am running pgpool-HA using pacemaker-corosync.
I am having following error in my setup. Does any body have any idea 
about the warning highlighted in logs ? Any type of help would be 
appreciated a lot...


Jul 24 18:40:41 squarepant attrd: [3772]: info: attrd_perform_update: 
Sent update 1010: probe_complete=true
Jul 24 18:40:41 squarepant cib: [3770]: WARN: cib_process_request: 
Operation complete: op cib_modify for section status 
(origin=local/attrd/1010, version=0.52.352): The object/attribute does 
not exist (rc=-22)
Jul 24 18:40:41 squarepant attrd: [3772]: WARN: attrd_cib_callback: 
Update 1010 for probe_complete=true failed: The object/attribute does 
not exist
Jul 24 18:40:41 squarepant pgpool[7635]: INFO: pgpoolRA: request stop, 
but not running.
Jul 24 18:40:41 squarepant crmd: [3896]: info: process_lrm_event: LRM 
operation pgpool_stop_0 (call=339, rc=0, cib-update=472, confirmed=true) ok
Jul 24 18:40:41 squarepant crmd: [3896]: info: match_graph_event: Action 
pgpool_stop_0 (2) confirmed on squarepant (rc=0)
Jul 24 18:40:41 squarepant crmd: [3896]: info: te_rsc_command: 
Initiating action 12: start pgpool_start_0 on squarepant (local)
Jul 24 18:40:41 squarepant crmd: [3896]: info: do_lrm_rsc_op: Performing 
key=12:112:0:6d68acf3-ab99-409f-b686-8533e4b24ca0 op=pgpool_start_0 )

Jul 24 18:40:41 squarepant lrmd: [3771]: info: rsc:pgpool:340: start
Jul 24 18:40:41 squarepant crmd: [3896]: info: te_pseudo_action: Pseudo 
action 5 fired and confirmed



ouput of crm_mon command is



Last updated: Sun Jul 24 18:15:59 2011
Stack: Heartbeat
Current DC: squarepant (85f4f3d6-650e-4620-8cbb-edb1bc9d389c) - 
partition with quorum

Version: 1.1.5-1.fc14-01e86afaaa6d4a8c4836f68df80ababd6ca3902f
1 Nodes configured, unknown expected votes
3 Resources configured.


Online: [ squarepant ]

 DBIP   (ocf::heartbeat:IPaddr2):   Started squarepant
 postgresql (ocf::heartbeat:pgsql): Started squarepant
 pgpool (ocf::heartbeat:pgpool):Started squarepant FAILED

Failed actions:
pgpool_monitor_3 (node=squarepant, call=1874, rc=7, 
status=complete): not running



Thanks  Regards
Sanjay

[GENERAL] pgpool-II 3.0.4 release delayed

[Tatsuo Ishii]

Sorry for off topic posting but...

Since pgfoundry has been down, the schedule for releasing pgpool-II
3.0.4, to be released today, will be delayed. Sorry for
inconvenience. We will start to continue the releasing work as soon as
pgfoundry comes back.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] pgpool slony slaves only

[Frank Jansen]

Hi,

as the Subject says i want pgpool to talk only to my slony slaves.
In my company we have a Slony Setup with 1 master replicating to 2 
slaves. Our application only does read queries (never ever writes) to 
the 2 slaves through pgpool. While upgrading to pgpool 3.0.1 (from 
2.2.5) i detected that the former admin has set up pgpool with 
replication_mode on. I now thought about going to master_salve_mode , 
but i am bit lost between the various possibilities of modes, especially 
cause i dont need any write queries deleivered to my slony master. cause 
the application we run will just read and never write. We want just 
connection pooling and load balacing through pgpool to our 2 slaves.


Couldnt find anyone on google using pgpool to access read only his slony 
slaves, maybe someone has suggestions for me.


Regards,

Frank Jansen

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pgpool-II 3.0 + postgres 9rc1 + md5 authentication not working

[Bryan Murphy]

On Mon, Sep 20, 2010 at 6:23 PM, Tatsuo Ishii is...@sraoss.co.jp wrote:

 I have used PostgreSQL 9.0 + pgpool-II 3.0 and they work fine with md5
 auth.  Your log seems to indicate that the password in pool_passwd and
 the one in pg_shadow are not identical. Can you verify that?
 The query result:

 select passwd from pg_shadow where usename = 'test';

 must be identical the password in pool_passwd for user test.


Ah, that was it!  I'll double check how I'm generating the passwords in
pool_passwd, but once I copied the md5 from pg_shadow to pool_passwd it
started working.

Many thanks for your help!

Bryan

Re: [GENERAL] pgpool-II 3.0 + postgres 9rc1 + md5 authentication not working

[Bryan Murphy]

On Tue, Sep 21, 2010 at 10:26 AM, Bryan Murphy bmurphy1...@gmail.comwrote:

 On Mon, Sep 20, 2010 at 6:23 PM, Tatsuo Ishii is...@sraoss.co.jp wrote:

 I have used PostgreSQL 9.0 + pgpool-II 3.0 and they work fine with md5
 auth.  Your log seems to indicate that the password in pool_passwd and
 the one in pg_shadow are not identical. Can you verify that?
 The query result:

 select passwd from pg_shadow where usename = 'test';

 must be identical the password in pool_passwd for user test.


 Ah, that was it!  I'll double check how I'm generating the passwords in
 pool_passwd, but once I copied the md5 from pg_shadow to pool_passwd it
 started working.

 Many thanks for your help!

 Bryan


I'm sorry, when I went back over to double check my steps I realized I ran
the wrong command.  I am *still* having the problem.  It appears that the
MD5 hashes now match, but it's still failing.  I have postgres and pgpool
installed in /opt/postgresql, but I have the same problem when I put
pool_passwd in /etc and /opt/postgresql/etc.


psql -h 127.0.0.1 -p  template1 test
Password for user test:
psql: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.


psql -h 10.240.163.95 template1 test
Password for user test:
psql (9.0.0)
Type help for help.

template1=# select usename, passwd from pg_shadow where usename = 'test';
 usename |   passwd
-+-
 test| md505a671c66aefea124cc08b76ea6d30bb
(1 row)


cat /opt/postgresql/etc/pool_passwd
test:05a671c66aefea124cc08b76ea6d30bb


cat /opt/postgresql/etc/pgpool.conf | grep hostname0
backend_hostname0 = '10.240.163.95'


pgpool log:
2010-09-21 15:36:26 LOG:   pid 4661: connection received: host=127.0.0.1
port=41418
2010-09-21 15:36:26 DEBUG: pid 4661: Protocol Major: 3 Minor: 0 database:
template1 user: test
2010-09-21 15:36:26 DEBUG: pid 4661: new_connection: connecting 0 backend
2010-09-21 15:36:26 DEBUG: pid 4661: pool_ssl: SSL requested but SSL support
is not available
2010-09-21 15:36:26 DEBUG: pid 4661: pool_read_message_length: slot: 0
length: 12
2010-09-21 15:36:26 DEBUG: pid 4661: pool_do_auth: auth kind:5
2010-09-21 15:36:26 DEBUG: pid 4661: trying md5 authentication
2010-09-21 15:36:26 DEBUG: pid 4661: DB node id: 0 salt: fa1f213a
2010-09-21 15:36:26 DEBUG: pid 4661: read_password_packet_password: failed
to read password packet p
2010-09-21 15:36:26 ERROR: pid 4661: do_md5: read_password_packet failed
2010-09-21 15:36:26 DEBUG: pid 4661: do_md5failed in slot 0
2010-09-21 15:36:27 DEBUG: pid 4717: I am 4717 accept fd 6
2010-09-21 15:36:27 LOG:   pid 4717: connection received: host=127.0.0.1
port=41420
2010-09-21 15:36:27 DEBUG: pid 4717: Protocol Major: 3 Minor: 0 database:
template1 user: test
2010-09-21 15:36:27 DEBUG: pid 4717: new_connection: connecting 0 backend
2010-09-21 15:36:27 DEBUG: pid 4717: pool_ssl: SSL requested but SSL support
is not available
2010-09-21 15:36:27 DEBUG: pid 4717: pool_read_message_length: slot: 0
length: 12
2010-09-21 15:36:27 DEBUG: pid 4717: pool_do_auth: auth kind:5
2010-09-21 15:36:27 DEBUG: pid 4717: trying md5 authentication
2010-09-21 15:36:27 DEBUG: pid 4717: DB node id: 0 salt: 1c8e5961
2010-09-21 15:36:27 DEBUG: pid 4654: reap_handler called
2010-09-21 15:36:27 DEBUG: pid 4654: reap_handler: call wait3
2010-09-21 15:36:27 ERROR: pid 4654: Child process 4717 was terminated by
segmentation fault
2010-09-21 15:36:27 DEBUG: pid 4654: child 4717 exits with status 11 by
signal 11
2010-09-21 15:36:27 DEBUG: pid 4654: fork a new child pid 4722
2010-09-21 15:36:27 DEBUG: pid 4654: reap_handler: normally exited
2010-09-21 15:36:27 DEBUG: pid 4722: I am 4722

Re: [GENERAL] pgpool-II 3.0 + postgres 9rc1 + md5 authentication not working

[Bryan Murphy]

On Tue, Sep 21, 2010 at 10:45 AM, Bryan Murphy bmurphy1...@gmail.comwrote:

 I'm sorry, when I went back over to double check my steps I realized I ran
 the wrong command.  I am *still* having the problem.  It appears that the
 MD5 hashes now match, but it's still failing.  I have postgres and pgpool
 installed in /opt/postgresql, but I have the same problem when I put
 pool_passwd in /etc and /opt/postgresql/etc.


Here is a gdb backtrace from the child process when it sigsegs after I enter
the correct password.  I'm digging in a little deeper now and I thought this
might be useful.

(gdb) bt
#0  0xefcbf24f in ?? ()
#1  0x08052ac5 in do_md5 (backend=0xefcbf24f, frontend=0x9dfc5c0,
reauth=value optimized out, protoMajor=3)
at /usr/include/bits/string3.h:52
#2  0x08052e7b in pool_do_auth (frontend=0x9df97c0, cp=0x9df8fa8) at
pool_auth.c:222
#3  0x080509a9 in connect_backend (unix_fd=4, inet_fd=5) at child.c:1143
#4  do_child (unix_fd=4, inet_fd=5) at child.c:293
#5  0x0804bbdf in fork_a_child (unix_fd=4, inet_fd=5, id=value optimized
out) at main.c:1024
#6  0x0804ddef in main (argc=3, argv=0xbfab6754) at main.c:514

Re: [GENERAL] pgpool-II 3.0 + postgres 9rc1 + md5 authentication not working

[Tatsuo Ishii]

 On Tue, Sep 21, 2010 at 10:45 AM, Bryan Murphy bmurphy1...@gmail.comwrote:
 
 I'm sorry, when I went back over to double check my steps I realized I ran
 the wrong command.  I am *still* having the problem.  It appears that the
 MD5 hashes now match, but it's still failing.  I have postgres and pgpool
 installed in /opt/postgresql, but I have the same problem when I put
 pool_passwd in /etc and /opt/postgresql/etc.

 
 Here is a gdb backtrace from the child process when it sigsegs after I enter
 the correct password.  I'm digging in a little deeper now and I thought this
 might be useful.

Unfortunately the gdb backtrace does not show enough information
because of optimization, I guess.  Can you take a backtrace with
optimization disabled binary?

You can obtain this by editing Makefile around line 147.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

 (gdb) bt
 #0  0xefcbf24f in ?? ()
 #1  0x08052ac5 in do_md5 (backend=0xefcbf24f, frontend=0x9dfc5c0,
 reauth=value optimized out, protoMajor=3)
 at /usr/include/bits/string3.h:52
 #2  0x08052e7b in pool_do_auth (frontend=0x9df97c0, cp=0x9df8fa8) at
 pool_auth.c:222
 #3  0x080509a9 in connect_backend (unix_fd=4, inet_fd=5) at child.c:1143
 #4  do_child (unix_fd=4, inet_fd=5) at child.c:293
 #5  0x0804bbdf in fork_a_child (unix_fd=4, inet_fd=5, id=value optimized
 out) at main.c:1024
 #6  0x0804ddef in main (argc=3, argv=0xbfab6754) at main.c:514
 
 



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pgpool-II 3.0 + postgres 9rc1 + md5 authentication not working

[Bryan Murphy]

On Tue, Sep 21, 2010 at 8:08 PM, Tatsuo Ishii is...@postgresql.org wrote:

 Unfortunately the gdb backtrace does not show enough information
 because of optimization, I guess.  Can you take a backtrace with
 optimization disabled binary?

 You can obtain this by editing Makefile around line 147.


I edited configure and change every instance of  -O2 to -O for postgres and
pgpool and added --enable-debug.  Here's the new backtrace:

(gdb) bt
#0  0xb75fc346 in ?? () from /lib/tls/i686/nosegneg/libc.so.6
#1  0x0805f342 in pool_flush (cp=0x3) at pool_stream.c:486
#2  0x08052e7b in pool_do_auth (frontend=0x946b778, cp=0x0) at
pool_auth.c:222
#3  0x080509a9 in connect_backend (unix_fd=4, inet_fd=5) at child.c:1143
#4  do_child (unix_fd=4, inet_fd=5) at child.c:293
#5  0x0804bbdf in fork_a_child (unix_fd=4, inet_fd=5, id=value optimized
out) at main.c:1024
#6  0x0804ddef in main (argc=3, argv=0xbfaad2d4) at main.c:514

Re: [GENERAL] pgpool-II 3.0 + postgres 9rc1 + md5 authentication not working

[Bryan Murphy]

On Sun, Sep 19, 2010 at 11:31 PM, Tatsuo Ishii is...@sraoss.co.jp wrote:

 Sorry for delay. I had a trip outside Japan.


No problem.


 I found nasty bug with pgpool. Please try attached patches.


I tried the patch file and I still cannot connect.  The only other
difference is that I've already upgraded our images to Postgres 9.0.0 from
rc1.

Here is the pgpool debug log:

2010-09-20 19:43:19 DEBUG: pid 1329: I am 1329 accept fd 6
2010-09-20 19:43:19 LOG:   pid 1329: connection received: host=127.0.0.1
port=39064
2010-09-20 19:43:19 DEBUG: pid 1329: Protocol Major: 3 Minor: 0 database:
template1 user: test
2010-09-20 19:43:19 DEBUG: pid 1329: new_connection: connecting 0 backend
2010-09-20 19:43:19 DEBUG: pid 1329: pool_ssl: SSL requested but SSL support
is not available
2010-09-20 19:43:19 DEBUG: pid 1329: pool_read_message_length: slot: 0
length: 12
2010-09-20 19:43:19 DEBUG: pid 1329: pool_do_auth: auth kind:5
2010-09-20 19:43:19 DEBUG: pid 1329: trying md5 authentication
2010-09-20 19:43:19 DEBUG: pid 1329: DB node id: 0 salt: 89d02c38
2010-09-20 19:43:19 DEBUG: pid 1329: read_password_packet_password: failed
to read password packet p
2010-09-20 19:43:19 ERROR: pid 1329: do_md5: read_password_packet failed
2010-09-20 19:43:19 DEBUG: pid 1329: do_md5failed in slot 0
2010-09-20 19:43:21 DEBUG: pid 1350: I am 1350 accept fd 6
2010-09-20 19:43:21 LOG:   pid 1350: connection received: host=127.0.0.1
port=39066
2010-09-20 19:43:21 DEBUG: pid 1350: Protocol Major: 3 Minor: 0 database:
template1 user: test
2010-09-20 19:43:21 DEBUG: pid 1350: new_connection: connecting 0 backend
2010-09-20 19:43:21 DEBUG: pid 1350: pool_ssl: SSL requested but SSL support
is not available
2010-09-20 19:43:21 DEBUG: pid 1350: pool_read_message_length: slot: 0
length: 12
2010-09-20 19:43:21 DEBUG: pid 1350: pool_do_auth: auth kind:5
2010-09-20 19:43:21 DEBUG: pid 1350: trying md5 authentication
2010-09-20 19:43:21 DEBUG: pid 1350: DB node id: 0 salt: 474a91ef
2010-09-20 19:43:21 DEBUG: pid 1350: send_password_packet: backend does not
return R
2010-09-20 19:43:21 DEBUG: pid 1350: do_md5failed in slot 0

Re: [GENERAL] pgpool-II 3.0 + postgres 9rc1 + md5 authentication not working

[Tatsuo Ishii]

 I tried the patch file and I still cannot connect.  The only other
 difference is that I've already upgraded our images to Postgres 9.0.0 from
 rc1.
 
 Here is the pgpool debug log:
 
 2010-09-20 19:43:19 DEBUG: pid 1329: I am 1329 accept fd 6
 2010-09-20 19:43:19 LOG:   pid 1329: connection received: host=127.0.0.1
 port=39064
 2010-09-20 19:43:19 DEBUG: pid 1329: Protocol Major: 3 Minor: 0 database:
 template1 user: test
 2010-09-20 19:43:19 DEBUG: pid 1329: new_connection: connecting 0 backend
 2010-09-20 19:43:19 DEBUG: pid 1329: pool_ssl: SSL requested but SSL support
 is not available
 2010-09-20 19:43:19 DEBUG: pid 1329: pool_read_message_length: slot: 0
 length: 12
 2010-09-20 19:43:19 DEBUG: pid 1329: pool_do_auth: auth kind:5
 2010-09-20 19:43:19 DEBUG: pid 1329: trying md5 authentication
 2010-09-20 19:43:19 DEBUG: pid 1329: DB node id: 0 salt: 89d02c38
 2010-09-20 19:43:19 DEBUG: pid 1329: read_password_packet_password: failed
 to read password packet p
 2010-09-20 19:43:19 ERROR: pid 1329: do_md5: read_password_packet failed
 2010-09-20 19:43:19 DEBUG: pid 1329: do_md5failed in slot 0
 2010-09-20 19:43:21 DEBUG: pid 1350: I am 1350 accept fd 6
 2010-09-20 19:43:21 LOG:   pid 1350: connection received: host=127.0.0.1
 port=39066
 2010-09-20 19:43:21 DEBUG: pid 1350: Protocol Major: 3 Minor: 0 database:
 template1 user: test
 2010-09-20 19:43:21 DEBUG: pid 1350: new_connection: connecting 0 backend
 2010-09-20 19:43:21 DEBUG: pid 1350: pool_ssl: SSL requested but SSL support
 is not available
 2010-09-20 19:43:21 DEBUG: pid 1350: pool_read_message_length: slot: 0
 length: 12
 2010-09-20 19:43:21 DEBUG: pid 1350: pool_do_auth: auth kind:5
 2010-09-20 19:43:21 DEBUG: pid 1350: trying md5 authentication
 2010-09-20 19:43:21 DEBUG: pid 1350: DB node id: 0 salt: 474a91ef
 2010-09-20 19:43:21 DEBUG: pid 1350: send_password_packet: backend does not
 return R
 2010-09-20 19:43:21 DEBUG: pid 1350: do_md5failed in slot 0

I have used PostgreSQL 9.0 + pgpool-II 3.0 and they work fine with md5
auth.  Your log seems to indicate that the password in pool_passwd and
the one in pg_shadow are not identical. Can you verify that?
The query result:

select passwd from pg_shadow where usename = 'test';
 
must be identical the password in pool_passwd for user test.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pgpool-II 3.0 + postgres 9rc1 + md5 authentication not working

[Tatsuo Ishii]

Sorry for delay. I had a trip outside Japan.

 Thanks, I should have thought to try that location. :)
 
 I put the pool_passwd file in the expected location (custom build,
 /opt/postgres/etc/pool_passwd and /etc/pool_passwd) and reran my test.  I'm
 still having the same connection problem.

I found nasty bug with pgpool. Please try attached patches.

 I think it would be good to add an additional command line parameter to
 pgpool to allow overriding the location of that file (the same way the
 location of pcp.conf, pgpool.conf, and pgpool_hba.conf can be overriden).

Thanks for the suggestion. I'm thinking about to include your idea in
the next major pgpool version up.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp
Index: pool_auth.c
===
RCS file: /cvsroot/pgpool/pgpool-II/pool_auth.c,v
retrieving revision 1.25
diff -c -r1.25 pool_auth.c
*** pool_auth.c 17 Aug 2010 02:22:17 -  1.25
--- pool_auth.c 20 Sep 2010 04:27:36 -
***
*** 5,11 
   * pgpool: a language independent connection pool server for PostgreSQL
   * written by Tatsuo Ishii
   *
!  * Copyright (c) 2003-2009PgPool Global Development Group
   *
   * Permission to use, copy, modify, and distribute this software and
   * its documentation for any purpose and without fee is hereby
--- 5,11 
   * pgpool: a language independent connection pool server for PostgreSQL
   * written by Tatsuo Ishii
   *
!  * Copyright (c) 2003-2010PgPool Global Development Group
   *
   * Permission to use, copy, modify, and distribute this software and
   * its documentation for any purpose and without fee is hereby
***
*** 131,136 
--- 131,138 
  
authkind = ntohl(authkind);
  
+   pool_debug(pool_do_auth: auth kind:%d, authkind);
+ 
/* trust? */
if (authkind == 0)
{
***
*** 833,838 
--- 835,848 
  
if (!RAW_MODE  NUM_BACKENDS  1)
{
+   /* Read password entry from pool_passwd */
+   pool_passwd = pool_get_passwd(frontend-username);
+   if (!pool_passwd)
+   {
+   pool_debug(do_md5: %s does not exist in pool_passwd, 
frontend-username);
+   return -1;
+   }
+ 
/* master? */
if (IS_MASTER_NODE_ID(backend-db_node_id))
{
***
*** 852,864 
}
  
/* Check the password using my salt + pool_passwd */
-   pool_passwd = pool_get_passwd(frontend-username);
-   if (!pool_passwd)
-   {
-   pool_debug(do_md5: %s does not exist in 
pool_passwd, frontend-username);
-   return -1;
-   }
- 
pg_md5_encrypt(pool_passwd+strlen(md5), salt, 
sizeof(salt), encbuf);
if (strcmp(password, encbuf))
{
--- 862,867 

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pgpool-II 3.0 + postgres 9rc1 + md5 authentication not working

[Bryan Murphy]

On Tue, Sep 14, 2010 at 6:55 PM, Tatsuo Ishii is...@sraoss.co.jp wrote:

 Sorry for not enough description about pool_passwd. It's located under
 the same directory as pgpool.conf.  So the default is
 /usr/local/etc/pool_passwd.

 You need to create /usr/local/etc/pool_passwd if the uid to run pgpool
 server does not have the right to create a new file under
 /usr/local/etc, you need to create an empty /usr/local/etc/pool_passwd
 owned by the same uid as pgpool. After that you should be able to
 register your password as decribed in the doc.

 Authentication / Access Controls
 --
 Tatsuo Ishii
 SRA OSS, Inc. Japan
 English: http://www.sraoss.co.jp/index_en.php
 Japanese: http://www.sraoss.co.jp


Thanks, I should have thought to try that location. :)

I put the pool_passwd file in the expected location (custom build,
/opt/postgres/etc/pool_passwd and /etc/pool_passwd) and reran my test.  I'm
still having the same connection problem.

I think it would be good to add an additional command line parameter to
pgpool to allow overriding the location of that file (the same way the
location of pcp.conf, pgpool.conf, and pgpool_hba.conf can be overriden).


direct connection:
psql -h 10.192.165.125 -p 5432 template1 test
Password for user test:
psql (9.0rc1)
Type help for help.

template1=#



proxied connection:
psql -h 127.0.0.1 -p  template1 test
Password for user test:
psql: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.



pgpool log:
2010-09-15 15:48:36 LOG:   pid 2324: connection received: host=127.0.0.1
port=46999
2010-09-15 15:48:36 DEBUG: pid 2324: Protocol Major: 3 Minor: 0 database:
template1 user: test
2010-09-15 15:48:36 DEBUG: pid 2324: new_connection: connecting 0 backend
2010-09-15 15:48:36 DEBUG: pid 2324: pool_ssl: SSL requested but SSL support
is not available
2010-09-15 15:48:36 DEBUG: pid 2324: pool_read_message_length: slot: 0
length: 12
2010-09-15 15:48:36 DEBUG: pid 2324: trying md5 authentication
2010-09-15 15:48:36 DEBUG: pid 2324: DB node id: 0 salt: 458c816a
*2010-09-15 15:48:36 DEBUG: pid 2324: read_password_packet_password: failed
to read password packet p*
2010-09-15 15:48:36 ERROR: pid 2324: do_md5: read_password_packet failed
2010-09-15 15:48:36 DEBUG: pid 2324: do_md5failed in slot 0
2010-09-15 15:48:38 DEBUG: pid 2323: I am 2323 accept fd 6
2010-09-15 15:48:38 LOG:   pid 2323: connection received: host=127.0.0.1
port=47001
2010-09-15 15:48:38 DEBUG: pid 2323: Protocol Major: 3 Minor: 0 database:
template1 user: test
2010-09-15 15:48:38 DEBUG: pid 2323: new_connection: connecting 0 backend
2010-09-15 15:48:38 DEBUG: pid 2323: pool_ssl: SSL requested but SSL support
is not available
2010-09-15 15:48:38 DEBUG: pid 2323: pool_read_message_length: slot: 0
length: 12
2010-09-15 15:48:38 DEBUG: pid 2323: trying md5 authentication
2010-09-15 15:48:38 DEBUG: pid 2323: DB node id: 0 salt: 6d1da26
2010-09-15 15:48:38 DEBUG: pid 2260: reap_handler called
2010-09-15 15:48:38 DEBUG: pid 2260: reap_handler: call wait3
2010-09-15 15:48:38 ERROR: pid 2260: Child process 2323 was terminated by
segmentation fault
2010-09-15 15:48:38 DEBUG: pid 2260: child 2323 exits with status 11 by
signal 11
2010-09-15 15:48:38 DEBUG: pid 2260: fork a new child pid 2328
2010-09-15 15:48:38 DEBUG: pid 2260: reap_handler: normally exited
2010-09-15 15:48:38 DEBUG: pid 2328: I am 2328



postgres log:
LOG:  invalid password packet size
LOG:  unexpected EOF on client connection



psql --version
psql (PostgreSQL) 9.0rc1
contains support for command-line editing



pgpool --version
pgpool-II version 3.0 (umiyameboshi)

[GENERAL] pgpool-II 3.0 + postgres 9rc1 + md5 authentication not working

[Bryan Murphy]

I can't get md5 authentication working with postgres 9rc1 and pgpool-II 3.0.
 I see references to pool_passwd in the pgpool documentation, but I see
nothing indicating *where* this file should exist and how pgpool finds it.
 I've set my accounts up in pcp.conf, however, I do not believe this is what
is expected.

When I try to access the database with md5 turned on, I see the following
error in my pgpool debug log: read_password_packet_password: failed to read
password packet p

I can get everything working with trust.

postgres log:
*LOG:  unexpected EOF on client connection*
LOG:  disconnection: session time: 0:00:00.007 user=test database=test
host=10.201.121.204 port=42812

psql output:
psql -h 127.0.0.1 -p  test test
Password for user test:
*psql: server closed the connection unexpectedly
   This probably means the server terminated abnormally
   before or while processing the request.
*
pgpool debug log:
2010-09-14 21:38:51 DEBUG: pid 6633: I am 6633 accept fd 6
2010-09-14 21:38:51 LOG:   pid 6633: connection received: host=127.0.0.1
port=48336
2010-09-14 21:38:51 DEBUG: pid 6633: Protocol Major: 3 Minor: 0 database:
test user: test
2010-09-14 21:38:51 DEBUG: pid 6633: new_connection: connecting 0 backend
2010-09-14 21:38:51 DEBUG: pid 6633: pool_ssl: SSL requested but SSL support
is not available
2010-09-14 21:38:51 DEBUG: pid 6633: pool_read_message_length: slot: 0
length: 12
2010-09-14 21:38:51 DEBUG: pid 6633: trying md5 authentication
2010-09-14 21:38:51 DEBUG: pid 6633: DB node id: 0 salt: d3da5234
*2010-09-14 21:38:51 DEBUG: pid 6633: read_password_packet_password: failed
to read password packet p
2010-09-14 21:38:51 ERROR: pid 6633: do_md5: read_password_packet failed
*2010-09-14 21:38:51 DEBUG: pid 6633: do_md5failed in slot 0
2010-09-14 21:38:53 DEBUG: pid 6633: I am 6633 accept fd 6
2010-09-14 21:38:53 LOG:   pid 6633: connection received: host=127.0.0.1
port=48338
2010-09-14 21:38:53 DEBUG: pid 6633: Protocol Major: 3 Minor: 0 database:
test user: test
2010-09-14 21:38:53 DEBUG: pid 6633: new_connection: connecting 0 backend
2010-09-14 21:38:53 DEBUG: pid 6633: pool_ssl: SSL requested but SSL support
is not available
2010-09-14 21:38:53 DEBUG: pid 6633: pool_read_message_length: slot: 0
length: 12
2010-09-14 21:38:53 DEBUG: pid 6633: trying md5 authentication
2010-09-14 21:38:53 DEBUG: pid 6633: DB node id: 0 salt: 855592aa
2010-09-14 21:38:53 DEBUG: pid 6603: reap_handler called
2010-09-14 21:38:53 DEBUG: pid 6603: reap_handler: call wait3
*2010-09-14 21:38:53 ERROR: pid 6603: Child process 6633 was terminated by
segmentation fault
2010-09-14 21:38:53 DEBUG: pid 6603: child 6633 exits with status 11 by
signal 11
*2010-09-14 21:38:53 DEBUG: pid 6603: fork a new child pid 6671
2010-09-14 21:38:53 DEBUG: pid 6603: reap_handler: normally exited

pg_hba.conf:
local   all all trust
hostall all 127.0.0.1/32trust
hostall all ::1/128 trust
hostall all 0.0.0.0/0   md5

pool_hba.conf:
local   all all   trust
host   all all 0.0.0.0/0
md5

pgpool.conf:
listen_addresses = '*'
port = 
pcp_port = 9898
socket_dir = '/srv/pgpool/run'
pcp_socket_dir = '/srv/pgpool/run'
backend_socket_dir = '/srv/pgpool/run'
pcp_timeout = 10
num_init_children = 64
max_pool = 4
child_life_time = 300
connection_life_time = 0
child_max_connections = 0
client_idle_limit = 0
authentication_timeout = 60
logdir = '/srv/pgpool/log'
pid_file_name = '/srv/pgpool/run/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 = true
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 = '10.215.74.98'
backend_port0 = 5432
backend_weight0 = 1
*enable_pool_hba = true
*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 = 100

Re: [GENERAL] pgpool-II 3.0 + postgres 9rc1 + md5 authentication not working

[Tatsuo Ishii]

 I can't get md5 authentication working with postgres 9rc1 and pgpool-II 3.0.
  I see references to pool_passwd in the pgpool documentation, but I see
 nothing indicating *where* this file should exist and how pgpool finds it.
  I've set my accounts up in pcp.conf, however, I do not believe this is what
 is expected.

Sorry for not enough description about pool_passwd. It's located under
the same directory as pgpool.conf.  So the default is
/usr/local/etc/pool_passwd.

You need to create /usr/local/etc/pool_passwd if the uid to run pgpool
server does not have the right to create a new file under
/usr/local/etc, you need to create an empty /usr/local/etc/pool_passwd
owned by the same uid as pgpool. After that you should be able to
register your password as decribed in the doc.

Authentication / Access Controls
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

 When I try to access the database with md5 turned on, I see the following
 error in my pgpool debug log: read_password_packet_password: failed to read
 password packet p
 
 I can get everything working with trust.
 
 postgres log:
 *LOG:  unexpected EOF on client connection*
 LOG:  disconnection: session time: 0:00:00.007 user=test database=test
 host=10.201.121.204 port=42812
 
 psql output:
 psql -h 127.0.0.1 -p  test test
 Password for user test:
 *psql: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
 *
 pgpool debug log:
 2010-09-14 21:38:51 DEBUG: pid 6633: I am 6633 accept fd 6
 2010-09-14 21:38:51 LOG:   pid 6633: connection received: host=127.0.0.1
 port=48336
 2010-09-14 21:38:51 DEBUG: pid 6633: Protocol Major: 3 Minor: 0 database:
 test user: test
 2010-09-14 21:38:51 DEBUG: pid 6633: new_connection: connecting 0 backend
 2010-09-14 21:38:51 DEBUG: pid 6633: pool_ssl: SSL requested but SSL support
 is not available
 2010-09-14 21:38:51 DEBUG: pid 6633: pool_read_message_length: slot: 0
 length: 12
 2010-09-14 21:38:51 DEBUG: pid 6633: trying md5 authentication
 2010-09-14 21:38:51 DEBUG: pid 6633: DB node id: 0 salt: d3da5234
 *2010-09-14 21:38:51 DEBUG: pid 6633: read_password_packet_password: failed
 to read password packet p
 2010-09-14 21:38:51 ERROR: pid 6633: do_md5: read_password_packet failed
 *2010-09-14 21:38:51 DEBUG: pid 6633: do_md5failed in slot 0
 2010-09-14 21:38:53 DEBUG: pid 6633: I am 6633 accept fd 6
 2010-09-14 21:38:53 LOG:   pid 6633: connection received: host=127.0.0.1
 port=48338
 2010-09-14 21:38:53 DEBUG: pid 6633: Protocol Major: 3 Minor: 0 database:
 test user: test
 2010-09-14 21:38:53 DEBUG: pid 6633: new_connection: connecting 0 backend
 2010-09-14 21:38:53 DEBUG: pid 6633: pool_ssl: SSL requested but SSL support
 is not available
 2010-09-14 21:38:53 DEBUG: pid 6633: pool_read_message_length: slot: 0
 length: 12
 2010-09-14 21:38:53 DEBUG: pid 6633: trying md5 authentication
 2010-09-14 21:38:53 DEBUG: pid 6633: DB node id: 0 salt: 855592aa
 2010-09-14 21:38:53 DEBUG: pid 6603: reap_handler called
 2010-09-14 21:38:53 DEBUG: pid 6603: reap_handler: call wait3
 *2010-09-14 21:38:53 ERROR: pid 6603: Child process 6633 was terminated by
 segmentation fault
 2010-09-14 21:38:53 DEBUG: pid 6603: child 6633 exits with status 11 by
 signal 11
 *2010-09-14 21:38:53 DEBUG: pid 6603: fork a new child pid 6671
 2010-09-14 21:38:53 DEBUG: pid 6603: reap_handler: normally exited
 
 pg_hba.conf:
 local   all all trust
 hostall all 127.0.0.1/32trust
 hostall all ::1/128 trust
 hostall all 0.0.0.0/0   md5
 
 pool_hba.conf:
 local   all all   trust
 host   all all 0.0.0.0/0
 md5
 
 pgpool.conf:
 listen_addresses = '*'
 port = 
 pcp_port = 9898
 socket_dir = '/srv/pgpool/run'
 pcp_socket_dir = '/srv/pgpool/run'
 backend_socket_dir = '/srv/pgpool/run'
 pcp_timeout = 10
 num_init_children = 64
 max_pool = 4
 child_life_time = 300
 connection_life_time = 0
 child_max_connections = 0
 client_idle_limit = 0
 authentication_timeout = 60
 logdir = '/srv/pgpool/log'
 pid_file_name = '/srv/pgpool/run/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
 

[GENERAL] pgpool-II 3.0 beta1/pgpoolAdmin 3.0 beta1 released

[Tatsuo Ishii]

Pgpool Global Development Group is pleased to announce the
availability of pgpool-II 3.0 beta1. In the mean time new version of
pgpoolAdmin, the GUI tool for pgpool-II, pgpoolAdmin 3.0 beta1 is
released.

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

http://pgfoundry.org/frs/download.php/2778/pgpool-II-3.0-beta1.tar.gz
http://pgfoundry.org/frs/download.php/2777/pgpoolAdmin-3.0-beta1.tar.gz

Pgpool-II 3.0 is an important version in its history. The internal
structure has been dramatically enhanced to make it more robust and
easier to maintenance. Also this version adopts to PostgreSQL 9.0's
built-in replication. Many enhancements are added also. So users of
PostgreSQL 8.4 or prior version should be able to enjoy pgpool-II
3.0's enhancements as well.

Pgpool-II 3.0 beta1 has been tested with PostgreSQL 9.0 beta4 and
prior version of PostgreSQL including 7.3.

Users of prior version of pgpool-II should be able to upgrade to
pgpool-II 3.0 without much pain. Especially pgpool-II 2.3.x users
should be able to migrate to 3.0 without changing pgpool.conf(it is
recommneded to use new pgpool.conf, though). If you find any
imcomaptibilty, please let us know.

However be warned that output format of some of pcp command are
changed. Please look into the documents. Also you should use
pgpoolAdmin 3.0 with pgpool-II 3.0. Prior version of pgpoolAdmin is
not compatible with pgpool-II 3.0.

Attached are release note of pgpool-II 3.0 and pgpoolAdmin 3.0.

Please help us in testing and making pgpool-II 3.0 better!

---
  This is the first version of pgpool-II 3.0 series.
  That is, a major version up from 2.2 or 2.3 series.

  The biggest news is, this version adapts to PostgreSQL 9.0's new
  feature: Streaming Replication/Hot Standby. Streaming
  replication can be used as a sub mode of master slave
  mode. Master slave mode itself heavily enhanced:

  - SELECTs in explicit transactions can be load balanced

  - In extended protocol, PARSE/BIND/DESCRIBE messages are sent to
the node which execute EXECUTE message, not all node. This
will reduce lock contentions.

  - Auto start of transaction happens only when it needed.

  - Temporary tables can be used safely.

  - SELECT which calls functions possibly write to database
executes on master(primary)
  
  Also many new features are added and major refactoring has been
  made to the internal structure of pgpoo-II. For example, in
  replication mode, SELECTs calling functions possibly write to
  database will not allow to load balance.

  * New features

  - Online recovery can be used with master/slave/streaming
replication mode(Tatsuo)

  - New directive delay_threshold is added to monitor
replication delay in master/slave/streaming replication
mode. If replication delay is too much, SELECTs are not load
balanced(Tatsuo)

  - show pool_status shows replication delay in
master/slave/streaming replication mode(Tatsuo)

  - New directive log_standby_delay is added to control logging of
replication delay in master/slave/streaming replication
mode(Tatsuo)

  - Add support for more SHOW commands: pool_nodes,
pool_processes, pool_nodes, and pool_version(Guillaume Lelarge)

  - Backend process id and whether frontend connects to this
connection pool or not are added to pcp_proc_info's
output(Tatsuo)

  - Gracefuly detach option is added to pcp_detatch_node. With
this option, pcp_detatch_node waits until all frontends
disconnected(Tatsuo)

  - New directive white_function_list and black_function_list
are added to register functions those do not or do write to
database(Tatsuo)

  - In master/slave mode, SELECTs to system catalogs executes only
on master/primary(Tatsuo)

  - In master/slave mode, SELECTs to temporary table executes only
on master/primary(Tatsuo)

  - In master/slave mode, write queries outside of explicit
transactions no longer trigger to start internal
transaction(Tatsuo)

  - In master/slave mode, SELECTs inside explicit transactions are
load balanced(Tatsuo, Kitagawa)

  - In master/slave mode, commands are no longer sent to all DB
nodes. This will prevent unnecessary locking(Tatsuo, Kitagawa)

  - New command option adds to ignore the status file when
starting up(Tatsuo)

  - Supports PostgreSQL 9.0's new VACUUM syntax(Tatsuo)

  - New directive failover_if_affected_tuples_mismatch controls
the behavior when number of result rows of
INSERT/UPDATE/DELETE are 

[GENERAL] pgpool-II (max_pool and num_init_children)

[Geoffrey]

I'm trying to get a handle on sane values for these two parameters.  I 
assume that they should somehow correlate  to my existing 
max_connections in my postgresql.conf file.  Anyone using pgpool-II care 
to comment?


I'm sure it's a balancing act between how many connections you want 
verses how many cached connections you have, but not sure how to 
properly assess this issue.


(I've tried posting to the pgpool list, but it's apparently unavailable 
at this time)


--
Until later, Geoffrey

I predict future happiness for America if they can prevent
the government from wasting the labors of the people under
the pretense of taking care of them.
- Thomas Jefferson

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pgpool-II (max_pool and num_init_children)

[Tatsuo Ishii]

 I'm trying to get a handle on sane values for these two parameters.  I 
 assume that they should somehow correlate  to my existing 
 max_connections in my postgresql.conf file.  Anyone using pgpool-II care 
 to comment?
 
 I'm sure it's a balancing act between how many connections you want 
 verses how many cached connections you have, but not sure how to 
 properly assess this issue.
 
 (I've tried posting to the pgpool list, but it's apparently unavailable 
 at this time)

Here is an recommended rule:

max_pool*num_init_children = (max_connections - superuser_reserved_connections)

Because num_init_children defines the max concurrent connections
pgpool-II can accept, you might want to keep num_init_children as
large as possible. In this case you could lower max_pool to as low as
1, which may cause performance degration because of low effect of
connection cache if you would have more than 1 user/database
combinations. However lower max_pool will not any error or any
problems except performance.

If you want to use query cancelation frequetntly, the formula above
will become:

max_pool*num_init_children*2 = (max_connections - 
superuser_reserved_connections)

because you need extra connection to use query cancelation.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pgpool-II (max_pool and num_init_children)

[Geoffrey]

Tatsuo Ishii wrote:
I'm trying to get a handle on sane values for these two parameters.  I 
assume that they should somehow correlate  to my existing 
max_connections in my postgresql.conf file.  Anyone using pgpool-II care 
to comment?


I'm sure it's a balancing act between how many connections you want 
verses how many cached connections you have, but not sure how to 
properly assess this issue.


(I've tried posting to the pgpool list, but it's apparently unavailable 
at this time)


Here is an recommended rule:

max_pool*num_init_children = (max_connections - superuser_reserved_connections)

Because num_init_children defines the max concurrent connections
pgpool-II can accept, you might want to keep num_init_children as
large as possible. In this case you could lower max_pool to as low as
1, which may cause performance degration because of low effect of
connection cache if you would have more than 1 user/database
combinations. However lower max_pool will not any error or any
problems except performance.


We have a complicated set up.  We have 13 databases with 12 postmasters. 
  So that means 2 databases share a postmaster, the rest have their 
own.  Consequently, separate pools for each postmaster.  We will 
frequently have 20-30 or more users connecting to any one database, 
although some databases are much lower.  This is further complicated by 
a web application that also connects to the databases.


To complicate matters even further, we will likely have multiple pools 
for each database, in order to give priority to local users over the web 
access.  In other words, a large pool of connections for local users, 
and a smaller pool for web access.


That being said, I'm assuming:

(max_pool1*num_init_children) + (max_pool2*num_init_children) =
(max_connections - superuser_reserved_connections)


If you want to use query cancelation frequetntly, the formula above
will become:

max_pool*num_init_children*2 = (max_connections - 
superuser_reserved_connections)

because you need extra connection to use query cancelation.


I don't believe we'll have frequent query cancellations.


--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp




--
Until later, Geoffrey

I predict future happiness for America if they can prevent
the government from wasting the labors of the people under
the pretense of taking care of them.
- Thomas Jefferson

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pgpool-II (max_pool and num_init_children)

[Guillaume Lelarge]

Le 02/07/2010 15:46, Geoffrey a écrit :
 Tatsuo Ishii wrote:
 I'm trying to get a handle on sane values for these two parameters. 
 I assume that they should somehow correlate  to my existing
 max_connections in my postgresql.conf file.  Anyone using pgpool-II
 care to comment?

 I'm sure it's a balancing act between how many connections you want
 verses how many cached connections you have, but not sure how to
 properly assess this issue.

 (I've tried posting to the pgpool list, but it's apparently
 unavailable at this time)

 Here is an recommended rule:

 max_pool*num_init_children = (max_connections -
 superuser_reserved_connections)

 Because num_init_children defines the max concurrent connections
 pgpool-II can accept, you might want to keep num_init_children as
 large as possible. In this case you could lower max_pool to as low as
 1, which may cause performance degration because of low effect of
 connection cache if you would have more than 1 user/database
 combinations. However lower max_pool will not any error or any
 problems except performance.
 
 We have a complicated set up.  We have 13 databases with 12 postmasters.
   So that means 2 databases share a postmaster, the rest have their
 own.  Consequently, separate pools for each postmaster.  We will
 frequently have 20-30 or more users connecting to any one database,
 although some databases are much lower.  This is further complicated by
 a web application that also connects to the databases.
 

You won't be able to handle more than one postmaster with one pgPool-II
in pooling mode. You'll need at least one pgPool-II for each postmaster.
And so, do one configuration for each pgPool-II.


-- 
Guillaume
 http://www.postgresql.fr
 http://dalibo.com

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pgpool-II (max_pool and num_init_children)

[Geoffrey]

Guillaume Lelarge wrote:

Le 02/07/2010 15:46, Geoffrey a écrit :

Tatsuo Ishii wrote:
I'm trying to get a handle on sane values for these two parameters. 
I assume that they should somehow correlate  to my existing

max_connections in my postgresql.conf file.  Anyone using pgpool-II
care to comment?

I'm sure it's a balancing act between how many connections you want
verses how many cached connections you have, but not sure how to
properly assess this issue.

(I've tried posting to the pgpool list, but it's apparently
unavailable at this time)

Here is an recommended rule:

max_pool*num_init_children = (max_connections -
superuser_reserved_connections)

Because num_init_children defines the max concurrent connections
pgpool-II can accept, you might want to keep num_init_children as
large as possible. In this case you could lower max_pool to as low as
1, which may cause performance degration because of low effect of
connection cache if you would have more than 1 user/database
combinations. However lower max_pool will not any error or any
problems except performance.

We have a complicated set up.  We have 13 databases with 12 postmasters.
  So that means 2 databases share a postmaster, the rest have their
own.  Consequently, separate pools for each postmaster.  We will
frequently have 20-30 or more users connecting to any one database,
although some databases are much lower.  This is further complicated by
a web application that also connects to the databases.



You won't be able to handle more than one postmaster with one pgPool-II
in pooling mode. You'll need at least one pgPool-II for each postmaster.
And so, do one configuration for each pgPool-II.


Yes, that is the intent.  But, we are considering having multiple 
pgPool-II for one postmaster.


--
Until later, Geoffrey

I predict future happiness for America if they can prevent
the government from wasting the labors of the people under
the pretense of taking care of them.
- Thomas Jefferson

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pgpool

[Joshua Tolley]

On Tue, Jun 22, 2010 at 07:07:33AM -0400, Geoffrey wrote:
 A couple of questions regarding pgpool:

 Is there a problem with using multiple connection pools for the same  
 database?  Point being, we might want to give a higher number of  
 connections to one group of users then another.  I can see doing this by  
 having separate connection pools.  The higher priority users have a  
 larger connection pool.

That's entirely reasonable.

 Is there a problem with using connection pooling and traditional  
 connections to connect to the same database?

Nope.

--
Joshua Tolley / eggyknap
End Point Corporation
http://www.endpoint.com


signature.asc
Description: Digital signature

Re: [GENERAL] pgpool

[Geoffrey]

A couple of questions regarding pgpool:

Is there a problem with using multiple connection pools for the same 
database?  Point being, we might want to give a higher number of 
connections to one group of users then another.  I can see doing this by 
having separate connection pools.  The higher priority users have a 
larger connection pool.


Is there a problem with using connection pooling and traditional 
connections to connect to the same database?


--
Until later, Geoffrey

I predict future happiness for America if they can prevent
the government from wasting the labors of the people under
the pretense of taking care of them.
- Thomas Jefferson

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] pgpool

[Geoffrey]

So, I've got it installed, and tweaked the configuration, but I simply 
can not figure out how to connect to my databases via pgpool.  Is this 
simply transparent?  I don't see how.


So I have a postmaster running on port 5434, how do I connect to that 
database via pgpool?  I simply can not find this piece of info in the docs?


--
Until later, Geoffrey

I predict future happiness for America if they can prevent
the government from wasting the labors of the people under
the pretense of taking care of them.
- Thomas Jefferson

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pgpool

[Gerd Koenig]

Hi Geoffrey,

you do not need to connect to your database directly, just connect to pgpool 
itself.
e.g.: your database runs on port 5434, pgpool runs on port 5432
=
* pgpool has to be configured in that way that it connects to the database on 
port 5434 
* you/your app's should connect to the server where pgpool is running on port 
5432 (the connection to the database is established by pgpool)

hth...::GERD::...


On Monday, June 21, 2010 02:10:22 pm Geoffrey wrote:
 So, I've got it installed, and tweaked the configuration, but I simply
 can not figure out how to connect to my databases via pgpool.  Is this
 simply transparent?  I don't see how.
 
 So I have a postmaster running on port 5434, how do I connect to that
 database via pgpool?  I simply can not find this piece of info in the docs?

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pgpool

[Geoffrey]

Gerd Koenig wrote:

Hi Geoffrey,

you do not need to connect to your database directly, just connect to pgpool 
itself.

e.g.: your database runs on port 5434, pgpool runs on port 5432
=
* pgpool has to be configured in that way that it connects to the database on 
port 5434 
* you/your app's should connect to the server where pgpool is running on port 
5432 (the connection to the database is established by pgpool)


hth...::GERD::...


So I've got 13 different databases on 13 different postmasters, now does 
pgpool know which databases I'm trying to connect to?





On Monday, June 21, 2010 02:10:22 pm Geoffrey wrote:

So, I've got it installed, and tweaked the configuration, but I simply
can not figure out how to connect to my databases via pgpool.  Is this
simply transparent?  I don't see how.

So I have a postmaster running on port 5434, how do I connect to that
database via pgpool?  I simply can not find this piece of info in the docs?





--
Until later, Geoffrey

I predict future happiness for America if they can prevent
the government from wasting the labors of the people under
the pretense of taking care of them.
- Thomas Jefferson

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pgpool

[Geoffrey]

Gerd Koenig wrote:

Hi Geoffrey,

you do not need to connect to your database directly, just connect to pgpool 
itself.

e.g.: your database runs on port 5434, pgpool runs on port 5432
=
* pgpool has to be configured in that way that it connects to the database on 
port 5434 


What parameter in the config file is this?  I can't seem to locate it?

* you/your app's should connect to the server where pgpool is running on port 
5432 (the connection to the database is established by pgpool)


hth...::GERD::...


On Monday, June 21, 2010 02:10:22 pm Geoffrey wrote:

So, I've got it installed, and tweaked the configuration, but I simply
can not figure out how to connect to my databases via pgpool.  Is this
simply transparent?  I don't see how.

So I have a postmaster running on port 5434, how do I connect to that
database via pgpool?  I simply can not find this piece of info in the docs?





--
Until later, Geoffrey

I predict future happiness for America if they can prevent
the government from wasting the labors of the people under
the pretense of taking care of them.
- Thomas Jefferson

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pgpool

[Geoffrey]

Geoffrey wrote:

Gerd Koenig wrote:

Hi Geoffrey,

you do not need to connect to your database directly, just connect to 
pgpool itself.

e.g.: your database runs on port 5434, pgpool runs on port 5432
=
* pgpool has to be configured in that way that it connects to the 
database on port 5434 


What parameter in the config file is this?  I can't seem to locate it?


I mis-read the docs, figured this out, thanks.



* you/your app's should connect to the server where pgpool is running 
on port 5432 (the connection to the database is established by pgpool)


hth...::GERD::...


On Monday, June 21, 2010 02:10:22 pm Geoffrey wrote:

So, I've got it installed, and tweaked the configuration, but I simply
can not figure out how to connect to my databases via pgpool.  Is this
simply transparent?  I don't see how.

So I have a postmaster running on port 5434, how do I connect to that
database via pgpool?  I simply can not find this piece of info in the 
docs?








--
Until later, Geoffrey

I predict future happiness for America if they can prevent
the government from wasting the labors of the people under
the pretense of taking care of them.
- Thomas Jefferson

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pgpool

[Geoffrey]

So I've got the following:

port = 
.
.
backend_hostname0 = 'localhost'
backend_port0 = 5434
backend_weight0 = 1
backend_data_directory0 = '/data/pgsql/master'
backend_hostname1 = 'localhost'
backend_port1 = 5435
backend_weight1 = 1
backend_data_directory1 = '/data/pgsql/mwv'

In my pgpool.conf file and I've restarted the pgpool processes.  I can 
connect to the first entry as follows:


psql -p  master

But if I attempt to connect to the second postmaster as follows:

psql -p  mwv

I can not connect.  What am I missing?

--
Until later, Geoffrey

I predict future happiness for America if they can prevent
the government from wasting the labors of the people under
the pretense of taking care of them.
- Thomas Jefferson

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pgpool

[Guillaume Lelarge]

Le 21/06/2010 15:52, Geoffrey a écrit :
 So I've got the following:
 
 port = 
 .
 .
 backend_hostname0 = 'localhost'
 backend_port0 = 5434
 backend_weight0 = 1
 backend_data_directory0 = '/data/pgsql/master'
 backend_hostname1 = 'localhost'
 backend_port1 = 5435
 backend_weight1 = 1
 backend_data_directory1 = '/data/pgsql/mwv'
 
 In my pgpool.conf file and I've restarted the pgpool processes.  I can
 connect to the first entry as follows:
 
 psql -p  master
 
 But if I attempt to connect to the second postmaster as follows:
 
 psql -p  mwv
 
 I can not connect.  What am I missing?
 

You can't. backend_hostnamen, with n greater then 0, is only used in
replication, and load balancing mode.

If you want to be able to connect on various databases from various
servers, pgBouncer is what you're looking for.


-- 
Guillaume
 http://www.postgresql.fr
 http://dalibo.com

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pgpool

[Geoffrey]

Guillaume Lelarge wrote:

Le 21/06/2010 15:52, Geoffrey a écrit :

So I've got the following:

port = 
.
.
backend_hostname0 = 'localhost'
backend_port0 = 5434
backend_weight0 = 1
backend_data_directory0 = '/data/pgsql/master'
backend_hostname1 = 'localhost'
backend_port1 = 5435
backend_weight1 = 1
backend_data_directory1 = '/data/pgsql/mwv'

In my pgpool.conf file and I've restarted the pgpool processes.  I can
connect to the first entry as follows:

psql -p  master

But if I attempt to connect to the second postmaster as follows:

psql -p  mwv

I can not connect.  What am I missing?



You can't. backend_hostnamen, with n greater then 0, is only used in
replication, and load balancing mode.

If you want to be able to connect on various databases from various
servers, pgBouncer is what you're looking for.


Ouch, okay, thanks for the clarification.  I simply did not pick up on 
that in the docs.



--
Until later, Geoffrey

I predict future happiness for America if they can prevent
the government from wasting the labors of the people under
the pretense of taking care of them.
- Thomas Jefferson

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pgpool

[Geoffrey]

Guillaume Lelarge wrote:


If you want to be able to connect on various databases from various
servers, pgBouncer is what you're looking for.


It does not appear that pgbouncer will 'limit exceeding connections' as 
does pgpool.  So if I have a pool of 20 connections and 20 connections 
are used, what happens to the 21st connection attempt?  Is it rejected 
or put into a queue to wait for the next available connection?


--
Until later, Geoffrey

I predict future happiness for America if they can prevent
the government from wasting the labors of the people under
the pretense of taking care of them.
- Thomas Jefferson

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pgpool

[John R Pierce]

On 06/21/10 5:37 AM, Geoffrey wrote:
So I've got 13 different databases on 13 different postmasters, now 
does pgpool know which databases I'm trying to connect to?


you would need 13 different connection pools.



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pgpool

[Geoffrey]

John R Pierce wrote:

On 06/21/10 5:37 AM, Geoffrey wrote:
So I've got 13 different databases on 13 different postmasters, now 
does pgpool know which databases I'm trying to connect to?


you would need 13 different connection pools.


Can this be done?


--
Until later, Geoffrey

I predict future happiness for America if they can prevent
the government from wasting the labors of the people under
the pretense of taking care of them.
- Thomas Jefferson

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pgpool

[Scott Marlowe]

On Mon, Jun 21, 2010 at 1:45 PM, Geoffrey li...@serioustechnology.com wrote:
 John R Pierce wrote:

 On 06/21/10 5:37 AM, Geoffrey wrote:

 So I've got 13 different databases on 13 different postmasters, now does
 pgpool know which databases I'm trying to connect to?

 you would need 13 different connection pools.

 Can this be done?

Yes

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pgpool

[Geoffrey]

Scott Marlowe wrote:

On Mon, Jun 21, 2010 at 1:45 PM, Geoffrey li...@serioustechnology.com wrote:

John R Pierce wrote:

On 06/21/10 5:37 AM, Geoffrey wrote:

So I've got 13 different databases on 13 different postmasters, now does
pgpool know which databases I'm trying to connect to?

you would need 13 different connection pools.

Can this be done?


Yes


Can you point me to any docs that explain how this is done?  As there is 
only one pgpool.conf file, I'm wondering how to go about that process.



--
Until later, Geoffrey

I predict future happiness for America if they can prevent
the government from wasting the labors of the people under
the pretense of taking care of them.
- Thomas Jefferson

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pgpool

[Scott Marlowe]

On Mon, Jun 21, 2010 at 2:42 PM, Geoffrey li...@serioustechnology.com wrote:
 Scott Marlowe wrote:

 On Mon, Jun 21, 2010 at 1:45 PM, Geoffrey li...@serioustechnology.com
 wrote:

 John R Pierce wrote:

 On 06/21/10 5:37 AM, Geoffrey wrote:

 So I've got 13 different databases on 13 different postmasters, now
 does
 pgpool know which databases I'm trying to connect to?

 you would need 13 different connection pools.

 Can this be done?

 Yes

 Can you point me to any docs that explain how this is done?  As there is
 only one pgpool.conf file, I'm wondering how to go about that process.

sudo apt-get install pgpool2
(password, yes, wait...)
pgpool --help
Usage:
  pgpool [ -c] [ -f CONFIG_FILE ] [ -F PCP_CONFIG_FILE ] [ -a HBA_CONFIG_FILE ]
 [ -n ] [ -d ]
  pgpool [ -f CONFIG_FILE ] [ -F PCP_CONFIG_FILE ] [ -a HBA_CONFIG_FILE ]
 [ -m SHUTDOWN-MODE ] stop
  pgpool [ -f CONFIG_FILE ] [ -F PCP_CONFIG_FILE ] [ -a HBA_CONFIG_FILE ] reload

Common options:
  -a HBA_CONFIG_FILE  Sets the path to the pool_hba.conf configuration file
  (default: /etc/pool_hba.conf)
  -f CONFIG_FILE  Sets the path to the pgpool.conf configuration file
  (default: /etc/pgpool.conf)
  -F PCP_CONFIG_FILE  Sets the path to the pcp.conf configuration file
  (default: /etc/pcp.conf)
  -h  Prints this help

Looks like there's a -f and -F option to use various files.
Admittedly, you might not be starting it up with /etc/init.d/pgpool2
each time now.  But the basics are there.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pgpool

[Geoffrey]

Scott Marlowe wrote:

On Mon, Jun 21, 2010 at 2:42 PM, Geoffrey li...@serioustechnology.com wrote:

Scott Marlowe wrote:

On Mon, Jun 21, 2010 at 1:45 PM, Geoffrey li...@serioustechnology.com
wrote:

John R Pierce wrote:

On 06/21/10 5:37 AM, Geoffrey wrote:

So I've got 13 different databases on 13 different postmasters, now
does
pgpool know which databases I'm trying to connect to?

you would need 13 different connection pools.

Can this be done?

Yes

Can you point me to any docs that explain how this is done?  As there is
only one pgpool.conf file, I'm wondering how to go about that process.


sudo apt-get install pgpool2
(password, yes, wait...)
pgpool --help
Usage:
  pgpool [ -c] [ -f CONFIG_FILE ] [ -F PCP_CONFIG_FILE ] [ -a HBA_CONFIG_FILE ]
 [ -n ] [ -d ]
  pgpool [ -f CONFIG_FILE ] [ -F PCP_CONFIG_FILE ] [ -a HBA_CONFIG_FILE ]
 [ -m SHUTDOWN-MODE ] stop
  pgpool [ -f CONFIG_FILE ] [ -F PCP_CONFIG_FILE ] [ -a HBA_CONFIG_FILE ] reload

Common options:
  -a HBA_CONFIG_FILE  Sets the path to the pool_hba.conf configuration file
  (default: /etc/pool_hba.conf)
  -f CONFIG_FILE  Sets the path to the pgpool.conf configuration file
  (default: /etc/pgpool.conf)
  -F PCP_CONFIG_FILE  Sets the path to the pcp.conf configuration file
  (default: /etc/pcp.conf)
  -h  Prints this help

Looks like there's a -f and -F option to use various files.
Admittedly, you might not be starting it up with /etc/init.d/pgpool2
each time now.  But the basics are there.


Thanks muchly, that should give me a good kick start.


--
Until later, Geoffrey

I predict future happiness for America if they can prevent
the government from wasting the labors of the people under
the pretense of taking care of them.
- Thomas Jefferson

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] PGPOOL II 2.3.3 encryption

[AI Rumman]

Is it possible to send encrypted text at all the nodes in replication mode
for PGPOOL II 2.3.3?

Any help please.

Re: [GENERAL] pgpool error, pid not found!

[Tatsuo Ishii]

I don't think pgpool can be compiled on Windows without heavy
modifications.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

 I tried compiling on windows. I didnt get success in that.
 
 I tried gcc 2 ways.
 1) gcc -o pool_status_query pool_status_query.c
 2)make pool_status_query
 
 none of them gave me proper answer. there are errors but I cant see all
 errors or understand them.
 Do you have any clue?
 On Sun, Feb 21, 2010 at 7:14 AM, Tatsuo Ishii is...@sraoss.co.jp wrote:
 
   Hi,
  
   I made some changes in Pgpool .Now I am using Dev c++ to compile the
  code.
   Is that the right tool to complie the pgpool code? or I should use Linux
   compiler.
 
  Do you mean that Windows C compiler? I have no experience with
  compiling pgpool on Windows. Please let me know if you suceed.
 
   can you advice me which linux compiler I can use?
 
  Plane gcc is fine.
  --
  Tatsuo Ishii
  SRA OSS, Inc. Japan
  English: http://www.sraoss.co.jp/index_en.php
  Japanese: http://www.sraoss.co.jp
 
   Thanks
  
   On Thu, Feb 18, 2010 at 7:26 PM, Tatsuo Ishii is...@sraoss.co.jp
  wrote:
  
The last version of pgpool(not pgpool-II) had been released almost 3
years ago. So I guess it has many bugs found during this 3 years.
However as long as the bug does not bite you, it's ok.
   
I just recommend to use pgpool-II in the real world use.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp
   
 one of the student at my school worked on this. I need to extend her
thesis.
 so thats why I am using this.
 Is there any problem if I use this version?
 Is this stable version?
 Finally I am able to work both pgpool and postgres working togather.

 
 From: Tatsuo Ishii [is...@sraoss.co.jp]
 Sent: Thursday, February 18, 2010 1:56 AM
 To: megha198...@gmail.com
 Cc: pgsql-general@postgresql.org
 Subject: Re: [GENERAL] pgpool error, pid not found!

 Wao. This is pgpool, right? It's not maintained anymore(it was almost
 3 years ago). Please use pgpool-II. The latest version is pgpool-II
 2.3.2.1.
 --
 Tatsuo Ishii
 SRA OSS, Inc. Japan
 English: http://www.sraoss.co.jp/index_en.php
 Japanese: http://www.sraoss.co.jp

  Sorry for late reply. I work full time. so I work on postgres in
  night.
  here is the command that I use.
 
  I started pgpool using this command.
  pgpool/bin/pgpool  -f /home/megha/pgpool/etc/pgpool.conf -a
  /home/megha/pgpool/etc/pool_hba.conf
 
  pgpool.conf is attached.
 
  I have been trying to configure postgres and pgpool for past 2
  months
and I
  am getting too many problems with this.
  I hope you help me out to solve my errors.
 
  Waiting for your reply.
  Thanks
 
  On Tue, Feb 16, 2010 at 10:20 PM, Tatsuo Ishii 
  is...@postgresql.org
wrote:
 
   How did you start pgpool exactly(command line)?
  
   Also, the number  dpends on pgpool.conf.
   Can you show me pgpool.conf?
   --
   Tatsuo Ishii
   SRA OSS, Inc. Japan
   English: http://www.sraoss.co.jp/index_en.php
   Japanese: http://www.sraoss.co.jp
  
From the README file , I found that  psql -p  -c 'show
pool_status'
template1 command gives the internal status of pgpool.
so when i start pgpool, I dont get any inernal status.
I get this error message
   
psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket /tmp/.s.PGSQL.?
   
   
   
On Tue, Feb 16, 2010 at 10:09 PM, Tatsuo Ishii 
is...@postgresql.org
   wrote:
   
 pgpool.pid file contains pgpool's parent process id.  That
message
 indicates that the file exists but the pgpool process id does
  not
 exist. Probably pgpool was stopped in unclean way. You may
  ignore
the
 message as long as pgpool starts ok.
 --
 Tatsuo Ishii
 SRA OSS, Inc. Japan
 English: http://www.sraoss.co.jp/index_en.php
 Japanese: http://www.sraoss.co.jp

  Hi
 
  I have installed pgpool in /home/megha/pgpool dir.
  I found pgpool.pid file in /tmp folder.
 
  when I try to start pgpool I get this error.
  *pid file found but it seems bogus. Trying to start pgpool
anyway..*
 
  Please help me!
 
 
  Thanks,
 
  --
  Megha

   
   
   
--
Megha
  
 
 
 
  --
  Megha
   
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org

Re: [GENERAL] pgpool error, pid not found!

[Tatsuo Ishii]

The last version of pgpool(not pgpool-II) had been released almost 3
years ago. So I guess it has many bugs found during this 3 years.
However as long as the bug does not bite you, it's ok.

I just recommend to use pgpool-II in the real world use.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

 one of the student at my school worked on this. I need to extend her thesis.
 so thats why I am using this. 
 Is there any problem if I use this version?
 Is this stable version? 
 Finally I am able to work both pgpool and postgres working togather.
 
 
 From: Tatsuo Ishii [is...@sraoss.co.jp]
 Sent: Thursday, February 18, 2010 1:56 AM
 To: megha198...@gmail.com
 Cc: pgsql-general@postgresql.org
 Subject: Re: [GENERAL] pgpool error, pid not found!
 
 Wao. This is pgpool, right? It's not maintained anymore(it was almost
 3 years ago). Please use pgpool-II. The latest version is pgpool-II
 2.3.2.1.
 --
 Tatsuo Ishii
 SRA OSS, Inc. Japan
 English: http://www.sraoss.co.jp/index_en.php
 Japanese: http://www.sraoss.co.jp
 
  Sorry for late reply. I work full time. so I work on postgres in night.
  here is the command that I use.
 
  I started pgpool using this command.
  pgpool/bin/pgpool  -f /home/megha/pgpool/etc/pgpool.conf -a
  /home/megha/pgpool/etc/pool_hba.conf
 
  pgpool.conf is attached.
 
  I have been trying to configure postgres and pgpool for past 2 months and I
  am getting too many problems with this.
  I hope you help me out to solve my errors.
 
  Waiting for your reply.
  Thanks
 
  On Tue, Feb 16, 2010 at 10:20 PM, Tatsuo Ishii is...@postgresql.org wrote:
 
   How did you start pgpool exactly(command line)?
  
   Also, the number  dpends on pgpool.conf.
   Can you show me pgpool.conf?
   --
   Tatsuo Ishii
   SRA OSS, Inc. Japan
   English: http://www.sraoss.co.jp/index_en.php
   Japanese: http://www.sraoss.co.jp
  
From the README file , I found that  psql -p  -c 'show 
pool_status'
template1 command gives the internal status of pgpool.
so when i start pgpool, I dont get any inernal status.
I get this error message
   
psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket /tmp/.s.PGSQL.?
   
   
   
On Tue, Feb 16, 2010 at 10:09 PM, Tatsuo Ishii is...@postgresql.org
   wrote:
   
 pgpool.pid file contains pgpool's parent process id.  That message
 indicates that the file exists but the pgpool process id does not
 exist. Probably pgpool was stopped in unclean way. You may ignore the
 message as long as pgpool starts ok.
 --
 Tatsuo Ishii
 SRA OSS, Inc. Japan
 English: http://www.sraoss.co.jp/index_en.php
 Japanese: http://www.sraoss.co.jp

  Hi
 
  I have installed pgpool in /home/megha/pgpool dir.
  I found pgpool.pid file in /tmp folder.
 
  when I try to start pgpool I get this error.
  *pid file found but it seems bogus. Trying to start pgpool anyway..*
 
  Please help me!
 
 
  Thanks,
 
  --
  Megha

   
   
   
--
Megha
  
 
 
 
  --
  Megha

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pgpool error, pid not found!

[Tatsuo Ishii]

Wao. This is pgpool, right? It's not maintained anymore(it was almost
3 years ago). Please use pgpool-II. The latest version is pgpool-II
2.3.2.1.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

 Sorry for late reply. I work full time. so I work on postgres in night.
 here is the command that I use.
 
 I started pgpool using this command.
 pgpool/bin/pgpool  -f /home/megha/pgpool/etc/pgpool.conf -a
 /home/megha/pgpool/etc/pool_hba.conf
 
 pgpool.conf is attached.
 
 I have been trying to configure postgres and pgpool for past 2 months and I
 am getting too many problems with this.
 I hope you help me out to solve my errors.
 
 Waiting for your reply.
 Thanks
 
 On Tue, Feb 16, 2010 at 10:20 PM, Tatsuo Ishii is...@postgresql.org wrote:
 
  How did you start pgpool exactly(command line)?
 
  Also, the number  dpends on pgpool.conf.
  Can you show me pgpool.conf?
  --
  Tatsuo Ishii
  SRA OSS, Inc. Japan
  English: http://www.sraoss.co.jp/index_en.php
  Japanese: http://www.sraoss.co.jp
 
   From the README file , I found that  psql -p  -c 'show pool_status'
   template1 command gives the internal status of pgpool.
   so when i start pgpool, I dont get any inernal status.
   I get this error message
  
   psql: could not connect to server: No such file or directory
   Is the server running locally and accepting
   connections on Unix domain socket /tmp/.s.PGSQL.?
  
  
  
   On Tue, Feb 16, 2010 at 10:09 PM, Tatsuo Ishii is...@postgresql.org
  wrote:
  
pgpool.pid file contains pgpool's parent process id.  That message
indicates that the file exists but the pgpool process id does not
exist. Probably pgpool was stopped in unclean way. You may ignore the
message as long as pgpool starts ok.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp
   
 Hi

 I have installed pgpool in /home/megha/pgpool dir.
 I found pgpool.pid file in /tmp folder.

 when I try to start pgpool I get this error.
 *pid file found but it seems bogus. Trying to start pgpool anyway..*

 Please help me!


 Thanks,

 --
 Megha
   
  
  
  
   --
   Megha
 
 
 
 
 -- 
 Megha

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] pgpool error, pid not found!

[Megha]

Hi

I have installed pgpool in /home/megha/pgpool dir.
I found pgpool.pid file in /tmp folder.

when I try to start pgpool I get this error.
*pid file found but it seems bogus. Trying to start pgpool anyway..*

Please help me!


Thanks,

-- 
Megha

Re: [GENERAL] pgpool error, pid not found!

[Tatsuo Ishii]

pgpool.pid file contains pgpool's parent process id.  That message
indicates that the file exists but the pgpool process id does not
exist. Probably pgpool was stopped in unclean way. You may ignore the
message as long as pgpool starts ok.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

 Hi
 
 I have installed pgpool in /home/megha/pgpool dir.
 I found pgpool.pid file in /tmp folder.
 
 when I try to start pgpool I get this error.
 *pid file found but it seems bogus. Trying to start pgpool anyway..*
 
 Please help me!
 
 
 Thanks,
 
 -- 
 Megha

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pgpool error, pid not found!

[Tatsuo Ishii]

How did you start pgpool exactly(command line)?

Also, the number  dpends on pgpool.conf.
Can you show me pgpool.conf?
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

 From the README file , I found that  psql -p  -c 'show pool_status'
 template1 command gives the internal status of pgpool.
 so when i start pgpool, I dont get any inernal status.
 I get this error message
 
 psql: could not connect to server: No such file or directory
 Is the server running locally and accepting
 connections on Unix domain socket /tmp/.s.PGSQL.?
 
 
 
 On Tue, Feb 16, 2010 at 10:09 PM, Tatsuo Ishii is...@postgresql.org wrote:
 
  pgpool.pid file contains pgpool's parent process id.  That message
  indicates that the file exists but the pgpool process id does not
  exist. Probably pgpool was stopped in unclean way. You may ignore the
  message as long as pgpool starts ok.
  --
  Tatsuo Ishii
  SRA OSS, Inc. Japan
  English: http://www.sraoss.co.jp/index_en.php
  Japanese: http://www.sraoss.co.jp
 
   Hi
  
   I have installed pgpool in /home/megha/pgpool dir.
   I found pgpool.pid file in /tmp folder.
  
   when I try to start pgpool I get this error.
   *pid file found but it seems bogus. Trying to start pgpool anyway..*
  
   Please help me!
  
  
   Thanks,
  
   --
   Megha
 
 
 
 
 -- 
 Megha

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [Pgpool-general] panic: index siblings mismatch

[Tatsuo Ishii]

Have you turned off autovacuum on the first db node before starting
online recovery?
--
Tatsuo Ishii
SRA OSS, Inc. Japan

 Hello,
 
 today I recognized an error while playing with pgpool-II 2.1 and
 postgresql 8.3.5 (on 64bit linux).
 I really don't know if the error is caused by pgpool or if it's
 postgresql-related...?!?
 
 There are up to 200 active connections in parallel and thereby sometimes
 high load (with high write:read ratio, peak level ~60% write).
 Can this be a problem in such a usage scenario, are there some
 limitations in pgpool-II - number of connections / load ???
 
 any hints are much appreciated, since we don't know what happened...
 
 thanks in advance...::GERD::...
 
 
 
 LOG DETAILS:
 
 a) postgresql log of second db node where the error occured
 b) pgpool log
 
 
 a)
 ==
 2009-03-10 11:16:33 CET [11008]: [1-1] PANIC:  right sibling's left-link
 doesn't match: block 695 links to 785 instead of expected 694 in index
 PartyAddress_index
 2009-03-10 11:16:33 CET [11008]: [2-1] STATEMENT:  INSERT INTO
 PartyAddress (Name1, Name2, Street1, Street2, 
 City, Postcode, Region, Country, Timezone, Qualifier,
 Compressed, PositionId) VALUES (E'Testing', E'', E'
 Public street', E'', E'somewhere', E'12345', E'-', E'DE', E'UTC+1',
 E'address.loading', FALSE, NULL); select lastval()
 2009-03-10 11:16:33 CET [29026]: [3-1] LOG:  server process (PID 11008)
 was terminated by signal 6: Aborted
 2009-03-10 11:16:33 CET [29026]: [4-1] LOG:  terminating any other
 active server processes
 2009-03-10 11:16:33 CET [10978]: [5-1] WARNING:  terminating connection
 because of crash of another server process
 2009-03-10 11:16:33 CET [10978]: [6-1] DETAIL:  The postmaster has
 commanded this server process to roll back the curren
 t transaction and exit, because another server process exited abnormally
 and possibly corrupted shared memory.
 2009-03-10 11:16:33 CET [10978]: [7-1] HINT:  In a moment you should be
 able to reconnect to the database and repeat you
 r command.
 2009-03-10 11:16:33 CET [10947]: [1-1] WARNING:  terminating connection
 because of crash of another server process
 .
 .
 2009-03-10 11:16:33 CET [29026]: [6-1] LOG:  all server processes
 terminated; reinitializing
 2009-03-10 11:16:33 CET [11015]: [1-1] LOG:  database system was
 interrupted; last known up at 2009-03-10 11:09:10 CET
 2009-03-10 11:16:33 CET [11015]: [2-1] LOG:  starting archive recovery
 2009-03-10 11:16:33 CET [11015]: [3-1] LOG:  restore_command = 'cp
 /exchange/wal_from_iweblin/%f %p'
 cp: cannot stat `/exchange/wal_from_iweblin/0003.history': No such
 file or directory
 cp: cannot stat `/exchange/wal_from_iweblin/0003000100AA':
 No such file or directory
 2009-03-10 11:16:33 CET [11015]: [4-1] LOG:  automatic recovery in progress
 2009-03-10 11:16:33 CET [11015]: [5-1] LOG:  redo starts at 1/AABEBFA8
 2009-03-10 11:16:33 CET [11015]: [6-1] LOG:  unexpected pageaddr
 0/36BEE000 in log file 1, segment 170, offset 12509184
 2009-03-10 11:16:33 CET [11015]: [7-1] LOG:  redo done at 1/AABED938
 2009-03-10 11:16:33 CET [11015]: [8-1] LOG:  last completed transaction
 was at log time 2009-03-10 11:15:17.533913+01
 cp: cannot stat `/exchange/wal_from_iweblin/0003000100AA':
 No such file or directory
 cp: cannot stat `/exchange/wal_from_iweblin/0004.history': No such
 file or directory
 2009-03-10 11:16:33 CET [11015]: [9-1] LOG:  selected new timeline ID: 4
 cp: cannot stat `/exchange/wal_from_iweblin/0003.history': No such
 file or directory
 2009-03-10 11:16:33 CET [11015]: [10-1] LOG:  archive recovery complete
 2009-03-10 11:16:33 CET [11015]: [11-1] LOG:  checkpoint starting:
 shutdown immediate
 2009-03-10 11:16:34 CET [11015]: [12-1] LOG:  checkpoint complete: wrote
 4 buffers (0.0%); 0 transaction log file(s) added, 0 removed, 0
 recycled; write=0.005 s, sync=0.086 s, total=0.242 s
 2009-03-10 11:16:34 CET [11023]: [1-1] LOG:  autovacuum launcher started
 2009-03-10 11:16:34 CET [29026]: [7-1] LOG:  database system is ready to
 accept connections
 ===
 
 
 
 b)
 ==
 2009-03-10 11:16:23 DEBUG: pid 19059: read kind from frontend X(58)
 2009-03-10 11:16:31 DEBUG: pid 27107: read kind from frontend Q(51)
 2009-03-10 11:16:31 LOG:   pid 27107: statement: SELECT d.oid, datname
 as name, pg_get_userbyid(datdba) as owner, ds.des
 cription, pg_encoding_to_char(encoding) as dbencoding, datistemplate,
 datlastsysoid, datconfig, s.spcname as tblspace, d
 atconnlimit AS conlimit FROM pg_database d LEFT OUTER JOIN
 pg_description ds ON d.oid = ds.objoid LEFT OUTER JOIN pg_tab
 lespace s ON d.dattablespace = s.oid WHERE datname = 'test'
 
 2009-03-10 11:16:31 DEBUG: pid 27107: waiting for backend 0 completing
 the query
 2009-03-10 11:16:31 DEBUG: pid 27107: read_kind_from_backend: read kind
 from 0 th backend T NUM_BACKENDS: 1
 2009-03-10 11:16:31 

[GENERAL] Pgpool-II and Slony-I : query lag, sequences and stored procedures need help

[Andrea Moretto]

Hello there!

I am currently evaluating the best solution for a high-availability,  
load balanced cluster.
Currently I am testing this environment : postgres 8.3.1, pgpool-II  
2.1, Slony-I 1.2.14.
I've setup a master server to replicate all tables with Slony-I to a  
slave, and a frontend with pgpool
that load-balances the queries (load_balance=true,  
replication_mode=false, parallel_query=false,

enable_query_cache=false).

There is a problem : a stored procedure that returns a sequence value  
(a global unique ID, used as primary key in INSERTs)
returns the same values in successive calls, under certain conditions.  
This is clearly due to the replication lag led by Slony-I.

I would call it a race condition.

Now the question : is there a way to force pgpool to redirect all  
queries that involves a specific sequence or a stored procedure
to a specific backend? I tried to setup query partitioning, but only  
tables are supported so far.


I know that all queries belonging to a single transaction should be  
redirected to the same backend, but I would like to
find out a solution working on the backend, avoiding to check a huge  
amount of code that works. ;)
I also know that using the pgpool replication mode will solve the  
issue, but it can lead to downtime when adding new backends,
since a synch operation is required. Slony-I implement replication in  
a more suitable way from this point of view.


I do not use autoincrement or serial primary key because the  
application AS IS doesn't use it.


For the sake of clarity I write down the stored procedure and the  
sequence instantiation code:


-- begin code excerpt

CREATE SEQUENCE numgen
 INCREMENT 1
 MINVALUE 1
 MAXVALUE 9223372036854775807
 START 165024182
 CACHE 1;
ALTER TABLE numgen OWNER TO root;

CREATE OR REPLACE FUNCTION pr_next_id(OUT next_number character varying)
 RETURNS character varying AS
$BODY$
declare  myyear char(4);
declare  ii integer;
declare  tmp1 varchar(10);
declare  tmp2 char(10);
BEGIN
   MYYEAR = CAST(EXTRACT(YEAR FROM LOCALTIMESTAMP) AS CHAR(4));
   select CAST(nextval('NUMGEN') AS varchar(10)) into TMP1;
   TMP2 = '00';
   II = length(TMP1);
   NEXT_NUMBER = SSUBSTR(MYYEAR,3,4) || SSUBSTR(TMP2,1,10 - II) ||  
TMP1;

END;
$BODY$
 LANGUAGE 'plpgsql' VOLATILE
 COST 100;
ALTER FUNCTION pr_next_id() OWNER TO root;

-- end code excerpt
The pr_next_id returns the following values when called on the top of  
pgpool repeatedly (WRONG RESULT):


080165024184
080165024185
080165024184  [DUPLICATED VALUE]
080165024185  [DUPLICATED VALUE]
080165024186
080165024187
080165024188

If I call the pr_next_id repeatedly directly on the postgres engine, I  
get the following (CORRECT RESULT):


080165024112
080165024113
080165024114
080165024115
080165024116
080165024117
080165024118
080165024119
080165024120

Even this post could be off topic here, I think that some people could  
have same needs as mine. I already posted in the pgpool mailing

list, but so far no answer.

Thanks in advance!

Regards,

 Andrea Moretto

Andrea Moretto
[EMAIL PROTECTED]
---
CONFIDENTIALITY NOTICE
This message and its attachments are addressed solely to the persons
above and may contain confidential information. If you have received
the message in error, be informed that any use of the content hereof
is prohibited. Please return it immediately to the sender and delete
the message.
---


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [Pgpool-general] Trying to switch from PgPool1 to PgPool2

[Henry]

I scratched around some more, found doc/pgpool-en.html and my ignorance
has been somewhat lessened.

My previous config was almost right except for:

pgpool2_hostname = ''
#backend_data_directory0
#backend_data_directory1

and my Linux distro had assigned hostname 'slave1' to localhost, on which
Pg wasn't LISTENing at time...  a deal-killer :P

Regards
Henry


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [Pgpool-general] Trying to switch from PgPool1 to PgPool2

[Henry]

On Sun, August 10, 2008 3:03 pm, Henry wrote:

 I scratched around some more, found doc/pgpool-en.html and my ignorance
 has been somewhat lessened.

oi, wrong list /blushes

I really should *not* use multi-users under one login in squirrelmail...


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] pgpool and slony

[Josh Harrison]

Hi,
This is a newbie pgpool question. I have 2 servers (master-slave) and Slony
is doing the replication. I had installed pgpool for load balance where i
had disabled the replication_mode=false and load_balance_mode=true in
pgpool.conf

pcp_node_info gives this output.

 -sh-3.00$pcp_node_info 10 localhost 9898 josh postgres 1
172.31.0.67 5432 2 16383.50

-sh-3.00$ pcp_node_info 10 localhost 9898 josh postgres 0
172.31.0.68 5432 2 16383.50

So does this mean that pgpool is working properly with slony-1. Also how do
I test to see if the load balancing with pgpool is working properly. What
kind of query will help me confirm that it is load balanced ?

Also will pgpool automate failover? How can I test that?

sorry if my question is silly
thanks
josh

Re: [GENERAL] pgpool and slony

[Tom Lane]

Josh Harrison [EMAIL PROTECTED] writes:
 This is a newbie pgpool question.
 ...
 sorry if my question is silly

It doesn't sound silly, but this isn't the best place to ask it.
The slony project has its own mailing list(s) and you'll get
better-informed answers there.  Not sure if pgpool has its own list.

regards, tom lane

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] pgpool and slony

[Tatsuo Ishii]

 Hi,
 This is a newbie pgpool question. I have 2 servers (master-slave) and Slony
 is doing the replication. I had installed pgpool for load balance where i
 had disabled the replication_mode=false and load_balance_mode=true in
 pgpool.conf
 
 pcp_node_info gives this output.
 
  -sh-3.00$pcp_node_info 10 localhost 9898 josh postgres 1
 172.31.0.67 5432 2 16383.50
 
 -sh-3.00$ pcp_node_info 10 localhost 9898 josh postgres 0
 172.31.0.68 5432 2 16383.50
 
 So does this mean that pgpool is working properly with slony-1.

You need to trun on master_slave_mode. This will ensure that write
queries are sent to only master (remember that Slony's slave does not
accept write queries).

 Also how do
 I test to see if the load balancing with pgpool is working properly. What
 kind of query will help me confirm that it is load balanced ?

Turn on query logging on PostgreSQL and look into the log file.

 Also will pgpool automate failover? How can I test that?

For Slony slave, it's easy. Stop the slave's postmaster using pg_ctl
command. I recommend to use -m f option.

For Slony master, it's a little complicated. In this case Slony
requires user to manualy promote one of slaves to new master (correct
me if I am wrong). While pgpool automaticaly detach the broken master
and chose the second PostgreSQL (second means written in the second
place in pgpool.conf) and regard it as a new master. So there needs
some communication between pgpool and Slony. As of pgpool-II 2.0，a
new directive called failover_command is added. User can specify
shell command to be executed when a node is detached by pgpool.

You can specify following in the directive:
# special values:  %d = node id
#  %h = host name
#  %p = port number
#  %D = database cluster path
#  %% = '%' character

So you could invoke some Slony management command by using above to
sync pgpool and Slony(I'm not familiar with Slony, and hope someone
help us in this regard).

 sorry if my question is silly

Not at all. Thanks for testing pgpool.

 thanks
 josh

---(end of broadcast)---
TIP 6: explain analyze is your friend