Обсуждение: changing port numbers so pgbouncer can read geoserver and postgres

I get this error in geoserver :Original exception error:

Unable to obtain connection: Cannot create PoolableConnectionFactory (ERROR:
client_login_timeout (server down))

when I change the store port number from 5432 to 6432 so that geoserver can
use the  pgbouncer instead of calling on the postgres db directly. Not sure
if thats the way i'm suppose to do it.


5432 is the port that my postgresql is working from and 6432 is where my
pgbouncer is calling from.

geoserver is using port 8080, some how they don't seem to be speaking to
each other.



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/changing-port-numbers-so-pgbouncer-can-read-geoserver-and-postgres-tp5776631.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On 01/11/2013 14:44, si24 wrote:
> I get this error in geoserver :Original exception error:
>
> Unable to obtain connection: Cannot create PoolableConnectionFactory (ERROR:
> client_login_timeout (server down))
>
> when I change the store port number from 5432 to 6432 so that geoserver can
> use the  pgbouncer instead of calling on the postgres db directly. Not sure
> if thats the way i'm suppose to do it.

it should be ok.


have you specified in your pgbouncer.ini auth_type and auth_file ?

And I think you really need to check pgbouncer log !!!


>
> 5432 is the port that my postgresql is working from and 6432 is where my
> pgbouncer is calling from.

pgbouncer is not calling from 6432!!!

postgresql server listening on port 5432
pgbouncer listening on port 6432




>
> geoserver is using port 8080, some how they don't seem to be speaking to
> each other.

I don't know what is this geoserver, but this port 8080 I think it's not
relevant in this.

>
>
>
> --
> View this message in context:
http://postgresql.1045698.n5.nabble.com/changing-port-numbers-so-pgbouncer-can-read-geoserver-and-postgres-tp5776631.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>

>have you specified in your pgbouncer.ini auth_type and auth_file ?

my auth_type is md5 and my auth_file is D:\Program
Files\PostgreSQL\etc\userlist.txt


>And I think you really need to check pgbouncer log !!!

I get a lot of this im my pgbouncer log
2013-11-01 12:17:49.228 2860 LOG Stats: 0 req/s, in 0 b/s, out 0 b/s,query 0
us
2013-11-01 14:38:38.490 2860 WARNING lookup failed: localhost: result=11001
2013-11-01 14:38:38.491 2860 LOG S-0188d930: manifold/postgrest@(bad-af):0
closing because: server dns lookup failed (age=5)

>I don't know what is this geoserver, but this port 8080 I think it's not
relevant in this.

geoserver is my map webpage that uses the postgres database to make the map
in away.



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/changing-port-numbers-so-pgbouncer-can-read-geoserver-and-postgres-tp5776631p5776634.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On 01/11/2013 15:39, si24 wrote:
>> have you specified in your pgbouncer.ini auth_type and auth_file ?
>
> my auth_type is md5 and my auth_file is D:\Program
> Files\PostgreSQL\etc\userlist.txt
>
>
>> And I think you really need to check pgbouncer log !!!
>
> I get a lot of this im my pgbouncer log
> 2013-11-01 12:17:49.228 2860 LOG Stats: 0 req/s, in 0 b/s, out 0 b/s,query 0
> us
> 2013-11-01 14:38:38.490 2860 WARNING lookup failed: localhost: result=11001
> 2013-11-01 14:38:38.491 2860 LOG S-0188d930: manifold/postgrest@(bad-af):0
> closing because: server dns lookup failed (age=5)
>

Looks like dns error.

The postgresql server is on the same host with pgbouncer?

How you specify the postgresql server host in pgbouncer.ini? as IP
address or hostname?

show the [databases] section in the pgbouncer.ini



>> I don't know what is this geoserver, but this port 8080 I think it's not
> relevant in this.
>
> geoserver is my map webpage that uses the postgres database to make the map
> in away.
>
>
>
> --
> View this message in context:
http://postgresql.1045698.n5.nabble.com/changing-port-numbers-so-pgbouncer-can-read-geoserver-and-postgres-tp5776631p5776634.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>

On 01/11/2013 15:39, si24 wrote:
>> have you specified in your pgbouncer.ini auth_type and auth_file ?
>
> my auth_type is md5 and my auth_file is D:\Program
> Files\PostgreSQL\etc\userlist.txt
>
>
>> And I think you really need to check pgbouncer log !!!
>
> I get a lot of this im my pgbouncer log
> 2013-11-01 12:17:49.228 2860 LOG Stats: 0 req/s, in 0 b/s, out 0 b/s,query 0
> us
> 2013-11-01 14:38:38.490 2860 WARNING lookup failed: localhost: result=11001
> 2013-11-01 14:38:38.491 2860 LOG S-0188d930: manifold/postgrest@(bad-af):0
> closing because: server dns lookup failed (age=5)
>
>> I don't know what is this geoserver, but this port 8080 I think it's not
> relevant in this.
>
> geoserver is my map webpage that uses the postgres database to make the map
> in away.
>

 From pgbouncer README:


Building for WIN32
------------------

At the moment only build env tested is MINGW32 / MSYS.  Cygwin
and Visual $ANYTHING are untested.  Libevent 2.x is required
for DNS hostname lookup.


I don't know how building works on windows and don't know how you get
pgbouncer ... but seems you need Libevent 2.x too

Maybe try with IP addresses.


>
>
> --
> View this message in context:
http://postgresql.1045698.n5.nabble.com/changing-port-numbers-so-pgbouncer-can-read-geoserver-and-postgres-tp5776631p5776634.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>

On 11/01/2013 06:29 AM, Birta Levente wrote:

>
>
>>
>> geoserver is using port 8080, some how they don't seem to be speaking to
>> each other.
>
> I don't know what is this geoserver, but this port 8080 I think it's not
> relevant in this.
>

If I am following correctly the OP chain of connections as originally
set up and I believe still is:

End User --> port 8080 (Tomcat) --> port 5432 (Postgres)

and they are trying to get to

End User --> port 808 (Tomcat) --> port 6432 (pgBouncer) --> port 5432
(Postgres)


--
Adrian Klaver
adrian.klaver@gmail.com

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of Birta Levente
> Sent: Friday, November 01, 2013 9:50 AM
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Re: changing port numbers so pgbouncer can read
> geoserver and postgres
>
> On 01/11/2013 15:39, si24 wrote:
> >> have you specified in your pgbouncer.ini auth_type and auth_file ?
> >
> > my auth_type is md5 and my auth_file is D:\Program
> > Files\PostgreSQL\etc\userlist.txt
> >
> >
> >> And I think you really need to check pgbouncer log !!!
> >
> > I get a lot of this im my pgbouncer log
> > 2013-11-01 12:17:49.228 2860 LOG Stats: 0 req/s, in 0 b/s, out 0 b/s,query 0
> > us
> > 2013-11-01 14:38:38.490 2860 WARNING lookup failed: localhost:
> result=11001
> > 2013-11-01 14:38:38.491 2860 LOG S-0188d930: manifold/postgrest@(bad-
> af):0
> > closing because: server dns lookup failed (age=5)
> >
>
> Looks like dns error.
>
> The postgresql server is on the same host with pgbouncer?
>
> How you specify the postgresql server host in pgbouncer.ini? as IP
> address or hostname?
>
> show the [databases] section in the pgbouncer.ini
>
>
>
> >> I don't know what is this geoserver, but this port 8080 I think it's not
> > relevant in this.
> >
> > geoserver is my map webpage that uses the postgres database to make
> the map
> > in away.
> >


Obviously, your PgBouncer is not connecting to your Postgres.

I assume, you run them both on the same machine.
So, do you you have localhost mapped to 127.0.0.1 in your hosts file under windows\system32\drivers\ets directory?
If not, change your pgbouncer.ini config file to use IP address 127.0.0.1 instead of localhost in [database] section.

Regards,
Igor Neyman

On 01/11/2013 13:58, Adrian Klaver wrote:
> On 11/01/2013 06:29 AM, Birta Levente wrote:
>
>>
>>
>>>
>>> geoserver is using port 8080, some how they don't seem to be
>>> speaking to
>>> each other.
>>
>> I don't know what is this geoserver, but this port 8080 I think it's not
>> relevant in this.
>>
>
> If I am following correctly the OP chain of connections as originally
> set up and I believe still is:
>
> End User --> port 8080 (Tomcat) --> port 5432 (Postgres)
>
> and they are trying to get to
>
> End User --> port 808 (Tomcat) --> port 6432 (pgBouncer) --> port 5432
> (Postgres)
>
>


That sounds right, but port 8080 is definitely irrelevant in this case,
as it is just a detail of the application - it could just as well be a
desktop application with no associated network port.

For the sake of what needs to be configured, the chain is just:

Before:
(application using PostgreSQL) --> port 5432 (Postgres)

After:
(application using PostgreSQL) --> port 6432 (pgBouncer) --> port 5432
(Postgres)

So there are two things to configure:

a) in the application, tell it to connect to port 6432 for its database
connections, instead of port 5432
b) in pgBouncer, make sure it can connect properly to the postgres
server on port 5432

It sounds like (b) is currently the issue.

--
Rowan Collins
[IMSoP]

On 11/02/2013 08:11 AM, Rowan Collins wrote:
> On 01/11/2013 13:58, Adrian Klaver wrote:
>> On 11/01/2013 06:29 AM, Birta Levente wrote:
>>
>>>
>>>
>>>>
>
>> If I am following correctly the OP chain of connections as originally
>> set up and I believe still is:
>>
>> End User --> port 8080 (Tomcat) --> port 5432 (Postgres)
>>
>> and they are trying to get to
>>
>> End User --> port 808 (Tomcat) --> port 6432 (pgBouncer) --> port 5432
>> (Postgres)
>>
>>
>
>
> That sounds right, but port 8080 is definitely irrelevant in this case,
> as it is just a detail of the application - it could just as well be a
> desktop application with no associated network port.
>
> For the sake of what needs to be configured, the chain is just:
>
> Before:
> (application using PostgreSQL) --> port 5432 (Postgres)
>
> After:
> (application using PostgreSQL) --> port 6432 (pgBouncer) --> port 5432
> (Postgres)

Well, what I showed is supposition on my part and is not necessarily the
truth. Part of the issue is discerning the truth of how information
flows through the system, in particular what exactly is/are the
application(s) talking to Postgres/pgBouncer. It is still unknown, at
least to me, where geoserver fits into the above and whether it is
connecting directly to Postgres or going through the Tomcat server. Once
some sort of schematic for connection(current, desired) is provided then
it would be possible to do as you suggest. The confusion from what I am
following is that the "(application using PostgreSQL) --> port 5432
(Postgres)" part is not known.



>
> So there are two things to configure:
>
> a) in the application, tell it to connect to port 6432 for its database
> connections, instead of port 5432
> b) in pgBouncer, make sure it can connect properly to the postgres
> server on port 5432
>
> It sounds like (b) is currently the issue.
>


--
Adrian Klaver
adrian.klaver@gmail.com

On 11/2/2013 9:56 AM, Adrian Klaver wrote:
> Well, what I showed is supposition on my part and is not necessarily
> the truth. Part of the issue is discerning the truth of how
> information flows through the system, in particular what exactly
> is/are the application(s) talking to Postgres/pgBouncer. It is still
> unknown, at least to me, where geoserver fits into the above and
> whether it is connecting directly to Postgres or going through the
> Tomcat server. Once some sort of schematic for connection(current,
> desired) is provided then it would be possible to do as you suggest.
> The confusion from what I am following is that the "(application using
> PostgreSQL) --> port 5432 (Postgres)" part is not known.

tomcat itself has no database layer.  its purely a web appserver. the
java apps running on tomcat use JDBC to connect to a database server
like postgresql.



--
john r pierce                                      37N 122W
somewhere on the middle of the left coast

>show the [databases] section in the pgbouncer.ini

[databases]
manifold = host=127.0.0.1 port=5432 dbname=manifold user=postgrest
password=123ert

I have tested now with both the ip address and using localhost in the host
section

I have also ended up swapping round the 2 port numbers so now postgres is
6432 and pgbouncer is 5432 the only thing is when I use the admin command to
check the databases for the pgbouncer i get this:

pgbouncer=# show databases;
   name      |          host    | port  | database   | force_user |
pool_size | reserve_pool
-----------+--------------+------+-----------+------------+-----------+--------------
 manifold    |  127.0.0.1     | 5432 | manifold     | postgrest   |
20 |0
 pgbouncer |                    | 5432 | pgbouncer  | pgbouncer  |
2 |0
 postgres   | 127.0.0.1       | 5432 | postgres    |                 |
20 |0

It seems that they are all are using the same port.

I'm at my winds end now and I need to get this working badley.
Maybe I'm not understanding it at all if some could explaine it to me in
more lamens terms hopefully that would help otherwise I don't know what to
do.
I can't change to linux which might of made this job a lot easier I have to
use windows.



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/changing-port-numbers-so-pgbouncer-can-read-geoserver-and-postgres-tp5776631p5776821.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On 11/04/2013 01:25 AM, si24 wrote:
>> show the [databases] section in the pgbouncer.ini
>
> [databases]
> manifold = host=127.0.0.1 port=5432 dbname=manifold user=postgrest
> password=123ert
>
> I have tested now with both the ip address and using localhost in the host
> section
>
> I have also ended up swapping round the 2 port numbers so now postgres is
> 6432 and pgbouncer is 5432 the only thing is when I use the admin command to
> check the databases for the pgbouncer i get this:
>
> pgbouncer=# show databases;
>     name      |          host    | port  | database   | force_user |
> pool_size | reserve_pool
> -----------+--------------+------+-----------+------------+-----------+--------------
>   manifold    |  127.0.0.1     | 5432 | manifold     | postgrest   |
> 20 |0
>   pgbouncer |                    | 5432 | pgbouncer  | pgbouncer  |
> 2 |0
>   postgres   | 127.0.0.1       | 5432 | postgres    |                 |
> 20 |0
>
> It seems that they are all are using the same port.
>
> I'm at my winds end now and I need to get this working badley.
> Maybe I'm not understanding it at all if some could explaine it to me in
> more lamens terms hopefully that would help otherwise I don't know what to
> do.


Well the first thing would be to work on one thing at a time and  also
maintain some consistency. For example in the above you say you have
switched the Postgres port to 6432, yet in your [databases] example you
show a port of 5432. So one of those statements is wrong.

In order to work with this we will need to see a more detailed
description of what you are doing, example:


client application --> 5432 pgBouncer --> 6432 Postgres

and the associated config files

pgbouncer.ini pgBouncer

from postgresql.conf Postgres

    listen_addresses
    port

pg_hba.conf Postgres

also

Make sure when making any changes to reload the appropriate software
after the changes.

> I can't change to linux which might of made this job a lot easier I have to
> use windows.
>
>
>
> --
> View this message in context:
http://postgresql.1045698.n5.nabble.com/changing-port-numbers-so-pgbouncer-can-read-geoserver-and-postgres-tp5776631p5776821.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>


--
Adrian Klaver
adrian.klaver@gmail.com

would any of this help make it more clear on what I seem to be doing wrong?

pgbouncer=# show config;
            key            |                      value
| changeable
---------------------------+-------------------------------------------------+------------
 job_name                  | pgbouncer
| no
 service_name              | pgbouncer
| no
 conffile                  | C:\Program Files\PostgreSQL\share\pgbouncer.ini
| yes
 logfile                   | C:\Program Files\PostgreSQL\log\pgbouncer.log
| yes
 pidfile                   | C:\Program Files\PostgreSQL\log\pgbouncer.pid
| no
 listen_addr               | *
| no
 listen_port               | 5432
| no
 listen_backlog            | 128
| no
 auth_type                 | md5
| yes
 auth_file                 | C:\Program Files\PostgreSQL\etc\userlist.txt
| yes
 pool_mode                 | transaction
| yes
 max_client_conn           | 400
| yes
 default_pool_size         | 100
| yes
 min_pool_size             | 0
| yes
 reserve_pool_size         | 0
| yes
 reserve_pool_timeout      | 5
| yes
 syslog                    | 0
| yes
 syslog_facility           | daemon
| yes
 syslog_ident              | pgbouncer
| yes
 autodb_idle_timeout       | 3600
| yes
 server_reset_query        | DISCARD ALL
| yes
 server_check_query        | select 1
| yes
 server_check_delay        | 30
| yes
 query_timeout             | 0
| yes
 query_wait_timeout        | 0
| yes
 client_idle_timeout       | 0
| yes
 client_login_timeout      | 60
| yes
 idle_transaction_timeout  | 0
| yes
 server_lifetime           | 1200
| yes
 server_idle_timeout       | 60
| yes
 server_connect_timeout    | 15
| yes
 server_login_retry        | 15
| yes
 server_round_robin        | 0
| yes
 suspend_timeout           | 10
| yes
 ignore_startup_parameters | application_name,extra_float_digits
| yes
 disable_pqexec            | 0
| no
 dns_max_ttl               | 15
| yes
 dns_zone_check_period     | 0
| yes
 max_packet_size           | 2147483647
| yes
 pkt_buf                   | 2048
| no
 sbuf_loopcnt              | 5
| yes
 tcp_defer_accept          | 0
| yes
 tcp_socket_buffer         | 0
| yes
 tcp_keepalive             | 1
| yes
 tcp_keepcnt               | 0
| yes
 tcp_keepidle              | 0
| yes
 tcp_keepintvl             | 0
| yes
 verbose                   | 0
| yes
 admin_users               | postgres
| yes
 stats_users               | postgres
| yes
 stats_period              | 60
| yes
 log_connections           | 1
| yes
 log_disconnections        | 1
| yes
 log_pooler_errors         | 1
| yes
(54 rows)



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/changing-port-numbers-so-pgbouncer-can-read-geoserver-and-postgres-tp5776631p5777453.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

has it got to do with the DNS I have checked on the admin consol and a
recieved this:

pgbouncer=# show dns_hosts;
 hostname | ttl | addrs
----------+-----+-------
(0 rows)


pgbouncer=# show dns_zones;
 zonename | serial | count
----------+--------+-------
(0 rows)

Not to sure if this helps.



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/changing-port-numbers-so-pgbouncer-can-read-geoserver-and-postgres-tp5776631p5777457.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Ok now I seem to have gotten it working by changing the store port numbers to
pgbouncer because its using port no 5432 and postgres is now using 6432.

So now it seems to be giving me the server details that is being used when
the map gets run:

pgbouncer=# show servers;
type |   user   | database | state |   addr    | port | local_addr |
local_port |    connect_time     |    request_time     |   ptr    | link

------+----------+----------+-------+-----------+------+------------+------------+---------------------+---------------------+----------+------
 S    | postgres | manifold | idle  | 127.0.0.1 | 6432 | 127.0.0.1  |
62261 | 2013-11-08 15:08:37 | 2013-11-08 15:08:48 | 0197da20 |
 S    | postgres | manifold | idle  | 127.0.0.1 | 6432 | 127.0.0.1  |
62259 | 2013-11-08 15:08:37 | 2013-11-08 15:08:47 | 0197d930 |
(2 rows)

and connections have gone down from 100 to 5 when I check on pgadmin for the
connections.

Is it because of putting the default pool size from 20 - 2 ( I was trying to
test something).

Is meant to show that its idle or that its being used on the server part on
the top



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/changing-port-numbers-so-pgbouncer-can-read-geoserver-and-postgres-tp5776631p5777468.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On 11/08/2013 05:41 AM, si24 wrote:
> Ok now I seem to have gotten it working by changing the store port numbers to
> pgbouncer because its using port no 5432 and postgres is now using 6432.
>
> So now it seems to be giving me the server details that is being used when
> the map gets run:
>
> pgbouncer=# show servers;
> type |   user   | database | state |   addr    | port | local_addr |
> local_port |    connect_time     |    request_time     |   ptr    | link
>
------+----------+----------+-------+-----------+------+------------+------------+---------------------+---------------------+----------+------
>   S    | postgres | manifold | idle  | 127.0.0.1 | 6432 | 127.0.0.1  |
> 62261 | 2013-11-08 15:08:37 | 2013-11-08 15:08:48 | 0197da20 |
>   S    | postgres | manifold | idle  | 127.0.0.1 | 6432 | 127.0.0.1  |
> 62259 | 2013-11-08 15:08:37 | 2013-11-08 15:08:47 | 0197d930 |
> (2 rows)
>
> and connections have gone down from 100 to 5 when I check on pgadmin for the
> connections.
>
> Is it because of putting the default pool size from 20 - 2 ( I was trying to
> test something).
>
> Is meant to show that its idle or that its being used on the server part on
> the top


In order to get answers you will need to provide more and consistent
information. For instance in the configure file you sent earlier default
pool size was 100, then you mention it was 20 and now is 2. Also what is
the store and how does it fit into the scheme of things? More generally
what is the scheme of things? In other words detail how your
applications(s), server(s) are laid out relative to each other when it
comes to the flow of information. As to what the above means it would
depend on what 'when the map gets run' means. Some of that could be
found out by tailing the Postgres log directly and seeing what is
happening at the same time.


--
Adrian Klaver
adrian.klaver@gmail.com