Navigation :

Running mode

pgpool-II 3.6.2 Documentation
Prev Up Chapter 5. Server Configuration Next

5.3.1. Master slave mode

This mode is used to couple Pgpool-II with another master/slave replication software (like Slony-I and Streaming replication), that is responsible for doing the actual data replication.

Note: The number of slave nodes are not limited to 1 and Pgpool-II can have up to 127 slave nodes. master/slave mode can also work just master node without any slave nodes.

Load balancing (see Section 5.7 ) can also be used with master/slave mode to distribute the read load on the standby backend nodes.

Following options are required to be specified for master/slave mode.

master_slave_mode ( boolean )

Setting to on enables the master/slave mode. Default is off.

Note: master_slave_mode and replication_mode are mutually exclusive and only one can be enabled at a time.

This parameter can only be set at server start.

master_slave_sub_mode ( enum )

Specifies the external replication system used for data replication between PostgreSQL nodes. Below table contains the list of valid values for the parameter.

Table 5-1. master slave sub mode options

Value Description
'slony' Suitable for Slony-I
'stream' Suitable for PostgreSQL 's built-in replication system (Streaming Replication)

Default is 'slony' .

This parameter can only be set at server start.

5.3.2. Replication mode

This mode makes the Pgpool-II to replicate data between PostgreSQL backends.

Load balancing (see Section 5.7 ) can also be used with replication mode to distribute the load to the attached backend nodes.

Following options affect the behavior of Pgpool-II in the replication mode.

replication_mode ( boolean )

Setting to on enables the replication mode. Default is off.

Note: replication_mode and master_slave_mode are mutually exclusive and only one can be enabled at a time.

This parameter can only be set at server start.

replication_stop_on_mismatch ( boolean )

When set to on, and all nodes do not reply with the same packet kind to the query that was sent to all PostgreSQL backend nodes, then the backend node whose reply differs from the majority is degenerated by the Pgpool-II . If replication_stop_on_mismatch is set to off and a similar situation happens then the Pgpool-II only terminates the current user session but does not degenerate a backend node.

Note: Pgpool-II does not examine the data returned by the backends and takes the decision only by comparing the result packet types.

A typical use case of enabling the replication_stop_on_mismatch is to guard against the data inconsistency among the backend nodes. For example, you may want to degenerate a backend node if an UPDATE statement fails on one backend node while passes on others.

Default is off.

This parameter can be changed by reloading the Pgpool-II configurations.

failover_if_affected_tuples_mismatch ( boolean )

When set to on, and all nodes do not reply with the same number of affected tuples to the INSERT/UPDATE/DELETE query, then the backend node whose reply differs from the majority is degenerated by the Pgpool-II . If failover_if_affected_tuples_mismatch is set to off and a similar situation happens then the Pgpool-II only terminates the current user session but does not degenerate a backend node.

Note: In case of a tie, when two or more groups have the same number of nodes, then the group containing the master node (backend node having the youngest node id) gets the precedence.

Default is off.

This parameter can be changed by reloading the Pgpool-II configurations.

replicate_select ( boolean )

When set to on, Pgpool-II enables the SELECT query replication mode. i.e. The SELECT queries are sent to all backend nodes.

Table 5-2. replicate_select with load_balance_mode affects on SELECT routing

replicate_select is true Y N
load_balance_mode is true ANY Y N
SELECT is inside a transaction block ANY Y N ANY
Transaction isolation level is SERIALIZABLE and the transaction has issued a write query Y N ANY ANY ANY
results(R:replication, M: send only to master, L: load balance) R M L L M

Default is off.

This parameter can be changed by reloading the Pgpool-II configurations.

insert_lock ( boolean )

When set to on, Pgpool-II will automatically lock the table on PostgreSQL before an INSERT statement is issued for that.

When replicating a table with SERIAL data type, the SERIAL column value may get different values on the different backends. The workaround to this problem is to explicitly lock the table before issuing the INSERT.

So for automatically locking the table Pgpool-II do the following transformation: 

INSERT INTO ...

to 

BEGIN;
LOCK TABLE ...
INSERT INTO ...
COMMIT;

Caution

This approach severely degrades the transactions' parallelism

Pgpool-II V2.2 or later, automatically detects whether the table has a SERIAL columns or not, so it never locks the table if it desn't have the SERIAL columns.

Pgpool-II V3.0 until Pgpool-II V3.0.4 uses a row lock against the sequence relation, rather than table lock. This is intended to minimize lock conflict with VACUUM (including autovacuum). However this can lead to another problem. After transaction wraparound happens, row locking against the sequence relation causes PostgreSQL internal error (more precisely, access error on pg_clog, which keeps transaction status). To prevent this, PostgreSQL core developers decided to disallow row locking against sequences and this broke the Pgpool-II , of course (the "fixed" version of PostgreSQL was released as 9.0.5, 8.4.9, 8.3.16 and 8.2.22).

Pgpool-II V3.0.5 or later uses a row lock against pgpool_catalog.insert_lock table because new PostgreSQL disallows a row lock against the sequence relation. So creating insert_lock table in all databases which are accessed via Pgpool-II beforehand is required. See Section 3.7 for more details. If does not exist insert_lock table, Pgpool-II locks the insert target table. This behavior is same as Pgpool-II V2.2 and V2.3 series.

If you want to use insert_lock which is compatible with older releases, you can specify lock method by configure script. See Section 3.4 for more details.

For fine (per statement) control:

  • set insert_lock to true, and add /*NO INSERT LOCK*/ at the beginning of an INSERT statement for which you do not want to acquire the table lock.

  • set insert_lock to false, and add /*INSERT LOCK*/ at the beginning of an INSERT statement for which you want to acquire the table lock.

Note: If insert_lock is enabled, the regression tests for PostgreSQL 8.0 gets fail in transactions, privileges, rules, and alter_table.

The reason for this is that Pgpool-II tries to LOCK the VIEW for the rule test, and it produces the below error message: 

! ERROR: current transaction is aborted, commands ignored until
end of transaction block

For example, the transactions test tries an INSERT into a table which does not exist, and Pgpool-II causes PostgreSQL to acquire the lock for the table. Of cause this results in an error. The transaction will be aborted, and the following INSERT statement produces the above error message.

Default is off.

This parameter can be changed by reloading the Pgpool-II configurations.

lobj_lock_table ( string )

Specifies a table name used for large object replication control. If it is specified, Pgpool-II will lock the table specified by lobj_lock_table and generate a large object id by looking into pg_largeobject system catalog and then call lo_create to create the large object. This procedure guarantees that Pgpool-II will get the same large object id in all DB nodes in replication mode.

Note: PostgreSQL 8.0 and older does not have lo_create , so this feature does not work with PostgreSQL 8.0 and older versions.

A call to the libpq function lo_creat() triggers this feature. Also large object creation through Java API ( JDBC driver), PHP API ( pg_lo_create , or similar API in PHP library such as PDO), and this same API in various programming languages are known to use a similar protocol, and thus should work.

This feature does not works with following operations on large objects.

  • All APIs using lo_create , lo_import_with_oid .

  • lo_import function in backend called in SELECT.

  • lo_create function in backend called in SELECT.

Note: All PostgreSQL users must have a write access on lobj_lock_table and it can be created in any schema.

Example to create a large object lock table: 

CREATE TABLE public.my_lock_table ();
GRANT ALL ON public.my_lock_table TO PUBLIC;

Default is '' (empty), which disables the feature.