The load-balancing mode in pgpool presumably distributes connections according to server weight. Then, master/slave mode defines which servers are read-only as opposed to writable.
But can we depend on this behavior? We should at least verify these claims before using such a configuration in a production environment. Our uptime depends upon it.
Make sure pgpool is installed and configured according to the Installing pgpool and Configuring pgpool for master/slave mode recipes. We will follow these two recipes by testing a pool setup with write activity, so we need a fully functional pgpool environment.
To simplify this recipe, perform all the tests as the postgres system user. To facilitate this, we may need to set all the pg_hba.conf authentication types to trust, though we strongly suggest user and password combinations instead.
If our primary PostgreSQL server is on 192.168.56.10, we can connect to pgpool by using port 9999. With psql, we can connect to the postgres database through pgpool with this command:
psql -p 9999 -h 192.168.56.10 postgres
Follow these steps to test as the postgres database user. Feel free to substitute where appropriate:
- Connect to the primary database and create a test table with the following SQL:
CREATE TABLE foo (bar INTEGER);
- Connect to pgpool and issue a query that will write to the test table with the following SQL:
INSERT INTO foo SELECT generate_series(1, 100);
- Execute the following bash snippet at the command line to test the
INSERTredirection:for x in {1..10}; do psql -h 192.168.56.10 -p 9999 -U postgres -d postgres -c "INSERT INTO foo SELECT generate_series(1, 100)" done - Execute the following bash snippet at the command line to test the
DELETEredirection:for x in {1..100}; do psql -h 192.168.56.10 -p 9999 -U postgres -d postgres -c "DELETE FROM foo WHERE bar=$x" done
In order to successfully test the capabilities of pgpool, we will try a couple of different scenarios that cause PostgreSQL to write to the database. If we tried to write to the replica instead of the primary server, we will get an error like this:
ERROR: cannot execute INSERT in a read-only transaction
Our first step is to create a table where we can try to insert data. We connect directly to the primary server for this step so that we know the table exists and that pgpool didn't get a chance to taint our results. The test table has only one column, so we can populate it with the
generate_series PostgreSQL function.
The first test we attempt is with a single connection to pgpool that we create manually. Since the server weight is equal for both the primary and replica servers, we have a 50 percent chance of being assigned to the read-only replica server. This test should succeed, but there's still a 50 percent chance that it was just a coincidence.
Therefore, our second test runs the same INSERT statement ten times in a loop. Each psql line is a separate connection attempt, so each should carry a 50 percent chance of being directed to the read-only server. Yet, all of these tests will also succeed.
Finally, we run one final loop that will delete all the rows we inserted, and this time the loop will invoke 100 times. Again, all of these are separate connection attempts, and all of them will execute without an error.
There is one caveat to this functionality. It is not uncommon for databases to perform the write activity within a function body. For example:
CREATE FUNCTION test_insert() RETURNS VOID AS $$ INSERT INTO foo SELECT generate_series(1, 100); $$ LANGUAGE SQL;
By creating this function, we obfuscate the INSERT statement enough that pgpool won't recognize it. This means that pgpool will improperly send the query to a read-only server and produce an error. We can avoid this by using the black_function_list configuration setting. For example, if we add our new function to this setting, it resembles this:
black_function_list = 'currval,lastval,nextval,setval,test_insert'
Now, pgpool will understand that queries which include a call to test_insert should only execute on the primary node. This configuration setting also honors regular expressions, so it's a very good idea to follow a naming scheme when building functions that may alter database contents.