A.
B. Setting di Master Node
1. Create user replication
su - enterprisedb
CREATE USER replicate REPLICATION LOGIN CONNECTION LIMIT 1 ENCRYPTED PASSWORD 'admin';
ALTER ROLE replicate CONNECTION LIMIT 5;
[root@teguhth ~]# su - enterprisedb
Last login: Thu Jul 20 14:21:33 WIB 2023 on pts/0
-bash-4.2$ psql edb
psql (15.3.0, server 15.3.0)
Type "help" for help.
edb=# CREATE USER replicate REPLICATION LOGIN CONNECTION LIMIT 1 ENCRYPTED PASSWORD 'admin';
CREATE ROLE
edb=#
edb=# ALTER ROLE replicate CONNECTION LIMIT 5;
ALTER ROLE
edb=#
2. Setting pg_hba
setting hba in master & slave
-bash-4.2$ cat /var/lib/edb/as15/data/pg_hba.conf
#.............................
# 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 ident
# IPv6 local connections:
host all all ::1/128 ident
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all peer
host replication all 127.0.0.1/32 ident
host replication all ::1/128 ident
host replication replicate 10.10.10.0/24 md5
host replication replicate 10.10.10.71/32 md5
host replication replicate 10.10.10.72/32 md5
-bash-4.2$
-bash-4.2$ hostname
teguhth
-bash-4.2$
3. setting edb
-bash-4.2$ cat /var/lib/edb/as15/data/postgresql.conf | grep listen_address
listen_addresses = '*' # what IP address(es) to listen on;
-bash-4.2$ hostname
teguhth
-bash-4.2$
4. Restart postgresql-13
[root@teguhth ~]# systemctl restart edb-as-15
[root@teguhth ~]#
C. Setting di Slave Node
1. Backup data directory Standby/slave
2. stop edb service
[root@teguhth ~]# systemctl stop edb-as-15
3. move
su - enterprisedb
mv /var/lib/edb/as15/data /var/lib/edb/as15/data_old
4. Replikasi dari Master ke Slave
pg_basebackup -h 10.10.10.71 -U replicate -p 5444 -D /var/lib/edb/as15/data -Fp -Xs -P -R
-bash-4.2$ pg_basebackup -h 10.10.10.71 -U replicate -p 5444 -D /var/lib/edb/as15/data -Fp -Xs -P -R
Password:
85577/85577 kB (100%), 1/1 tablespace
-bash-4.2$ hostname
teguhth02
-bash-4.2$
cat /var/lib/edb/as15/data/postgresql.auto.conf
cat /var/lib/edb/as15/data/standby.signal
[root@teguhth02 data]# su - enterprisedb
Last login: Mon Jul 17 16:54:04 WIB 2023 on pts/0
-bash-4.2$
-bash-4.2$ cat /var/lib/edb/as15/data/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.71 port=5444 sslmode=prefer sslcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=any'
-bash-4.2$
-bash-4.2$ cat /var/lib/edb/as15/data/standby.signal
-bash-4.2$
5. Restart postgresql
[root@teguhth ~]# systemctl restart edb-as-15
C. Checking after replicate
check from master
# node01 / master
psql -x -c "select * from pg_stat_replication;" ;
psql -x -c "select * FROM pg_stat_wal_receiver;" ;
[root@teguhth ~]# su - enterprisedb
Last login: Thu Jul 20 14:21:56 WIB 2023 on pts/0
-bash-4.2$ psql teguhth
psql (15.3.0, server 15.3.0)
Type "help" for help.
teguhth=# 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
------+----------+-----------+------------------+-------------+-----------------+-------------+----------------------------------+--------------+-----------+-----
------+-----------+-----------+------------+-----------+-----------+------------+---------------+------------+----------------------------------
1219 | 16439 | replicate | walreceiver | 10.10.10.72 | | 39314 | 20-JUL-23 14:12:53.825479 +07:00 | | streaming | 0/58
8C6C8 | 0/588C6C8 | 0/588C6C8 | 0/588C6C8 | | | | 0 | async | 20-JUL-23 14:31:24.593775 +07:00
(1 row)
teguhth=# select * FROM pg_stat_wal_receiver;
pid | status | receive_start_lsn | receive_start_tli | written_lsn | flushed_lsn | received_tli | last_msg_send_time | last_msg_receipt_time | latest_end_lsn | l
atest_end_time | slot_name | sender_host | sender_port | conninfo
-----+--------+-------------------+-------------------+-------------+-------------+--------------+--------------------+-----------------------+----------------+--
---------------+-----------+-------------+-------------+----------
(0 rows)
teguhth=#
check from slave
# node02 / slave
psql -x -c "select * from pg_stat_replication;" ;
psql -x -c "select * FROM pg_stat_wal_receiver;" ;
[root@teguhth02 data]# su - enterprisedb
Last login: Thu Jul 20 14:16:38 WIB 2023 on pts/0
-bash-4.2$ psql teguhth
psql (15.3.0, server 15.3.0)
Type "help" for help.
teguhth=# 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_l
sn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state | reply_time
-----+----------+---------+------------------+-------------+-----------------+-------------+---------------+--------------+-------+----------+-----------+--------
---+------------+-----------+-----------+------------+---------------+------------+------------
(0 rows)
teguhth=# select * FROM pg_stat_wal_receiver;
pid | status | receive_start_lsn | receive_start_tli | written_lsn | flushed_lsn | received_tli | last_msg_send_time | last_msg_receipt_ti
me | latest_end_lsn | latest_end_time | slot_name | sender_host | sender_port |
conninfo
------+-----------+-------------------+-------------------+-------------+-------------+--------------+----------------------------------+-------------------------
--------+----------------+----------------------------------+-----------+-------------+-------------+-------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------
1027 | streaming | 0/5000000 | 1 | 0/588C6C8 | 0/588C6C8 | 1 | 20-JUL-23 14:32:55.485877 +07:00 | 20-JUL-23 14:32:54.86689
+07:00 | 0/588C6C8 | 20-JUL-23 14:22:54.132853 +07:00 | | 10.10.10.71 | 5444 | user=replicate password=******** channel_binding=prefer dbna
me=replication host=10.10.10.71 port=5444 fallback_application_name=walreceiver sslmode=prefer sslcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmo
de=prefer krbsrvname=postgres target_session_attrs=any
(1 row)
teguhth=#
6. testing insert like this
No comments:
Post a Comment