--- name: "pgpool_setup" section: "1" platform: "linux" package: "pgpool2" version: "4.6.1-2" release: "Debian 13 (Trixie)" platforms: ["linux"] url: "https://new.carta.tech/pgpool_setup" --- # pgpool\_setup(1) Create a temporary installation of Pgpool-II cluster ## NAME pgpool\_setup - Create a temporary installation of Pgpool-II cluster ## SYNOPSIS **pgpool\_setup** \[*option*...\] ## DESCRIPTION pgpool\_setup creates a temporary installation of Pgpool-II cluster, which includes a Pgpool-II installation and specified number of PostgreSQL installations under current directory. Current directory must be empty before running pgpool\_setup. pgpool\_setup is for testing purpose only and should not be used to create production installations. pgpool\_setup executes ssh against localhost. You need to configure ssh so that it can login to localhost without password. Currently pgpool\_setup supports streaming replication mode, native replication mode, raw mode, logical replication mode and snapshot isolation mode. To support watchdog, see [**watchdog\_setup**(1)](/watchdog_setup/1.md) for details. ## OPTIONS pgpool\_setup accepts the following command-line arguments: - **-m** *mode* Specifies the running mode. *mode* can be r (native replication mode), s (streaming replication mode), n (raw mode), l (logical replication mode), y (slony mode) or i (snapshot isolation mode). If this is omitted, s is assumed. - **-n** *num\_clusters* Specifies the number of PostgreSQL installations. If this is omitted, 2 is used. - **-p** *base\_port* Specify the base port number used by Pgpool-II and PostgreSQL. Pgpool-II port is base\_port. pcp port is base\_port + 1. The first PostgreSQL node's port is base\_port + 2, second PostgreSQL node's port is base\_port + 3 and so on. If -pg option is specified, the first PostgreSQL node's port is assigned to pg\_base\_port, the second PostgreSQL node's port is pg\_base\_port + 1 and so on. If this is omitted, 11000 is used. - **-pg** *pg\_base\_port* Specify the base port number used by PostgreSQL. The first PostgreSQL node's port is base\_port + 2, second PostgreSQL node's port is base\_port + 3 and so on. If this is omitted, base\_port+2 is used. - **--no-stop** Do not stop pgpool and PostgreSQL after the work. - **-d** Start pgpool with debug mode. - **-s** In streaming replication mode, use replication slot instead of archive. Since the archive directory is shared by all PostgreSQL clusters, if a standby is promoted, the time line in the archive directory will be changed and other standby servers will be stopped. Using a replication slot does not have this problem and is always preferable if you can use PostgreSQL 9.4 or later, which supports replication slot. The replication slot name used by pgpool\_setup is pgpool\_setup\_slot. - **-r** Use **pg\_rewind** command in recovery script (basebackup.sh). If the command fails, switch to use ordinal rsync command. In certain cases recovery using **pg\_rewind** is much faster than rsync since it does not copy whole database cluster. - **-e** Omit to create PostgreSQL database This option is intended to be used by **watchdog\_setup** and probably useless for other purposes. - **-t** Set some additional parameters to pgpool.conf so that the regression test can performs tests against existing installation. Currently the parameter is health\_check\_test. - **-c** Use the sample scripts and configuration files installed in /etc/pgpool-II/ directory. This option was added for the purpose of testing the sample scripts and configuration files contained in Pgpool-II RPM packages. Make sure you have installed the sample scripts and configuration files in /etc/pgpool-II/ directory before you specify this option. ## ENVIRONMENT VARIABLES pgpool\_setup recognizes following environment variables: **PGPOOL\_INSTALL\_DIR** Specifies the Pgpool-II installation directory. Pgpool-II binaries is 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 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 Unix socket directory. The default is /tmp. **INITDBARG** Specifies the arguments for initdb command. The default is "--no-locale -E UTF\_8". **USE\_REPLICATION\_SLOT** If "true", in streaming replication mode, use replication slot instead of archive. This brings the same effect as "-s" option is specified. **USE\_PG\_REWIND** If "true", in streaming replication mode, use **pg\_rewind** in basebackup.sh script. This brings the same effect as "-r" option is specified. **ENABLE\_TEST** If "true", act as if "-t" option is specified. **TEST\_SAMPLES** If "true", act as if "-c" option is specified. ## EXAMPLE ``` $ pgpool_setup PostgreSQL major version: 124 Starting set up in streaming replication mode creating startall and shutdownall creating failover script creating database cluster /tmp/test/data0...done. update postgresql.conf creating pgpool_remote_start creating basebackup.sh creating recovery.conf creating database cluster /tmp/test/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 node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change ---------+----------+-------+--------+-----------+---------+------------+-------------------+-------------------+-------------------+------------------------+--------------------- 0 | /tmp | 11002 | up | 0.500000 | primary | 0 | true | 0 | | | 2020-08-18 13:50:19 1 | /tmp | 11003 | down | 0.500000 | standby | 0 | false | 0 | | | 2020-08-18 13:50:18 (2 rows) recovery node 1...pcp_recovery_node -- Command Successful done. creating follow primary script node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change ---------+----------+-------+--------+-----------+---------+------------+-------------------+-------------------+-------------------+------------------------+--------------------- 0 | /tmp | 11002 | up | 0.500000 | primary | 0 | true | 0 | | | 2020-08-18 13:50:19 1 | /tmp | 11003 | up | 0.500000 | standby | 0 | false | 0 | | | 2020-08-18 13:50:23 (2 rows) shutdown all pgpool-II setting for streaming replication mode is done. To start the whole system, use /tmp/test/startall. To shutdown the whole system, use /tmp/test/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 11002 #2 port is 11003 pgpool port is 11000 pcp port is 11001 The info above is in README.port. t-ishii$ ./startall waiting for server to start....5744 2020-08-18 13:50:27 JST LOG: starting PostgreSQL 12.4 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit 5744 2020-08-18 13:50:27 JST LOG: listening on IPv4 address "0.0.0.0", port 11002 5744 2020-08-18 13:50:27 JST LOG: listening on IPv6 address "::", port 11002 5744 2020-08-18 13:50:27 JST LOG: listening on Unix socket "/tmp/.s.PGSQL.11002" 5744 2020-08-18 13:50:27 JST LOG: redirecting log output to logging collector process 5744 2020-08-18 13:50:27 JST HINT: Future log output will appear in directory "log". done server started waiting for server to start....5757 2020-08-18 13:50:27 JST LOG: starting PostgreSQL 12.4 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit 5757 2020-08-18 13:50:27 JST LOG: listening on IPv4 address "0.0.0.0", port 11003 5757 2020-08-18 13:50:27 JST LOG: listening on IPv6 address "::", port 11003 5757 2020-08-18 13:50:27 JST LOG: listening on Unix socket "/tmp/.s.PGSQL.11003" 5757 2020-08-18 13:50:27 JST LOG: redirecting log output to logging collector process 5757 2020-08-18 13:50:27 JST HINT: Future log output will appear in directory "log". done server started t-ishii$ psql -p 11000 test psql (12.4) Type "help" for help. test=# show pool_nodes; node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change ---------+----------+-------+--------+-----------+---------+------------+-------------------+-------------------+-------------------+------------------------+--------------------- 0 | /tmp | 11002 | up | 0.500000 | primary | 0 | true | 0 | | | 2020-08-18 13:50:32 1 | /tmp | 11003 | up | 0.500000 | standby | 0 | false | 0 | streaming | async | 2020-08-18 13:50:32 (2 rows) ```