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

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


1 2 >

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

mercredi 18 avril 2012

Useful links for Streaming Replication with 9.1