A.
B. Setting di Master Node
1. Create user replication
su - postgres
CREATE USER replicate REPLICATION LOGIN CONNECTION LIMIT 1 ENCRYPTED PASSWORD 'admin'
[root@teguhth01 13]# su - postgresq;
su: user postgresq does not exist
[root@teguhth01 13]# su - postgres
Last login: Sun Aug 1 13:38:09 WIB 2021 on pts/0
-bash-4.2$
-bash-4.2$
-bash-4.2$ psql
psql (13.3)
Type "help" for help.
postgres=# CREATE USER replicate REPLICATION LOGIN CONNECTION LIMIT 1 ENCRYPTED PASSWORD 'admin';
CREATE ROLE
postgres=# ALTER ROLE replicate CONNECTION LIMIT 5;
ALTER ROLE
postgres=#
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
replicate | Replication +| {}
| 5 connections |
postgres=#
2. Setting pg_hba
host replication replicate 10.10.10.13/24 md5
host replication replicate 10.10.10.14/24 md5
-bash-4.2$ pwd
/var/lib/pgsql/13/data
-bash-4.2$ cat pg_hba.conf
# PostgreSQL Client Authentication Configuration File
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all peer
# IPv4 local connections:
host all all 127.0.0.1/32 scram-sha-256
# IPv6 local connections:
host all all ::1/128 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all peer
host replication all 127.0.0.1/32 scram-sha-256
host replication all ::1/128 scram-sha-256
host replication replicate 10.10.10.13/24 md5
host replication replicate 10.10.10.14/24 md5
-bash-4.2$
3. setting postgresql
-bash-4.2$ pwd
/var/lib/pgsql/13/data
-bash-4.2$ cat postgresql.conf | grep listen_address
listen_addresses = '*' # what IP address(es) to listen on;
-bash-4.2$
4. Restart postgresql-13
[root@teguhth01 ~]# systemctl restart postgresql-13
[root@teguhth01 ~]#
[root@teguhth01 ~]# systemctl status postgresql-13
● postgresql-13.service - PostgreSQL 13 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-13.service; enabled; vendor preset: disabled)
Active: active (running) since Sun 2021-08-01 13:54:41 WIB; 19s ago
Docs: https://www.postgresql.org/docs/13/static/
Process: 1587 ExecStartPre=/usr/pgsql-13/bin/postgresql-13-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 1592 (postmaster)
CGroup: /system.slice/postgresql-13.service
├─1592 /usr/pgsql-13/bin/postmaster -D /var/lib/pgsql/13/data/
├─1594 postgres: logger
├─1596 postgres: checkpointer
├─1597 postgres: background writer
├─1598 postgres: walwriter
├─1599 postgres: autovacuum launcher
├─1600 postgres: stats collector
└─1601 postgres: logical replication launcher
Aug 01 13:54:40 teguhth01 systemd[1]: Stopped PostgreSQL 13 database server.
Aug 01 13:54:40 teguhth01 systemd[1]: Starting PostgreSQL 13 database server...
Aug 01 13:54:41 teguhth01 postmaster[1592]: 2021-08-01 13:54:41.080 WIB [1592] LOG:...ss
Aug 01 13:54:41 teguhth01 postmaster[1592]: 2021-08-01 13:54:41.080 WIB [1592] HINT...".
Aug 01 13:54:41 teguhth01 systemd[1]: Started PostgreSQL 13 database server.
Hint: Some lines were ellipsized, use -l to show in full.
[root@teguhth01 ~]#
C. Setting di Slave Node
1. Backup data directory Standby/slave< stop service postgresql first>
[root@teguhth02 ~]# su - postgres
Last login: Wed Jul 28 09:58:08 WIB 2021 on pts/1
-bash-4.2$ mv /var/lib/pgsql/13/data/ /var/lib/pgsql/13/data_ori/
-bash-4.2$
-bash-4.2$ cd /var/lib/pgsql/13
-bash-4.2$ ls
backups data_ori initdb.log
-bash-4.2$
2. Replikasi dari Master ke Slave
pg_basebackup -h 10.10.10.13 -U replicate -p 5432 -D /var/lib/pgsql/13/data/ -Fp -Xs -P -R
-bash-4.2$ cd /var/lib/pgsql/13
-bash-4.2$ ls
backups data_ori initdb.log
-bash-4.2$
-bash-4.2$ pg_basebackup -h 10.10.10.13 -U replicate -p 5432 -D /var/lib/pgsql/13/data/ -Fp -Xs -P -R
Password:
24987/24987 kB (100%), 1/1 tablespace
-bash-4.2$ ls
backups data data_ori initdb.log
-bash-4.2$
-bash-4.2$ cd data
-bash-4.2$ ls
backup_label pg_commit_ts pg_notify pg_subtrans postgresql.auto.conf
backup_manifest pg_dynshmem pg_replslot pg_tblspc postgresql.conf
base pg_hba.conf pg_serial pg_twophase standby.signal
current_logfiles pg_ident.conf pg_snapshots PG_VERSION
global pg_logical pg_stat pg_wal
log pg_multixact pg_stat_tmp pg_xact
-bash-4.2$ cat postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
primary_conninfo = 'user=replicate password=admin channel_binding=prefer host=10.10.10.13 port=5432 sslmode=prefer sslcompression=0 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=any'
-bash-4.2$ cat standby.signal
-bash-4.2$
3. Restart postgresql
[root@teguhth02 13]# systemctl start postgresql-13
[root@teguhth02 13]#
[root@teguhth02 13]# systemctl status postgresql-13
● postgresql-13.service - PostgreSQL 13 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-13.service; enabled; vendor preset: disabled)
Active: active (running) since Sun 2021-08-01 14:08:40 WIB; 15s ago
Docs: https://www.postgresql.org/docs/13/static/
Process: 1724 ExecStartPre=/usr/pgsql-13/bin/postgresql-13-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 1729 (postmaster)
CGroup: /system.slice/postgresql-13.service
├─1729 /usr/pgsql-13/bin/postmaster -D /var/lib/pgsql/13/data/
├─1731 postgres: logger
├─1732 postgres: startup recovering 000000010000000000000005
├─1733 postgres: checkpointer
├─1734 postgres: background writer
├─1735 postgres: stats collector
└─1736 postgres: walreceiver streaming 0/5000060
Aug 01 14:08:40 teguhth02 systemd[1]: Starting PostgreSQL 13 database server...
Aug 01 14:08:40 teguhth02 postmaster[1729]: 2021-08-01 14:08:40.333 WIB [1729] LOG:...ss
Aug 01 14:08:40 teguhth02 postmaster[1729]: 2021-08-01 14:08:40.333 WIB [1729] HINT...".
Aug 01 14:08:40 teguhth02 systemd[1]: Started PostgreSQL 13 database server.
Hint: Some lines were ellipsized, use -l to show in full.
[root@teguhth02 13]#
C. Checking after replicate
1. Server master
# node01 / master
psql -x -c "select * from pg_stat_replication;" ;
# node02 / slave
psql -x -c "select * FROM pg_stat_wal_receiver;" ;
[root@teguhth01 ~]# su - postgres
Last login: Sun Aug 1 13:46:31 WIB 2021 on pts/0
-bash-4.2$ psql -x -c "select * from pg_stat_replication;"
-[ RECORD 1 ]----+------------------------------
pid | 1741
usesysid | 16384
usename | replicate
application_name | walreceiver
client_addr | 10.10.10.14
client_hostname |
client_port | 50402
backend_start | 2021-08-01 14:08:40.661056+07
backend_xmin |
state | streaming
sent_lsn | 0/5000060
write_lsn | 0/5000060
flush_lsn | 0/5000060
replay_lsn | 0/5000060
write_lag |
flush_lag |
replay_lag |
sync_priority | 0
sync_state | async
reply_time | 2021-08-01 14:10:30.668783+07
-bash-4.2$
-bash-4.2$ psql -x -c "select * from pg_stat_wal_receiver"
(0 rows)
-bash-4.2$
postgres=# select * from pg_stat_replication;
pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_lsn | write_lsn | flush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state | reply_time
------+----------+-----------+------------------+-------------+-----------------+-------------+-------------------------------+--------------+-----------+-----------+-----------+-----------+------------+-----------+-----------+------------+---------------+------------+-------------------------------
1741 | 16384 | replicate | walreceiver | 10.10.10.14 | | 50402 | 2021-08-01 14:08:40.661056+07 | | streaming | 0/5000060 | 0/5000060 | 0/5000060 | 0/5000060 | | | | 0 | async | 2021-08-01 14:11:20.705703+07
(1 row)
(END)
postgres=# select usename, client_addr, client_port, state, sync_state from pg_stat_replication;
usename | client_addr | client_port | state | sync_state
-----------+-------------+-------------+-----------+------------
replicate | 10.10.10.14 | 50402 | streaming | async
(1 row)
postgres=#
postgres=# select datname from pg_database;
datname
-----------
postgres
template1
template0
(3 rows)
postgres=#
2. Server slave
# node01 / master
psql -x -c "select * from pg_stat_replication;" ;
# node02 / slave
psql -x -c "select * FROM pg_stat_wal_receiver;" ;
[root@teguhth02 ~]# su - postgres;
Last login: Sun Aug 1 14:27:00 WIB 2021 on pts/1
-bash-4.2$ psql -x -c "select * from pg_stat_replication;";
(0 rows)
-bash-4.2$ hostname
teguhth02
-bash-4.2$ psql -x -c "select * from pg_stat_wal_receiver;" ;
-[ RECORD 1 ]---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
pid | 5876
status | streaming
receive_start_lsn | 0/5000060
receive_start_tli | 1
written_lsn | 0/5000060
flushed_lsn | 0/5000060
received_tli | 1
last_msg_send_time | 2021-09-10 12:43:27.857898+07
last_msg_receipt_time | 2021-09-10 12:43:27.73573+07
latest_end_lsn | 0/D000060
latest_end_time | 2021-09-10 12:40:57.482462+07
slot_name |
sender_host | 10.10.10.13
sender_port | 5432
conninfo | user=replicate password=******** channel_binding=prefer dbname=replication host=10.10.10.13 port=5432 fallback_application_name=walreceiver sslmode=prefer sslcompression=0 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=any
-bash-4.2$
-bash-4.2$
-bash-4.2$ psql
psql (13.3)
Type "help" for help.
postgres=# select * from pg_stat_replication;
pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_lsn | write_lsn | flush_lsn | replay_lsn | write_lag |
flush_lag | replay_lag | sync_priority | sync_state | reply_time
-----+----------+---------+------------------+-------------+-----------------+-------------+---------------+--------------+-------+----------+-----------+-----------+------------+-----------+
-----------+------------+---------------+------------+------------
(0 rows)
postgres=#
postgres=# select datname from pg_database;
datname
-----------
postgres
template1
template0
(3 rows)
postgres=#
D. Testing replication
1. Server master
postgres=# create database teguhth;
CREATE DATABASE
postgres=# select datname from pg_database;
datname
-----------
postgres
teguhth
template1
template0
(4 rows)
postgres=#
postgres=# SELECT pg_is_in_recovery();
pg_is_in_recovery
-------------------
f
(1 row)
postgres=#
postgres=# select datname from pg_database;
datname
-----------
postgres
teguhth
template1
template0
(4 rows)
postgres=# \c teguhth;
You are now connected to database "teguhth" as user "postgres".
teguhth=# \dt;
Did not find any relations.
teguhth=# select * from barang;
ERROR: relation "barang" does not exist
LINE 1: select * from barang;
^
teguhth=# create table barang(KODE_BARANG char(6) not null , NAMA_BARANG varchar(25), SATUAN_BARANG varchar(20), STOK_BARANG decimal(4), primary key (KODE_BARANG));
CREATE TABLE
teguhth=# \dt
List of relations
Schema | Name | Type | Owner
--------+--------+-------+----------
public | barang | table | postgres
(1 row)
teguhth=# \d barang
Table "public.barang"
Column | Type | Collation | Nullable | Default
---------------+-----------------------+-----------+----------+---------
kode_barang | character(6) | | not null |
nama_barang | character varying(25) | | |
satuan_barang | character varying(20) | | |
stok_barang | numeric(4,0) | | |
Indexes:
"barang_pkey" PRIMARY KEY, btree (kode_barang)
teguhth=# select * from barang;
kode_barang | nama_barang | satuan_barang | stok_barang
-------------+-------------+---------------+-------------
(0 rows)
teguhth=# insert into barang(KODE_BARANG,NAMA_BARANG,SATUAN_BARANG,STOK_BARANG) values('ELK-01','RICE COOKER','BUAH',20);
arang(KODE_BARANG,NAMA_BARANG,SATUAN_BARANG,STOK_BARANG) values('ELK-03','TELEVISI','UNIT',30);
insert into barang(KODE_BARANG,NAMA_BARANG,SATUAN_BARANG,STOK_BARANG) values('ELK-04','RADIO/TAPE','BUAH',35);
insert into barang(KODE_BARANG,NAMA_BARANG,SATUAN_BARANG,STOK_BARANG) values('ELK-05','KOMPUTER','UNIT',28);
insert into barang(KODE_BARANG,NAMA_BARANG,SATUAN_BARANG,STOK_BARANG) values('ELK-06','KIPAS ANGIN','BUAH',38);
select * from barang;INSERT 0 1
teguhth=# insert into barang(KODE_BARANG,NAMA_BARANG,SATUAN_BARANG,STOK_BARANG) values('ELK-02','LEMARI ES','UNIT',8);
INSERT 0 1
teguhth=# insert into barang(KODE_BARANG,NAMA_BARANG,SATUAN_BARANG,STOK_BARANG) values('ELK-03','TELEVISI','UNIT',30);
INSERT 0 1
teguhth=# insert into barang(KODE_BARANG,NAMA_BARANG,SATUAN_BARANG,STOK_BARANG) values('ELK-04','RADIO/TAPE','BUAH',35);
INSERT 0 1
teguhth=# insert into barang(KODE_BARANG,NAMA_BARANG,SATUAN_BARANG,STOK_BARANG) values('ELK-05','KOMPUTER','UNIT',28);
INSERT 0 1
teguhth=# insert into barang(KODE_BARANG,NAMA_BARANG,SATUAN_BARANG,STOK_BARANG) values('ELK-06','KIPAS ANGIN','BUAH',38);
INSERT 0 1
teguhth=# select * from barang;
kode_barang | nama_barang | satuan_barang | stok_barang
-------------+-------------+---------------+-------------
ELK-01 | RICE COOKER | BUAH | 20
ELK-02 | LEMARI ES | UNIT | 8
ELK-03 | TELEVISI | UNIT | 30
ELK-04 | RADIO/TAPE | BUAH | 35
ELK-05 | KOMPUTER | UNIT | 28
ELK-06 | KIPAS ANGIN | BUAH | 38
(6 rows)
teguhth=#
2. Server slave
postgres=# select datname from pg_database;
datname
-----------
postgres
teguhth
template1
template0
(4 rows)
postgres=# SELECT pg_is_in_recovery();
pg_is_in_recovery
-------------------
t
(1 row)
postgres=#
postgres=# \c teguhth;
You are now connected to database "teguhth" as user "postgres".
teguhth=# \dt;
Did not find any relations.
teguhth=# select * from barang;
ERROR: relation "barang" does not exist
LINE 1: select * from barang;
^
teguhth=# \dt
List of relations
Schema | Name | Type | Owner
--------+--------+-------+----------
public | barang | table | postgres
(1 row)
teguhth=# select * from barang;
kode_barang | nama_barang | satuan_barang | stok_barang
-------------+-------------+---------------+-------------
ELK-01 | RICE COOKER | BUAH | 20
ELK-02 | LEMARI ES | UNIT | 8
ELK-03 | TELEVISI | UNIT | 30
ELK-04 | RADIO/TAPE | BUAH | 35
ELK-05 | KOMPUTER | UNIT | 28
ELK-06 | KIPAS ANGIN | BUAH | 38
(6 rows)
teguhth=#
E. Optional: Enabling Synchronous Replication
1. Change Synchronous status
-bash-4.2$ hostname
teguhth01
-bash-4.2$ psql -c "ALTER SYSTEM SET synchronous_standby_names TO '*';"
ALTER SYSTEM
-bash-4.2$ psql -x -c "select * from pg_stat_replication;"
-[ RECORD 1 ]----+------------------------------
pid | 1741
usesysid | 16384
usename | replicate
application_name | walreceiver
client_addr | 10.10.10.14
client_hostname |
client_port | 50402
backend_start | 2021-08-01 14:08:40.661056+07
backend_xmin |
state | streaming
sent_lsn | 0/5000060
write_lsn | 0/5000060
flush_lsn | 0/5000060
replay_lsn | 0/5000060
write_lag |
flush_lag |
replay_lag |
sync_priority | 0
sync_state | async
reply_time | 2021-08-01 14:10:30.668783+07
-bash-4.2$
2. reload service
[root@teguhth01 ~]# systemctl reload postgresql-13.service
[root@teguhth01 ~]#
3. Check Synchronous status
-bash-4.2$ psql -x -c "select * from pg_stat_replication;"
-[ RECORD 1 ]----+------------------------------
pid | 1741
usesysid | 16384
usename | replicate
application_name | walreceiver
client_addr | 10.10.10.14
client_hostname |
client_port | 50402
backend_start | 2021-08-01 14:08:40.661056+07
backend_xmin |
state | streaming
sent_lsn | 0/5000060
write_lsn | 0/5000060
flush_lsn | 0/5000060
replay_lsn | 0/5000060
write_lag |
flush_lag |
replay_lag |
sync_priority | 0
sync_state | sync
reply_time | 2021-08-01 14:10:30.668783+07
-bash-4.2$
[root@node01 ~]# systemctl reload postgresql-13.service
[root@node01 ~]#
No comments:
Post a Comment