Cascading replication has been introduced in 9.2, but have you ever tried long chains of cascading nodes in Postgres?
In the case of this post, I simply tried to run as many nodes as possible in a VM with 4GB of memory. Each node has the following set of parameters, the goal being to minimize the use of memory for each one.
max_connections = 3 superuser_reserved_connections = 0 max_wal_senders = 2 hot_standby = on wal_level = hot_standby archive_mode = on work_mem = 64kB shared_buffers = 128kB temp_buffers = 800kB maintenance_work_mem = 1MB max_stack_depth = 100kB
In this case at least 3 max_connections have been necessary, max_wal_senders needing to have 2 slots for the replication and pg_basebackup as each slave was taking a pg_basebackup from the node it connects to. This could be done far better with a single base backup from the master though and max_connections set at 2, I just wanted here to have the node creation method consistent for ALL the nodes. :)
Then, some explanations about how I did that with a script coded in 10 minutes… First here is the script:
#!/bin/bash # Some initialization NUMBER_NODES=10 BASE_PGDATA=~/desktop/cascade BASE_MASTER=$BASE_PGDATA/master MASTER_PORT=`postgres_get_port` # Create master -c cascade rm -rf $BASE_MASTER postgres_node_init -p $MASTER_PORT -d $BASE_MASTER -c cascade pg_ctl -D $BASE_MASTER start sleep 1 createdb -p $MASTER_PORT $USER psql -d $USER -p $MASTER_PORT -c 'CREATE TABLE aa(a int)' # Initialize port slave node needs to connect to ROOT_PORT=$MASTER_PORT for count in `seq 1 $NUMBER_NODES` do SLAVE_PORT=`postgres_get_port` SLAVE_DATA=$BASE_PGDATA/slave_$SLAVE_PORT rm -rf $SLAVE_DATA # Create slave node postgres_node_init -d $SLAVE_DATA -p $SLAVE_PORT -q $ROOT_PORT -s \ -c cascade pg_ctl -D $SLAVE_DATA start sleep 1 psql -d $USER -p $MASTER_PORT -c "INSERT INTO aa VALUES ($count);" # Next slave will connect to this node ROOT_PORT=$SLAVE_PORT done
It does nothing extraordinary, simply initializing a master node, and then it creates a slave with a for loop, each slave connecting to the previous node created. Note that this script runs using two in-house scripts that I simply use for Postgres development: postgres_init_node, able to initialize a Postgres master or slave node, and postgres_get_port. Both things are available on github… postgres_init_node can take in argument the following things:
- -d PATH for the data folder of the new node
- -p PORT for the port of the new node
- -s to define if the node is a standby or not
- -q PORT to define the port where node needs to connect if it is a standby
- -c SUFFIX is an internal mechanism used to enforce the node initialization to use a set of parameters for postgresql.conf, in this case the memory values minimized
This script remains simple, as it is assumed that all the nodes run locally, particularly useful for development purposes. The second script, called postgres_get_node, scans psql ports already in use and gets a new one. There is nothing complicated in, and it can be easily broken as well. But for development on a VM, this is more than enough when you need to deploy your tools quickly and efficiently… And in this case only a git clone is enough.
Using those things, how many nodes have run in chain? Here are the results of this experiment… At 100 nodes, still fine… At 128 nodes, system complained about the maximum number of semaphores reached… A quick look at the dedicated system file later…
$ cat /proc/sys/kernel/sem 250 32000 32 128
… And after updating it to far higher values, process just went up to 275 nodes in chain but stopped there as the VM was lacking in disk space (only 16G, memory was getting close to its limit btw). Still this number is already interesting knowing that everything ran a single VM.
Note that the cascading was working really smoothly even with this high number of nodes. I did not really have any motivation to check the replication delay, but by running commands like the one below I did not see any delays when checking on the last node of the chain the creation of the new table on the master node:
psql -c 'create table ac (a int)'; psql -p $LAST_NODE_PORT -c '\d'