Synchronous_streaming_replication

• Basics
  • Master settings
  • Slave settings
  • Fallback to slave
  • Extra notes
• Synchronous streaming replication for Postgres-XC

Basics

This part is related to all the background necessary to setup a synchronous replicated server with PostgreSQL. Before beginning the settings, it is essential to set up 2 data repositories for both slave and master.

Master settings

• postgresql.conf

  wal_level = hot_standby
  archive_mode = on
  archive_command = 'cp -i %p $HOME/bin/postgres/archive/%f'
  max_wal_senders = 10

OK, archive_command depends on where you want to locate WAL archives

• pg_hba.conf, necessary to allow slave contact master via replication mode

  host replication michael 127.0.0.1/32 trust
  host replication michael ::1/128 trust

Port of master is in this case 5432, so the default value.

Slave settings

• Copy the master configuration file postgresql.conf, then modify the following lines to make it a slave.

  hot_standby = on
  port = 5433

• recovery.conf, located in share/ as recovery.conf.sample

  standby_mode = on
  primary_conninfo = 'host=localhost port=5432'
  restore_command = 'cp -i %p $HOME/bin/postgres/archive/%f'

restore_command should call archives at the same place as archive_command for postgresql.conf.

Fallback to slave

If master dies, the following lines permit to fallback easily to slave.

 echo "standby_mode = off" >> slave/recovery.conf
 echo "port = 5432" >> slave/postgresql.conf
 ./bin/pg_ctl -D slave restart

This basically just changes the slave port to the former port of master and makes the slave react as a master node by setting standby_mode at off. After restart is completed, reconvery.conf is renamed recovery.done to avoid recovery to be done several times.

Extra notes

 pg_start_backup('backup')

This can be used to start the creation of archives backup on disk. This can accelerate the slave creation by not having to copy all the data from master.

 pg_stop_backup()

This can be used to stop on-the-way the backup archiver.

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.