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

Aller au contenu | Aller au menu | Aller à la recherche


< 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 >

mercredi 13 juin 2012

Liens pour streaming replication

  • 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

lundi 30 avril 2012

PostgreSQL: PG Day 7 Juin 2012 à Lyon (Je me suis inscris!)

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

samedi 28 avril 2012

Xen 4.2 VGA PassThrough NVIDIA: reverse revision 25240 to build Xen from revision >= 25240.

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.

dimanche 22 avril 2012

Streaming Replication: failover with several slaves

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
  1. RESULTS SHOWN IN THIS ARTICLE ARE FOR MY PERSONAL USAGE/EXPERIMENT WITH STREAMING REPLICATION.
  2. 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