[pgpool-general: 5184] Re: FW: Re: Fedora 24 adding pgpool to system services
Avi Weinberg
AviW at gilat.com
Wed Dec 21 00:10:38 JST 2016
When you check the pgpool service status what do you get? Maybe pgpool is down.
Did you change the configuration on both master and slave machines? Connection configuration needs to be the same on both nodes. Did you check pgpool log?
From: Vola Maminiaina Nirina Andriamiadana [mailto:vola.andriamiadana at gmail.com]
Sent: Tuesday, December 20, 2016 4:59 PM
To: Avi Weinberg <AviW at gilat.com>
Subject: Re: [pgpool-general: 5182] FW: Re: Fedora 24 adding pgpool to system services
Avi,so what I do to make the port of pgpool accept the connection because i don't understand anything so far, if i change max-pool=1 it doesn't work yet
postgres:~>pg_ctl -D /var/lib/pgsql/9.1/data restart
waiting for server to shut down.................... done
server stopped
server starting
postgres:~>psql -p 9999
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.9999"?
postgres:~>psql
psql (9.1.23)
Type "help" for help.
postgres=#
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------
# - Connection Settings -
listen_addresses = '*' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost', '*' = all
# (change requires restart)
port = 5432 # (change requires restart)
max_connections = ??????? # (change requires restart)
# Note: Increasing max_connections costs ~400 bytes of shared memory per
# connection slot, plus lock space (see max_locks_per_transaction).
superuser_reserved_connections = ???? # (change requires restart)
#------------------------------------------------------------------------------
# POOLS
#------------------------------------------------------------------------------
# - Pool size -
num_init_children = 32
# Number of pools
# (change requires restart)
max_pool = ?????????
Please,
Vola,
2016-12-20 16:18 GMT+03:00 Avi Weinberg <AviW at gilat.com<mailto:AviW at gilat.com>>:
Hi Vola,
If your pgpool configuration is num_init_children = 32 and max_pool = 4 it means that you have 32 connections and each connection can have 4 combinations of user and database.
You can read some more here
http://www.pgpool.net/mediawiki/index.php/Relationship_between_max_pool,_num_init_children,_and_max_connections
However if you always connect with the same user and to the same database you practically have only 32 connections in pgpool and 100 in postgres which can explain why you can connect to postgres and not to pgpool. If you have several users and several databases you can have 32*4 connections which means that you have more connections to pgpool than defined in postgres.
If you have 1 user and 1 database you may want to have num_init_children = 100 max_pool = 1 since you never going to use the max_pool =4
Connection configuration requires some fine tuning to get it right…
Avi
From: Vola Maminiaina Nirina Andriamiadana [mailto:vola.andriamiadana at gmail.com]
Sent: Tuesday, December 20, 2016 2:56 PM
To: Avi Weinberg <AviW at gilat.com<mailto:AviW at gilat.com>>
Subject: Re: [pgpool-general: 5181] Re: Fedora 24 adding pgpool to system services
Hello Avi,
Thank you verry much for your answer,please what is the error in my configuration if you say that:
The relevant parameters in pgpool.conf are: num_init_children and max_pool
The relevant parameters in postgresql.conf are max_connections and superuser_reserved_connections.
What should I change in this configuration please
Thank you for your help!
In my postgresql.conf
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------
# - Connection Settings -
listen_addresses = '*' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost', '*' = all
# (change requires restart)
port = 5432 # (change requires restart)
max_connections = 100 # (change requires restart)
# Note: Increasing max_connections costs ~400 bytes of shared memory per
# connection slot, plus lock space (see max_locks_per_transaction).
superuser_reserved_connections = 3 # (change requires restart)
and in pgpool.conf
#------------------------------------------------------------------------------
# POOLS
#------------------------------------------------------------------------------
# - Pool size -
num_init_children = 32
# Number of pools
# (change requires restart)
max_pool = 4
# Number of connections per pool
# (change requires restart)
Vola,
2016-12-20 15:16 GMT+03:00 Avi Weinberg <AviW at gilat.com<mailto:AviW at gilat.com>>:
Hi,
If you can connect with port 5432 and not with 9999 it means your pgpool and postgres connection configuration do not match (you defined more connections to postgres than the connections you defined for pgpool connection pool).
The relevant parameters in pgpool.conf are: num_init_children and max_pool
The relevant parameters in postgresql.conf are max_connections and superuser_reserved_connections.
If you cannot get it to work you can temporarily disable pgpool connection pool with connection_cache = off until you figure out the right configuration
Avi
From: pgpool-general-bounces at pgpool.net<mailto:pgpool-general-bounces at pgpool.net> [mailto:pgpool-general-bounces at pgpool.net<mailto:pgpool-general-bounces at pgpool.net>] On Behalf Of Vola Maminiaina Nirina Andriamiadana
Sent: Monday, December 19, 2016 2:50 PM
To: Mikola Rose <mrose at power-soft.net<mailto:mrose at power-soft.net>>
Cc: pgpool-general at pgpool.net<mailto:pgpool-general at pgpool.net>
Subject: [pgpool-general: 5178] Re: Fedora 24 adding pgpool to system services
Hello Mikola and Vlad,
I have a big problem after configuring my pgpool I can't connect
to port 9999 but I can connect to 5432 very well
Please help me! :(
2016-12-15 3:18 GMT+03:00 Mikola Rose <mrose at power-soft.net<mailto:mrose at power-soft.net>>:
Awesome , thanks Vlad
On Dec 14, 2016, at 4:17 PM, Vlad Novikov <xou.slackware at gmail.com<mailto:xou.slackware at gmail.com>> wrote:
Well then you'll need to write a unit file and register the service with chkconfig. Otherwise you can take an existing unit file from older RPM and modify it appropriately. If you're going to create those from scratch, google with keywords "unit files" and "systemd". In fact, that's more Linux question rather than pgpool-II related.
On Mon, Dec 12, 2016 at 9:45 PM, Mikola Rose <mrose at power-soft.net<mailto:mrose at power-soft.net>> wrote:
Thanks for the pointer but I was using 3.6 src to trouble shoot some issues I was having.
On Dec 12, 2016, at 8:57 PM, Vlad Novikov <xou.slackware at gmail.com<mailto:xou.slackware at gmail.com>> wrote:
If 3.6.0 is not critical, you can just install pgpool-II 3.5.4 from PostgreSQL official repository (https://download.postgresql.org/pub/repos/yum/9.4/fedora/fedora-24-x86_64/). It'll configure unit scripts and make it run as a system service.
On Fri, Dec 9, 2016 at 4:16 PM, Mikola Rose <mrose at power-soft.net<mailto:mrose at power-soft.net>> wrote:
Hi list users,
Can anyone suggest or point me in the right direction on how to configure pgpool 3.6.0 as a system service on fedora 24.
thnx
Mik
_______________________________________________
pgpool-general mailing list
pgpool-general at pgpool.net<mailto:pgpool-general at pgpool.net>
http://www.pgpool.net/mailman/listinfo/pgpool-general
_______________________________________________
pgpool-general mailing list
pgpool-general at pgpool.net<mailto:pgpool-general at pgpool.net>
http://www.pgpool.net/mailman/listinfo/pgpool-general
_______________________________________________
pgpool-general mailing list
pgpool-general at pgpool.net<mailto:pgpool-general at pgpool.net>
http://www.pgpool.net/mailman/listinfo/pgpool-general
IMPORTANT - This email and any attachments is intended for the above named addressee(s), and may contain information which is confidential or privileged. If you are not the intended recipient, please inform the sender immediately and delete this email: you should not copy or use this e-mail for any purpose nor disclose its contents to any person.
_______________________________________________
pgpool-general mailing list
pgpool-general at pgpool.net<mailto:pgpool-general at pgpool.net>
http://www.pgpool.net/mailman/listinfo/pgpool-general
IMPORTANT - This email and any attachments is intended for the above named addressee(s), and may contain information which is confidential or privileged. If you are not the intended recipient, please inform the sender immediately and delete this email: you should not copy or use this e-mail for any purpose nor disclose its contents to any person.
_______________________________________________
pgpool-general mailing list
pgpool-general at pgpool.net<mailto:pgpool-general at pgpool.net>
http://www.pgpool.net/mailman/listinfo/pgpool-general
IMPORTANT - This email and any attachments is intended for the above named addressee(s), and may contain information which is confidential or privileged. If you are not the intended recipient, please inform the sender immediately and delete this email: you should not copy or use this e-mail for any purpose nor disclose its contents to any person.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20161220/a96e1084/attachment-0001.html>
More information about the pgpool-general
mailing list