회사 DB 한 테이블 전체를 Update 해버렸다?
개발하다 보면 “이건 금방 끝나겠는데?” 싶은 작업이 있다.
이번 일이 딱 그랬다.
PostgreSQL의 어떤 테이블에 원래 NULL이어야 할 값들이 NaN으로 들어가 있었다. PostgreSQL은 숫자 타입에서 NaN을 지원하니까 DB 입장에서는 이상한 값은 아니었지만, 우리 쪽에서는 이걸 NULL로 정리해야 했다.
그래서 여러 컬럼에 NULLIF를 적용하기 시작했다.
1
2
3
4
5
UPDATE target_table
SET
column_a = NULLIF(column_a, 'NaN'),
column_b = NULLIF(column_b, 'NaN'),
column_c = NULLIF(column_c, 'NaN');
문제는 컬럼이 많았다는 것.
비슷한 구문을 계속 복붙하다가 결국 사고를 쳤다.
1
column_b = NULLIF(column_a, 'NaN')
column_b를 처리해야 하는데 오른쪽을 column_a로 그대로 둔 것이다.
더 무서운 건 이게 문법적으로는 완벽하게 정상이라는 점이다.
DB는 아무 말 없이 아주 성실하게 실행해줬다.
그리고 잠시 후 데이터를 보면서 생각했다.
“어?”
일단 ROLLBACK?
제일 먼저 떠오른 건 당연히 ROLLBACK이었다.
그런데 이미 autocommit 상태에서 UPDATE가 끝난 뒤였다.
1
ROLLBACK;
당연히 아무것도 돌아오지 않았다.
그제야 BEGIN이라도 걸고 작업할 걸 싶었다.
사고가 나기 전에는 생각하지 않았던 게 사고가 나니까 하나씩 떠오르기 시작했다.
임시 테이블 만들어서 복구하면 되지 않을까?
다행히 작업 전에 혹시 몰라 데이터를 Export해두긴 했다.
그러면 복구용 테이블 하나 만들어서 데이터를 넣고 PK 기준으로 조인해서 원래 값만 UPDATE하면 되지 않을까 싶었다.
좋은 생각이었다.
내게 권한만 있었다면.
당시 계정은 정말 DML 권한만 있었다.
SELECT, INSERT, UPDATE, DELETE.
끝.
일반 테이블 생성도 안 되고 TEMP TABLE 생성 권한도 없었다.
복구 방법 하나 삭제.
PostgreSQL에서 이전 시점으로 돌릴 수는 없나?
그다음에는 DB 자체를 이전 상태로 복원할 방법을 생각했다.
PostgreSQL에는 WAL도 있고, 백업 구성이 되어 있다면 PITR 같은 방법도 있다.
그런데 이건 내가 DML 계정 하나 들고 할 수 있는 작업이 아니었다.
DBA나 인프라 쪽 도움이 필요한 영역이었다.
그리고 무엇보다 이 DB는 개발망이었다.
운영 데이터가 날아간 상황은 아니었기 때문에, 일단 내가 가지고 있는 백업 데이터로 해결할 방법을 더 찾아보기로 했다.
그런데 잠깐, 왜 SQL로 뽑았지?
여기서 또 하나의 문제가 있었다.
나는 작업 전에 데이터를 Export해뒀다고 생각했다.
그래서 마음 한편으로는 “그래도 백업은 있으니까 괜찮겠지” 하고 있었다.
그런데 사고가 난 뒤 파일을 자세히 보니 CSV가 아니었다.
.sql 파일이었다.
안에는 이런 INSERT 문이 잔뜩 들어 있었다.
1
2
3
INSERT INTO target_table (...) VALUES (...);
INSERT INTO target_table (...) VALUES (...);
INSERT INTO target_table (...) VALUES (...);
그때 든 생각은 하나였다.
“왜 이걸 SQL로 뽑았지?”
돌이켜보면 이유도 별거 없었다.
이전에 Export할 때 .sql 형식으로 설정해둔 적이 있었고, 이번에도 당연히 CSV겠거니 생각하고 옵션을 제대로 확인하지 않은 채 진행 버튼을 눌렀다.
백업을 해두긴 했는데, 정작 어떤 형식으로 백업했는지 확인도 안 한 것이다.
이 부분도 꽤 안일했다.
CSV였다면 훨씬 단순했을 것이다.
대량 적재라면 PostgreSQL의 COPY나 \copy를 사용할 수도 있고, 필요하면 데이터를 가공하거나 일부 컬럼만 다시 넣기도 편했을 것이다.
예를 들면 이런 식이다.
1
2
3
COPY target_table
FROM '/path/data.csv'
WITH (FORMAT csv, HEADER true);
혹은 클라이언트 파일이라면 \copy를 사용할 수도 있다.
결국 “백업을 했다”는 사실만 믿을 게 아니라, 그 백업이 실제 복구하기 좋은 형태인지까지 확인했어야 했다.
그래. 그냥 다 지우고 다시 넣자
그래도 원본 데이터 자체는 있었으니 이런 생각이 들었다.
“어차피 원본 데이터 다 있는데 그냥 테이블 데이터 밀고 다시 넣으면 되는 거 아닌가?”
그런데 여기서 또 바로 실행했으면 두 번째 사고였을 것 같다.
이미 한 번 감으로 UPDATE를 날렸다가 사고를 냈는데, 복구까지 감으로 할 수는 없었다.
그래서 개발 DB를 건드리기 전에 로컬 PostgreSQL에서 먼저 테스트했다.
다행히 해당 테이블에는 FK도 없었고 sequence도 없었다.
로컬에서 기존 데이터를 삭제한 뒤 .sql 파일로 다시 데이터를 넣어보고, 건수와 주요 데이터도 확인했다.
가능한 검증은 최대한 해봤고 별문제가 없었다.
그제야 개발 DB에서도 전체 데이터를 다시 적재하는 방법을 선택할 수 있었다.
그런데 데이터가 100만 건이 넘는다
여기까지 왔으면 끝인 줄 알았다.
아니었다.
.sql 파일을 DBeaver에서 열었다.
그리고 DBeaver가 힘들어하기 시작했다.
데이터가 100만 건이 넘으니 INSERT 문도 엄청 많았다.
그때 알았다.
대용량 SQL 파일을 굳이 에디터에서 열 필요가 없다는 걸.
1
psql -h host -U user -d database -f backup.sql
이런 식으로 psql에 파일을 직접 넘기면 된다.
지금 생각하면 당연한데 당시에는 DBeaver에서 SQL을 열고 실행해야 한다는 생각에 갇혀 있었다.
그리고 또 하나 배운 게 있다.
대량 데이터를 다시 넣어야 하는 상황이라면 INSERT 수십만, 수백만 번보다는 PostgreSQL의 COPY나 \copy를 사용하는 게 훨씬 적합하다.
결국 처음부터 CSV 형태로 데이터를 제대로 Export해뒀다면 복구 과정이 훨씬 편했을 가능성이 높다.
다행히 개발망이었다
이번 일은 다행히 운영 DB가 아니라 개발 DB에서 발생했다.
그래서 서비스 장애로 이어지지는 않았다.
FK도 없었고, sequence도 없었고, 작업 전 데이터도 가지고 있었고, 로컬에서 재적재 테스트까지 정상적으로 끝났다.
운이 좋았던 부분이 꽤 많았다.
하지만 오히려 그래서 기억에 오래 남았다.
사고가 나기 전에는 단순히:
“NaN을 NULL로 바꾸면 되는 작업이네.”
정도로 생각했다.
사고가 난 뒤에는 생각할 게 갑자기 많아졌다.
_ROLLBACK은 가능한가?
autocommit은 켜져 있었나?
복구용 테이블을 만들 권한은 있나?
DB 백업에서 돌아갈 수 있나?
전체 데이터를 다시 넣어도 되나?
FK는 없나?
sequence는 없나?
그리고 Export 파일은 정말 내가 생각한 형식으로 받아둔 게 맞나?
100만 건짜리 SQL 파일은 어떻게 실행해야 하나?_
결국 배운 것
지금 다시 같은 작업을 한다면 UPDATE부터 작성하지 않을 것 같다.
먼저 SELECT로 변경 대상을 확인하고, 예상 변경 건수를 확인할 것이다.
컬럼이 많다면 복붙으로 SQL을 만들기보다 SQL을 생성하는 방법을 생각할 것 같다.
가능하면 명시적으로 트랜잭션을 열고 결과를 확인한 뒤 COMMIT할 것이다.
그리고 백업을 한다면 “뽑아뒀다”에서 끝내지 않고, 파일 형식과 실제 복구 방법까지 확인할 것 같다.
무엇보다 작업을 시작하기 전에 한 번쯤 생각할 것 같다.
“이거 잘못되면 나는 어떻게 복구하지?”
SQL 문법 하나 더 외운 것보다 이 질문을 먼저 하게 된 게, 이번 실수에서 얻은 제일 큰 수확이었다.
그리고 한 가지 더.
비슷한 코드 복붙은 생각보다 무섭고,
Export 버튼은 옵션을 보고 눌러야 한다.
DB는 내가 시킨 일을 너무 잘한다.