1. Install postgres di primary dan standby seperti biasa :
#./configure ,-------------> ./configure --with-openssl --with-tcl --enable-thread-safety
# make
# make install
# useradd postgres ----gunakan useradd
# mkdir /usr/local/pgsql/data
# chown postgres /usr/local/pgsql/data
# su - postgres
2. tentukan ip mis primary : 192.168.5.11
standby : 192.168.5.16
2. buat initial database di primary sepertibiasa menggunakan :
#/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
3. edit postgresql.conf di primary
listen_addresses = '*'
4. edit pg_hba.conf di primary
host replication postgres 192.168.5.16/32 trust
5. buat direktory untuk menyimpan segmen logfile ( untuk postgresql.conf di archive_command )
buat di primary dan standby:
mkdir /home/pgsql/data/archive
mkdir /home/pgsql/data/pg_log # tambahan untuk menyimpan log
6. edit lagi postgresql.conf
wal_level = hot_standby
archive_mode = on
archive_command = 'cp %p /home/pgsql/data/archive/%f'
max_wal_senders = 5
wal_keep_segments = 6000
---------> cek untuk primary postgresql.conf di hot_standby=off
7. start postgres di primary
============================================================================================
8. buat sama postgresql.conf di standby dengan postgresql.conf di primary ( bisa di copykan )
supaya bisa bertindak sebagai master setelah failover ( coba cek di archive command sesuaikan path nya)
9. masuk ke consol baru dengan user postgres (copy data primary ke standby ) di primary
#su - postgres
$/usr/local/pgsql/bin/psql -c "SELECT pg_start_backup('label',true)"
$rsync -a -v -e ssh /home/pgsql/data/ 192.168.5.16:/home/pgsql/data/ --exclude postmaster.pid -->ada 2 path perhatikan
$ /usr/local/pgsql/bin/psql -c "SELECT pg_stop_backup()"
10.edit postgresql.conf di slave :
hot_standby = on
11.buat file recovery.conf di slave dengan mengcopy dari /usr/local/pgsql/share/recovery.conf.sample
#cp /usr/local/pgsql/share/recovery.conf.sample /home/pgsql/data
12.edit recovery.conf tersebut :
restore_command = 'cp /home/pgsql/data/archive/%f "%p"'
standby_mode = 'on'
primary_conninfo = 'host=192.168.5.11 port=5432 user=postgres' #ip primary
trigger_file = '/home/pgsql/data/failover'
13.start service postgresql di standby
$/usr/local/pgsql/bin/postgres -D /home/pgsql/data >logfile 2>&1 &
/usr/local/pgsql/bin/postgres -D /opt/db/data >/opt/db/data/logfile 2>&1 &
14.bandingkan current WAL write di primary dengan receive di standby:
di standby :
$/usr/local/pgsql/bin/psql -c "SELECT pg_last_xlog_receive_location()"
pg_last_xlog_receive_location
-------------------------------
0/5000078
(1 row)
di primary :
$/usr/local/pgsql/bin/psql -c "SELECT pg_current_xlog_location()"
pg_current_xlog_location
--------------------------
0/5000078
(1 row)
15.cek progress streaming dengan command :
di primary :
$ ps -ef | grep sender
postgres 6879 6831 0 10:31 ? 00:00:00 postgres: wal receiver process streaming 0/5000078
di standby
$ ps -ef | grep receiver
postgres 6878 6872 1 10:31 ? 00:00:01 postgres: wal receiver process streaming 0/5000078
How to failover:
* touch /tmp/pgsql.trigger ... start querying to failover server
How to restart replication after failover :
* remake a fresh backup. master doesn't have to be stopped
How to restart replication after standby fails:
* restart postgres in standby after eliminating cause of failure
How to disconnect standby from primary:
* touch /tmp/pgsql.trigger in slave while primary running.
How to re-sync standby after isolation:
* shutdown standby, make a fresh backup as per above
------------------------------------------------------------------
monitoring : http://www.postgresql.org/docs/9.0/static/warm-standby.html
25.2.5.2. Monitoring
An important health indicator of streaming replication is the amount of WAL records generated in the primary,
but not yet applied in the standby. You can calculate this lag by comparing the current WAL write location on
the primary with the last WAL location received by the standby. They can be retrieved using pg_current_xlog_location
on the primary and the pg_last_xlog_receive_location on the standby, respectively (see Table 9-56 and Table 9-57 for details).
The last WAL receive location in the standby is also displayed in the process status of the WAL receiver process,
displayed using the ps command (see Section 27.1 for details).
-----------------------------------------------------------------
test :
1.kalau kabel diputus ( standby ) kira2 10 jam terus sisi primary di insert/delete data
dan create db, kemudian kabel di pasang lagi maka terjadi streaming data di standby ,
dan..... proses create db, insert/delete data juga terjadi di standby ( ada sinkonisasi data )
2.kalau kabel di putus di sisi primary, dan di standby di insert/delete data atau create db
maka proses ini tidak bisa dilakukan, muncul " cannot execute xxxxxxxxxxx in a read-only transaction"
dan kalau kabel disambung lagi maka proses transacsi tetap bisa dilakukan di primary.
3.proses pemutusan koneksi dapat dilakukan dengan menggunakan triger di standby : membuat file failover dengan
path seperti yang ada di recovery.conf : trigger_file = '/home/pgsql/data2/failover '
berikan perintah : touch /home/pgsql/dqtq2/failover
setelah nya maka standby bertindak sebagai master dan bisa digunakan untuk transaksi langsung
perintah insert/delete data atau create db bisa dilakukan.
4.jika server standby di matikan mendadak ( tekan tombol power lama ),di sisi primary diberikan transaksi
( insert2 dan create db ) kemudian standby dinyalakan lagi
setelah di ping bisa startup service postgre nya (/usr/local/pgsql/bin/pg_ctl -D /home/db/postgres/data2 start )
-------------------------------------------proses startup standby ----------------------
postgres@<===>:~$ /usr/local/pgsql/bin/pg_ctl -D /home/pgsql/data2 start
pg_ctl: another server might be running; trying to start server anyway
LOG: database system was interrupted while in recovery at log time 2011-01-20 10:12:29 WIT
HINT: If this has occurred more than once some data might be corrupted and you might need to choose an earlier recovery target.
LOG: entering standby mode
cp: cannot stat `/home/pgsql/data2/archive/00000001000000000000000C': No such file or directory
LOG: consistent recovery state reached at 0/C008260
LOG: redo starts at 0/C0081D0
LOG: record with zero length at 0/C008260
LOG: database system is ready to accept read only connections
cp: cannot stat `/home/pgsql/data2/archive/00000001000000000000000C': No such file or directory
LOG: streaming replication successfully connected to primary
server starting
postgres@<===>:~$ ps -ax
-------------------------------------------------------------------------------------------
dan di cek standby ternyata terjadi sinkronisasi ( data yang transaksi di primari juga ada di standby ).
5.jika sisi primary di matikan mendadak ( tekan tombol power ) sisi standby tetap tidak bisa buat transaksi
terdapat pesan " cannot execute INSERT in a read-only transaction "
dan jika primary di nyalakan lagi dan service postgres dinyalakan belum terjadi streaming lagi.
streaming bisa dilakukan lagi bila standby di stop service postgre nya dan di startup lagi servicenya ( restart service )
6.jika triger failover di buat maka standby akan menjadi master. selanjutnya master ini disebut mastersy
dan master awal bisa dijadikan slave selanjutnya slave ini disebut standbym
standbym buat initial baru ( direktory baru buat data base ) dengan initdb
proses selanjutnya sama dengan diatas untuk membuat standby biasa hanya saja perlu di ubah untuk path dan ip
yang perlu diperhatikan :
akan ada direktory failover ( berasal dr file untuk triger failover di mastersy ) direktory ini dihapus dulu.
7.jika ada satu primary dan 2 standby, dan satu standby di trigger file fileover maka
proses streaming replikasi akan putus dan stanby yg putus seolah2 jadi master sendiri /independent, dan proses
primary dan standbya masih berjalan.\
8. jika ada satu primary dan 2 standby kemudian service primari di stop maka di kedua client akan ada message :
cp: cannot stat `/home/pgsql/data2/archive/000000020000000000000029': No such file or directory
cp: cannot stat `/home/pgsql/data2/archive/000000020000000000000029': No such file or directory
FATAL: could not connect to the primary server: could not connect to server: Connection refused
Is the server running on host "192.168.5.16" and accepting
TCP/IP connections on port 5432?
cp: cannot stat `/home/pgsql/data2/archive/000000020000000000000029': No such file or directory
cp: cannot stat `/home/pgsql/data2/archive/000000020000000000000029': No such file or directory
FATAL: could not connect to the primary server: could not connect to server: Connection refused
Is the server running on host "192.168.5.16" and accepting
TCP/IP connections on port 5432?
cp: cannot stat `/home/pgsql/data2/archive/000000020000000000000029': No such file or directory
cp: cannot stat `/home/pgsql/data2/archive/000000020000000000000029': No such file or directory
FATAL: could not connect to the primary server: could not connect to server: Connection refused
Is the server running on host "192.168.5.16" and accepting
TCP/IP connections on port 5432?
message iniberulang2 sampai service primary aktive lagi
9.jika service primary masih nyala dan tiba2 primary mati mendadak...maka standby masih aktif dan masih bisa dilihat
tapi tidak bisa buat transaksi.
10. Ada 1 primary dan 2 standby,kemudian primary mati mendadak, salah satu dari slave kemudian di jadikan primary
dengan memberikan file triger (failover) saat itu standby yang lain masih dlm keadaan menunggu sampai primary utama aktif( nyala).
saat standby dijadikan primary standby yang lain tidak bisa langsung dijadikan standby terhadap primary baru.
matikan dulu service postgres di standby kemudian :
dengan mengedit :
recovery.conf di standby pada bagian :
recovery_target_timeline = 'latest'
primary_conninfo = 'host=192.168.5.16 port=5432 user=postgres '
restore_command = 'cp /home/pgsql/cobadb/archive/%f "%p"' ----> sesuaikan pathnya
kemudian lakukan copy dr primary baru ke standby ( 2 file terakhir di archive termasuk history)
/usr/local/pgsql/bin/psql -c "SELECT pg_start_backup('label',true)"
rsync -a -v -e ssh /home/pgsql/cobadb/archive/000000020000000000000036 192.168.5.11:/home/pgsql/cobadb/archive/ -->ada 2 path perhatikan
rsync -a -v -e ssh /home/pgsql/cobadb/archive/00000003.history 192.168.5.11:/home/pgsql/cobadb/archive/
/usr/local/pgsql/bin/psql -c "SELECT pg_stop_backup()"
setelah itu aktifkan service postgres maka "standby akan ready to accept connections"
Tidak ada komentar:
Posting Komentar