Replikasi Database master-slave pada postgres

yuhu dah lama uci gk mampir di sini bikin tuliasan di sini lagi lumayan banyak kegiatan di kampus (alamak blom lulus-lulus damn) oke deh uci kebetulan lagi oprek postgresql di sini uchi bikin master-slave pada data base postgres (server database nya hehehe)
asumsi kan kita punya dua server berbeda dengan masing-masing ip sbb:
dbhost1=192.168.0.1 host:dbhost1
dbhost2=192.168.0.1 host:dbhost2
yuk kita lanjut ke instalasi nya

lakukan di kedua server (di dbhost1 dab dbhost2)

1.ambil source di https://yum.postgresql.org/repopackages.php
ambil yang versi 9.3 silahkan sesuaikan dengan os anda 32 atau 64 bit
curl -O https://download.postgresql.org/pub/repos/yum/9.3/redhat/rhel-7-x86_64/pgdg-centos93-9.3-3.noarch.rpm
2.install posgresql
rpm -ivh pgdg-centos93-9.3-3.noarch.rpm
yum list postgres* <<< cek versi postgresql yang ada pada repository
yum install posgrest-server << mulai instalasi posgresql
setelah instalasi jalankan perintah berikut:
/usr/pgsql-9.3/bin/postgresql93-setup initdb << inisialisai database
chkconfig postgresql-9.3 on <<< configurasi supaya bisa boot pas restart
service postgresql-9.3 start<< start posgress
su - postgres << cek masuk ke posgres
psql>> masuk ke dalam database posgres

config pada dbhost1 sebagai master

1.edit file [root@dlp ~]# vi /var/lib/pgsql/data/postgresql.conf

line 59: uncomment and change

listen_addresses = ‘*’

line 165: uncomment and change

wal_level = hot_standby

line 168: uncomment and change

on ⇒ sync

remote_write ⇒ memory sync

local ⇒ slave is asynchronous

off ⇒ asynchronous

synchronous_commit = local

line 194: uncomment and change (enable archive_mode)

archive_mode = on

line 196: uncomment and change (command to get archives)

archive_command = ‘cp %p /var/lib/pgsql/archive/%f’

line 212: uncomment and change (slave servers + 1)

max_wal_senders = 2

line 214: uncomment and change

wal_keep_segments = 10

line 221: uncomment and change (any name you like)

synchronous_standby_names = ‘slave01’
2 edit file vi /var/lib/pgsql/data/pg_hba.conf

add to the end

host replication [replication user] [allowed IP addresses] password

host replication replica 192.168.0.2/24 trust
3 create a user for replication
[root@dlp ~]# su - postgres
-bash-4.2$ createuser --replication -P replica
Enter password for new role:
Enter it again:
4 setting firewal open port 5432
firewall-cmd --permanent --add-port=5432/tcp
firewall-cmd --reload
5.buat file archive
mkdir /var/lib/pgsql/archive
chmod -R 700 /var/lib/pgsql/archive
chown -R postgres:posgres /var/lib/pgsql/archive

config dbhost2 sebagai slave
matikan dolo service posgres
service postgresql-9.3 stop
su - postgres

copy dan delet isi dalam foder data yang nanti nya akan kita isi kembali denga copy file dari dbhost1(master server)
cp -R /var/lib/pgsql/9.3/data /var/lib/pgsql/9.3/data.ori
rm -rf /var/lib/pgsql/9.3/data/*

get backup from Master Server

pg_basebackup -h 192.68.0.1 -U replica -D /var/lib/pgsql/data -P --xlog
Password: # “replica” user’s password

3.config file vi /var/lib/pgsql/data/postgresql.conf

line 230: uncomment and change

hot_standby = on
4.buat file recovery.conf
touch /var/lib/pgsql/9.3/recovery.conf
isi kan sbg berikut
restore_command = ‘scp 192.168.0.1:/var/lib/pgsql/archive/%f %p’
standby_mode = on
primary_conninfo = ‘host=192.168.0.1 port=5432 user=replica password=password application_name=slave01’
5.buat folder archive
mkdir archive
chmod -R 700 archive
chown postgres:postgres archive

hidupkan service postgres
service postgresql-9.3 start
ok konfig di kedua server sudah selesai
jalan skrip berikut pada server master
-bash-4.2$ psql -c “select application_name, state, sync_priority, sync_state from pg_stat_replication;”

maka output akan sperti ini
application_name | state | sync_priority | sync_state
------------------±----------±--------------±-----------
slave01 | streaming | 1 | sync
(1 row)

test replikasi
buat database di master sbb
CREATE TABLE popsicles (
pop_id serial PRIMARY KEY,
flavor varchar (50) NOT NULL,
amount int NOT NULL,
size varchar (10) check (size in (‘small’, ‘normal’, ‘large’))
);

cek db di master dengan perintah berikut
\dt
postgres=# \dt
List of relations
Schema | Name | Type | Owner
--------±----------±------±---------
public | popsicles | table | postgres
(1 row)
cek di server slave database yang kita buat dengan mengetikan perintah berikut
postgres-# \dt
List of relations
Schema | Name | Type | Owner
--------±----------±------±---------
public | popsicles | table | postgres
(1 row)
apa bila ada berarti anda berhasi membuat replikasi master dan slave di posgresql versi 9.3

:meluncur :meluncur :meluncur :meluncur :meluncur :meluncur :meluncur :sip_dc: :sip_dc: :sip_dc: :sip_dc: :meluncur :meluncur :meluncur :meluncur :meluncur :meluncur :meluncur :meluncur

mantep om :asik

Berhubung ane ga ngerti, kasi rep+ aja :mantap:
btw kalo trid nya dirapihin pasti tambah keren zmo :smangat: