Pgpool-II に PostgreSQL をデタッチ／アタッチしてみたメモ。
- 状態を確認する
$ psql "host=localhost dbname=postgres port=9999 user=awsuser" psql (9.2.24, server 12.4) WARNING: psql version 9.2, server version 12.0. Some psql features might not work. Type "help" for help. postgres=> show pool_nodes; node_id | hostname | port | status | lb_weight | role | selec t_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change ---------+---------------------------------------------------------------------------------------------+------+--------+-----------+---------+------ ------+-------------------+-------------------+-------------------+------------------------+--------------------- 0 | aurora-postgres124.cluster-********.ap-northeast-1.rds.amazonaws.com | 5432 | up | 0.000000 | primary | 0 | false | 0 | | | 2021-09-15 21:30:55 1 | aurora-postgres124-instance-1-ap-northeast-1a.********.ap-northeast-1.rds.amazonaws.com | 5432 | up★ | 0.500000 | standby | 0 | true | 0 | | | 2021-09-15 21:34:06 2 | reader2.********.ap-northeast-1.rds.amazonaws.com | 5432 | up | 0.500000 | standby | 0 | false | 0 | | | 2021-09-15 22:39:21 (3 rows) postgres=>
- デタッチする
- U: PCP ユーザー
- h: Pgpool-II ホスト
- n: Pgpool-II のバックエンドのデータベース
- g: すべてのクライアントが接続を終了するまで待機する
$ pcp_detach_node -U pgpool -h localhost -n 1 -g Password: pgpool # PCP パスワードを入力 pcp_detach_node -- Command Successful
- アタッチする
$ pcp_attach_node -U pgpool -h localhost -n 1
- ステータスを確認する
- "-g" オプションをつけると、down にならないので要調査
postgres=> show pool_nodes; node_id | hostname | port | status | lb_weight | role | selec t_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change ---------+---------------------------------------------------------------------------------------------+------+--------+-----------+---------+------ ------+-------------------+-------------------+-------------------+------------------------+--------------------- 0 | aurora-postgres124.cluster-********.ap-northeast-1.rds.amazonaws.com | 5432 | up | 0.000000 | primary | 0 | false | 0 | | | 2021-09-15 21:30:55 1 | aurora-postgres124-instance-1-ap-northeast-1a.*******.ap-northeast-1.rds.amazonaws.com | 5432 | down★ | 0.500000 | standby | 0 | false | 0 | | | 2021-09-15 22:54:08 2 | reader2.*******.ap-northeast-1.rds.amazonaws.com | 5432 | up | 0.500000 | standby | 0 | true | 0 | | | 2021-09-15 22:39:21
