Fix mất đồng bộ từ Standby trong PostgreSQL 12

1.VẤN ĐỀ

Master: 192.3.151.41,PGDATA=/postgres/data

Slave: 192.3.151.43, PGDATA=/u01/pgdata

PostreSQL: 12 on Oracle Linux 7.9

ERROR: Mất đồng bộ giữa Slave và Master

2.NGUYÊN NHÂN

- Mất wal_log lâu

3.GIẢI PHÁP

--SLAVE

--1.STOP PG

pg_ctl stop

[postgres@vietdba-dbnew 12]$ netstat -an |grep 5432

tcp        0      0 192.3.151.43:5432        10.3.63.32:49244        FIN_WAIT2  

tcp        0      0 192.3.151.43:5432        10.3.63.32:49219        FIN_WAIT2  

tcp        0      0 192.3.151.43:5432        10.3.63.32:49233        FIN_WAIT2  

tcp        0      0 192.3.151.43:5432        10.3.63.32:49249        FIN_WAIT2  

tcp        0      0 192.3.151.43:5432        10.3.63.32:49229        FIN_WAIT2  

tcp        0      0 192.3.151.43:5432        10.3.63.32:49216        FIN_WAIT2  

tcp        0      0 192.3.151.43:5432        10.3.63.32:49206        FIN_WAIT2  

tcp        0      0 192.3.151.43:5432        10.3.63.32:49205        FIN_WAIT2  

tcp        0      0 192.3.151.43:5432        10.3.63.32:49232        FIN_WAIT2  

tcp        0      0 192.3.151.43:5432        10.38.29.161:54110      FIN_WAIT2  

tcp        0      0 192.3.151.43:5432        10.3.63.32:49250        FIN_WAIT2  

tcp        0      0 192.3.151.43:5432        10.3.63.32:49210        FIN_WAIT2  

tcp        0      0 192.3.151.43:5432        10.3.63.32:49228        FIN_WAIT2  

tcp        0      0 192.3.151.43:5432        10.3.63.32:49226        FIN_WAIT2  

tcp        0      0 192.3.151.43:5432        10.3.63.32:49227        FIN_WAIT2  

tcp        0      0 192.3.151.43:5432        10.3.18.8:54651         FIN_WAIT2  

tcp   1042560      0 192.3.151.43:22244       192.3.151.41:5432        ESTABLISHED

tcp        0    118 192.3.151.43:5432        10.3.18.8:54754         FIN_WAIT1  

tcp        0      0 192.3.151.43:43010       192.3.151.41:5432        TIME_WAIT  

tcp        0      0 192.3.151.43:5432        10.3.18.8:54738         FIN_WAIT2  

tcp        0      0 192.3.151.43:5432        10.3.63.32:49211        FIN_WAIT2  

[postgres@vietdba-dbnew pgdata]$ which pg_basebackup

/usr/pgsql-12/bin/pg_basebackup

--2.XOA DATA

cd /u01/pgdata/

ls

rm -rf /u01/pgdata/*

--3.DUPLICATE

pg_basebackup -h 192.3.151.41 -U replication -p 5432 -D /u01/pgdata -Fp -Xs -P -R

pg_basebackup -h 192.3.151.41 -U replication -p 5432 -D /u01/pgdata -Fp -Xs -P -R -c fast

pg_basebackup: error: FATAL:  password authentication failed for user "replication"

--> pass: replication

Password: 

  17449243/1807027588 kB (0%), 0/1 tablespace

--P/s: Backup van dang chay binh thuong vao /hddt_vt01 tren NFS 10.38.23.127:/u02/backup/hddtvt

[postgres@vietdba-dbnew pgdata]$ ps -ef |grep pg_dump

root      5308  5305 90 Feb04 ?        13:14:14 /usr/pgsql-12/bin/pg_dump -h 192.3.151.41 -U postgres -F c invoice --no-synchronized-snapshot

postgres 14409 13958  0 12:42 pts/2    00:00:00 grep --color=auto pg_dump

  

--4.CONFIG

cp pgdata.bak.conf/postgresql.conf  pgdata/

(hoac

$ vi /u01/pgdata/postgresql.conf 

 sửa các tham số trong vi postgresql.conf 

  listen_addresses = '*'

  max_locks_per_transaction = 128 is a lower setting than on the master server (its value was 1024)

  max_worker_processes = 14 is a lower setting than on the master server (its value was 16)

  max_connections = 1000 is a lower setting than on the master server (its value was 2000)

--5.START

pg_ctl start

    2026-02-05 17:00:06.773 +07 [7729] LOG:  database system was interrupted while in recovery at log time 2026-02-05 12:36:59 +07

2026-02-05 17:00:06.773 +07 [7729] HINT:  If this has occurred more than once some data might be corrupted and you might need to choose an earlier recovery target.

2026-02-05 17:00:06.782 +07 [7730] FATAL:  the database system is starting up

2026-02-05 17:00:08.449 +07 [7732] FATAL:  the database system is starting up

2026-02-05 17:00:08.457 +07 [7733] FATAL:  the database system is starting up

2026-02-05 17:00:08.736 +07 [7734] FATAL:  the database system is starting up

2026-02-05 17:00:08.848 +07 [7729] LOG:  entering standby mode

2026-02-05 17:00:08.857 +07 [7729] LOG:  redo starts at 230C/E2000028

2026-02-05 17:00:10.830 +07 [7739] FATAL:  the database system is starting up

2026-02-05 17:00:11.814 +07 [7748] FATAL:  the database system is starting up

2026-02-05 17:00:12.220 +07 [7749] FATAL:  the database system is starting up

2026-02-05 17:00:15.135 +07 [7753] FATAL:  the database system is starting up

2026-02-05 17:00:15.143 +07 [7754] FATAL:  the database system is starting up

2026-02-05 17:00:15.267 +07 [7755] FATAL:  the database system is starting up

2026-02-05 17:00:15.274 +07 [7756] FATAL:  the database system is starting up

2026-02-05 17:00:15.767 +07 [7757] FATAL:  the database system is starting up

2026-02-05 17:00:18.488 +07 [7760] FATAL:  the database system is starting up

2026-02-05 17:00:20.363 +07 [7763] FATAL:  the database system is starting up

2026-02-05 17:00:20.564 +07 [7764] FATAL:  the database system is starting up

2026-02-05 17:00:22.596 +07 [7767] FATAL:  the database system is starting up

2026-02-05 17:00:23.001 +07 [7768] FATAL:  the database system is starting up

2026-02-05 17:00:24.361 +07 [7770] FATAL:  the database system is starting up

2026-02-05 17:00:24.813 +07 [7771] FATAL:  the database system is starting up

2026-02-05 17:00:24.819 +07 [7772] FATAL:  the database system is starting up

2026-02-05 17:00:26.473 +07 [7729] LOG:  consistent recovery state reached at 230D/52E23940

2026-02-05 17:00:26.474 +07 [7726] LOG:  database system is ready to accept read only connections

2026-02-05 17:00:26.707 +07 [7776] LOG:  started streaming WAL from primary at 230D/53000000 on timeline 1

2026-02-05 17:00:26.746 +07 [7776] FATAL:  could not receive data from WAL stream: ERROR:  requested WAL segment 000000010000230D00000053 has already been removed

2026-02-05 17:00:26.770 +07 [7782] LOG:  started streaming WAL from primary at 230D/53000000 on timeline 1

2026-02-05 17:00:26.771 +07 [7782] FATAL:  could not receive data from WAL stream: ERROR:  requested WAL segment 000000010000230D00000053 has already been removed

2026-02-05 17:00:30.519 +07 [7786] ERROR:  cannot execute INSERT in a read-only transaction

2026-02-05 17:00:30.519 +07 [7786] STATEMENT:  INSERT INTO dbo.wb_log_search(wb_log_search_id, created_date, ip, security_number, type) 

        VALUES ($1, current_timestamp,$2, $3, $4)

2026-02-05 17:00:31.768 +07 [7788] LOG:  started streaming WAL from primary at 230D/53000000 on timeline 1

2026-02-05 17:00:31.768 +07 [7788] FATAL:  could not receive data from WAL stream: ERROR:  requested WAL segment 000000010000230D00000053 has already been removed

2026-02-05 17:00:32.649 +07 [7786] ERROR:  cannot execute INSERT in a read-only transaction

2026-02-05 17:00:32.649 +07 [7786] STATEMENT:  INSERT INTO dbo.wb_log_search(wb_log_search_id, created_date, ip, security_number, type) 

2026-02-05 17:00:30.519 +07 [7786] ERROR:  cannot execute INSERT in a read-only transaction

2026-02-05 17:00:30.519 +07 [7786] STATEMENT:  INSERT INTO dbo.wb_log_search(wb_log_search_id, created_date, ip, security_number, type) 

        VALUES ($1, current_timestamp,$2, $3, $4)

2026-02-05 17:00:31.768 +07 [7788] LOG:  started streaming WAL from primary at 230D/53000000 on timeline 1

2026-02-05 17:00:31.768 +07 [7788] FATAL:  could not receive data from WAL stream: ERROR:  requested WAL segment 000000010000230D00000053 has already been removed

2026-02-05 17:00:32.649 +07 [7786] ERROR:  cannot execute INSERT in a read-only transaction

2026-02-05 17:00:32.649 +07 [7786] STATEMENT:  INSERT INTO dbo.wb_log_search(wb_log_search_id, created_date, ip, security_number, type) 

        VALUES ($1, current_timestamp,$2, $3, $4)

2026-02-05 17:00:36.773 +07 [7793] LOG:  started streaming WAL from primary at 230D/53000000 on timeline 1

2026-02-05 17:00:36.774 +07 [7793] FATAL:  could not receive data from WAL stream: ERROR:  requested WAL segment 000000010000230D00000053 has already been removed

2026-02-05 17:00:38.953 +07 [7798] ERROR:  cannot execute INSERT in a read-only transaction

2026-02-05 17:00:38.953 +07 [7798] STATEMENT:  INSERT INTO dbo.wb_log_search(wb_log_search_id, created_date, ip, security_number, type) 

        VALUES ($1, current_timestamp,$2, $3, $4)

2026-02-05 17:00:41.780 +07 [7801] LOG:  started streaming WAL from primary at 230D/53000000 on timeline 1

2026-02-05 17:00:41.781 +07 [7801] FATAL:  could not receive data from WAL stream: ERROR:  requested WAL segment 000000010000230D00000053 has already been removed

2026-02-05 17:00:45.630 +07 [7811] ERROR:  cannot execute INSERT in a read-only transaction

2026-02-05 17:00:45.630 +07 [7811] STATEMENT:  INSERT INTO dbo.wb_log_search(wb_log_search_id, created_date, ip, security_number, type) 

        VALUES ($1, current_timestamp,$2, $3, $4)

2026-02-05 17:00:46.783 +07 [7813] LOG:  started streaming WAL from primary at 230D/53000000 on timeline 1

2026-02-05 17:00:46.783 +07 [7813] FATAL:  could not receive data from WAL stream: ERROR:  requested WAL segment 000000010000230D00000053 has already been removed

2026-02-05 17:00:51.785 +07 [7832] LOG:  started streaming WAL from primary at 230D/53000000 on timeline 1

2026-02-05 17:00:51.785 +07 [7832] FATAL:  could not receive data from WAL stream: ERROR:  requested WAL segment 000000010000230D00000053 has already been removed

2026-02-05 17:00:56.792 +07 [7837] LOG:  started streaming WAL from primary at 230D/53000000 on timeline 1

2026-02-05 17:00:56.792 +07 [7837] FATAL:  could not receive data from WAL stream: ERROR:  requested WAL segment 000000010000230D00000053 has already been removed

---duplcate lan 1 bi loi

2026-02-05 22:53:27.425 +07 [14130] LOG:  started streaming WAL from primary at 230E/C000000 on timeline 1

2026-02-05 22:53:27.425 +07 [14130] FATAL:  could not receive data from WAL stream: ERROR:  requested WAL segment 000000010000230E0000000C has already been removed

--6.CHECK

--Trên standby:

SELECT status, received_lsn, latest_end_lsn

FROM pg_stat_wal_receiver;

Kỳ vọng: status = streaming

--+ kiểm tra alert log

--Trên master:

SELECT application_name, state,

       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes

FROM pg_stat_replication;

--+ kiểm tra alert log

--Nếu vẫn lôi thì check tiếp

--7.cấu hình standby lấy WAL từ archive

--Bước 0 — Xác định version (Postgres 12+ hay thấp hơn)

--Trên standby:

SHOW server_version;

--Bước 1 — Stop PostgreSQL trên SLAVE

--Trên 192.3.15.143:

systemctl stop postgresql || pg_ctl stop -D /u01/pgdata -m fast

--Bước 2 — Tạo đường dẫn archive mà SLAVE truy cập được

--⚠️ Đây là điểm sống còn:

--File WAL đang nằm trên Master local path: /u01/wal_archive/...

--Standby không tự nhìn thấy thư mục đó trừ khi:

-- mount NFS, hoặc copy/rsync WAL sang standby, hoặc dùng scp trong restore_command

--✅ Cách đơn giản và dùng được ngay: dùng scp

--Tạo thư mục trên standby:

mkdir -p /u01/wal_archive

chown -R postgres:postgres /u01/wal_archive

chmod 700 /u01/wal_archive

--Đảm bảo user postgres trên standby SSH được sang master không cần password:

su - postgres

ssh-keygen -t rsa -b 4096

ssh-copy-id postgres@10.3.15.141

ssh postgres@192.3.15.141 "ls -l /u01/wal_archive | head"

--Bước 3 — Cấu hình restore_command trên standby

--Sửa file:

/u01/pgdata/postgresql.conf (hoặc file include)

--và thêm:

restore_command = 'scp -q postgres@10.3.15.141:/u01/wal_archive/%f %p'

--Giải thích:

%f = tên WAL file cần lấy (ví dụ 000000010000230E0000000C)

%p = đường dẫn đích trong pg_wal/ của standby

✅ Nếu muốn “copy qua thư mục local rồi mới trả”, cũng được, nhưng trên standby %p là chuẩn nhất.

--Bước 4 — Start lại standby

systemctl start postgresql || pg_ctl start -D /u01/pgdata

--Quay lại bước 6 kiểm tra

-------------------------------------

  [root@vietdba-db1 pg_wal]# systemctl status postgresql-12 

postgresql-12.service - PostgreSQL 12 database server

   Loaded: loaded (/usr/lib/systemd/system/postgresql-12.service; enabled; vendor preset: disabled)

   Active: active (running) since Thu 2026-01-08 06:22:07 +07; 3 weeks 6 days ago

max_wal_size = 10GB

ALTER SYSTEM SET max_wal_size = '100GB';

ALTER SYSTEM SET  archive_mode = on;

ALTER SYSTEM SET  archive_command = 'cp %p /u01/wal_archive/%f';

ALTER SYSTEM SET wal_keep_size = '1GB';

SELECT pg_reload_conf();  

ls /u01/wal_archive/*

find /u01/wal_archive -type f -mtime +7 -delete

--Cách 3 – Replication slot (giải pháp CHỐT)

--Trên MASTER:

SELECT pg_create_physical_replication_slot('standby_10_3_15_143');

--+Kiểm tra:

SELECT slot_name, active, restart_lsn

FROM pg_replication_slots;

--Trên STANDBY (postgresql.conf):

primary_slot_name = 'standby_10_3_15_143'

--Restart standby:

pg_ct stop

pg_ctl start

systemctl restart postgresql

-----------------------------MASTER---------------------------------------

PGDATA=/postgres/data

ALERT LOG: tail -1000f /postgres/data/log/postgresql-Thu.log   

bash-4.2$ pwd

/var/lib/pgsql/12/data

-----------------------------SLAVE---------------------------------------

[postgres@vietdba-dbnew hddt_vt01]$ env |grep PG

PGDATA=/u01/pgdata

AERLT LOG: tail -100f /u01/pgdata/log/postgresql-Thu.log

psql -t -c "SELECT pg_is_in_recovery();" --t

psql -t -c "select now() - pg_last_xact_replay_timestamp() as lagtime;" -- 8 days 17:00:36.223541

check_pg_recovery2.sh  check_pg_recovery.sh  logfile_check_recover.log

[postgres@vietdba-dbnew scripts]$ pwd

/home/postgres/scripts

/usr/pgsql-12/bin/pg_ctl -D /var/lib/pgsql/12/data/ -l logfile start

root      5308  5305 90 Feb04 ?        13:03:13 /usr/pgsql-12/bin/pg_dump -h 192.3.151.41 -U postgres -F c invoice --no-synchronized-snapshot

/var/lib/pgsql/12

[root@vietdba-dbnew pgdata]# ls -la

total 116

drwx------. 21 postgres postgres  4096 Feb  5 00:00 .

drwxr-xr-x.  4 root     root        43 Feb  5 11:28 ..

drwx------.  2 postgres postgres  8192 Oct 15 13:02 archive

-rw-r-----.  1 postgres postgres   230 Oct 15 08:57 backup_label.old

drwxr-x---. 10 postgres postgres   120 Oct 17 08:58 base

-rw-------.  1 postgres postgres    30 Feb  5 00:00 current_logfiles

drwxr-x---.  2 postgres postgres  4096 Jan 31 01:01 global

drwxr-x---.  2 postgres postgres   188 Oct 15 13:01 log

drwxr-x---.  2 postgres postgres     6 Oct 15 13:01 pg_commit_ts

drwxr-x---.  2 postgres postgres     6 Oct 15 13:01 pg_dynshmem

-rwxr-x---.  1 postgres postgres  5082 Oct 16 14:43 pg_hba.conf

-rwxr-x---.  1 postgres postgres  5014 Oct 16 14:31 pg_hba.conf.df

-rwxr-x---.  1 postgres postgres  4494 Oct 16 11:24 pg_hba.conf.vietnh

-rwxr-x---.  1 postgres postgres  1636 Oct 15 13:01 pg_ident.conf

drwxr-x---.  4 postgres postgres    68 Jan 27 23:20 pg_logical

drwxr-x---.  4 postgres postgres    36 Oct 15 13:02 pg_multixact

drwxr-x---.  2 postgres postgres    18 Jan 29 23:28 pg_notify

drwxr-x---.  2 postgres postgres     6 Oct 15 13:02 pg_replslot

drwxr-x---.  2 postgres postgres     6 Oct 15 13:02 pg_serial

drwxr-x---.  2 postgres postgres     6 Oct 15 13:02 pg_snapshots

drwxr-x---.  2 postgres postgres     6 Jan 29 23:28 pg_stat

drwxr-x---.  2 postgres postgres   157 Feb  5 11:56 pg_stat_tmp

drwxr-x---.  2 postgres postgres    18 Jan 27 23:20 pg_subtrans

drwxr-x---.  2 postgres postgres     6 Oct 15 13:02 pg_tblspc

drwxr-x---.  2 postgres postgres     6 Oct 15 13:02 pg_twophase

-rwxr-x---.  1 postgres postgres     3 Oct 15 13:02 PG_VERSION

drwxr-x---.  3 postgres postgres  4096 Jan 27 23:20 pg_wal

drwxr-x---.  2 postgres postgres  4096 Jan 27 13:18 pg_xact

-rwxr-x---.  1 postgres postgres   269 Oct 15 13:02 postgresql.auto.conf

-rwxr-x---.  1 postgres postgres 26694 Oct 16 14:38 postgresql.conf

-rw-------.  1 postgres postgres    27 Jan 29 22:28 postmaster.opts

-rw-------.  1 postgres postgres    85 Jan 29 23:28 postmaster.pid

-rw-r-----.  1 postgres postgres     0 Oct 15 13:02 standby.signal

=============================
TƯ VẤN: Click Here hoặc Hotline/Zalo 090.29.12.888
=============================
Website không chứa bất kỳ quảng cáo nào, mọi đóng góp để duy trì phát triển cho website (donation) xin vui lòng gửi về STK 90.2142.8888 - Ngân hàng Vietcombank Thăng Long - TRAN VAN BINH
=============================
Nếu bạn không muốn bị AI thay thế và tiết kiệm 3-5 NĂM trên con đường trở thành DBA chuyên nghiệp hay làm chủ Database thì hãy đăng ký ngay KHOÁ HỌC ORACLE DATABASE A-Z ENTERPRISE, được Coaching trực tiếp từ tôi với toàn bộ bí kíp thực chiến, thủ tục, quy trình của gần 20 năm kinh nghiệm (mà bạn sẽ KHÔNG THỂ tìm kiếm trên Internet/Google) từ đó giúp bạn dễ dàng quản trị mọi hệ thống Core tại Việt Nam và trên thế giới, đỗ OCP.
- CÁCH ĐĂNG KÝ: Gõ (.) hoặc để lại số điện thoại hoặc inbox https://m.me/tranvanbinh.vn hoặc Hotline/Zalo 090.29.12.888
- Chi tiết tham khảo:
https://bit.ly/oaz_w
=============================
2 khóa học online qua video giúp bạn nhanh chóng có những kiến thức nền tảng về Linux, Oracle, học mọi nơi, chỉ cần có Internet/4G:
- Oracle cơ bản: https://bit.ly/admin_1200
- Linux: https://bit.ly/linux_1200
=============================
KẾT NỐI VỚI CHUYÊN GIA TRẦN VĂN BÌNH:
📧 Mail: binhoracle@gmail.com
☎️ Mobile/Zalo: 0902912888
👨 Facebook: https://www.facebook.com/BinhOracleMaster
👨 Inbox Messenger: https://m.me/101036604657441 (profile)
👨 Fanpage: https://www.facebook.com/tranvanbinh.vn
👨 Inbox Fanpage: https://m.me/tranvanbinh.vn
👨👩 Group FB: https://www.facebook.com/groups/DBAVietNam
👨 Website: https://www.tranvanbinh.vn
👨 Blogger: https://tranvanbinhmaster.blogspot.com
🎬 Youtube: https://www.youtube.com/@binhguru
👨 Tiktok: https://www.tiktok.com/@binhguru
👨 Linkin: https://www.linkedin.com/in/binhoracle
👨 Twitter: https://twitter.com/binhguru
👨 Podcast: https://www.podbean.com/pu/pbblog-eskre-5f82d6
👨 Địa chỉ: Tòa nhà Sun Square - 21 Lê Đức Thọ - Phường Mỹ Đình 1 - Quận Nam Từ Liêm - TP.Hà Nội

=============================
cơ sở dữ liệu, cơ sở dữ liệu quốc gia, database, AI, trí tuệ nhân tạo, artificial intelligence, machine learning, deep learning, LLM, ChatGPT, DeepSeek, Grok, oracle tutorial, học oracle database, Tự học Oracle, Tài liệu Oracle 12c tiếng Việt, Hướng dẫn sử dụng Oracle Database, Oracle SQL cơ bản, Oracle SQL là gì, Khóa học Oracle Hà Nội, Học chứng chỉ Oracle ở đầu, Khóa học Oracle online,sql tutorial, khóa học pl/sql tutorial, học dba, học dba ở việt nam, khóa học dba, khóa học dba sql, tài liệu học dba oracle, Khóa học Oracle online, học oracle sql, học oracle ở đâu tphcm, học oracle bắt đầu từ đâu, học oracle ở hà nội, oracle database tutorial, oracle database 12c, oracle database là gì, oracle database 11g, oracle download, oracle database 19c/21c/23c/23ai, oracle dba tutorial, oracle tunning, sql tunning , oracle 12c, oracle multitenant, Container Databases (CDB), Pluggable Databases (PDB), oracle cloud, oracle security, oracle fga, audit_trail,oracle RAC, ASM, oracle dataguard, oracle goldengate, mview, oracle exadata, oracle oca, oracle ocp, oracle ocm , oracle weblogic, postgresql tutorial, mysql tutorial, mariadb tutorial, ms sql server tutorial, nosql, mongodb tutorial, oci, cloud, middleware tutorial, docker, k8s, micro service, hoc solaris tutorial, hoc linux tutorial, hoc aix tutorial, unix tutorial, securecrt, xshell, mobaxterm, putty