dimanche 22 avril 2012
Streaming Replication: failover with several slaves
Par david techer, dimanche 22 avril 2012 à 14:31 :: EnterpriseDB
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
|
Prerequisites: On Xen...to install
mercuryis 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
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
- 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
- Use
xen-create-image(fromxen-tools) to create the VMsxen-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
- Change vCPUS assigned to VM
sed -i "s:vcpus = '1':vcpus = '4':g" /etc/xen/machines/mercury-xen-pg0*
- 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. - Uncompress the file
mkdir -pv /var/archives echo "mercury:/opt/shared/archives/ /var/archives nfs4 _netdev,auto 0 0" >> /etc/fstab echo "nfs" > /etc/modules
usermod -s /bin/bash enterprisedb
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)
-
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_
- Add following lines in
pg_hba.confenterprisedb@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.confIt implies that we getenterprisedb@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
- Increate shared_buffers (I forget to add it!)
echo "shared_buffers = 900MB" >> /var/database/9.1AS/data/master.conf
- Restart the server
pg_ctl restart -m immediate
- 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
- 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_replicationenterprisedb@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 columnsync_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 VMmercury-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 fileenterprisedb@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
- 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)
- 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)
Here is a quick summary for this error
