Le blog de Jean David TECHER, un Réunionnais à Saint-Priest/Lyon

• http://bradmontgomery.net/blog/streaming-replication-in-postgresql-91/
• http://wiki.postgresql.org/wiki/Streaming_Replication
• http://wiki.postgresql.org/wiki/Binary_Replication_Tutorial#5_Minutes_to_Simple_Replication

Ca y est! On fait chauffer la carte bleue, on clique sur le bouton. On imprime...Tout est bon. Je me suis inscris pour le PG Day 2012. Il se déroulera le 7 Juin 2012 à Lyon.

Ce sera l'occasion pour moi de me revoir des têtes que je n'ai pas revu depuis bien longtemps!

Pour s'inscrire: http://www.pgday.fr/inscriptions

Modifications done for revision = 25240 have to be commented out (I think). For more details, please have a look at http://xenbits.xensource.com/hg/staging/xen-unstable.hg/rev/8fd318f89728. Indeed

This patch makes sure the passthrough device belongs to pciback before allow them passthrough to the guest. There are still many other checks missing.

However I am used to assign Video Card using pci-stub (not pciback). Then I need to comment out those modifications in tools/libxl/libxl_pci.c file

 667 /*
 668 static int libxl_pcidev_assignable(libxl_ctx *ctx, libxl_device_pci *pcidev)
 669 {
 670     libxl_device_pci *pcidevs;
 671     int num, i;
 672 
 673     pcidevs = libxl_device_pci_list_assignable(ctx, &num);
 674     for (i = 0; i < num; i++) {
 675         if (pcidevs[i].domain == pcidev->domain &&
 676             pcidevs[i].bus == pcidev->bus &&
 677             pcidevs[i].dev == pcidev->dev &&
 678             pcidevs[i].func == pcidev->func)
 679         {
 680             return 1;
 681         }
 682     }
 683     return 0;
 684 }
 685 */
 686 int libxl__device_pci_add(libxl__gc *gc, uint32_t domid, libxl_device_pci *pcidev, int starting)
 687 {
 688     libxl_ctx *ctx = libxl__gc_owner(gc);
 689     unsigned int orig_vdev, pfunc_mask;
 690     libxl_device_pci *assigned;
 691     int num_assigned, i, rc;
 692     int stubdomid = 0;
 693 
 694     rc = libxl__device_pci_setdefault(gc, pcidev);
 695     if (rc) goto out;
 696 
 697 /*    if (!libxl_pcidev_assignable(ctx, pcidev)) {
 698         LIBXL__LOG(ctx, LIBXL__LOG_ERROR, "PCI device %x:%x:%x.%x is not assignable",
 699                    pcidev->domain, pcidev->bus, pcidev->dev, pcidev->func);
 700         rc = ERROR_FAIL;
 701         goto out;
 702     }
 703 */

Then I can compil Xen as usual.

From what I heard, Xen 4.2 may be frozen in a couple of weeks (first release candidate may appear in a couple of weeks...Sounds good!) That's the reason why I don't include those modifications in NVIDIA patches for the moment.

Streaming Replication (SR) has been enhanced for PPAS 9.1

Notice: in this article

• SR = Streaming Replication
• VM = Virtual Machine. VMs are managed/generated on Xen 4.2 unstable.

NOTES RESULTS SHOWN IN THIS ARTICLE ARE FOR MY PERSONAL USAGE/EXPERIMENT WITH STREAMING REPLICATION. I AM NOT RESPONSIBLE FOR ANY ISSUE THAT YOU MAY ENCOUNTER WITH YOUR OWN MATERIAL

Prerequisites: On Xen...to install

required informations

• mercury is the dom0 (physical machine). A NFS Server has been installed on this dom0 for this tutorial. WAL archives are stored on mercury (NFS4)
• mercury-xen--pg0? are VMs

Xen could be replaced by a VMWare solution (I think).

Prerequisites: Build 4 VMs (Xen 4.2)

All VMs are hosted on Xen 4.2 unstable (changeset 25216:d1b0a8a84ccf). dom0 is Debian Squeeze 6.0.4 with kernel 3.1.10.

VMs Settings

• OS = Ubuntu Lucid 10.04 64 Bits - supported version for EnterpriseDB.
• CPUs = 4
• Disk Space = 20 GB (ext3).
• RAM = 1024MB

Create the VM

1. For the settings above, the configuration file will be.

   root@mercury:~# cat /etc/xen-tools/templates/xen-tools-Ubuntu-lucid.conf 
   lvm = xenvg
   install-method = debootstrap
   size   = 20Gb      # Disk image size.
   memory = 1024    # Memory size
   swap   = 2048Mb    # Swap size
   fs     = ext3     # use the EXT3 filesystem for the disk image.
   dist   = lucid     # Default distribution to install.
   image  = sparse   # Specify sparse vs. full disk images.
   gateway   = 192.168.2.253
   netmask   = 255.255.255.0
   broadcast = 192.168.2.255
   passwd = 1
   accounts = 0
   #kernel      = /boot/vmlinuz-`uname -r`
   #initrd      = /boot/initrd.img-`uname -r`
   #arch = i386
   arch  = amd64
   mirror = http://fr.archive.ubuntu.com/ubuntu
   ext3_options   = noatime,nodiratime,errors=remount-ro
   ext2_options   = noatime,nodiratime,errors=remount-ro
   xfs_options    = defaults
   reiser_options = defaults
   serial_device = hvc0
   disk_device = xvda
   output    = /etc/xen/machines
   extension = .cfg
   role=udev,pygrub
   

2. Use xen-create-image (from xen-tools) to create the VMs

   xen-create-image --force --hostname=mercury-xen-pg01 --ip=192.168.2.231 --config=/etc/xen-tools/templates/xen-tools-Ubuntu-lucid.conf
   xen-create-image --force --hostname=mercury-xen-pg02 --ip=192.168.2.232 --config=/etc/xen-tools/templates/xen-tools-Ubuntu-lucid.conf
   xen-create-image --force --hostname=mercury-xen-pg03 --ip=192.168.2.233 --config=/etc/xen-tools/templates/xen-tools-Ubuntu-lucid.conf
   xen-create-image --force --hostname=mercury-xen-pg04 --ip=192.168.2.235 --config=/etc/xen-tools/templates/xen-tools-Ubuntu-lucid.conf
   

3. Change vCPUS assigned to VM

   sed -i "s:vcpus       = '1':vcpus       = '4':g" /etc/xen/machines/mercury-xen-pg0*

4. Start VMs

   seq 1 4|xargs -i xl create /etc/xen/machines/mercury-xen-pg0{}.cfg
   

Prerequisites before install

echo "kernel.shmmax = 1073741824" >> /etc/sysctl.conf
echo "kernel.shmmni = 4096" >> /etc/sysctl.conf
sysctl -p
apt-get update
apt-get -y install language-pack-fr
dpkg-reconfigure locales
apt-get -y install bzip2 nfs-common util-linux vim

Prerequisites: Install PPAS 9.1 on VMs

• Be sure to have a account on EnterpriseDB Web Site
• Download PPAS 9.1 on dom0 and copy it on VMs (scp ....)

  scp ppasmeta-9.1.2.2-linux-x64.tar.gz 192.168.2.231:
  scp ppasmeta-9.1.2.2-linux-x64.tar.gz 192.168.2.232:
  scp ppasmeta-9.1.2.2-linux-x64.tar.gz 192.168.2.233:
  scp ppasmeta-9.1.2.2-linux-x64.tar.gz 192.168.2.235:
  

• On all VMs
  • Uncompress the file

    tar xvf  ppasmeta-9.1.2.2-linux-x64.tar.gz 

  Install using a configuration file for installation

  cd ppasmeta-9.1.2.2-linux-x64/
  
  cat << _EOF_ > ppas9.1.2_install.cfg
  mode=text
  installer-language=en
  enable-components=dbserver,connectors,infinitecache,edbmtk,pem_client,pgpool,edbplus,replication,xdbrep,pgbouncer
  superaccount=enterprisedb
  servicename=ppas-9.1
  serviceaccount=enterprisedb
  webusername=YOUR EMAIL REGISTRATION ON ENTERPRISEDB WEB SITE
  webpassword=YOUR PASSWORD REGSITRATION ON ENTERPRISEDB WEB SITE
  prefix=/opt/PostgresPlus/9.1AS
  datadir=/var/database/9.1AS/data
  xlogdir=/var/database/9.1AS/data/pg_xlog
  databasemode=oracle
  superpassword=YOUR PASSWORD HERE
  serverport=5432
  create_samples=yes
  server_utilization=66
  workload_profile=mixed
  xdbrepconsole=yes
  xdbpubserver=yes
  xdbsubserver=yes
  xdbpubport=9051
  xdbsubport=9052
  xdbcontroldb=edb
  pgbouncerport=6432
  autostart_pgbouncer=yes
  autostart_pgagent=yes
  install_server_monitor=yes
  _EOF_
  
  ./ppasmeta-9.1.2.2-linux-x64.run --optionfile ppas9.1.2_install.cfg
  
  

  Just reply 'Y' to all questions.
• For sharing WAL archives, on all VMS

  mkdir -pv /var/archives
  
  echo "mercury:/opt/shared/archives/ /var/archives nfs4    _netdev,auto  0  0" >> /etc/fstab
  echo "nfs" > /etc/modules
  

• Change Shell script for user 'enterprisedb' on all VMs

  usermod  -s /bin/bash enterprisedb

• Take a LVM Snapshot of all VMs before going on. Then from dom0 (mercury), just run

  root@mercury:~# seq 1 4 | xargs -i lvcreate -s -n mercury-xen-pg0{}-disk.snapshot00 -L20G /dev/xenvg/mercury-xen-pg0{}-disk
    Logical volume "mercury-xen-pg01-disk.snapshot00" created
    Logical volume "mercury-xen-pg02-disk.snapshot00" created
    Logical volume "mercury-xen-pg03-disk.snapshot00" created
    Logical volume "mercury-xen-pg04-disk.snapshot00" created
  
  

Streaming Replication: Asynchronous Replication...

What we want...

Prepare the master (mercury-xen-pg01)

1. enterprisedb@mercury-xen-pg01:~$ source pgplus_env.sh 
   enterprisedb@mercury-xen-pg01:~$ echo "include 'master.conf'" >> /var/database/9.1AS/data/postgresql.conf 
   enterprisedb@mercury-xen-pg01:~$ cat << _EOF_ > /var/database/9.1AS/data/master.conf
   > archive_command = 'cp -i %p /var/archives/%f && chmod 777 /var/archives/%f'
   > archive_mode = on
   > max_wal_senders = 4
   > wal_keep_segments = 60
   > wal_level = hot_standby
   > _EOF_
   

2. Add following lines in pg_hba.conf

   enterprisedb@mercury-xen-pg01:~$ seq 1 4 | xargs -i echo "host    replication     enterprisedb            192.168.2.23{}/32        trust" >> /var/database/9.1AS/data/pg_hba.conf

   It implies that we get

   enterprisedb@mercury-xen-pg01:~$ sed -i "s:234:235:g" /var/database/9.1AS/data/pg_hba.conf # fix for 235 instead of 234
   enterprisedb@mercury-xen-pg01:~$ tail -n4 /var/database/9.1AS/data/pg_hba.conf  
   host    replication     enterprisedb            192.168.2.231/32        trust
   host    replication     enterprisedb            192.168.2.232/32        trust
   host    replication     enterprisedb            192.168.2.233/32        trust
   host    replication     enterprisedb            192.168.2.235/32        trust
   
   

3. Increate shared_buffers (I forget to add it!)

   echo "shared_buffers = 900MB" >> /var/database/9.1AS/data/master.conf

4. Restart the server

   pg_ctl restart -m immediate

5. We are going to create a small database sr_test. This datase is filled with a few data

   enterprisedb@mercury-xen-pg01:~$ edb-psql
   edb-psql (9.1.2.2)
   Type "help" for help.
   
   edb=# create database sr_test;
   CREATE DATABASE
   edb=# \c sr_test
   You are now connected to database "sr_test" as user "enterprisedb".
   sr_test=# create table test_1(id int4,data text);
   CREATE TABLE
   sr_test=# insert into test_1 select generate_series(1,100000),'text...';
   INSERT 0 100000
   sr_test=# insert into test_1 select generate_series(1,900000),'text...';
   INSERT 0 900000
   sr_test=# UPDATE test_1 set data=data||id;
   UPDATE 1000000
   sr_test=# \q

6. Check for WAL archives

   enterprisedb@mercury-xen-pg01:~$ ls -clt /var/archives/
   total 164040
   -rwxrwxrwx 1 nobody nogroup 16777216 Apr 22  2012 00000001000000000000000B
   -rwxrwxrwx 1 nobody nogroup 16777216 Apr 22  2012 00000001000000000000000A
   -rwxrwxrwx 1 nobody nogroup 16777216 Apr 22  2012 000000010000000000000009
   -rwxrwxrwx 1 nobody nogroup 16777216 Apr 22  2012 000000010000000000000008
   -rwxrwxrwx 1 nobody nogroup 16777216 Apr 22  2012 000000010000000000000007
   -rwxrwxrwx 1 nobody nogroup 16777216 Apr 22  2012 000000010000000000000006
   -rwxrwxrwx 1 nobody nogroup 16777216 Apr 22  2012 000000010000000000000005
   -rwxrwxrwx 1 nobody nogroup 16777216 Apr 22  2012 000000010000000000000004
   -rwxrwxrwx 1 nobody nogroup 16777216 Apr 22  2012 000000010000000000000003
   -rwxrwxrwx 1 nobody nogroup 16777216 Apr 22  2012 000000010000000000000002
   

Prepare the slaves (mercury-xen-pg0X with X=2,3,4)

To do on mercury-xen-pg0X, X=2,3,4

# Stop PostgreSQL server
su - enterprisedb
source pgplus_env.sh
pg_ctl stop -m immediate
exit

# Rebuild PGDATA directory properly
rm -rf /var/database/9.1AS/data
mkdir /var/database/9.1AS/data
chown enterprisedb /var/database/9.1AS/data
chmod 0700 /var/database/9.1AS/data

# Use pg_basebackup
su - enterprisedb
source pgplus_env.sh
pg_basebackup -D $PGDATA -v -Fp -l base-backup -h192.168.2.231 -U$USER -P

# Generate the recovery.conf
cat << _EOF_ > /var/database/9.1AS/data/recovery.conf
restore_command = 'cp /var/archives/%f %p'
standby_mode = on
primary_conninfo = 'host=192.168.2.231 port=5432 application_name=sync_replication_$(hostname | sed -e "s:mercury-xen-pg0::g")'
trigger_file = '/tmp/makeprimary.trigger'  
_EOF_

# Optional: hot_standy.
echo "hot_standby = on" >>  /var/database/9.1AS/data/postgresql.conf

# Start PostgreSQL server
pg_ctl start

Tests it!

• Use pg_stat_replication

  enterprisedb@mercury-xen-pg01:~$ edb-psql        
  edb-psql (9.1.2.2)
  Type "help" for help.
  
  edb=# select * from pg_stat_replication;
   procpid | usesysid |   usename    |  application_name  |  client_addr  | client_hostname | client_port |          backend_start           |   state   | sent_location | write_location | flush_location | replay_location | sync_priority | sync_state
  ---------+----------+--------------+--------------------+---------------+-----------------+-------------+----------------------------------+-----------+---------------+----------------+----------------+-----------------+---------------+-----------
      1067 |       10 | enterprisedb | sync_replication_2 | 192.168.2.232 |                 |       43689 | 22-APR-12 16:50:55.528554 +02:00 | streaming | 0/13E13F60    | 0/13E13F60     | 0/13E13F60     | 0/13E0E4E8      |             0 | async
      1081 |       10 | enterprisedb | sync_replication_3 | 192.168.2.233 |                 |       52367 | 22-APR-12 16:53:33.904462 +02:00 | streaming | 0/13E13F60    | 0/13E13F60     | 0/13E13F60     | 0/13E0E4E8      |             0 | async
      1098 |       10 | enterprisedb | sync_replication_4 | 192.168.2.235 |                 |       42580 | 22-APR-12 16:55:24.762942 +02:00 | streaming | 0/13E13F60    | 0/13E13F60     | 0/13E13F60     | 0/13E0E4E8      |             0 | async
  (3 rows)
  

Streaming replication: Synchonous replication...test with sync priority...

Configure the master with a sync priority...(*)

(*): Keep in mind that this section is only for theory. In practice, we must have a good network (...) to be sure that your favorite slave is always Ok.

• echo "synchronous_standby_names = 'sync_replication_3, sync_replication_2, sync_replication_4'" >> /var/database/9.1AS/data/master.conf
  

• On the master, reload the new configuration

  pg_ctl reload

• Check it

  enterprisedb@mercury-xen-pg01:~$ edb-psql 
  edb-psql (9.1.2.2)
  Type "help" for help.
  
  edb=# select * from pg_stat_replication;
   procpid | usesysid |   usename    |  application_name  |  client_addr  | client_hostname | client_port |          backend_start           |   state   | sent_location | write_location | flush_location | replay_location | sync_priority | sync_state
  ---------+----------+--------------+--------------------+---------------+-----------------+-------------+----------------------------------+-----------+---------------+----------------+----------------+-----------------+---------------+-----------
      1067 |       10 | enterprisedb | sync_replication_2 | 192.168.2.232 |                 |       43689 | 22-APR-12 16:50:55.528554 +02:00 | streaming | 0/13E14250    | 0/13E14250     | 0/13E14250     | 0/13E14250      |             2 | potential
      1081 |       10 | enterprisedb | sync_replication_3 | 192.168.2.233 |                 |       52367 | 22-APR-12 16:53:33.904462 +02:00 | streaming | 0/13E14250    | 0/13E14250     | 0/13E14250     | 0/13E14250      |             1 | sync
      1098 |       10 | enterprisedb | sync_replication_4 | 192.168.2.235 |                 |       42580 | 22-APR-12 16:55:24.762942 +02:00 | streaming | 0/13E14250    | 0/13E14250     | 0/13E14250     | 0/13E14250      |             3 | potential
  (3 rows)
  

  Have a look on column sync_priority!

Streaming replication: Synchonous replication...test when a slave needs to "catch up" after being down ...

Stop the slave mercury-xen-pg04

enterprisedb@mercury-xen-pg04:~$ pg_ctl stop -m immediate
waiting for server to shut down.... done
server stopped

Add data on master now

enterprisedb@mercury-xen-pg01:~$ edb-psql 
edb-psql (9.1.2.2)
Type "help" for help.

edb=# \c sr_test
You are now connected to database "sr_test" as user "enterprisedb".
sr_test=# create table test_3(id int4, data text);
CREATE TABLE
sr_test=# insert into test_3 select generate_series(1,2000000),'text...';
INSERT 0 2000000
sr_test=# 

Restart the slave mercury-xen-pg04

enterprisedb@mercury-xen-pg04:~$ pg_ctl start
server starting
enterprisedb@mercury-xen-pg04:~$ 2012-04-22 15:47:20 GMTLOG:  
	
	** EnterpriseDB Dynamic Tuning Agent ********************************************
	*       System Utilization: 66 %                                                *
	*         Database Version: 9.1.2.2                                             *
	* Operating System Version:                                                     *
	*     Number of Processors: 0                                                   *
	*           Processor Type:                                                     *
	*   Processor Architecture:                                                     *
	*            Database Size: 0.1    GB                                           *
	*                      RAM: 1.0    GB                                           *
	*            Shared Memory: 995    MB                                           *
	*       Max DB Connections: 104                                                 *
	*               Autovacuum: on                                                  *
	*       Autovacuum Naptime: 60   Seconds                                        *
	*            InfiniteCache: off                                                 *
	*    InfiniteCache Servers: 0                                                   *
	*       InfiniteCache Size: 0.000  GB                                           *
	*********************************************************************************
	
2012-04-22 17:47:22 CESTLOG:  loaded library "$libdir/dbms_pipe"
2012-04-22 17:47:22 CESTLOG:  loaded library "$libdir/edb_gen"
2012-04-22 17:47:22 CESTLOG:  loaded library "$libdir/plugins/plugin_debugger"
2012-04-22 17:47:22 CESTLOG:  loaded library "$libdir/plugins/plugin_spl_debugger"

enterprisedb@mercury-xen-pg04:~$ cat /var/database/9.1AS/data/pg_log/enterprisedb-2012-04-22_174724.log 
2012-04-22 17:47:24 CESTLOG:  database system was interrupted while in recovery at log time 2012-04-22 17:39:46 CEST
2012-04-22 17:47:24 CESTHINT:  If this has occurred more than once some data might be corrupted and you might need to choose an earlier recovery target.
2012-04-22 17:47:24 CESTLOG:  entering standby mode
2012-04-22 17:47:24 CESTLOG:  could not stat file "/var/database/9.1AS/data/base/13173/pg_internal.init": No such file or directory
2012-04-22 17:47:24 CESTLOG:  
	
	** EnterpriseDB Dynamic Tuning Agent ********************************************
	*       System Utilization: 66 %                                                *
	*         Database Version: 9.1.2.2                                             *
	* Operating System Version:                                                     *
	*     Number of Processors: 0                                                   *
	*           Processor Type:                                                     *
	*   Processor Architecture:                                                     *
	*            Database Size: 0.2    GB                                           *
	*                      RAM: 1.0    GB                                           *
	*            Shared Memory: 995    MB                                           *
	*       Max DB Connections: 104                                                 *
	*               Autovacuum: on                                                  *
	*       Autovacuum Naptime: 60   Seconds                                        *
	*            InfiniteCache: off                                                 *
	*    InfiniteCache Servers: 0                                                   *
	*       InfiniteCache Size: 0.000  GB                                           *
	*********************************************************************************
	
2012-04-22 17:47:24 CESTLOG:  restored log file "000000010000000000000013" from archive
2012-04-22 17:47:28 CESTLOG:  consistent recovery state reached at 0/13E14400
2012-04-22 17:47:28 CESTLOG:  redo starts at 0/13E14370
2012-04-22 17:47:28 CESTLOG:  database system is ready to accept read only connections
2012-04-22 17:47:28 CESTLOG:  restored log file "000000010000000000000014" from archive
2012-04-22 17:47:29 CESTLOG:  restored log file "000000010000000000000015" from archive
2012-04-22 17:47:29 CESTLOG:  restored log file "000000010000000000000016" from archive
2012-04-22 17:47:30 CESTLOG:  restored log file "000000010000000000000017" from archive
2012-04-22 17:47:30 CESTLOG:  restored log file "000000010000000000000018" from archive
2012-04-22 17:47:31 CESTLOG:  restored log file "000000010000000000000019" from archive
2012-04-22 17:47:32 CESTLOG:  restored log file "00000001000000000000001A" from archive
2012-04-22 17:47:32 CESTLOG:  restored log file "00000001000000000000001B" from archive
cp: cannot stat `/var/archives/00000001000000000000001C': No such file or directory
2012-04-22 17:47:33 CESTLOG:  streaming replication successfully connected to primary
2012-04-22 17:47:40 CESTERROR:  cannot execute UPDATE in a read-only transaction
2012-04-22 17:47:40 CESTSTATEMENT:  UPDATE _edb_scheduler.rrep_sch_pub_TRIGGERS SET TRIGGER_STATE = $1 WHERE (TRIGGER_STATE = $2 OR TRIGGER_STATE = $3) AND NEXT_FIRE_TIME < $4
2012-04-22 17:47:40 CESTERROR:  cannot execute UPDATE in a read-only transaction
2012-04-22 17:47:40 CESTSTATEMENT:  UPDATE _edb_scheduler.rrep_sch_sub_TRIGGERS SET TRIGGER_STATE = $1 WHERE (TRIGGER_STATE = $2 OR TRIGGER_STATE = $3) AND NEXT_FIRE_TIME < $4
enterprisedb@mercury-xen-pg04:~$ edb-psql
edb-psql (9.1.2.2)
Type "help" for help.

edb=# \c sr_test
You are now connected to database "sr_test" as user "enterprisedb".
sr_test=# select count(*) from test_3;
  count  
---------
 2000000
(1 row)

Streaming replication: Synchonous replication...test with failover...

Simulate the failover

On dom0; just destroy the VM mercury-xen-pg01

root@mercury:~# xl list
Name                                        ID   Mem VCPUs	State	Time(s)
Domain-0                                     0  1024     4     r-----     221.4
mercury-xen-pg04                            27  1024     4     -b----     110.3
mercury-xen-pg01                            28  1024     4     -b----     212.4
mercury-xen-pg02                            29  1024     4     -b----      22.3
mercury-xen-pg03                            30  1024     4     -b----     107.1
root@mercury:~# xl destroy 28
root@mercury:~# xl list
Name                                        ID   Mem VCPUs	State	Time(s)
Domain-0                                     0  1024     4     r-----     222.0
mercury-xen-pg04                            27  1024     4     -b----     110.4
mercury-xen-pg02                            29  1024     4     -b----      22.3
mercury-xen-pg03                            30  1024     4     -b----     107.2

Promote mercury-xen-pg03 as new master

Just generate the trigger file

enterprisedb@mercury-xen-pg03:~$ cat /var/database/9.1AS/data/recovery.conf 
restore_command = 'cp /var/archives/%f %p'
standby_mode = on
primary_conninfo = 'host=192.168.2.231 port=5432 application_name=sync_replication_3'
trigger_file = '/tmp/makeprimary.trigger'  
enterprisedb@mercury-xen-pg03:~$ touch /tmp/makeprimary.trigger
enterprisedb@mercury-xen-pg03:~$ ls -clt /var/database/9.1AS/data/pg_log/|head -n4
total 112
-rw------- 1 enterprisedb enterprisedb 88536 Apr 22 18:06 enterprisedb-2012-04-22_165330.log
-rw------- 1 enterprisedb enterprisedb  2685 Apr 22 16:53 enterprisedb-2012-04-22_150027.log
-rw------- 1 enterprisedb enterprisedb  4327 Apr 22 16:53 enterprisedb-2012-04-22_154915.log

On slaves (mercury-xen-pg02 and mercury-xen-pg04)

enterprisedb@mercury-xen-pg02:~$ echo "recovery_target_timeline = 'latest'" >> /var/database/9.1AS/data/recovery.conf 
enterprisedb@mercury-xen-pg02:~$ sed -i "s:231:233:g" /var/database/9.1AS/data/recovery.conf 
enterprisedb@mercury-xen-pg02:~$ pg_ctl restart -m fast

Check on master

enterprisedb@mercury-xen-pg03:~$ edb-psql
edb-psql (9.1.2.2)
Type "help" for help.

edb=# select * from pg_stat_replication;
 procpid | usesysid |   usename    |  application_name  |  client_addr  | client_hostname | client_port |          backend_start           |   state   | sent_location | write_location | flush_location | replay_location | sync_priority | sync_state 
---------+----------+--------------+--------------------+---------------+-----------------+-------------+----------------------------------+-----------+---------------+----------------+----------------+-----------------+---------------+------------
     877 |       10 | enterprisedb | sync_replication_2 | 192.168.2.232 |                 |       55088 | 22-APR-12 18:12:21.670696 +02:00 | streaming | 0/1C7DF510    | 0/1C7DF510     | 0/1C7DF510     | 0/1C7DF510      |             0 | async
     878 |       10 | enterprisedb | sync_replication_4 | 192.168.2.235 |                 |       60516 | 22-APR-12 18:12:40.151349 +02:00 | streaming | 0/1C7DF510    | 0/1C7DF510     | 0/1C7DF510     | 0/1C7DF510      |             0 | async
(2 rows)

edb=# 

Add data on new master (mercury-xen-pg03)

enterprisedb@mercury-xen-pg03:~$ edb-psql
edb-psql (9.1.2.2)
Type "help" for help.

edb=# \c sr_test
You are now connected to database "sr_test" as user "enterprisedb".
sr_test=# create table test_4(id int4,data text);
CREATE TABLE
sr_test=# insert into test_4 select generate_series(1,50000),'text...';
INSERT 0 50000
sr_test=# UPDATE test_4 set data=data||id;
UPDATE 50000
sr_test=#

Check on slaves

1. On mercury-xen-pg02

   enterprisedb@mercury-xen-pg02:~$ edb-psql
   edb-psql (9.1.2.2)
   Type "help" for help.
   
   edb=# \c sr_test
   You are now connected to database "sr_test" as user "enterprisedb".
   sr_test=# select * from test_4 limit 10;
    id |   data    
   ----+-----------
     1 | text...1
     2 | text...2
     3 | text...3
     4 | text...4
     5 | text...5
     6 | text...6
     7 | text...7
     8 | text...8
     9 | text...9
    10 | text...10
   (10 rows)
   

2. On mercury-xen-pg04

   enterprisedb@mercury-xen-pg04:~$ edb-psql
   edb-psql (9.1.2.2)
   Type "help" for help.
   
   edb=# \c sr_test
   You are now connected to database "sr_test" as user "enterprisedb".
   sr_test=# select * from test_4 limit 10;
    id |   data    
   ----+-----------
     1 | text...1
     2 | text...2
     3 | text...3
     4 | text...4
     5 | text...5
     6 | text...6
     7 | text...7
     8 | text...8
     9 | text...9
    10 | text...10
   (10 rows)
   

SOURCES FOR THIS ARTICLE

• http://vibhork.blogspot.fr/2011/10/asynchronoussynchronous-streaming.html