ErogameScape －エロゲー批評空間－ Blog

[pgpool@erogamescape14 ~]$ /usr/local/pgpool-II-4.0.4/bin/pgpool -d

Apr 27 22:49:21 erogamescape14 [29985]: [37-1] 2019-04-27 22:49:21: pid 29985: DEBUG:  watchdog exec interface up/down command failed
Apr 27 22:49:21 erogamescape14 [29985]: [37-2] 2019-04-27 22:49:21: pid 29985: DETAIL:  'sudo ip addr add $_IP_$/24 dev eth1 label eth1:0' failed. exit status: 1
Apr 27 22:49:21 erogamescape14 [29985]: [38-1] 2019-04-27 22:49:21: pid 29985: LOG:  failed to acquire the delegate IP address
Apr 27 22:49:21 erogamescape14 [29985]: [38-2] 2019-04-27 22:49:21: pid 29985: DETAIL:  'if_up_cmd' failed
Apr 27 22:49:21 erogamescape14 [29985]: [39-1] 2019-04-27 22:49:21: pid 29985: WARNING:  watchdog escalation failed to acquire delegate IP

[pgpool@erogamescape14 ~]$ /usr/local/pgpool-II-4.0.4/bin/pgpool -d -n > /dev/null 2>&1 &

Apr 27 22:51:46 erogamescape14 pgpool[31446]: [37-1] 2019-04-27 22:51:46: pid 31446: DEBUG:  watchdog exec interface up/down command: 'sudo ip addr add $_IP_$/24 dev eth1 label eth1:0' succeeded

[ap2@erogamescape14 ~]$ pcp_attach_node -p 8898 -U pgpool 1
pcp_attach_node: invalid option -- 'p'
pcp_attach_node - attach a node from pgpool-II

Usage: pcp_attach_node [-d] timeout hostname port# username password nodeID
Usage: pcp_attach_node -h

  -d       - enable debug message (optional)
  timeout  - connection timeout value in seconds. command exits on timeout
  hostname - pgpool-II hostname
  port#    - pgpool-II port number
  username - username for PCP authentication
  password - password for PCP authentication
  nodeID   - ID of a node to be attached
  -h       - print this help

[ap2@erogamescape14 ~]$ which pcp_attach_node
/usr/local/bin/pcp_attach_node

[root@erogamescape14 pgpool]# find / -name pcp_attach_node
中略
/usr/local/pgpool-II-3.5.1/bin/pcp_attach_node
/usr/local/pgpool-II-3.6.0/bin/pcp_attach_node
/usr/local/pgpool-II-3.3.1/bin/pcp_attach_node
/usr/local/pgpool-II-4.0.4/bin/pcp_attach_node
/usr/local/bin/pcp_attach_node

[ap2@erogamescape14 ~]$ /usr/local/pgpool-II-4.0.4/bin/pcp_attach_node -p 8898 -U pgpool 1

ap2=# SHOW POOL_NODES ;
 node_id |    hostname    | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay | last_
status_change
---------+----------------+------+--------+-----------+---------+------------+-------------------+-------------------+------
---------------
 0       | localhost      | 5433 | up     | 0.500000  | primary | 7056       | true              | 0                 | 2019-
01-02 22:22:16
 1       | 192.168.86.135 | 5433 | up     | 0.500000  | standby | 4833       | false             | 0                 | 2019-
01-02 22:26:21
(2 rows)

2019/01/02 22:48
[root@postgresql11-testserver-2 ap2]# systemctl stop postgresql-11.service

[root@postgresql11-testserver-1 ap2]# tail -f /var/log/pgpool/pgpool.log
Jan  2 22:36:52 postgresql11-testserver-1 pgpool[4041]: [863-1] 2019-01-02 22:36:52: pid 4041: LOG:  fork a new child process with pid: 9867
Jan  2 22:52:29 postgresql11-testserver-1 pgpool[4041]: [864-1] 2019-01-02 22:52:29: pid 4041: LOG:  child process with pid: 9476 exits with status 256
Jan  2 22:52:29 postgresql11-testserver-1 pgpool[4041]: [865-1] 2019-01-02 22:52:29: pid 4041: LOG:  fork a new child process with pid: 10914

[ap2@postgresql11-testserver-1 ~]$ psql -p 9999
psql: FATAL:  failed to create a backend connection
DETAIL:  executing failover on backend

Jan  2 23:10:50 postgresql11-testserver-1 pgpool[10914]: [865-1] 2019-01-02 23:10:50: pid 10914: LOG:  failed to connect to PostgreSQL server on "192.168.86.135:5433", getsockopt() detected error "Connection refused"
Jan  2 23:10:50 postgresql11-testserver-1 pgpool[10914]: [866-1] 2019-01-02 23:10:50: pid 10914: LOG:  received degenerate backend request for node_id: 1 from pid [10914]
Jan  2 23:10:50 postgresql11-testserver-1 pgpool[10914]: [867-1] 2019-01-02 23:10:50: pid 10914: FATAL:  failed to create a backend connection

[root@postgresql11-testserver-1 ap2]# emacs /etc/pgpool-II/pgpool.conf

- health_check_period = 0
+ health_check_period = 10

- health_check_user = 'nobody'
+ health_check_user = 'postgres'

[root@postgresql11-testserver-1 ap2]# systemctl restart pgpool.service

[root@postgresql11-testserver-2 data]# date ; systemctl stop postgresql-11.service
Wed Jan  2 23:23:25 JST 2019

Jan  2 23:23:30 postgresql11-testserver-1 pgpool[13058]: [6-1] 2019-01-02 23:23:30: pid 13058: LOG:  failed to connect to PostgreSQL server on "192.168.86.135:5433", getsockopt() detected error "Connection refused"
Jan  2 23:23:30 postgresql11-testserver-1 pgpool[13058]: [7-1] 2019-01-02 23:23:30: pid 13058: ERROR:  failed to make persistent db connection
Jan  2 23:23:30 postgresql11-testserver-1 pgpool[13058]: [7-2] 2019-01-02 23:23:30: pid 13058: DETAIL:  connection to host:"192.168.86.135:5433" failed
Jan  2 23:23:30 postgresql11-testserver-1 pgpool[13058]: [8-1] 2019-01-02 23:23:30: pid 13058: LOG:  health check failed on node 1 (timeout:0)

ap2=# SHOW POOL_NODES ;
 node_id |    hostname    | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay | last_
status_change
---------+----------------+------+--------+-----------+---------+------------+-------------------+-------------------+------
---------------
 0       | localhost      | 5433 | up     | 0.500000  | primary | 1          | true              | 0                 | 2019-
01-02 17:43:57
 1       | 192.168.86.135 | 5433 | down   | 0.500000  | standby | 0          | false             | 0                 | 2019-
01-02 20:10:19
(2 rows)

[ap2@postgresql11-testserver-1 tmp]$ cat pgpool_status
up
down

ap2=# SHOW POOL_NODES ;
 node_id |    hostname    | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay | last_
status_change
---------+----------------+------+--------+-----------+---------+------------+-------------------+-------------------+------
---------------
 0       | localhost      | 5433 | up     | 0.500000  | primary | 1          | true              | 0                 | 2019-
01-02 17:43:57
 1       | 192.168.86.135 | 5433 | down   | 0.500000  | standby | 0          | false             | 0                 | 2019-
01-02 20:10:19
(2 rows)

ap2=# SHOW POOL_NODES ;
 node_id |    hostname    | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay | last_
status_change
---------+----------------+------+--------+-----------+---------+------------+-------------------+-------------------+------
---------------
 0       | localhost      | 5433 | up     | 0.500000  | primary | 0          | true              | 0                 | 2018-
12-29 23:53:02
 1       | 192.168.86.135 | 5433 | up     | 0.500000  | standby | 0          | false             | 0                 | 2018-
12-30 00:01:57
(2 rows)

[root@postgresql11-testserver-2 ap2]# systemctl stop postgresql-11.service

ap2=# SHOW POOL_NODES ;
 node_id |    hostname    | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay | last_
status_change
---------+----------------+------+--------+-----------+---------+------------+-------------------+-------------------+------
---------------
 0       | localhost      | 5433 | up     | 0.500000  | primary | 0          | true              | 0                 | 2018-
12-29 23:53:02
 1       | 192.168.86.135 | 5433 | up     | 0.500000  | standby | 0          | false             | 0                 | 2018-
12-30 00:01:57
(2 rows)

ap2=# INSERT INTO sample (a) VALUES (9) ;
INSERT 0 1
ap2=# SHOW POOL_NODES ;
 node_id |    hostname    | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay | last_
status_change
---------+----------------+------+--------+-----------+---------+------------+-------------------+-------------------+------
---------------
 0       | localhost      | 5433 | up     | 0.500000  | primary | 0          | true              | 0                 | 2018-
12-29 23:53:02
 1       | 192.168.86.135 | 5433 | up     | 0.500000  | standby | 0          | false             | 0                 | 2018-
12-30 00:01:57
(2 rows)

• “show pool_nodes”の機能追加
• [pgpool-general: 5444] Re: what does "load_balance_node" mean in pool_nodes?
• PostgreSQL/Pgpool-II/負荷分散
• pgpool-IIの3.6以降におけるフェイルオーバー時のクライアントセッション

ap2=# SHOW POOL_NODES ;
 node_id |    hostname    | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay | last_
status_change
---------+----------------+------+--------+-----------+---------+------------+-------------------+-------------------+------
---------------
 0       | localhost      | 5433 | up     | 0.500000  | standby | 3          | true              | 0                 | 2018-
12-16 22:53:53
 1       | 192.168.86.135 | 5433 | up     | 0.500000  | standby | 4          | false             | 0                 | 2018-
12-16 22:53:53
(2 rows)

sr_check_user = 'nobody
sr_check_password = ''
sr_check_database = 'postgres'

ap2=# SHOW POOL_NODES ;
 node_id |    hostname    | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay | last_
status_change
---------+----------------+------+--------+-----------+---------+------------+-------------------+-------------------+------
---------------
 0       | localhost      | 5433 | up     | 0.500000  | primary | 0          | true              | 0                 | 2018-
12-16 23:12:16
 1       | 192.168.86.135 | 5433 | up     | 0.500000  | standby | 0          | false             | 0                 | 2018-
12-16 23:12:16
(2 rows)

2016-12-27 03:31:33: pid 28889: LOG:  received degenerate backend request for node_id: 1 from pid [28889]
2016-12-27 03:31:33: pid 28889: WARNING:  write on backend 1 failed with error :"Success"
2016-12-27 03:31:33: pid 28889: DETAIL:  while trying to write data from offset: 0 wlen: 5
2016-12-27 03:31:33: pid 13031: LOG:  starting degeneration. shutdown host erogamescape13(5432)
2016-12-27 03:31:33: pid 19938: ERROR:  unable to read data from frontend
2016-12-27 03:31:33: pid 19938: DETAIL:  socket read failed with an error "Connection reset by peer"
2016-12-27 03:31:33: pid 30138: ERROR:  unable to read data from frontend
2016-12-27 03:31:33: pid 30138: DETAIL:  EOF encountered with frontend
2016-12-27 03:31:34: pid 13031: LOG:  Restart all children
2016-12-27 03:31:34: pid 16765: LOG:  child process received shutdown request signal 3
2016-12-27 03:31:34: pid 18712: LOG:  child process received shutdown request signal 3
2016-12-27 03:31:34: pid 18047: LOG:  child process received shutdown request signal 3
2016-12-27 03:31:34: pid 19036: LOG:  child process received shutdown request signal 3
2016-12-27 03:31:34: pid 13031: LOG:  failover: set new primary node: -1
2016-12-27 03:31:34: pid 13031: LOG:  failover: set new master node: 0

ap2=# \d arrangement
                   テーブル "public.arrangement"
     列     |             型              |         修飾語
------------+-----------------------------+------------------------
 music      | integer                     | not null
 creater    | integer                     | not null
 created_at | timestamp without time zone | not null default now()
 updated_at | timestamp without time zone | not null default now()

ap2=# \d bookstore.arrangement
                                      テーブル "bookstore.arrangement"
    列    |             型              |                               修飾語
----------+-----------------------------+--------------------------------------------------------------------
 id       | integer                     | not null default nextval('bookstore.arrangement_id_seq'::regclass)
 users_id | integer                     | not null
 group_id | integer                     | not null
 shelf_id | integer                     | not null
 x        | integer                     | not null
 volumes  | integer                     | not null
 asin     | text                        | not null
 z        | integer                     | not null
 created  | timestamp without time zone | not null default now()
 modified | timestamp without time zone | not null default now()

ap2=#  INSERT INTO public.arrangement ( music , creater ) VALUES ( '6419' , '1' ) ;
ERROR:  column "created" of relation "arrangement" does not exist
ap2=#  INSERT INTO arrangement ( music , creater ) VALUES ( '6419' , '1' ) ;
ERROR:  column "created" of relation "arrangement" does not exist

ap2=# select * FROM pg_available_extensions WHERE name = 'pgpool_regclass';
      name       | default_version | installed_version |         comment
-----------------+-----------------+-------------------+--------------------------
 pgpool_regclass | 1.0             |                   | replacement for regclass
(1 行)