WATCHDOG_SETUP(1)

📦
version 4.7.2 · 7 versions
, in ubuntu stonking

💖 WATCHDOG_SETUP 💖 (that's watchdog_setup(1), bestie — pgpool-II 4.7.2 era, section 1, the commands you actually type)

okay so. real talk. you want a whole little Pgpool-II cluster situation with watchdog turned on, but you do NOT want to commit to a real install, right? correct. that's exactly what this girl is for. she's the group project partner who shows up, builds everything in a folder, and dips.

✨ NAME ✨

watchdog_setup — creates a temporary installation of Pgpool-II clusters with watchdog 🐶💅

like, she makes you a temp installation. with watchdog. that's the whole vibe.

📜 SYNOPSIS 📜

watchdog_setup [option...]

just the command and vibes. options are optional, obviously. slay.

🌸 DESCRIPTION 🌸

okay so here's the tea, and I need you to actually listen for this part:

watchdog_setup creates a temporary installation of Pgpool-II clusters with watchdog enabled, and that little bundle includes a Pgpool-II installation plus however many PostgreSQL installations you asked for, all nested under the current directory. 🫶

🚨 vibe check, and it's a fail: the current directory must be empty before you run watchdog_setup. no cap. she needs a clean canvas. she's an artist. she's not cleaning up after you.
💀 ALSO, huge: watchdog_setup is for testing purpose only and should not be used to create production installations. she's a rehearsal, not the show. do not put your actual users on this. I'm literally begging. 😭

the way she gets stuff done? she uses pgpool_setup(1) as a workhorse. 🐴✨ like, pgpool_setup is the one doing the heavy lifting, watchdog_setup is the one holding the aux cord.

and currently, bestie, watchdog_setup supports these modes:

  • streaming replication mode 🌈
  • snapshot isolation mode 🎀
  • native replication mode 🦋
  • logical replication mode ✨
  • raw mode 💅

five flavors. all iconic in their own way.

🎛️ OPTIONS 🎛️

so watchdog_setup accepts the following command-line arguments, and yes we're going through all of them because I don't gatekeep:

  • -wn num_pgpool — sets the number of Pgpool-II installations. if you leave this out, she just uses 3. three pool girls. a trio. main character energy times three. 👑
  • -wp watchdog_base_port — this is the starting base port number used by both Pgpool-II and PostgreSQL, and the math is adorable so let's do it together. for the first Pgpool-II, the Pgpool-II port is watchdog_base_port. then:
    • pcp port is super cute at watchdog_base_port + 1
    • watchdog port is watchdog_base_port + 2
    • wd_heartbeat_port is watchdog_base_port + 3 (though, ngl, heartbeat is not used — she's just reserving the seat 😌)
    • the first PostgreSQL node's port is watchdog_base_port + 4
    • the second PostgreSQL node's port is watchdog_base_port + 5
    • and so on, you get the pattern, it just keeps sliding 💅

if you omit this one, 50000 is used.

  • -m mode — specifies the running mode. mode can be:
    • r → native replication mode
    • i → snapshot isolation mode
    • s → streaming replication mode
    • l → logical replication mode
    • n → raw mode

if you don't pass it, s is used. streaming girlies stay winning. ✨

  • -n num_clusters — specifies the number of PostgreSQL installations. omit it and you get 2. two nodes, a perfect little duo. 🫶
  • -vip [ip] — specifies the virtual IP. if ip is omitted, it is assumed that '127.0.0.1' is specified. the virtual IP you give gets set to the delegate_ip parameter. and here's the honest part, bestie, so listen: even if you specify a virtual IP, it will not be set to the system — you can just confirm the action trying to set the virtual IP in pcp_watchdog_info(1), or in the log file. but it's still useful for seeing how Pgpool-II reacts trying to set the virtual IP. 👀

and in addition to delegate_ip, the following parameters will be set too:

if_up_cmd = '/usr/bin/echo "if_up_cmd executed"'
if_down_cmd = 'usr/bin/echo "if_down_cmd executed"'
arping_cmd = '/usr/bin/true'

(yes, exactly like that. keep it character for character, that's the whole point of this exercise 🎀)

  • --no-stop — do not stop pgpool and PostgreSQL after the work. she just… leaves them running. standing there. thriving. 💅
  • -d — start pgpool with debug mode. verbose queen. she WILL tell you everything. 🔥

🌿 ENVIRONMENT VARIABLES 🌿

watchdog_setup recognizes the following environment variables, and she's very loyal about them:

  • PGPOOL_SETUP — specifies the path to the pgpool_setup command. the default is "pgpool_setup", which means it's assumed that pgpool_setup is chilling in the command search path. 🦋
  • PGPOOL_INSTALL_DIR — specifies the Pgpool-II installation directory. Pgpool-II binaries are expected to be placed under PGPOOL_INSTALL_DIR/bin, and pgpool.conf and pool_hba.conf etc. are expected to be placed under PGPOOL_INSTALL_DIR/etc. the default is /usr/local. 🏠
  • PGPOOLDIR — specifies the path to the Pgpool-II configuration files. the default is PGPOOL_INSTALL_DIR/etc. ✨
  • PGBIN — specifies the path to PostgreSQL commands such as initdb, pg_ctl and psql. the default is /usr/local/pgsql/bin. 🌈
  • PGLIB — specifies the path to PostgreSQL shared libraries. the default is /usr/local/pgsql/lib. 📚
  • PGSOCKET_DIR — specifies the path to the Unix socket directory. the default is /tmp. 🎀
  • INITDBARG — specifies the arguments for the initdb command. the default is "--no-locale -E UTF_8". 💜

and that's part one of this whole saga, bestie — there is literally more where this came from, so don't go anywhere. 🫶✨

💅 EXAMPLE 💅

okay bestie, buckle up, because this is the whole ✨experience✨ in one terminal session and honestly? it eats 💖. watch it set up three separate pgpool nodes, start them, connect, and then vibe-check the whole cluster. no cap.

Step one: the actual setup. you run watchdog_setup and it just… goes. watch how it does the same sacred ritual three times — once per <italic>pgpool</italic> node — and notice the ports wiggle as it goes: pgpool ports 50000, 50004, 50008, and pcp ports 50001, 50005, 50009. it literally tells you where that info lives at the end of each round 👑.

$ watchdog_setup
Starting set up
============= setting up pgpool 0 =============
Starting set up in streaming replication mode
creating startall and shutdownall
creating failover script
creating database cluster /home/t-ishii/work/pgpool-II/current/pgpool2/src/test/a/pgpool0/data0...done.
update postgresql.conf
creating pgpool_remote_start
creating basebackup.sh
creating recovery.conf
creating database cluster /home/t-ishii/work/pgpool-II/current/pgpool2/src/test/a/pgpool0/data1...done.
update postgresql.conf
creating pgpool_remote_start
creating basebackup.sh
creating recovery.conf
temporarily start data0 cluster to create extensions
temporarily start pgpool-II to create standby nodes
INFO:  unrecognized configuration parameter "debug_level"
node_id | hostname | port  | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay
---------+----------+-------+--------+-----------+---------+------------+-------------------+-------------------
0       | /tmp     | 51000 | up     | 0.500000  | primary | 0          | true              | 0
1       | /tmp     | 51001 | down   | 0.500000  | standby | 0          | false             | 0
(2 rows)

recovery node 1...pcp_recovery_node -- Command Successful
done.
creating follow primary script
Pager usage is off.
node_id | hostname | port  | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay
---------+----------+-------+--------+-----------+---------+------------+-------------------+-------------------
0       | /tmp     | 51000 | up     | 0.500000  | primary | 0          | false             | 0
1       | /tmp     | 51001 | up     | 0.500000  | standby | 0          | true              | 0
(2 rows)

shutdown all

pgpool-II setting for streaming replication mode is done.
To start the whole system, use /home/t-ishii/work/pgpool-II/current/pgpool2/src/test/a/pgpool0/startall.
To shutdown the whole system, use /home/t-ishii/work/pgpool-II/current/pgpool2/src/test/a/pgpool0/shutdownall.
pcp command user name is "t-ishii", password is "t-ishii".
Each PostgreSQL, pgpool-II and pcp port is as follows:
#1 port is 51000
#2 port is 51001
pgpool port is 50000
pcp port is 50001
The info above is in README.port.
============= setting up pgpool 1 =============
Starting set up in streaming replication mode
creating startall and shutdownall
creating failover script
creating database cluster /home/t-ishii/work/pgpool-II/current/pgpool2/src/test/a/pgpool1/data0...done.
update postgresql.conf
creating pgpool_remote_start
creating basebackup.sh
creating recovery.conf
creating database cluster /home/t-ishii/work/pgpool-II/current/pgpool2/src/test/a/pgpool1/data1...done.
update postgresql.conf
creating pgpool_remote_start
creating basebackup.sh
creating recovery.conf
temporarily start data0 cluster to create extensions
temporarily start pgpool-II to create standby nodes
INFO:  unrecognized configuration parameter "debug_level"
node_id | hostname | port  | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay
---------+----------+-------+--------+-----------+---------+------------+-------------------+-------------------
0       | /tmp     | 51000 | up     | 0.500000  | primary | 0          | true              | 0
1       | /tmp     | 51001 | down   | 0.500000  | standby | 0          | false             | 0
(2 rows)

recovery node 1...pcp_recovery_node -- Command Successful
done.
creating follow primary script
Pager usage is off.
node_id | hostname | port  | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay
---------+----------+-------+--------+-----------+---------+------------+-------------------+-------------------
0       | /tmp     | 51000 | up     | 0.500000  | primary | 0          | true              | 0
1       | /tmp     | 51001 | up     | 0.500000  | standby | 0          | false             | 0
(2 rows)

shutdown all

pgpool-II setting for streaming replication mode is done.
To start the whole system, use /home/t-ishii/work/pgpool-II/current/pgpool2/src/test/a/pgpool1/startall.
To shutdown the whole system, use /home/t-ishii/work/pgpool-II/current/pgpool2/src/test/a/pgpool1/shutdownall.
pcp command user name is "t-ishii", password is "t-ishii".
Each PostgreSQL, pgpool-II and pcp port is as follows:
#1 port is 51000
#2 port is 51001
pgpool port is 50004
pcp port is 50005
The info above is in README.port.
============= setting up pgpool 2 =============
Starting set up in streaming replication mode
creating startall and shutdownall
creating failover script
creating database cluster /home/t-ishii/work/pgpool-II/current/pgpool2/src/test/a/pgpool2/data0...done.
update postgresql.conf
creating pgpool_remote_start
creating basebackup.sh
creating recovery.conf
creating database cluster /home/t-ishii/work/pgpool-II/current/pgpool2/src/test/a/pgpool2/data1...done.
update postgresql.conf
creating pgpool_remote_start
creating basebackup.sh
creating recovery.conf
temporarily start data0 cluster to create extensions
temporarily start pgpool-II to create standby nodes
INFO:  unrecognized configuration parameter "debug_level"
node_id | hostname | port  | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay
---------+----------+-------+--------+-----------+---------+------------+-------------------+-------------------
0       | /tmp     | 51000 | up     | 0.500000  | primary | 0          | true              | 0
1       | /tmp     | 51001 | down   | 0.500000  | standby | 0          | false             | 0
(2 rows)

recovery node 1...pcp_recovery_node -- Command Successful
done.
creating follow primary script
Pager usage is off.
node_id | hostname | port  | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay
---------+----------+-------+--------+-----------+---------+------------+-------------------+-------------------
0       | /tmp     | 51000 | up     | 0.500000  | primary | 0          | true              | 0
1       | /tmp     | 51001 | up     | 0.500000  | standby | 0          | false             | 0
(2 rows)

shutdown all

pgpool-II setting for streaming replication mode is done.
To start the whole system, use /home/t-ishii/work/pgpool-II/current/pgpool2/src/test/a/pgpool2/startall.
To shutdown the whole system, use /home/t-ishii/work/pgpool-II/current/pgpool2/src/test/a/pgpool2/shutdownall.
pcp command user name is "t-ishii", password is "t-ishii".
Each PostgreSQL, pgpool-II and pcp port is as follows:
#1 port is 51000
#2 port is 51001
pgpool port is 50008
pcp port is 50009
The info above is in README.port.
not the INFO: unrecognized configuration parameter "debug_level" line showing up all three times 💀 it's giving harmless little gremlin noise, not a crash.

Step two: look at what we made. ls shows the three node dirs plus the top-level shutdownall and startall. then sh startall boots the whole squad and you get your postgres startup chatter, timestamps and all 🦋.

$ ls
pgpool0  pgpool1  pgpool2  shutdownall  startall

$ sh startall
waiting for server to start....16123 2016-08-18 16:26:53 JST LOG:  redirecting log output to logging collector process
16123 2016-08-18 16:26:53 JST HINT:  Future log output will appear in directory "pg_log".
done
server started
waiting for server to start....16136 2016-08-18 16:26:54 JST LOG:  redirecting log output to logging collector process
16136 2016-08-18 16:26:54 JST HINT:  Future log output will appear in directory "pg_log".
done
server started

Step three: actually walk in the door. connect to <italic>test</italic> through the first pgpool port, say hi to psql (9.5.4), then peace out. after that, pcp_watchdog_info -p 50001 -v flexes the whole cluster — who's leader, who's standby, quorum status, every port, every node name. this is the payoff and it is iconic 👑✨.

t-ishii@localhost: psql -p 50000 test

Pager usage is off.
psql (9.5.4)
Type "help" for help.

test=# \q

$ pcp_watchdog_info -p 50001 -v
Password:
Watchdog Cluster Information
Total Nodes              : 3
Remote Nodes             : 2
 Member Remote Nodes      : 2
 Nodes required for quorum: 2
Quorum state             : QUORUM EXIST
Alive Remote Nodes       : 2
VIP up on local node     : NO
Leader Node Name         : Linux_tishii-CF-SX3HE4BP_50004
Leader Host Name         : localhost

Watchdog Node Information
Node Name         : Linux_tishii-CF-SX3HE4BP_50000
Host Name         : localhost
Delegate IP       : Not_Set
Pgpool port       : 50000
Watchdog port     : 50002
Node priority     : 1
Status            : 7
Status Name       : STANDBY
 Membership Status : MEMBER

Node Name         : Linux_tishii-CF-SX3HE4BP_50004
Host Name         : localhost
Delegate IP       : Not_Set
Pgpool port       : 50004
Watchdog port     : 50006
Node priority     : 1
Status            : 4
Status Name       : LEADER
 Membership Status : MEMBER

Node Name         : Linux_tishii-CF-SX3HE4BP_50008
Host Name         : localhost
Delegate IP       : Not_Set
Pgpool port       : 50008
Watchdog port     : 50010
Node priority     : 1
Status            : 7
Status Name       : STANDBY
 Membership Status : MEMBER

so three nodes, one leader at 50004, two standbys at 50000 and 50008, quorum QUORUM EXIST, and VIP up on local node : NO. that's the whole story bestie, and it's a pretty one 🌸.

and that's the page!! this one's from pgpool-II 4.7.2, dated 2023 💖 go forth and set up your watchdog cluster, you absolute main character. mwah 🫶✨💅

📦 where this page comes from