[ The imperialist Japanese military must officially acknowledge and apologize for the coercion of young women into sexual slaves. / 幼い女性を性の奴隷にした帝國主義日本軍の?制性を公式に認め、謝罪しなければならない. ]

사용자

ID:
PW:

INDEX
01.게시판
게시판 [72]
02.File Book
File 자료실 [96]
Site Link [14]
개인폴더 [201]
03.Field Book
Altibase [19]
Tibero [31]
MS-SQL [32]
MySQL [47]
DB2 [79]
ORACLE [3202]
PSQL [143]
CUBRID [5]
기타정보 [194]
운영체제 [634]
04.Q/A Book
Q/A [53]
05.방명록
방명록 [54]
레벨업 [37]
구인/구직 [2]

기념일
Search
LINK
+ 가족 Hompy
+ DNSEver
IOTN :: Field Book :: PostgreSQL

 회원가입

HA 복구 test
최길호 [LIST]   2023-03-03 10:38:33, 조회 : 1,865

목적: 운영DB 복구, 관련된 HA도 복구 TEST

1. 백업
pg_ctl stop -D /home/postgres/ha
cd /home/postgres/
tar cf ha.tar ha

pg_ctl stop -D /home/postgres/15.1/data
cd /home/postgres/15.1
tar cf data.tar data
touch /home/postgres/arch/aaaaaaaaaaaaaaaaaaaaaaaa

-rw-------  1 postgres dba      5168  3월  3 08:22 0000000200000009000002EE.gz
-rw-------  1 postgres dba      9351  3월  3 08:32 0000000200000009000002EF.gz
-rw-------  1 postgres dba      5187  3월  3 08:41 0000000200000009000002F0.gz
-rw-r--r--  1 postgres dba         0  3월  3 08:52 aaaaaaaaaaaaaaaaaaaaaaaa

2. 서비스 시작 [ 정상 운영, archivelog 생성 ]

-rw-------  1 postgres dba      5168  3월  3 08:22 0000000200000009000002EE.gz
-rw-------  1 postgres dba      9351  3월  3 08:32 0000000200000009000002EF.gz
-rw-------  1 postgres dba      5187  3월  3 08:41 0000000200000009000002F0.gz
-rw-r--r--  1 postgres dba         0  3월  3 08:52 aaaaaaaaaaaaaaaaaaaaaaaa
-rw-------  1 postgres dba      5406  3월  3 09:02 0000000200000009000002F1.gz
-rw-------  1 postgres dba      4281  3월  3 09:02 0000000200000009000002F2.gz
-rw-------  1 postgres dba      5239  3월  3 09:12 0000000200000009000002F3.gz
-rw-------  1 postgres dba      5475  3월  3 09:22 0000000200000009000002F4.gz
-rw-------  1 postgres dba      5497  3월  3 09:32 0000000200000009000002F5.gz
-rw-------  1 postgres dba      5527  3월  3 09:42 0000000200000009000002F6.gz
-rw-------  1 postgres dba      5386  3월  3 09:48 0000000200000009000002F7.gz

3. 운영서버 복구

pg_ctl stop -D /home/postgres/ha
pg_ctl stop -D /home/postgres/15.1/data
-- archive log copy
gzip < /home/postgres/15.1/data/pg_wal/0000000200000009000002F8 > /home/postgres/arch/0000000200000009000002F8.gz
rm -rf /home/postgres/15.1/data

cd /home/postgres/15.1
tar xf data.tar

archive_command = 'gzip < %p > /home/postgres/arch/%f.gz'
restore_command = 'gunzip < /home/postgres/arch/%f.gz > %p'
recovery_target_timeline = 'current' # 'current', 'latest'또는 timeline ID

touch /home/postgres/15.1/data/recovery.signal
pg_ctl start -D /home/postgres/15.1/data



2023-03-03 11:11:40.443 KST [36759] LOG:  starting archive recovery
sh: /home/postgres/arch/00000002.history.gz: 그런 파일이나 디렉터리가 없습니다
2023-03-03 11:11:40.481 KST [36759] LOG:  restored log file "0000000200000009000002F1" from archive
2023-03-03 11:11:40.524 KST [36759] LOG:  consistent recovery state reached at 9/BC4000A0
2023-03-03 11:11:40.524 KST [36755] LOG:  database system is ready to accept read-only connections
2023-03-03 11:11:40.524 KST [36759] LOG:  redo starts at 9/BC4000A0
2023-03-03 11:11:40.557 KST [36759] LOG:  restored log file "0000000200000009000002F2" from archive
2023-03-03 11:11:40.622 KST [36759] LOG:  restored log file "0000000200000009000002F3" from archive
2023-03-03 11:11:40.686 KST [36759] LOG:  restored log file "0000000200000009000002F4" from archive
2023-03-03 11:11:40.751 KST [36759] LOG:  restored log file "0000000200000009000002F5" from archive
2023-03-03 11:11:40.804 KST [36759] LOG:  restored log file "0000000200000009000002F6" from archive
2023-03-03 11:11:40.866 KST [36759] LOG:  restored log file "0000000200000009000002F7" from archive
2023-03-03 11:11:40.929 KST [36759] LOG:  restored log file "0000000200000009000002F8" from archive
2023-03-03 11:11:40.960 KST [36759] LOG:  invalid record length at 9/BE0000A0: wanted 24, got 0
2023-03-03 11:11:40.960 KST [36759] LOG:  redo done at 9/BE000028 system usage: CPU: user: 0.00 s, system: 0.01 s, elapsed: 0.43 s
2023-03-03 11:11:40.960 KST [36759] LOG:  last completed transaction was at log time 2023-03-03 09:45:01.685902+09
2023-03-03 11:11:40.992 KST [36759] LOG:  restored log file "0000000200000009000002F8" from archive
sh: /home/postgres/arch/00000003.history.gz: 그런 파일이나 디렉터리가 없습니다
2023-03-03 11:11:41.035 KST [36759] LOG:  selected new timeline ID: 3                                                   ### timeline 3 변경
sh: /home/postgres/arch/00000002.history.gz: 그런 파일이나 디렉터리가 없습니다
2023-03-03 11:11:41.096 KST [36759] LOG:  archive recovery complete
2023-03-03 11:11:41.099 KST [36757] LOG:  checkpoint starting: end-of-recovery immediate wait
2023-03-03 11:11:41.171 KST [36757] LOG:  checkpoint complete: wrote 6 buffers (0.0%); 0 WAL file(s) added, 0 removed, 8 recycled; write=0.014 s, sync=0.006 s, total=0.075 s; sync files=4, longest=0.005 s, average=0.002 s; distance=28672 kB, estimate=28672 kB
2023-03-03 11:11:41.179 KST [36755] LOG:  database system is ready to accept connections

## HA 시작

2023-03-03 11:16:41.261 KST [36757] LOG:  checkpoint starting: time
2023-03-03 11:16:41.491 KST [36757] LOG:  checkpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.203 s, sync=0.006 s, total=0.230 s; sync files=3, longest=0.006 s, average=0.002 s; distance=3 kB, estimate=25805 kB
2023-03-03 11:21:41.593 KST [36757] LOG:  checkpoint starting: time
2023-03-03 11:21:41.850 KST [36757] LOG:  checkpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.229 s, sync=0.006 s, total=0.258 s; sync files=3, longest=0.005 s, average=0.002 s; distance=2 kB, estimate=23224 kB



## HA 시작
복구전 백업
tar cf ha.bk.tar ha

pg_ctl start -D ha

2023-03-03 11:17:35.011 KST [37667] LOG:  restored log file "00000003.history" from archive
sh: /home/postgres/arch/00000004.history.gz: 그런 파일이나 디렉터리가 없습니다
2023-03-03 11:17:35.015 KST [37667] LOG:  entering standby mode
2023-03-03 11:17:35.026 KST [37667] LOG:  restored log file "00000003.history" from archive
2023-03-03 11:17:35.062 KST [37667] LOG:  restored log file "0000000200000009000002F7" from archive
sh: /home/postgres/arch/00000002.history.gz: 그런 파일이나 디렉터리가 없습니다
2023-03-03 11:17:35.120 KST [37667] LOG:  redo starts at 9/BDC00B08
sh: /home/postgres/arch/0000000300000009000002F8.gz: 그런 파일이나 디렉터리가 없습니다
2023-03-03 11:17:35.158 KST [37667] LOG:  restored log file "0000000200000009000002F8" from archive
2023-03-03 11:17:35.188 KST [37667] LOG:  consistent recovery state reached at 9/BDC00BF0
2023-03-03 11:17:35.189 KST [37667] LOG:  invalid record length at 9/BE0000A0: wanted 24, got 0
2023-03-03 11:17:35.189 KST [37663] LOG:  database system is ready to accept read-only connections
2023-03-03 11:17:35.198 KST [37682] LOG:  started streaming WAL from primary at 9/BE000000 on timeline 3        ### timeline 3 변경

정상 sync

2023-03-03 11:22:35.103 KST [37665] LOG:  restartpoint starting: time
2023-03-03 11:22:35.347 KST [37665] LOG:  restartpoint complete: wrote 4 buffers (0.0%); 0 WAL file(s) added, 0 removed, 1 recycled; write=0.219 s, sync=0.006 s, total=0.244 s; sync files=3, longest=0.005 s, average=0.002 s; distance=4099 kB, estimate=4099 kB
2023-03-03 11:22:35.347 KST [37665] LOG:  recovery restart point at 9/BE001820
2023-03-03 11:22:35.347 KST [37665] DETAIL:  Last completed transaction was at log time 2023-03-03 11:20:01.369505+09.




추가: 테스트로 발생한 archivelog 중복 발생시 에러 상황
===================================================

운영 pg_wal/0000000200000009000002F1         #### 2F1 이 archivelog 위치에 있어서 발생 ( 전에 테스트로 생성 )

ha start
2023-03-03 12:41:54.860 KST [60284] LOG:  database system was shut down in recovery at 2023-03-03 08:40:44 KST
sh: /home/postgres/arch/00000003.history.gz: 그런 파일이나 디렉터리가 없습니다
2023-03-03 12:41:54.865 KST [60284] LOG:  entering standby mode
sh: /home/postgres/arch/00000002.history.gz: 그런 파일이나 디렉터리가 없습니다
2023-03-03 12:41:54.903 KST [60284] LOG:  restored log file "0000000200000009000002F0" from archive
2023-03-03 12:41:54.983 KST [60284] LOG:  restored log file "0000000200000009000002EF" from archive
2023-03-03 12:41:55.015 KST [60284] LOG:  redo starts at 9/BBC04080
2023-03-03 12:41:55.047 KST [60284] LOG:  restored log file "0000000200000009000002F0" from archive
2023-03-03 12:41:55.109 KST [60284] LOG:  restored log file "0000000200000009000002F1" from archive [ 운영중인 wal, archive있으면 안된다. 없도록 제거 했어야 한다. ]
2023-03-03 12:41:55.146 KST [60284] LOG:  consistent recovery state reached at 9/BC0009A0
2023-03-03 12:41:55.146 KST [60279] LOG:  database system is ready to accept read-only connections
2023-03-03 12:41:55.180 KST [60284] LOG:  restored log file "0000000200000009000002F2" from archive
2023-03-03 12:41:55.250 KST [60284] LOG:  restored log file "0000000200000009000002F3" from archive
2023-03-03 12:41:55.317 KST [60284] LOG:  restored log file "0000000200000009000002F4" from archive
2023-03-03 12:41:55.386 KST [60284] LOG:  restored log file "0000000200000009000002F5" from archive
2023-03-03 12:41:55.449 KST [60284] LOG:  restored log file "0000000200000009000002F6" from archive
2023-03-03 12:41:55.507 KST [60284] LOG:  restored log file "0000000200000009000002F7" from archive
2023-03-03 12:41:55.570 KST [60284] LOG:  restored log file "0000000200000009000002F8" from archive [ 전에 테스트한 로그로 복구 해서 sync 안되는 상황 ]
2023-03-03 12:41:55.608 KST [60284] LOG:  invalid record length at 9/BE0000A0: wanted 24, got 0
2023-03-03 12:41:55.616 KST [60311] LOG:  started streaming WAL from primary at 9/BE000000 on timeline 2
2023-03-03 12:41:55.616 KST [60311] FATAL:  could not receive data from WAL stream: ERROR:  requested starting point 9/BE000000 is ahead of the WAL flush position of this server 9/BC401350

운영측 로그 [ sync 실패 메세지 ]
2023-03-03 12:42:05.702 KST [60384] STATEMENT:  START_REPLICATION 9/BE000000 TIMELINE 2
2023-03-03 12:42:10.696 KST [60397] ERROR:  requested starting point 9/BE000000 is ahead of the WAL flush position of this server 9/BC401350
2023-03-03 12:42:10.696 KST [60397] STATEMENT:  START_REPLICATION 9/BE000000 TIMELINE 2
2023-03-03 12:42:15.705 KST [60407] ERROR:  requested starting point 9/BE000000 is ahead of the WAL flush position of this server 9/BC401350

ha 재시도 [ 2F8 가지 가서, 이건 복구 불가, 운영:2F1  ]
2023-03-03 12:44:52.273 KST [60671] LOG:  database system was shut down in recovery at 2023-03-03 12:42:15 KST
sh: /home/postgres/arch/00000003.history.gz: 그런 파일이나 디렉터리가 없습니다
2023-03-03 12:44:52.278 KST [60671] LOG:  entering standby mode
sh: /home/postgres/arch/00000002.history.gz: 그런 파일이나 디렉터리가 없습니다
2023-03-03 12:44:52.314 KST [60671] LOG:  restored log file "0000000200000009000002F8" from archive #########  2F8
2023-03-03 12:44:52.349 KST [60671] LOG:  consistent recovery state reached at 9/BE0000A0
2023-03-03 12:44:52.349 KST [60671] LOG:  invalid record length at 9/BE0000A0: wanted 24, got 0
2023-03-03 12:44:52.349 KST [60667] LOG:  database system is ready to accept read-only connections
2023-03-03 12:44:52.362 KST [60678] LOG:  started streaming WAL from primary at 9/BE000000 on timeline 2
2023-03-03 12:44:52.362 KST [60678] FATAL:  could not receive data from WAL stream: ERROR:  requested starting point 9/BE000000 is ahead of the WAL flush position of this server 9/BC800110

조치: ha db restore, 있으면 안되는 2F1 제거, 2F0는 사전에 wait 하도록 이름 변경
mv 0000000200000009000002F0.gz 0000000200000009000002F0.gz.old
mv 0000000200000009000002F1.gz 0000000200000009000002F1.gz.old

2023-03-03 12:48:28.388 KST [60992] LOG:  waiting for WAL to become available at 9/BC0009B8
sh: /home/postgres/arch/0000000200000009000002F0.gz: 그런 파일이나 디렉터리가 없습니다
2023-03-03 12:48:33.389 KST [61391] LOG:  started streaming WAL from primary at 9/BC000000 on timeline 2
2023-03-03 12:48:33.389 KST [61391] FATAL:  could not receive data from WAL stream: ERROR:  requested WAL segment 0000000200000009000002F0 has already been removed
sh: /home/postgres/arch/00000003.history.gz: 그런 파일이나 디렉터리가 없습니다
2023-03-03 12:48:33.394 KST [60992] LOG:  waiting for WAL to become available at 9/BC0009B8
sh: /home/postgres/arch/0000000200000009000002F0.gz: 그런 파일이나 디렉터리가 없습니다
2023-03-03 12:48:38.389 KST [61403] LOG:  started streaming WAL from primary at 9/BC000000 on timeline 2
2023-03-03 12:48:38.390 KST [61403] FATAL:  could not receive data from WAL stream: ERROR:  requested WAL segment 0000000200000009000002F0 has already been removed
sh: /home/postgres/arch/00000003.history.gz: 그런 파일이나 디렉터리가 없습니다
##### 메세지 반복
#####
##### mv 0000000200000009000002F0.gz.old 0000000200000009000002F0.gz
#####
2023-03-03 12:48:38.395 KST [60992] LOG:  waiting for WAL to become available at 9/BC0009B8
2023-03-03 12:48:43.413 KST [60992] LOG:  restored log file "0000000200000009000002F0" from archive
sh: /home/postgres/arch/0000000200000009000002F1.gz: 그런 파일이나 디렉터리가 없습니다
2023-03-03 12:48:43.453 KST [60992] LOG:  unexpected pageaddr 9/B8400000 in log segment 0000000200000009000002F1, offset 0                  #### 운영측과 통신
2023-03-03 12:48:43.460 KST [61416] LOG:  started streaming WAL from primary at 9/BC400000 on timeline 2
2023-03-03 12:51:18.283 KST [60990] LOG:  restartpoint starting: time
2023-03-03 12:51:18.545 KST [60990] LOG:  restartpoint complete: wrote 4 buffers (0.0%); 0 WAL file(s) added, 0 removed, 3 recycled; write=0.220 s, sync=0.006 s, total=0.262 s; sync files=3, longest=0.006 s, average=0.002 s; distance=12274 kB, estimate=12274 kB
2023-03-03 12:51:18.545 KST [60990] LOG:  recovery restart point at 9/BC800A10
2023-03-03 12:51:18.545 KST [60990] DETAIL:  Last completed transaction was at log time 2023-03-03 12:50:01.511011+09.
2023-03-03 12:56:18.572 KST [60990] LOG:  restartpoint starting: time
2023-03-03 12:56:18.809 KST [60990] LOG:  restartpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.211 s, sync=0.002 s, total=0.238 s; sync files=3, longest=0.002 s, average=0.001 s; distance=2 kB, estimate=11047 kB
2023-03-03 12:56:18.809 KST [60990] LOG:  recovery restart point at 9/BC801418
2023-03-03 12:56:18.809 KST [60990] DETAIL:  Last completed transaction was at log time 2023-03-03 12:55:01.884977+09.

216.73.216.248


  LIST

제목 작성자 작성일 조회
Admin  write db log msg    최길호 2025/04/07 7
Admin  CentOS 5 설치 가능 psql 11.22 설치    최길호 2024/11/21 9
Admin  ERROR: permission denied: "RI_ConstraintTrigger_331308" is a system trigger / violates foreign key constraint    최길호 2024/10/28 22
Admin  날짜 숫자 date unix epoch    최길호 2024/09/02 11
Admin  pg_backup_start / pg_backup_stop / psql 세션 유지 / WARNING: aborting backup due to backend exiting before pg_backup_stop was called    최길호 2023/12/21 69
Admin  psql 10.7 oracle dblink [ oracle_fdw db link ]    최길호 2023/07/25 75
Admin  HA 복구 test    최길호 2023/03/03 1865
Admin  log: COPY public.xxxx (xxx, xxx, xxx) TO stdout; / pg_dump 백업시 발생    최길호 2023/03/03 50
Admin  requested timeline x is not a child of this server's history    최길호 2023/03/02 63
Admin  recovery_target_action / archive log 받아서 계속 복구    최길호 2023/02/28 66
Admin  recovery_target_action = 'pause' / select pg_wal_replay_pause(); / select pg_get_wal_replay_pause_state();    최길호 2023/02/27 53
Admin  memory buffer cache    최길호 2023/02/06 47
Admin  active session, pid, lock sql , kill pid / pg_terminate_backend    최길호 2023/02/06 121
Admin  psql HA    최길호 2023/02/05 558
Admin  pg_ctl start -D . | postmaster.pid | /tmp/.s.PGSQL.5432 |    최길호 2023/02/03 81

    목록보기   다음페이지 1 [2][3][4][5][6][7][8]
       

Copyright 1999-2025 Zeroboard / skin by 최길호(gilho.kr@gmail.com)
최근 댓글
최근 게시물
07/02
[운영체제]
rsync -auv.
by 최길호
07/01
[기타정보]
shell for 001 ~ 100.
by 최길호
06/27
[ORACLE]
The clock on host node2 differ....
by 최길호