| OS: linux 7.5 DBMS: PostgreSQL 14.9 |
[목차여기]
pg_basebackup 명령어
-- 특정 경로에 날짜디렉토리에 tar.gz 형태로 백업 진행
-- 해당 경로에 파일이 있으면 에러 발생하므로 디렉토리를 계속 만들어 줘야 함
-- 아래 명령은 날짜별로 디렉토리 생성하여 백업 파일 tar.gz 으로 압축하여 생성
pg_basebackup -D /var/lib/pgsql/backup/`date +%Y%m%d` -F tar -z -X stream -P -v
-X 옵션 fetch 와 stream 차이
| - stream: pg_wal.tar.gz 파일이 따로 만들어지며 실시간 전송 - fetch: 백업이 끝날 때 수집되고 base.tar.gz 파일에 백업됨 - fetch 옵션에서 백업 중 발생한 WAL파일이 스위치 등으로 경로에서 사라지면 에러 메시지 발생(stream 옵션 사용하자) - "pg_basebackup: error: could not get COPY data stream: ERROR: requested WAL segment 000000010000000600000097 has already been removed" |
추가 테이블 스페이스 생성되어있으면 -F tar 옵션 필수
| 기본 경로는 -D 경로에 백업되지만 추가 테이블스페이스는 원본과 동일한 경로에 백업되어 에러발생 |
백업/복구 테스트
| -- 시나리오 1. pg_backupbackup 진행(시점 1) 2. tab100 테이블 생성(시점 2) 3. 데이터 추가 insert(시점 3) 4. 장애발생($PGDATA 디렉토리 삭제) 5. 복구 : 시점(1,2,3)별 복구 - 아카이브 로그 단위 복구 : PITR - recovery_target_time 사용한 복구 |
BACKUP
현재 WAL 파일 번호를 시점마다 확인하면서 진행
#################################
## 백업
#################################
test=# select * from tab100 ;
ERROR: relation "tab100" does not exist
LINE 1: select * from tab100 ;
^
test=# select pg_walfile_name(pg_current_wal_lsn()) ;
pg_walfile_name
--------------------------
000000010000000400000041
(1 row)
test=# select pg_switch_wal() ;
pg_switch_wal
---------------
4/4104EFC8
(1 row)
test=# select pg_walfile_name(pg_current_wal_lsn()) ;
pg_walfile_name
--------------------------
000000010000000400000042
(1 row)
-- 백업 실행
test=# \! pg_basebackup -D /home/postgres/backup/`date +%Y%m%d` -F tar -z -X stream -P -v
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 4/43000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_30033"
268836/268836 kB (100%), 1/1 tablespace
pg_basebackup: write-ahead log end point: 4/43000100
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed
test=# select pg_walfile_name(pg_current_wal_lsn()) ;
pg_walfile_name
--------------------------
000000010000000400000044
(1 row)
-- tab100 테이블 생성
test=# create table tab100 as select * from pg_class ;
SELECT 4135
test=# select pg_walfile_name(pg_current_wal_lsn()) ;
pg_walfile_name
--------------------------
000000010000000400000044
(1 row)
-- 로그 스위치
test=# select pg_switch_wal() ;
pg_switch_wal
---------------
4/4411E8E8
(1 row)
test=# select pg_walfile_name(pg_current_wal_lsn()) ;
pg_walfile_name
--------------------------
000000010000000400000045
(1 row)
-- 데이터 추가 insert
test=# insert into tab100 select * from pg_class ;
INSERT 0 4138
test=# select pg_walfile_name(pg_current_wal_lsn()) ;
pg_walfile_name
--------------------------
000000010000000400000045
(1 row)
-- 로그 스위치
test=# select pg_switch_wal() ;
pg_switch_wal
---------------
4/450E9ED8
(1 row)
test=# select pg_walfile_name(pg_current_wal_lsn()) ;
pg_walfile_name
--------------------------
000000010000000400000046
(1 row)
test=#
## 백업 파일
-- 아카이브
[postgres@svr1:/home/postgres/arch]$ pwd
/home/postgres/arch
[postgres@svr1:/home/postgres/arch]$ ls -al
total 131084
drwxr-xr-x 2 postgres postgres 4096 Nov 30 17:10 .
drwxr-xr-x 9 postgres postgres 4096 Nov 23 21:55 ..
-rw------- 1 postgres postgres 16777216 Nov 30 17:02 00000001000000040000003E
-rw------- 1 postgres postgres 16777216 Nov 30 17:05 00000001000000040000003F
-rw------- 1 postgres postgres 16777216 Nov 30 17:06 000000010000000400000040
-rw------- 1 postgres postgres 16777216 Nov 30 17:07 000000010000000400000041
-rw------- 1 postgres postgres 16777216 Nov 30 17:08 000000010000000400000042
-rw------- 1 postgres postgres 16777216 Nov 30 17:08 000000010000000400000043
-rw------- 1 postgres postgres 341 Nov 30 17:08 000000010000000400000043.00000028.backup
-rw------- 1 postgres postgres 16777216 Nov 30 17:09 000000010000000400000044
-rw------- 1 postgres postgres 16777216 Nov 30 17:10 000000010000000400000045
[postgres@svr1:/home/postgres/arch]$
-- 백업 파일
[postgres@svr1:/home/postgres/backup/20231130]$ pwd
/home/postgres/backup/20231130
[postgres@svr1:/home/postgres/backup/20231130]$ ls -al
total 60764
drwx------ 2 postgres postgres 69 Nov 30 17:08 .
drwxr-xr-x 3 postgres postgres 22 Nov 30 17:08 ..
-rw------- 1 postgres postgres 834124 Nov 30 17:08 backup_manifest
-rw------- 1 postgres postgres 61365373 Nov 30 17:08 base.tar.gz
-rw------- 1 postgres postgres 17076 Nov 30 17:08 pg_wal.tar.gz
[postgres@svr1:/home/postgres/backup/20231130]$
반응형
RESTORE
| rm -fr $PGDATA 명령으로 $PGDATA 경로의 모든 파일 삭제 후 백업 받은 tar 파일 + 아카이브 로그 파일 이용하여 복구 합니다. |
백업 파일 복구
#################################
## 복구
#################################
-- tar 풀기
[postgres@svr1:/home/postgres/backup/20231130]$ tar -xvf /home/postgres/backup/20231130/base.tar.gz -C $PGDATA/
-- 복구한 파일 중 backup_label 파일에서 백업정보 확인 가능
-- 복구에 필요한 로그파일은 000000010000000400000043 부터 필요하다
[postgres@svr1:/var/lib/pgsql/14/data]$ cat backup_label
START WAL LOCATION: 4/43000028 (file 000000010000000400000043)
CHECKPOINT LOCATION: 4/43000060
BACKUP METHOD: streamed
BACKUP FROM: primary
START TIME: 2023-11-30 17:08:09 KST
LABEL: pg_basebackup base backup
START TIMELINE: 1
[postgres@svr1:/var/lib/pgsql/14/data]$
-- 별도 생성한 테이블스페이스가 있다면 tablespace_map에서 확인
-- 백업경로에 번호에 해당하는 tar 파일이 테이블스페이스 백업 파일(16791.tar.gz 형태)
-- 복구안하면 LOG: could not open directory "pg_tblspc/16791/PG_14_202107181": No such file or directory 에러 발생
[postgres@svr1 pg_data]$ cat tablespace_map
16791 /pg_temp
[postgres@svr1 pg_data]$
-- 로그 없이 복구 시도(에러 발생, 필수 로그가 없어서 발생)
[postgres@svr1:/var/lib/pgsql/14/data]$ pg_ctl start
waiting for server to start....2023-11-30 17:30:50.418 KST [32418] LOG: redirecting log output to logging collector process
2023-11-30 17:30:50.418 KST [32418] HINT: Future log output will appear in directory "log".
... stopped waiting
pg_ctl: could not start server
Examine the log output.
[postgres@svr1:/var/lib/pgsql/14/data]$
2023-11-30 17:30:50.418 KST [32418] LOG: starting PostgreSQL 14.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2023-11-30 17:30:50.418 KST [32418] LOG: listening on IPv4 address "0.0.0.0", port 5432
2023-11-30 17:30:50.418 KST [32418] LOG: listening on IPv6 address "::", port 5432
2023-11-30 17:30:50.420 KST [32418] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2023-11-30 17:30:50.428 KST [32418] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2023-11-30 17:30:50.430 KST [32420] LOG: database system was interrupted; last known up at 2023-11-30 17:08:09 KST
2023-11-30 17:30:54.247 KST [32420] LOG: invalid checkpoint record
2023-11-30 17:30:54.247 KST [32420] FATAL: could not locate required checkpoint record
2023-11-30 17:30:54.247 KST [32420] HINT: If you are restoring from a backup, touch "/var/lib/pgsql/14/data/recovery.signal" and add required recovery options.
If you are not restoring from a backup, try removing the file "/var/lib/pgsql/14/data/backup_label".
Be careful: removing "/var/lib/pgsql/14/data/backup_label" will result in a corrupt cluster if restoring from a backup.
2023-11-30 17:30:54.247 KST [32418] LOG: startup process (PID 32420) exited with exit code 1
2023-11-30 17:30:54.247 KST [32418] LOG: aborting startup due to startup process failure
2023-11-30 17:30:54.248 KST [32418] LOG: database system is shut down
| WAL 파일 복구 시 아카이브 경로에서 복구해도 되고 없다면 pg_basebackup 시 함께 받았던 pg_wal.tar.gz 파일에서 아래 명령어로 복구 해 주세요 tar -xvf pg_wal.tar.gz -C $PGDATA/pg_wal/ |
백업 시점 복구(시점 1)
복구 원하는 시점까지의 로그만 복구한 후 DB기동 합니다
-- 백업 시점 복구(시점 1)
[postgres@svr1:/var/lib/pgsql/14/data/pg_wal]$ ls -al
total 16388
drwx------ 3 postgres postgres 60 Nov 30 17:33 .
drwx------ 20 postgres postgres 4096 Nov 30 17:30 ..
-rw------- 1 postgres postgres 16777216 Nov 30 17:33 000000010000000400000043
drwx------ 2 postgres postgres 6 Nov 30 17:08 archive_status
[postgres@svr1:/var/lib/pgsql/14/data/pg_wal]$ pg_ctl start
waiting for server to start....2023-11-30 17:33:51.198 KST [32721] LOG: redirecting log output to logging collector process
2023-11-30 17:33:51.198 KST [32721] HINT: Future log output will appear in directory "log".
.... done
server started
[postgres@svr1:/var/lib/pgsql/14/data/pg_wal]$ psql -d test
psql (14.9)
Type "help" for help.
-- 아직 tab100 테이블이 생성되기 전 시점
test=# select count(*) from tab100 ;
ERROR: relation "tab100" does not exist
LINE 1: select count(*) from tab100 ;
-- postgres.log
2023-11-30 17:33:51.198 KST [32721] LOG: starting PostgreSQL 14.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2023-11-30 17:33:51.198 KST [32721] LOG: listening on IPv4 address "0.0.0.0", port 5432
2023-11-30 17:33:51.198 KST [32721] LOG: listening on IPv6 address "::", port 5432
2023-11-30 17:33:51.200 KST [32721] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2023-11-30 17:33:51.203 KST [32721] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2023-11-30 17:33:51.206 KST [32723] LOG: database system was interrupted; last known up at 2023-11-30 17:08:09 KST
2023-11-30 17:33:55.281 KST [32723] LOG: redo starts at 4/43000028
2023-11-30 17:33:55.283 KST [32723] LOG: consistent recovery state reached at 4/43000100
2023-11-30 17:33:55.283 KST [32723] LOG: redo done at 4/43000100 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
2023-11-30 17:33:55.411 KST [32721] LOG: database system is ready to accept connections
2023-11-30 17:33:55.412 KST [32735] LOG: TimescaleDB background worker launcher connected to shared catalogs
2023-11-30 17:33:55.441 KST [32740] LOG: job 1000 (Compression Policy [1000]) exiting with success: execution time 12.44 ms
tab100 테이블 생성 시점 복구(시점 2)
복구 원하는 시점까지의 로그만 복구한 후 DB기동 합니다
-- tab100 테이블 생성 시점 복구(시점 2)
[postgres@svr1:/var/lib/pgsql/14/data/pg_wal]$ ls -al
total 32772
drwx------ 3 postgres postgres 92 Nov 30 17:43 .
drwx------ 20 postgres postgres 4096 Nov 30 17:41 ..
-rw------- 1 postgres postgres 16777216 Nov 30 17:43 000000010000000400000043
-rw------- 1 postgres postgres 16777216 Nov 30 17:43 000000010000000400000044
drwx------ 2 postgres postgres 6 Nov 30 17:08 archive_status
[postgres@svr1:/var/lib/pgsql/14/data/pg_wal]$ pg_ctl start
waiting for server to start....2023-11-30 17:43:16.023 KST [1516] LOG: redirecting log output to logging collector process
2023-11-30 17:43:16.023 KST [1516] HINT: Future log output will appear in directory "log".
.... done
server started
[postgres@svr1:/var/lib/pgsql/14/data/pg_wal]$ psql -d test
psql (14.9)
Type "help" for help.
test=# select count(*) from tab100 ;
count
-------
4135
(1 row)
-- postgres.log
2023-11-30 17:43:16.023 KST [1516] LOG: starting PostgreSQL 14.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2023-11-30 17:43:16.023 KST [1516] LOG: listening on IPv4 address "0.0.0.0", port 5432
2023-11-30 17:43:16.023 KST [1516] LOG: listening on IPv6 address "::", port 5432
2023-11-30 17:43:16.024 KST [1516] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2023-11-30 17:43:16.028 KST [1516] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2023-11-30 17:43:16.031 KST [1518] LOG: database system was interrupted; last known up at 2023-11-30 17:08:09 KST
2023-11-30 17:43:20.082 KST [1518] LOG: redo starts at 4/43000028
2023-11-30 17:43:20.083 KST [1518] LOG: consistent recovery state reached at 4/43000100
2023-11-30 17:43:20.086 KST [1518] LOG: redo done at 4/4411E8D0 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
2023-11-30 17:43:20.242 KST [1516] LOG: database system is ready to accept connections
2023-11-30 17:43:20.245 KST [1528] LOG: TimescaleDB background worker launcher connected to shared catalogs
2023-11-30 17:43:20.274 KST [1533] LOG: job 1000 (Compression Policy [1000]) exiting with success: execution time 12.82 ms
마지막 로그까지 복구(시점 3)
-- 마지막 로그까지 복구(시점 3)
[postgres@svr1:/var/lib/pgsql/14/data/pg_wal]$ ls -al
total 49156
drwx------ 3 postgres postgres 124 Nov 30 17:46 .
drwx------ 20 postgres postgres 4096 Nov 30 17:46 ..
-rw------- 1 postgres postgres 16777216 Nov 30 17:46 000000010000000400000043
-rw------- 1 postgres postgres 16777216 Nov 30 17:46 000000010000000400000044
-rw------- 1 postgres postgres 16777216 Nov 30 17:46 000000010000000400000045
drwx------ 2 postgres postgres 6 Nov 30 17:08 archive_status
[postgres@svr1:/var/lib/pgsql/14/data/pg_wal]$ pg_ctl start
waiting for server to start....2023-11-30 17:46:45.595 KST [1880] LOG: redirecting log output to logging collector process
2023-11-30 17:46:45.595 KST [1880] HINT: Future log output will appear in directory "log".
..... done
server started
[postgres@svr1:/var/lib/pgsql/14/data/pg_wal]$ psql -d test
psql (14.9)
Type "help" for help.
test=# select count(*) from tab100 ;
count
-------
8273
(1 row)
-- postgres.log
2023-11-30 17:46:45.595 KST [1880] LOG: starting PostgreSQL 14.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2023-11-30 17:46:45.595 KST [1880] LOG: listening on IPv4 address "0.0.0.0", port 5432
2023-11-30 17:46:45.595 KST [1880] LOG: listening on IPv6 address "::", port 5432
2023-11-30 17:46:45.597 KST [1880] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2023-11-30 17:46:45.600 KST [1880] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2023-11-30 17:46:45.603 KST [1882] LOG: database system was interrupted; last known up at 2023-11-30 17:08:09 KST
2023-11-30 17:46:50.745 KST [1882] LOG: redo starts at 4/43000028
2023-11-30 17:46:50.747 KST [1882] LOG: consistent recovery state reached at 4/43000100
2023-11-30 17:46:50.752 KST [1882] LOG: redo done at 4/450E9EC0 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
2023-11-30 17:46:50.939 KST [1880] LOG: database system is ready to accept connections
2023-11-30 17:46:50.940 KST [1894] LOG: TimescaleDB background worker launcher connected to shared catalogs
2023-11-30 17:46:50.970 KST [1899] LOG: job 1000 (Compression Policy [1000]) exiting with success: execution time 12.79 ms
recover_target_time 이용한 시점 복구
#################################
## Point-In-Time Recovery (PITR)
#################################
PostgreSQL 12 버전부터 recovery.conf 파일은 지원하지 않고 관련 파라미터들은 postgresql.conf에 정의되어야 함
만약 recover_target_time이 설정되어 있지 않다면 PostgreSQL은 아카이브 로그 파일을 끝까지 읽어서 리플레이를 수행
-- 리커버리 모드를 위한 recovery.signal 파일 생성
[postgres@svr1:/var/lib/pgsql/14/data]$ touch recovery.signal
-- postgresql.conf 파일에 아래 파라미터 수정
restore_command = 'cp /home/postgres/arch/%f %p'
recovery_target_time = '2023-11-30 17:09:30'
-- DB 기동
[postgres@svr1:/var/lib/pgsql/14/data]$ pg_ctl start
waiting for server to start....2023-11-30 18:23:30.176 KST [6127] LOG: redirecting log output to logging collector process
2023-11-30 18:23:30.176 KST [6127] HINT: Future log output will appear in directory "log".
... done
server started
[postgres@svr1:/var/lib/pgsql/14/data]$ psql -d test
psql (14.9)
Type "help" for help.
-- tab100 테이블 처음 데이터 적재 시점까지만 복구 되었음
test=# select count(*) from tab100 ;
count
-------
4135
(1 row)
-- postgres.log
2023-11-30 18:23:30.176 KST [6127] LOG: starting PostgreSQL 14.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2023-11-30 18:23:30.176 KST [6127] LOG: listening on IPv4 address "0.0.0.0", port 5432
2023-11-30 18:23:30.176 KST [6127] LOG: listening on IPv6 address "::", port 5432
2023-11-30 18:23:30.178 KST [6127] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2023-11-30 18:23:30.181 KST [6127] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2023-11-30 18:23:30.184 KST [6129] LOG: database system was interrupted; last known up at 2023-11-30 17:08:09 KST
cp: cannot stat ‘/home/postgres/arch/00000002.history’: No such file or directory
2023-11-30 18:23:34.014 KST [6129] LOG: starting point-in-time recovery to 2023-11-30 17:09:30+09
2023-11-30 18:23:34.020 KST [6129] LOG: restored log file "000000010000000400000043" from archive
2023-11-30 18:23:34.068 KST [6129] LOG: redo starts at 4/43000028
2023-11-30 18:23:34.070 KST [6129] LOG: consistent recovery state reached at 4/43000100
2023-11-30 18:23:34.070 KST [6127] LOG: database system is ready to accept read-only connections
2023-11-30 18:23:34.081 KST [6129] LOG: restored log file "000000010000000400000044" from archive
2023-11-30 18:23:34.139 KST [6129] LOG: restored log file "000000010000000400000045" from archive
2023-11-30 18:23:34.212 KST [6129] LOG: recovery stopping before commit of transaction 398157, time 2023-11-30 17:09:51.768637+09
2023-11-30 18:23:34.212 KST [6129] LOG: pausing at the end of recovery
2023-11-30 18:23:34.212 KST [6129] HINT: Execute pg_wal_replay_resume() to promote.
-- 리커버리 모드 종료
test=# select pg_wal_replay_resume();
pg_wal_replay_resume
----------------------
(1 row)
-- recovery.signal 파일이 사라짐
[postgres@svr1:/var/lib/pgsql/14/data]$ ls -lrt
total 31808
-rw------- 1 postgres postgres 88 Nov 8 20:06 postgresql.auto.conf
drwx------ 2 postgres postgres 18 Nov 8 20:06 pg_xact
-rw------- 1 postgres postgres 3 Nov 8 20:06 PG_VERSION
drwx------ 2 postgres postgres 6 Nov 8 20:06 pg_twophase
drwx------ 2 postgres postgres 6 Nov 8 20:06 pg_tblspc
drwx------ 2 postgres postgres 6 Nov 8 20:06 pg_snapshots
drwx------ 2 postgres postgres 6 Nov 8 20:06 pg_serial
drwx------ 2 postgres postgres 6 Nov 8 20:06 pg_notify
drwx------ 4 postgres postgres 36 Nov 8 20:06 pg_multixact
-rw------- 1 postgres postgres 1636 Nov 8 20:06 pg_ident.conf
drwx------ 2 postgres postgres 6 Nov 8 20:06 pg_dynshmem
drwx------ 2 postgres postgres 6 Nov 8 20:06 pg_commit_ts
-rw------- 1 postgres postgres 4652 Nov 8 21:05 pg_hba.conf
-rw------- 1 postgres postgres 32488851 Nov 23 22:27 base.tar.gz
drwx------ 2 postgres postgres 4096 Nov 30 16:22 log
drwx------ 7 postgres postgres 67 Nov 30 16:23 base
-rw------- 1 postgres postgres 0 Nov 30 17:08 tablespace_map.old
drwx------ 2 postgres postgres 6 Nov 30 17:08 pg_replslot
-rw------- 1 postgres postgres 227 Nov 30 17:08 backup_label.old
-rw------- 1 postgres postgres 29284 Nov 30 18:23 postgresql.conf
-rw------- 1 postgres postgres 37 Nov 30 18:27 current_logfiles
-rw------- 1 postgres postgres 27 Nov 30 18:27 postmaster.opts
drwx------ 2 postgres postgres 6 Nov 30 18:27 pg_stat
drwx------ 4 postgres postgres 68 Nov 30 18:28 pg_logical
drwx------ 2 postgres postgres 18 Nov 30 18:28 pg_subtrans
drwx------ 3 postgres postgres 4096 Nov 30 18:28 pg_wal
-rw------- 1 postgres postgres 95 Nov 30 18:28 postmaster.pid
drwx------ 2 postgres postgres 71 Nov 30 18:28 pg_stat_tmp
drwx------ 2 postgres postgres 4096 Nov 30 18:28 global
[postgres@svr1:/var/lib/pgsql/14/data]$
-- postgres.log
2023-11-30 18:28:31.526 KST [6519] LOG: redo done at 4/450D92A0 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 79.47 s
2023-11-30 18:28:31.526 KST [6519] LOG: last completed transaction was at log time 2023-11-30 17:09:14.206551+09
cp: cannot stat ‘/home/postgres/arch/00000002.history’: No such file or directory
2023-11-30 18:28:31.533 KST [6519] LOG: selected new timeline ID: 2
2023-11-30 18:28:31.565 KST [6519] LOG: archive recovery complete
cp: cannot stat ‘/home/postgres/arch/00000001.history’: No such file or directory
2023-11-30 18:28:31.606 KST [6517] LOG: database system is ready to accept connections
2023-11-30 18:28:31.608 KST [6647] LOG: TimescaleDB background worker launcher connected to shared catalogs
2023-11-30 18:28:31.635 KST [6654] LOG: job 1000 (Compression Policy [1000]) exiting with success: execution time 12.62 ms
-- 새로운 타임라인(2번) 생성됨
[postgres@svr1:/var/lib/pgsql/14/data]$ cd pg_wal
[postgres@svr1:/var/lib/pgsql/14/data/pg_wal]$ ls -lrt
total 65540
-rw------- 1 postgres postgres 16777216 Nov 30 18:27 000000020000000400000046
-rw------- 1 postgres postgres 16777216 Nov 30 18:27 000000020000000400000047
-rw------- 1 postgres postgres 16777216 Nov 30 18:27 000000010000000400000045
-rw------- 1 postgres postgres 51 Nov 30 18:28 00000002.history
drwx------ 2 postgres postgres 72 Nov 30 18:28 archive_status
-rw------- 1 postgres postgres 16777216 Nov 30 18:28 000000020000000400000045
[postgres@svr1:/var/lib/pgsql/14/data/pg_wal]$
'DBMS > PostgreSQL' 카테고리의 다른 글
| [PostgreSQL] 테이블 데이터 text 파일로 저장하기(COPY 명령) (0) | 2023.12.12 |
|---|---|
| [PostgreSQL] SQL 실행계획 관리(pg_store_plans) (2) | 2023.12.05 |
| [PostgreSQL] 아카이브 모드 설정 (1) | 2023.11.19 |
| [PostgreSQL] pg_hint_plan 설치하여 SQL hint 사용 (0) | 2023.11.16 |
| [PostgreSQL] timescaledb extension 설치 (0) | 2023.11.15 |
댓글