Thread: 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. > >
Attachment
>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. > >
Attachment
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. > >
Attachment
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
On Fri, Nov 1, 2013 at 9:29 AM, Birta Levente <blevi.linux@gmail.com> wrote:
I don't know what is this geoserver, but this port 8080 I think it's not relevant in this.
I might be able to help: http://geoserver.org/display/GEOS/Welcome. GeoServer is a Java web application that retrieves GIS data (as in PostGIS in the realm of PostgreSQL) and servers it up in standard formats. This allows for greater interoperability than would be possible with only the PostgreSQL/PostGIS database since many clients cannot communicate with PostgreSQL directly.
So the desired request chain is most likely something like this:
Client (E.g., ArcMap, a web page with OpenLayers or Leaflet, another web application that processes the data somehow) --> GeoServer (running on port 8080) --> pgBouncer --> PostgreSQL (with PostGIS extension)
It looks like GeoServer is having a problem connecting to PostgreSQL, so I think you're right. It's either between GeoServer and pgBouncer or pgBouncer and PostgreSQL.
It looks like GeoServer is having a problem connecting to PostgreSQL, so I think you're right. It's either between GeoServer and pgBouncer or pgBouncer and PostgreSQL.
Hopefully, that clears up some points of confusion.