[PostgreSQL] 복구 시 테이블에 AccessExclusiveLock

// version 10.15

// occur AccessExclusivLock on table when recover using recover_target_time

// 데이터베이스 사용자가 실수로 table drop을 했다고 가정하여 복구 테스트

 

 

// base backup

-bash-4.2$ pg_basebackup -D /postgres/basebackup/backup_20210315 -p 5401 -F t -z

-bash-4.2$ ls -l /postgres/basebackup/backup_20210315

-rw-r--r--. 1 postgres postgres 15531124 Mar 15 15:45 base.tar.gz
-rw-------. 1 postgres postgres    18432 Mar 15 15:45 pg_wal.tar.gz

 

// 운영서버

-bash-4.2$ psql -p 5401

postgres=# \connect mydb
You are now connected to database "mydb" as user "postgres".
mydb=# create table test3 (no int);
CREATE TABLE
mydb=# insert into test3 values (1);
INSERT 0 1
mydb=# insert into test3 values (2);
INSERT 0 1
mydb=# commit;
경고:  현재 트랜잭션 작업을 하지 않고 있습니다
COMMIT
mydb=# select now();
              now
-------------------------------
 2021-03-15 15:46:56.911005+09         // recovery_target_time
(1 row)

mydb=# drop table test3;
DROP TABLE

mysql=#\q

 

// 복제 DB 생성

-bash-4.2$ cd /tmp/postgres/data

-bash-4.2$ tar -zxvf /postgres/basebackup/backup_20210315/base.tar.gz

// port 변경

-bash-4.2$ vi postgresql.conf

port = 5402

// recovery.conf 생성

-bash-4.2$ vi recovery.conf

restore_command = 'cp /postgres/archive/%f %p'

recovery_target_time = '2021-03-15 15:46:56 KST'

// 운영DB에서 pg_wal(pg_xlog) 로그 가져오기

-bash-4.2$ cp -r $PGDATA/pg_wal/* /tmp/postgres/data/pg_wal

-bash-4.2$ pg_ctl start -D /tmp/postgres/data/pg_wal

 

// 운영DB table 복구를 위해 복제DB의 test3 테이블 pgdump 백업

-bash-4.2$ pg_dump  -p 5402 -t test3 -d mydb -f /postgres/dump/test3.sql

// hang 걸림

 

// 복제 DB 상태 확인

-bash-4.2$ psql -p 5402 -d mydb

mydb=# select oid,relname from pg_class where relname like 'test3';
  oid  | relname
-------+---------
 16487 | test3

// AccessExclusiveLock이 잡혀있음.

mydb=# select * from pg_lock_status() where relation = 16487;
 locktype | database | relation | ... | virtualtransaction | pid  |        mode         | granted | fastpath
----------+----------+----------+--+------------------+------+---------------------+---------+----------
 relation |    16384 |    16487 |  ..  | 1/0                | 9085 | AccessExclusiveLock | t       | f
(1 row)

mydb=# \q

-bash-4.2$ ps -ef | grep 9085 
postgres  9085   9380 13 18:08 ?        00:00:04 postgres: startup process   recovering 00000003000000000000003E

 

// recovery.done으로 바뀌지 않은 상태

-bash-4.2$ ls -l -bash-4.2$ ls -l recovery*
-rw-r--r--. 1 postgres postgres 113 Mar 15 18:08 recovery.conf

// log 확인

-bash-4.2$ vi /tmp/postgres/data/log/postgresql-Mon.log

2021-03-15 18:08:31.324 KST [9382] 로그:  복구 작업이 일시 중지 됨
2021-03-15 18:08:31.324 KST [9382] 힌트:  계속 진행하려면, pg_wal_replay_resume() 함수를 호출하세요.

 

-bash-4.2$ psql -p 5402

postgres=# select postgres=# select  pg_wal_replay_resume();
 pg_wal_replay_resume
----------------------

(1 row)

postgres=# \connect mydb

You are now connected to database "mydb" as user "postgres".

mydb=# \d
         List of relations
 Schema | Name  | Type  |  Owner
--------+-------+-------+----------
 public | test3 | table | postgres
(1 row)

 

mydb=# select * from test3;
 no
----
  1
  2
(2 rows)

 

mydb=# select * from pg_lock_status() where relation = 16487;
 locktype | database | relation | ... | virtualtransaction | pid  |        mode         | granted | fastpath
----------+----------+----------+--+------------------+------+---------------------+---------+----------
(0 row)

mydb=# \q

 

// 운영DB table 복구를 위해 복제DB의 test3 테이블 pgdump 백업 재시도

-bash-4.2$ pg_dump  -p 5402 -t test3 -d mydb -f /postgres/dump/test3.sql

-bash-4.2$ ls -l /postgres/dump
total 28
-rw-r--r--. 1 postgres postgres 6042 Jan  8 15:20 all.sql
-rw-r--r--. 1 postgres postgres 1583 Jan  8 15:38 serv.sql
-rw-r--r--. 1 postgres postgres  752 Jan  8 15:38 smile.sql
-rw-r--r--. 1 postgres postgres  977 Jan  8 15:42 tbl_test.sql
-rw-r--r--. 1 postgres postgres  840 Mar 15 19:12 test3.sql
-rw-r--r--. 1 postgres postgres  977 Mar 15 16:28 test.sql

 

// 운영DB test3 table 복구

-bash-4.2$ psql -p 5401 -d mydb < /postgres/dump/test3.sql

-bash-4.2$ psql -p 5401 -d mydb

mydb=# \d
         List of relations
 Schema | Name  | Type  |  Owner
--------+-------+-------+----------
 public | test3 | table | postgres
(1 row)

mydb=# select * from test3;
 no
----
  1
  2
(2 rows)