Postgres master & slave

Login postgres

sudo -i -u postgres
psql

Buat user & database

-- buat user
CREATE USER haidhi WITH PASSWORD 'supersecret';

-- kasih privilege
GRANT ALL PRIVILEGES ON DATABASE mydb TO haidhi;

-- buat database
CREATE DATABASE mydb OWNER haidhi;

Bikin replica

-- buat user replica
CREATE USER replicator REPLICATION LOGIN ENCRYPTED PASSWORD 'replicaPass';
ALTER ROLE replicator WITH REPLICATION;

-- konfirmasi 
\du replicator

Edit config postgres di VM master

sudo nano /etc/postgresql/15/main/postgresql.conf

# cari & ubah
listen_addresses = '*'
wal_level = replica
max_wal_senders = 10
wal_keep_size = 64

Tambah di /etc/postgresql/16/main/pg_hba.conf master

# Replication access
host    replication    replicator    <SLAVE_IP>/32    md5
host    all            replicator    <SLAVE_IP>/32    md5

Tambah di /etc/postgresql/16/main/pg_hba.conf slave

host    replication     replicator     < MASTER_IP>192.168.83.0/24         md5

Download Backup dari master (jalanin di slave)

sudo systemctl stop postgresql
sudo rm -rf /var/lib/postgresql/16/main/*
sudo chown -R postgres:postgres /var/lib/postgresql/16/main
sudo -i -u postgres
pg_basebackup -h <MASTER_IP> -D /var/lib/postgresql/16/main -U replicator -P --wal-method=stream

Di folder data slave (/var/lib/postgresql/16/main) harus ada file standby.signal. Kalo gak ada bikin sendiri

touch /var/lib/postgresql/16/main/standby.signal

Masih di slave, Harus ada entry primary_conninfo hasil dari pg_basebackup -R. Isinya kira² gini:

primary_conninfo = 'host=<MASTER_IP> port=5432 user=replicator password=replicaPass'

Cek di master

SELECT pid, client_addr, state, sync_state
FROM pg_stat_replication;