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. For this at least following two directives must be set:

    master_slave_mode = on
    master_slave_sub_mode = 'stream'
   

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_node" 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 
    ---------+----------+-------+--------+-----------+---------+------------+-------------------+-------------------
    0       | /tmp     | 11002 | up     | 0.500000  | primary | 0          | false             | 0
    1       | /tmp     | 11003 | up     | 0.500000  | standby | 0          | true              | 0
    (2 rows)
   

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