데이터베이스가 깨지면 다음 사용할 트랜잭션 번호가 왕창 커지게 되면서

autovacuum freezing 작업을 하는 일이 발생합니다.

그런 상황인 것 같네요.

이 경우 그 문제의 테이블이 사용자 테이블이고, 해당 테이블에서 그 자료를 지워도 되는 상황이면  참 다행인데,

지금 질문의 경우는 하필이면, 전역 테이블입니다. 제 생각으로는 dump & restore 방법 밖에는 없어보입니다.

1. vacuum full 로도 될까싶은데, 한번 해보세요. freeze로는 안풀릴겁니다.

2. 트랜잭션 로그 관련 설정값은 대부분 기본값일 터이니 따로 보아야할 것은 없습니다. 

3. 가만 놔두면, 사용할 수 있는 트랜잭션 번호를 다 사용해서 db가 읽기전용으로 바뀝니다.

4. 극단적인 상황은 새로운 디비 인스턴스를 만들고 (새로운 데이터베이스 서버를 만들고) 기존 자료를 dump해서 새 인스턴스에 부어넣어야할 것 같네요. pg_shdepend 테이블은 전역 테이블이라서 해당 xmin 또는 xmax 값의 자료를 찾아서 정리하는 것이 우선인것 같습니다.

김상기님

이런 경우 레플리케이션을 운영으로 전환하는게 나을 것 같은데 

레플리케이션이 있다면, 그 레플리케이션DB 도 마스터 전환하면 같은 문제가 나올까요? 

(문제가 없을 것이라 생각합니다만) 

예전에 다른 테이블이 비슷한 메시지 나왔는데 해당 테이블만 freezing 이 

되어서, 리네임 방식으로 그 테이블을 지우고 다시 만드니 해결되었습니다. 

당시에도 원인을 알 수 없다고 했는데, postgresql 에서 이런 점은 약간 걱정이 되네요. 

pg 전역 테이블이 문제가 되고 있고, DB가 깨진 상황도 될 수 있다는데서

운영하는 입장에서 많이 걱정이 됩니다. 

레플리케이션 구성상태에서 이상 없다면, 주기적으로 교체하고 다시 레플리케이션을 만드는 방안도 고려해야겠네요. 

그러게 말입니다. 이 불안한 DB를, 원인 분석도 어려운 이런 데이터베이스를 왜 쓰나 모르겠어요.

답변 감사합니다

여기가 replication 상태인 곳이라 standby 서버를 primary로 변경해서 해보려고 했으나 standby도 같은현상이 발생하네요​​​​

결국 pg_dump로 전체 재복구 하는걸로 방향을 잡긴 했는데..엔진 업데이트로 이런 문제 나오지 않을거라는 보장이 있을까요...?

pg 설치시에 표준 설치(yum, 설치파일 실행) 방식으로 하셨는지, 

아니면 컴파일 방식으로 하셨는지 여쭈어 봐도 될까요? 

DB 정지 후

pg_shdepend 테이블 백업 후 재생성은 안되는지요? 

vauum DB명 - 작업을 주기적으로 하시는지 궁금합니다. 

1. rpm으로 설치했습니다

2. 테이블 재생성 작업은 해보지 않았습니다

3. vacuum을 정기적으로 하고 있지는 않습니다

관련 내용에 대해 찾아보니 

pg_shdepend 테이블이 bloting 사례가 보입니다. 

해결 방안은 vacuum 을 하라는 것이지요. 

관련이 있는줄은 모르겠지만, 인덱스 rebuild 에 대해서는 

다음과 같은 문장이 있습니다. 

(standalone server 모드를 어떻게 하는줄은 모르겠습니다) 

standalone server must be used to repair it.

https://www.postgresql.org/docs/8.1/sql-reindex.html

If corruption is suspected in the indexes of any of the shared system catalogs (which are pg_authid, pg_auth_members, pg_database, pg_pltemplate, pg_shdepend, and pg_tablespace), then a standalone server must be used to repair it. REINDEX will not process shared catalogs in multiuser mode.

트랜잭션이 깨진 이유는 모르겠지만, 

이전에 비슷한 경험을 했었는데 

1) 테이블 리네임 및 문제 테이블 제거 

서비스의 테이블 접속  - 해당 테이블 데이터 백업(vacuum full 로 해결 안됨) -

신규 테이블 생성 - 신규 테이블에 데이터 migration - 원본 테이블 삭제 - 테이블 리네임 

2) vacuum analyze DB명 : 일주일 2회 이상 수행 or 1일 1회 수행 

으로 처리하였습니다. 이후로는 문제가 생기지 않았습니다. 

답변 감사합니다

​​​저도 검토 해봐야 겠네요

늦은 답변이지만, 

DB 의 모든 연결을 차단하고 

readonly 로 바꾼 뒤 

resetlog 로 올려서 XID 를 초기화 하면 어떻까요? 

한번 생각해본건데 마침 참고 사이트가 있습니다. 

https://splee75.tistory.com/100

긴급상황시 read only 읽기 전용으로 바로 바꾸기 

https://database.sarang.net/?inc=read&aid=10339&criteria=pgsql&subcrit=qna&id=0&limit=20&keyword=&page=2