1.2. Your First Replication

In this section we are going to explain how to manage a PostgreSQL cluster with streaming replication using Pgpool-II, which is one of most common setup.

Before going further, you should properly set up pgpool.conf with streaming replication mode. Sample configurations are provided with Pgpool-II, there configuration file are located at /usr/local/etc with default installation from source code. you can copy pgpool.conf.sample-stream as pgpool.conf.

    cp /usr/local/etc/pgpool.conf.sample-stream pgpool.conf
   

If you plan to use pgpool_setup, type:

    pgpool_setup
   

This will create a Pgpool-II with streaming replication mode installation, primary PostgreSQL installation, and a async standby PostgreSQL installation.

From now on, we assume that you use pgpool_setup to create the installation under current directory. Please note that the current directory must be empty before executing pgpool_setup.

To start the whole system, type:

    ./startall
   

Once the system starts, you can check the cluster status by issuing a pseudo SQL command called "show pool_nodes" to any of databases. pgpool_setup automatically creates "test" database. We use the database. Note that the port number is 11000, which is the default port number assigned to Pgpool-II by pgpool_setup.

    $ psql -p 11000 -c "show pool_nodes" test
    node_id | hostname | port  | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay | last_status_change
    ---------+----------+-------+--------+-----------+---------+------------+-------------------+-------------------+---------------------
    0       | /tmp     | 11002 | up     | 0.500000  | primary | 0          | false             | 0                 | 2019-01-31 10:23:09
    1       | /tmp     | 11003 | up     | 0.500000  | standby | 0          | true              | 0                 | 2019-01-31 10:23:09
    (2 rows)
   

The result shows that the "status" column is "up", which means the PostgreSQL is up and running, which is good.