Synchronous streaming replication/Standby nodes in a cluster for an HA solution

Linux, PostgreSQL No Responses »
Jun 222011

The post presents a proposal to implement an HA solution based on PostgreSQL streaming replication and Standby node structure. This solution is still in construction, so the final implementation design that will be chosen for Postgres-XC may slightly change.

Before reading this post and if you are not experienced with PostgreSQL 9.0/9.1 features, you should refer to the background about PostgreSQL Master/Slave fallback.

Synchronous streaming replication for Postgres-XC

This part specifies how synchronous streaming replication will be implemented in Postgres-XC. Even if functionalities in PostgreSQL 9.1 already implemented are pretty stable, some extensions related to node communication control have to be designed to have a real HA solution.

Some background

    Postgres-XC is a multi-master database cluster based on PostgreSQL.

  • It is made of a unique global transaction manager which feeds consistently transaction IDs and snapshots in the cluster to each node.
  • Nodes are made of 2 kinds of nodes: Coordinator and Datanode.
    • Coordinators are holding other node information. A coordinator is able to communicate with other Coordinators and other Datanodes through a connection pooler. This connection pooler saves all the connection parameters to nodes (host name, port number) to be able to distribute connection with a libpq string protocol depending on database name and user name. Coordinators also have all the catalog information, and primarily the distribution information of each table in the database. With this information, Coordinator is able to push down SQL queries to correct Datanodes and then merge results that are sent back to application according a new plan type called RemoteQuery. A coordinator does not hold table data, and all the data contained each Coordinator is the same. So one Coordinator is the clone of another one.
    • Datanodes react more or less like a PostgreSQL normal instance. They hold database data. What has been added is an interface to permit Datanodes to receive from Coordinators transaction IDs, snapshots, timestamp values instead of requesting them locally.

Limitations

  • Postgres-XC does not support yet tuple relocation from one node to another (impossible to update for instance column that holds the distribution key), so this design is limited to the case where the cluster has a fixed number of master nodes (for Datanodes and Coordinators (?)).
  • It is not thought here about trying to add or delete a Datanode on the fly. By that, it means that cluster configuration is not changed in a way that it modifies the node number and data distribution.
  • With those assumptions what remains is a cluster with a fixed size
  • This specification is based on PostgreSQL 9.1, but design is though to take into account as many replication features as possible.

Specifications

Here are the list of functionalities that will be added for the support of synchronous streaming replication. Most of them concern node management, master/slave identification and slave promotion system.

  • In no way Postgres-XC nodes are thought as being able to make system calls to be able to kick a slave initdb or something like this. NO WAY!
Catalog addition

A catalog called pgxc_nodes will be added with the following columns:

  • node type
  • node ID number
  • node host name
  • node port number
  • node immediate master ID
  • connection type: replication or not?

This table has as a primary key constraint on node number and node type.
“node immediate master ID” is the node ID that a slave is using to connect to a master or another slave (case of cascade replication, not implemented in PostgreSQL 9.1 though). This catalog is created on Coordinator.

Cluster startup
  • As a limitation, all the configuration files of postgres-XC coordinator nodes only contain master Coordinator numbers.
    With that, the initialization of the catalog table pgxc_nodes is made only with data of master nodes. In the case of a master node, “node immediate master ID” is filled with 0.
  • Once the cluster is up with a fixed number of nodes, the administrator has he possibility to update pgxc_nodes catalog with slaves already on that have already there configuration files set correctly to connect to the wanted node.
SQL interface
Adding a slave node after cluster start up

Following SQL is sent to Coordinators:

CREATE [COORDINATOR | DATANODE] SLAVE
IDENTIFIED BY ID $id_num
WITH CONNECTION ('$host1:$port1',...,'$hostN:$portN')
ON MASTER $master_number.
{ REPLICATION TO [SYNCHRONOUS | ASYNCHRONOUS] }

If only 1 host/port couple is specified, the same values are applied for all the coordinators. In case multiple host/port values are specified, they have to correspond to the number of Coordinators in the cluster. The following SQL is sent to all the coordinators. If replication option is not specified, slave is contacted to get the information.

Promoting a slave as a master

Following SQL is sent to Coordinators:

ALTER [COORDINATOR | DATANODE] SLAVE id PROMOTE TO MASTER
{WITH CONNECTION ( [:$port | $host: | $host:$port] )};

This will modify pgxc_nodes like this for a coordinator for example:

  • Former tuples:
    • Master: C, ID: 1, host:localhost, port:5432, master ID: 0
    • Slave: C, ID: 4, host:localhost, port:5433, master ID: 1
  • New tuples:
    • Former master: erased
    • Slaved promoted: C, ID: 1, host:localhost, port:5432, master ID: 0

The following restrictions apply at promotion

  • Before promoting the slave as a new master it is necessary to restart slave new parameters. Postgres-XC does not take responsabilities in kicking new nodes.
  • Promotion can be made on a synchronous slave only, this check is made on pgxc_nodes.
  • Before promoting, a check is made on slave to be sure that it has not been modified from synchronous to asynchronous mode when beginning the promotion. This check is done locally on pgxc_nodes.
  • When promoting, make a check on slave node to be sure that its standby mode is off. This has to be kicked from an external utility and not by XC itself.
Changing a slave status

Following SQL is sent to Coordinators:

ALTER [COORDINATOR | DATANODE] SLAVE $id REPLICATION TO [SYNCHRONOUS | ASYNCHRONOUS];
ALTER [COORDINATOR | DATANODE] SLAVE $id ID TO $new_id;

The following rules are applied:

  • Take an exclusive lock on pgxc_table to make other backends waiting on pgxc_nodes
  • The lock is taken externally with LOCK TABLE and sent to all the Coordinators first. Then the table is updated. Then lock is released on all the Coordinators from remote Coordinator
  • When changing replication mode, connect to slave node and check if mode has effectively been changed correctly by an external application kick.
Disabling a slave node from cluster

Following SQL is Sent to Datanodes:

DROP [COORDINATOR | DATANODE] SLAVE $id;

The following rules are applied:

  • Take an exclusive lock (SHARE ROW EXCLUSIVE MODE?) on pgxc_nodes and send this lock to all the Coordinators before performing the deletion from pgxc_nodes
  • Lock is released once deletion on all the nodes is completed
Pooler process modification

Connection pooling has to be modified with following guidelines:

  • At initialization phase, Pooler fills in the catalog table pgxc_nodes with initial value in global memory context with values found in postgresql.conf: pgxc_nodes has to be accessible from postmaster and child processes.
  • When a new slave is added with a new code ID, Pooler caches this new connection data on each Coordinator once pgxc_nodes has been updated on Coordinator associated with new node ID.
  • When a slave is dropped, Pooler information cached is updated also.
  • Pooler saves in shared memory information related to master nodes at cluster initialization.

Important:

Pooler only remains in charge in distributing connections. It does not have to know if connection is to a slave or a master. This is the reponsability of postgres child as it takes a row shared lock on pgxc_nodes when beginning a transaction on certain nodes.

Postmaster child process modification
  • A child returns an error to application in case it cannot read pgxc_nodes.
  • When a postmaster child determines the list of nodes for transaction, it needs to know if current transaction is read-only or write depending on SQL. Then node list is built from information in pgxc_nodes when requesting new connections.
  • When a child postmaster uses connection information of a slave/master when taking new connections, it takes a row shared lock on the associated tuples of pgxc_nodes where it took connections. This preserves catalog modification when running a transaction on those nodes.
    • If the transaction is read-only, connections to master/slave are both possible. Choice is made with round-robin.
    • If the transaction first requested read-only connections, but launches on the way a DML, new connections are requested from pooler to necessary masters.
    • If transaction was first write, then does read operations, keep going with connections to master.
Posted by Michael at 9:55 am Tagged with: , , , , , , , , , , , ,

Postgres 9.1: Setup a synchronous stand-by server in 5 minutes

PostgreSQL 13 Responses »
May 232011

PostgreSQL 9.1 is out, you can download it source code from here or use latest GIT repository as I did like this:
mkdir postgres
git remote add postgres http://git.postgresql.org/git/postgresql.git
git fetch postgres
git branch --track master postgres/master
git checkout master

Asynchronous streaming replication is here since 9.0, but as I keep being always busy with development stuff of Postgres-XC, I have not taken time to play around with streaming replication and HOT Standby.
As now synchronous streaming replication is going to be released soon (beta 1 at the moment of this post), and that Postgres-XC will soon be merged with PostgreSQL 9.1, I tried to set up a synchronous streaming replication server. This functionality could be used to extend XC so as to make it a full HA solution based on Postgres.

So, let’s give it a try.

To be honest, I have been surprised by how easy it is to set it up. PostgreSQL developers have really done a good job on it. As a beta 1, it is not completely polished, but it looks close to completion. Here are the main steps you need to do to set up your server.
First download code, and compile it (written above).
./configure --prefix=$HOME/bin/postgres
make
make install

In my case my installation is located at $HOME/bin/postgres, and I use the following folder for all my settings. You can of course use the folder you prefer, even for slave, master or archive data folders.

To understand what has to be set where, let’s first have a look at the configuration parameters.
This is important to understand how to set your own servers, without depending on this post.
Settings are different for slave and master servers.

Here are the parameters you have to set for postgresql.conf file of master.
wal_level = hot_standby
archive_mode = on
archive_command = 'cp -i %p $HOME/bin/postgres/archive/%f'
max_wal_senders = 10

archive_command is the command used when launching pg_start_backup. This allows slave to restore master data not from scratch, really accelerating slave’s start up. In this case, directory for archives is $HOME/bin/postgres/archive.
max_wal_senders is the number of processes allowed to send WAL data, it cannot be 0, or master cannot send data to slave.
For the time being synchronous_standby_names is not set to avoid master hanging on a slave commit.
It is also necessary to set up your master to authorize connection from slave for replication purposes. In this case, you have to add those lines in pg_hba.conf:
host replication michael 127.0.0.1/32 trust
host replication michael ::1/128 trust
This setup is OK if slave and master are on local host.

In case you want to have a slave with the same configuration parameters as the master, you should copy copy the master’s configuration file. Then modify the following parameters to make it a slave. In postgresql.conf:
hot_standby = on
port = 5433

It is also necessary to add an additional configuration file called recovery.conf in slave’s data folder. You can find a sample of this file in share/ called recovery.conf.sample.
Rename it to recovery.conf and copy it to the slave’s data folder. Then modify the following parameters in it.
standby_mode = on
primary_conninfo = 'host=localhost port=5432 application_name=slave1'
restore_command = 'cp -i $HOME/bin/postgres/archive/%f %p'

primary_conninfo contains all the connection parameters to allow slave to connect to master, for streaming replication purposes. In this parameter, application_name is the name used to identify slave on master.
restore_command contains a shell command that is used to copy archive files. This helps in speeding up slave startup by not having to copy all the WAL from scratch. In this case restore command picks up archive files in the same place where it has been saved by master.

Now, let’s have a look at how to use your master/slave configuration.
Master port is 5432 (PostgreSQL default). Slave port is 5433.

This is the script I used to automatize the whole setup.

#!/bin/bash
#Master has port 5432
#Slave has port 5433
PSQL_FOLDER=$HOME/bin/postgres
PSQL_BIN=$PSQL_FOLDER/bin #Binary folder
PSQL_CONFIG=$PSQL_FOLDER/config #Folder containing all the configuration files
PSQL_MASTER=$PSQL_FOLDER/master #Master data folder
PSQL_SLAVE=$PSQL_FOLDER/slave #Slave data folder
PSQL_ARCHIVE=$PSQL_FOLDER/archive #Archive folder
　
#clean up, take down violently all the processes
killall postgres
rm -rf $PSQL_MASTER $PSQL_SLAVE $PSQL_ARCHIVE
mkdir $PSQL_MASTER $PSQL_SLAVE $PSQL_ARCHIVE
sleep 1
　
#Initialize master
$PSQL_BIN/initdb -D $PSQL_MASTER
cp $PSQL_CONFIG/postgresql.conf.master $PSQL_MASTER/postgresql.conf
cp $PSQL_CONFIG/pg_hba.conf.master $PSQL_MASTER/pg_hba.conf
#Start master
$PSQL_BIN/postgres -D $PSQL_MASTER &
　
#Wait a little before server start up, let it finish initialization
echo "Master started"
sleep 2
　
#Initialize slave
#This is used to start the backup so as slave does not have to recover from
#scratch when being build. It definitely accelerates standby start up
$PSQL_BIN/psql postgres -c "select pg_start_backup('backup')"
cp -pr $PSQL_MASTER/* $PSQL_SLAVE/
$PSQL_BIN/psql postgres -c "select pg_stop_backup()"
sleep 1
echo "Backup performed"
　
#Setup synchronous commit mode on master
echo "synchronous_standby_names='slave1'" >> $PSQL_MASTER/postgresql.conf
$PSQL_BIN/pg_ctl reload -D $PSQL_MASTER
echo "Force master to synchronize mode"
sleep 1
　
#Then finish by copying all the configuration files for slave
cp -r $PSQL_MASTER/* $PSQL_SLAVE
cp $PSQL_CONFIG/postgresql.conf.slave $PSQL_SLAVE/postgresql.conf
cp $PSQL_CONFIG/recovery.conf.slave $PSQL_SLAVE/recovery.conf
rm $PSQL_SLAVE/postmaster.pid
rm -r $PSQL_SLAVE/pg_xlog/*
　
#Start Slave
chmod 700 $PSQL_SLAVE
$PSQL_BIN/postgres -D $PSQL_SLAVE &
echo "Slave started"
exit 0
With this script, you can set up your own master/slave server based on streaming replication.

If you have finished wetting up your environment, let’s check if it is working as planned. Slave can just perform read operations (no DML or DDL), and each write operation performed or master has to be seen on slave.
Let’s first create a database.
michael@lucid-virtual:~/bin/postgres $ ./bin/createdb test
Then create a table on master and fill it with some data.
michael@lucid-virtual:~/bin/postgres $ ./bin/psql test
psql (9.1beta1)
Type "help" for help.
test=# create table aa (a int);
CREATE TABLE
test=# insert into aa values (1);
INSERT 0 1
test=# select * from aa;
a
---
1
(1 row)
And what happens on slave?
michael@lucid-virtual:~/bin/postgres $ ./bin/psql -p 5433 test
psql (9.1beta1)
Type "help" for help.
test=# select * from aa;
a
---
1
(1 row)
test=# insert into aa values (2);
ERROR: cannot execute INSERT in a read-only transaction
This works as expected (Oh, no configuration miss), slave has received all the data from master and cannot perform any write operations.

On master you can check if slave is synchronized with master correctly.
test=# select application_name,state,sync_priority,sync_state from pg_stat_replication;
application_name | state | sync_priority | sync_state
------------------+-----------+---------------+------------
slave1 | streaming | 1 | sync
(1 row)
The keyword sync means that master and slave have synchronized commits.
If it were not the case, this would be in async mode.

The main point of a slave is to take care of the database operations in case master crashes or becomes inoperative.
So let’s imagine master crashes with something like.
kill -9 `ps ux | grep "postgres/master" | cut -d " " -f 3`
Now only slave is running, but it cannot perform any write operation, so fallback can be done by:
echo "standby_mode = off" >> slave/recovery.conf
echo "port = 5432" >> slave/postgresql.conf
./bin/pg_ctl -D slave restart

Setting standby_mode to off makes the slave react as a new master.
After restarting, recovery.conf has its name changed to recovery.done to prevent to reenter to a new backup.
After that a new master is up, based on the old slave. You can connect to it as if it was a normal master, and perform normal operations on it.

If you want to set up your own servers, here are the Standby setup files (tar archive) used in this post. It contains the following files:

  • Configuration files (saved in $PSQL_CONFIG for the automatized process)
    • Master: postgresql.conf file
    • Master: pg_hba.conf file
    • Slave: postgresql.conf file
    • Slave: recovery,conf file
  • Scripts
    • Setup Master/Slave servers
    • Fallback to Slave
    • Posted by Michael at 11:09 am Tagged with: , , , , , , , , , , , , ,
    ©2010-2013 Michael Paquier All content is ©Copyright of Otacoo.com 2010-2013. Privacy Policy - Terms of Use