[pgpool-general: 5368] Re: Abnormal status

Mephysto mephystoonhell at gmail.com
Wed Mar 15 16:34:01 JST 2017


Hi Tatsuo,
your hypothesis was exact. After deleting pgpool_status file 'show
pool_nodes' returns this output:

 node_id |        hostname         | port  | status | lb_weight |  role   |
select_cnt | load_balance_node | replication_delay
---------+-------------------------+-------+--------+-----------+---------+------------+-------------------+-------------------
 0       | citrix2.redevogames.com | 15004 | up     | 0.250000  | primary |
1          | false             | 0
 1       | 10.0.0.3                | 5997  | up     | 0.750000  | standby |
2          | true              | 0


For completeness, in debian standard installation pgpool_status is located
in this path:

/var/log/postgresql/



​Thank you very much for your help.


Meph​



On 15 March 2017 at 00:25, Tatsuo Ishii <ishii at sraoss.co.jp> wrote:

> It is possible that Pgpool-II server is looking into the old status
> file ("pgpool_status"). After stopping Pgpool-II server, remove the
> file and restart Pgpool-II. The location of pgpool_status varies to
> the installation. I'm not familiar with Pgpool-II debian package, but
> you could find the location by looking at "logdir" directive in the
> pgpool.conf. If logdir is not found in pgpool.conf, try to locate it
> by using "pgpool show logdir".
>
> Best regards,
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese:http://www.sraoss.co.jp
>
> > Hi there,
> > I installed pgpool-II 3.6.1 by debian package from postgres repository. I
> > copied configuration from old installation from source on other debian
> > server.
> > A the moment I don't understand the status of my pool:
> >
> >  node_id |        hostname         | port  | status | lb_weight |  role
>  |
> > select_cnt | load_balance_node | replication_delay
> > ---------+-------------------------+-------+--------+-------
> ----+---------+------------+-------------------+-------------------
> >  0       | citrix2.redevogames.com | 15004 | up     | 0.250000  |
> primary |
> > 15         | true              | 0
> >  1       | 10.0.0.3                | 5997  | unused | 0.750000  |
> standby |
> > 0          | false             | 0
> >
> >
> >
> > This is my configurationç
> >
> > backend_hostname0 = 'citrix2.redevogames.com'
> > backend_port0 = 15004
> > backend_weight0 = 0.25
> > backend_data_directory0 = '/opt/postgres/dataclusters/redevodb_test'
> > backend_flag0 = 'ALLOW_TO_FAILOVER'
> >
> > backend_hostname1 = '10.0.0.3'
> > backend_port1 = 5997
> > backend_weight1 = 0.75
> > backend_data_directory1 = '/opt/postgres/dataclusters/redevodb'
> > backend_flag1 = 'ALLOW_TO_FAILOVER'
> >
> > #-----------------------------------------------------------
> -------------------
> > # CONNECTION POOLING
> > #-----------------------------------------------------------
> -------------------
> >
> > connection_cache = on
> >                                    # Activate connection pools
> >                                    # (change requires restart)
> >
> >                                    # Semicolon separated list of queries
> >                                    # to be issued at the end of a session
> >                                    # The default is for 8.3 and later
> > reset_query_list = 'ABORT; DISCARD ALL'
> >                                    # The following one is for 8.2 and
> before
> > #reset_query_list = 'ABORT; RESET ALL; SET SESSION AUTHORIZATION DEFAULT'
> >
> >
> > #-----------------------------------------------------------
> -------------------
> > # REPLICATION MODE
> > #-----------------------------------------------------------
> -------------------
> >
> > replication_mode = off
> >                                    # Activate replication mode
> >                                    # (change requires restart)
> > replicate_select = off
> >                                    # Replicate SELECT statements
> >                                    # when in replication mode
> >                                    # replicate_select is higher priority
> > than
> >                                    # load_balance_mode.
> >
> > insert_lock = on
> >                                    # Automatically locks a dummy row or a
> > table
> >                                    # with INSERT statements to keep
> SERIAL
> > data
> >                                    # consistency
> >                                    # Without SERIAL, no lock will be
> issued
> > lobj_lock_table = ''
> >                                    # When rewriting lo_creat command in
> >                                    # replication mode, specify table
> name to
> >                                    # lock
> >
> > # - Degenerate handling -
> >
> > replication_stop_on_mismatch = off
> >                                    # On disagreement with the packet kind
> >                                    # sent from backend, degenerate the
> node
> >                                    # which is most likely "minority"
> >                                    # If off, just force to exit this
> session
> >
> > failover_if_affected_tuples_mismatch = off
> >                                    # On disagreement with the number of
> > affected
> >                                    # tuples in UPDATE/DELETE queries,
> then
> >                                    # degenerate the node which is most
> > likely
> >                                    # "minority".
> >                                    # If off, just abort the transaction
> to
> >                                    # keep the consistency
> >
> > #-----------------------------------------------------------
> -------------------
> > # LOAD BALANCING MODE
> > #-----------------------------------------------------------
> -------------------
> >
> > load_balance_mode = on
> >                                    # Activate load balancing mode
> >                                    # (change requires restart)
> > ignore_leading_white_space = on
> >                                    # Ignore leading white spaces of each
> > query
> > white_function_list = 'get.*,check.*,is.*'
> >                                    # Comma separated list of function
> names
> >                                    # that don't write to database
> >                                    # Regexp are accepted
> > black_function_list =
> > 'nextval,setval,nextval,setval,database_control.*,add.
> *,buy.*,change.*,create.*,delete.*,do.*,exec.*,remove.*,
> send.*,set.*,update.*,generate.*'
> >                                    # Comma separated list of function
> names
> >                                    # that write to database
> >                                    # Regexp are accepted
> >
> > database_redirect_preference_list = ''
> >                                                                    #
> comma
> > separated list of pairs of database and node id.
> >                                                                    #
> > example: postgres:primary,mydb[0-4]:1,mydb[5-9]:2'
> >                                                                    #
> valid
> > for streaming replicaton mode only.
> >
> > app_name_redirect_preference_list = ''
> >                                                                    #
> comma
> > separated list of pairs of app name and node id.
> >                                                                    #
> > example: 'psql:primary,myapp[0-4]:1,myapp[5-9]:standby'
> >                                                                    #
> valid
> > for streaming replicaton mode only.
> > allow_sql_comments = off
> >                                                                    # if
> on,
> > ignore SQL comments when judging if load balance or
> >                                                                    #
> query
> > cache is possible.
> >                                                                    # If
> > off, SQL comments effectively prevent the judgment
> >                                                                    # (pre
> > 3.4 behavior).
> >
> > #-----------------------------------------------------------
> -------------------
> > # MASTER/SLAVE MODE
> > #-----------------------------------------------------------
> -------------------
> >
> > master_slave_mode = on
> >                                    # Activate master/slave mode
> >                                    # (change requires restart)
> > master_slave_sub_mode = 'stream'
> >                                    # Master/slave sub mode
> >                                    # Valid values are combinations slony
> or
> >                                    # stream. Default is slony.
> >                                    # (change requires restart)
> >
> > # - Streaming -
> >
> > sr_check_period = 10
> >                                    # Streaming replication check period
> >                                    # Disabled (0) by default
> > sr_check_user = 'postgres'
> >                                    # Streaming replication check user
> >                                    # This is necessary even if you
> disable
> >                                    # streaming replication delay check
> with
> >                                    # sr_check_period = 0
> >
> >
> > Why my standby node are note used? Are there some errors in
> configuration?
> >
> > Can you help me plese?
> >
> > Thanks in advance.
> >
> >
> > Meph
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20170315/761ef220/attachment-0001.html>


More information about the pgpool-general mailing list