Postgres master & slave - Revision history

SanzSkuy at 07:38, 2 September 2025

2025-09-02T07:38:37Z

← Older revision		Revision as of 07:38, 2 September 2025
Line 57:		Line 57:
	sudo -i -u postgres		sudo -i -u postgres
	pg_basebackup -h <MASTER_IP> -D /var/lib/postgresql/16/main -U replicator -P --wal-method=stream		pg_basebackup -h <MASTER_IP> -D /var/lib/postgresql/16/main -U replicator -P --wal-method=stream
			</syntaxhighlight>

			Di folder data slave (<code>/var/lib/postgresql/16/main</code>) harus ada file <code>standby.signal</code>. Kalo gak ada bikin sendiri
			<syntaxhighlight lang=bash>
			touch /var/lib/postgresql/16/main/standby.signal
	</syntaxhighlight>		</syntaxhighlight>

SanzSkuy at 07:30, 2 September 2025

2025-09-02T07:30:06Z

@@ Line 36: / Line 36: @@
 max_wal_senders = 10
 wal_keep_size = 64
 </syntaxhighlight>
@@ Line 50: / Line 62: @@
 <syntaxhighlight lang=bash>
 primary_conninfo = 'host=<MASTER_IP> port=5432 user=replicator password=replicaPass'
 </syntaxhighlight>

SanzSkuy at 07:05, 2 September 2025

2025-09-02T07:05:09Z

← Older revision		Revision as of 07:05, 2 September 2025
Line 22:		Line 22:
	CREATE USER replicator REPLICATION LOGIN ENCRYPTED PASSWORD 'replicaPass';		CREATE USER replicator REPLICATION LOGIN ENCRYPTED PASSWORD 'replicaPass';
	ALTER ROLE replicator WITH REPLICATION;		ALTER ROLE replicator WITH REPLICATION;

			-- konfirmasi
			\du replicator
	</syntaxhighlight>		</syntaxhighlight>

Line 59:		Line 62:
	<syntaxhighlight lang=bash>		<syntaxhighlight lang=bash>
	host replication replicator < MASTER_IP>192.168.83.0/24 md5		host replication replicator < MASTER_IP>192.168.83.0/24 md5
			</syntaxhighlight>

			Cek di master
			<syntaxhighlight lang=bash>
			SELECT pid, client_addr, state, sync_state
			FROM pg_stat_replication;
	</syntaxhighlight>		</syntaxhighlight>

SanzSkuy: Created page with "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 'replicaPas..."

2025-09-02T03:58:51Z

Created page with "Login postgres <syntaxhighlight lang=bash> sudo -i -u postgres psql </syntaxhighlight> Buat user & database <syntaxhighlight lang=bash> -- 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; </syntaxhighlight> Bikin replica <syntaxhighlight lang=bash> -- buat user replica CREATE USER replicator REPLICATION LOGIN ENCRYPTED PASSWORD 'replicaPas..."

New page

Login postgres
<syntaxhighlight lang=bash>
sudo -i -u postgres
psql
</syntaxhighlight>

Buat user & database
<syntaxhighlight lang=bash>
-- 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;
</syntaxhighlight>

Bikin replica
<syntaxhighlight lang=bash>
-- buat user replica
CREATE USER replicator REPLICATION LOGIN ENCRYPTED PASSWORD 'replicaPass';
ALTER ROLE replicator WITH REPLICATION;
</syntaxhighlight>

Edit config postgres di VM master
<syntaxhighlight lang=bash>
sudo nano /etc/postgresql/15/main/postgresql.conf

# cari & ubah
listen_addresses = '*'
wal_level = replica
max_wal_senders = 10
wal_keep_size = 64
</syntaxhighlight>

Download Backup dari master (jalanin di slave)
<syntaxhighlight lang=bash>
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
</syntaxhighlight>

Masih di slave, Harus ada entry primary_conninfo hasil dari <code>pg_basebackup -R</code>. Isinya kira² gini:
<syntaxhighlight lang=bash>
primary_conninfo = 'host=<MASTER_IP> port=5432 user=replicator password=replicaPass'
</syntaxhighlight>

Tambah di <code>/etc/postgresql/16/main/pg_hba.conf</code> master
<syntaxhighlight lang=bash>
# Replication access
host replication replicator <SLAVE_IP>/32 md5
host all replicator <SLAVE_IP>/32 md5
</syntaxhighlight>

Tambah di <code>/etc/postgresql/16/main/pg_hba.conf</code> slave
<syntaxhighlight lang=bash>
host replication replicator < MASTER_IP>192.168.83.0/24 md5
</syntaxhighlight>