PGPOOL_SETUP(1)

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) 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)