{"id":119,"date":"2016-04-20T16:20:14","date_gmt":"2016-04-20T16:20:14","guid":{"rendered":"http:\/\/kloxo.web.id\/?p=119"},"modified":"2016-04-21T01:48:33","modified_gmt":"2016-04-21T01:48:33","slug":"spse-master-slave-streaming-replication-2","status":"publish","type":"post","link":"https:\/\/kloxo.web.id\/?p=119","title":{"rendered":"SPSE Master-Slave Streaming Replication #2"},"content":{"rendered":"

Melanjutkan catatan PostgreSQL 8.4 Upgrade ke 9.5 (SPSE Master-Slave Streaming Replication #1)<\/a>, pada langkah ini kita mulai mengaktifkan fitur hot_standy dan streaming replication pada Postgresql 9.5.<\/p>\n

Persiapan awal adalah menyiapkan 2 server yang sudah terinstall Postgresql 9.5, sudah dikonfigurasi dan berjalan dengan baik seperti petunjuk SPSE Master-Slave Streaming Replication #1.<\/p>\n

I. Konfigurasi Master Server<\/p>\n

Master-server adalah server utama yang diakses oleh pengguna, database dari server ini akan direplikasi dan dikirimkan secara realtime ke Slave-server<\/p>\n

1. nano \/var\/lib\/pgsql\/9.5\/data\/postgresql.conf
\n cari dan sesuaikan menjadi baris berikut :
\n listen_addresses = ‘localhost,192.168.100.194’
\n ##sesuaikan 192.168.100.194 dengan ip master-server anda
\n wal_level = hot_standby
\n max_wal_senders = 1
\n wal_keep_segments = 50
\n2. nano \/var\/lib\/pgsql\/9.5\/data\/pg_hba.conf
\n tambahkan pada akhir file baris berikut :
\n host replication postgres 192.168.100.195\/32 trust
\n ##sesuaikan 192.168.100.195 dengan ip slave-server anda
\n3. service postgresql-9.5 restart
\n4. persiapan data awal yang akan direplikasi dan dicopy ke slave-server :
\n su – postgres
\n psql -c “SELECT pg_start_backup(‘replbackup’);”
\n tar cfP \/tmp\/db_file_backup.tar \/var\/lib\/pgsql\/9.5\/data
\n psql -c “SELECT pg_stop_backup();”
\n exit
\n5. scp \/tmp\/db_file_backup.tar root@192.168.100.195:\/tmp\/<\/p>\n

Sampai pada langkah ini, persiapan replikasi di master-server sudah selesai.<\/p>\n

II. Konfigurasi Slave Server<\/p>\n

Slave-server adalah server cadangan dan tidak diakses langsung oleh pengguna, database dari Master-server direplikasi secara realtime ke Slave-server ini.<\/p>\n

1. service postgresql-9.5 stop
\n2. mv \/var\/lib\/pgsql\/9.5\/data\/ \/var\/lib\/pgsql\/9.5\/data.old
\n3. tar xvfP \/tmp\/db_file_backup.tar
\n4. rm -f \/var\/lib\/pgsql\/9.5\/data\/postmaster.pid
\n5. nano \/var\/lib\/pgsql\/9.5\/data\/postgresql.conf
\n cari dan sesuaikan menjadi baris berikut :
\n hot_standby = on
\n6. cp \/usr\/pgsql-9.5\/share\/recovery.conf.sample \/var\/lib\/pgsql\/9.5\/data\/recovery.conf
\n7. nano \/var\/lib\/pgsql\/9.5\/data\/recovery.conf
\n cari dan sesuaikan menjadi baris berikut :
\n standby_mode = on
\n primary_conninfo = ‘host=192.168.100.194 port=5432’
\n #sesuaikan ip 192.168.100.194 dengan ip master-server anda
\n8. chown postgres.postgres \/var\/lib\/pgsql\/9.5\/data\/recovery.conf
\n9. service postgresql-9.5 start<\/p>\n

Sampai sini selesai sudah catatan Master-Slave Streaming Replication #2, periksa log untuk memastikan proses replication berjalan sukses :<\/p>\n

tail -f \/var\/lib\/pgsql\/9.5\/data\/pg_log\/postgresql-[hari].log
\n—database system is ready to accept read only connections—<\/p>\n

pengujian replikasi sudah berjalan dengan baik (update 21\/04\/206) :
\n1. di master-server :
\n su – postgres
\n psql
\n CREATE TABLE rep_test (test varchar(40));
\n INSERT INTO rep_test VALUES (‘data satu’);
\n INSERT INTO rep_test VALUES (‘data dua’);
\n INSERT INTO rep_test VALUES (‘lalala’);
\n INSERT INTO rep_test VALUES (‘hello there’);
\n INSERT INTO rep_test VALUES (‘blahblah’);
\n \\q
\n exit<\/p>\n

2. di slave-server :
\n su – postgres
\n psql
\n SELECT * FROM rep_test;
\n #akan tampil data seperti yang diisikan ke database master-server <\/p>\n

Database slave-server sudah siap, lanjutkan dengan melakukan instalasi SPSE di slave-server dengan mengacu pada tuts Instalasi SPSE 3.6 SP2 dan SPSE 4.0<\/a> sehingga jika diperlukan slave-server siap diaktifkan mengganti tugas master-server.<\/p>\n

Untuk full-duplikasi SPSE, juga diperlukan folder \/home\/file\/file_production dilakukan rsync berkala (misalnya setiap 10 menit) sehingga konsistensi antara master-server dan slave-server terjaga.<\/p>\n

Selamat Mencoba D.W.Y.O.R…<\/p>\n

(referensi : https:\/\/opensourcedbms.com\/dbms\/setup-replication-with-postgres-9-2-on-centos-6redhat-el6fedora\/, https:\/\/www.digitalocean.com\/community\/tutorials\/how-to-set-up-master-slave-replication-on-postgresql-on-an-ubuntu-12-04-vps)<\/p>\n","protected":false},"excerpt":{"rendered":"

Melanjutkan catatan PostgreSQL 8.4 Upgrade ke 9.5 (SPSE Master-Slave Streaming Replication #1), pada langkah ini kita mulai mengaktifkan fitur hot_standy dan streaming replication pada Postgresql 9.5. Persiapan awal adalah menyiapkan 2 server yang sudah terinstall Postgresql 9.5, sudah dikonfigurasi dan berjalan dengan baik seperti petunjuk SPSE Master-Slave Streaming Replication #1. I. Konfigurasi Master Server Master-server […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-119","post","type-post","status-publish","format-standard","hentry","category-linux-server"],"_links":{"self":[{"href":"https:\/\/kloxo.web.id\/index.php?rest_route=\/wp\/v2\/posts\/119","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/kloxo.web.id\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kloxo.web.id\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kloxo.web.id\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/kloxo.web.id\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=119"}],"version-history":[{"count":9,"href":"https:\/\/kloxo.web.id\/index.php?rest_route=\/wp\/v2\/posts\/119\/revisions"}],"predecessor-version":[{"id":129,"href":"https:\/\/kloxo.web.id\/index.php?rest_route=\/wp\/v2\/posts\/119\/revisions\/129"}],"wp:attachment":[{"href":"https:\/\/kloxo.web.id\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=119"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kloxo.web.id\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=119"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kloxo.web.id\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=119"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}