database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
ㆍPostgreSQL
Firebird
Oracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
PostgreSQL Q&A 9548 게시물 읽기
No. 9548
[해결] table create 가 안됩니다.
작성자
이성필(splee75)
작성일
2015-04-29 10:40ⓒ
2015-05-04 17:05ⓜ
조회수
15,055

 안녕하세요. 눈팅만 하던 회원인데...

다급항에 데이터베이스 사랑넷에 글을 올립니다.

 

현재 postgresql-9.2.10 을 centos 에서 사용중입니다.

 

               DETAIL:  Multiple failures --- write error might be permanent.

               ERROR:  could not open file "pg_tblspc/143862353/PG_9.2_201204301/16439/199534370_fsm": No such file or directory

라는 에러를 발견하여 service 를 stop 후 pg_resetxlog 를 실행하고 서비스를 다시 실행했습니다. pg_log 의 로그상으로는 별다른 문제 없이 서비스가 잘 올라온 듯 했습니다.

 

문제는... DML은 잘 동작하는데... DDL 중에서도 create table이 안됩니다. pg_log 의 로그 파일에도 별다른 에러메시지나 반응이 없습니다.

create table 을 시도 하면, 마치 어떤 부분에 lock 이 걸린 듯한 모습으로 반응이 없습니다.

그래서 lock이 있는지 조회하는 쿼리로 보아도 lock은 발견할 수 없었습니다.

 

어떻게 문제를 풀 수 있을까요? 어디를 살펴보아야 할지도 막막하네요.

경험자 님들의 여러가지 의견이 궁금합니다.

 

감사합니다.

이 글에 대한 댓글이 총 9건 있습니다.

 lock 을 살펴본 쿼리 입니다.

SELECT blocked_locks.pid     AS blocked_pid,
blocked_activity.usename  AS blocked_user,
blocking_locks.pid     AS blocking_pid,
blocking_activity.usename AS blocking_user,
blocked_activity.query    AS blocked_statement,
blocking_activity.query   AS blocking_statement
FROM  pg_catalog.pg_locks         blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity  ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks         blocking_locks 
ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxidwhghl
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;
이성필(splee75)님이 2015-04-29 10:41에 작성한 댓글입니다.

해당 작업은 디스크를 쓰는 작업입니다.

디스크 입출력에 문제가 생겼나봅니다.

장애가 더 확대되기 전에 먼저 데이터베이스를 중지하고,

해당 데이터가 보관디는 디스크 볼륨의 파일 시스템 검사를 해야 할 것 같습니다.

 

해당 작업 권한이 없다면,

create 작업을 하는 세션 프로세스의 프로세스 상태를 담당자에게 보여주세요.

아마 Ds (Dead Sleep) 상태일것입니다.

이 상태는 해당 프로세스가  데이터베이스 서버 작업을 떠나 OS 차원에서 중지되고 있는 것을 의미하거든요. 

네트워크 통신 오류나, 디스크 입출력 오류 때 주로 발생합니다.

김상기(ioseph)님이 2015-04-29 12:54에 작성한 댓글입니다.

 소중한 답변 감사합니다.

 

프로세스 상태는 Rs 입니다. cpu도.. 좀 쓰는 것 같고... 아래는 ps -aux 로 본 정보 입니다.

-bash-4.1$ ps -aux | grep 23358
Warning: bad syntax, perhaps a bogus '-'? See /usr/share/doc/procps-3.2.8/FAQ
postgres 23358 95.0  0.0 32550932 6188 ?       Rs   13:30   8:38 postgres: postgres db_patent [local] CREATE TABLE               
postgres 23772  0.0  0.0 103248   852 pts/2    S+   13:40   0:00 grep 23358

 

혹시 pg_log 이외에 참고할 만한 상태 정보를 알 수 있는 방법이 있나요?

 

이성필님이 2015-04-29 13:42에 작성한 댓글입니다. Edit

lock 때문에 대기 상태면, 프로세스 상태가 Ss 로 바뀝니다.

R 상태라는게 이상하네요.

그렇다면, 트랜잭션 로그쪽 문제로 봐야하는데, 이도 DML 작업이 잘 일어난다고 하니, 아닐 것이고,

혹 참조키가 있어 그 참조하는 테이블 쪽에서 실마리를 찾아볼 수도 있겠네요.

( 그럼에도 불구하고, 저는 디스크 I/O 쪽으로 의심이 제일 많이 갑니다. OS dmesg 같은 OS 로그를 살펴보는 것도 좋을 것 같습니다.)

 

명확하게 DB문제로 보인다면, 디버그 모드로 재 컴파일하고, gdb attach 로 어느 부분에서 작업이 멈춰있는지 살펴보는 방법 밖에는 없어보입니다. (일단은 gdb 로 바로 attach 해서 backtrace  보는 것도 급하게 할 수 있는 조치입니다.)

김상기(ioseph)님이 2015-04-29 15:34에 작성한 댓글입니다.

 현재 데이터베이스의 물리 백업 을 다른 서버에 복구하여 동일한 상황을 만들고 이것 저것 테스트 중입니다.

 

일주일 전의 논리 백업 이 있기는 한데...  이것으로 복구하면, 데이터를 맞추어 줄 수없어서 pg_dump 가 사용 가능한지 확인 했지만... pg_dump 가 사용이 안되는 상황입니다.

다른 부분은 이상이 없고, 테이블 생성 등의 부분이 이상이 있으므로... reindex system [dbname] force; 를 실행하고 결과를 기다리는 중입니다만... 상당히 오래 걸리네요.. 문제는 이 구문이 실행되는 동안 데이터베이스의 다른 연결을 허용하지 않는 듯 합니다. 

ps -aux 로 프로세스 상태를 보면.. Rs 이고.. cpu 도 많이 쓰고.. 해서 놀고 있지는 않은데... 조급해서 그런지 실행시간이 상당히 오래 걸리는 것 같습니다.

혹시 다른 방법이 생각 나는 것이 있으면... 알려주셨으면 합니다.

 

김상기님께서 말씀해 주신 gdb 는... 저에게 너무 어려운 내용이라 ... 테스트 하다 끝을 보지 못했습니다. 그래도 시도의 방법을 알려주셔서 감사합니다.

이성필(splee75)님이 2015-04-29 20:23에 작성한 댓글입니다.

 혹시나 해서 제가 gdb로 테스트한 내용을 올립니다.

-----------------------------------

-bash-4.1$ ps -aux | grep ^postgres
Warning: bad syntax, perhaps a bogus '-'? See /usr/share/doc/procps-3.2.8/FAQ
postgres  7382  0.0  0.0 179636  1216 ?        Ss   19:03   0:00 postgres: logger process                                        
postgres  7803  0.0  0.0 33307480 1496 ?       Ss   19:30   0:00 postgres: postgres db_patent [local] authentication             
postgres  8267  0.0  0.0 108468  1868 pts/2    S    19:41   0:00 -bash
postgres  8826  0.2  1.0 33291228 1428796 ?    S    20:12   0:01 /usr/pgsql-9.2/bin/postmaster -p 5432 -D /var/lib/pgsql/9.2/data
postgres  8828  0.0  0.0 179636  1216 ?        Ss   20:12   0:00 postgres: logger process                                        
postgres  8830  0.0  0.1 33306592 201672 ?     Ss   20:12   0:00 postgres: checkpointer process                                  
postgres  8831  0.0  0.1 33306592 201116 ?     Ss   20:12   0:00 postgres: writer process                                        
postgres  8832  0.0  0.0 33306592 1480 ?       Ss   20:12   0:00 postgres: wal writer process                                    
postgres  8833  0.0  0.0 33313868 2824 ?       Ss   20:12   0:00 postgres: autovacuum launcher process                           
postgres  8834  0.0  0.0 181876  1464 ?        Ss   20:12   0:00 postgres: stats collector process                               
postgres  8839  0.0  0.0 108464  1892 pts/1    S    20:12   0:00 -bash
postgres  8876  0.0  0.0 100936   608 pts/1    S+   20:12   0:00 tail -f postgresql-2015-04-29_201203.csv
postgres  8881  0.0  0.0 170800  2964 pts/2    S+   20:13   0:00 psql -d db_patent
postgres  8882 97.6  0.2 33314864 337600 ?     Rs   20:13  12:08 postgres: postgres db_patent [local] REINDEX                    
postgres  8884  0.0  0.0 33314116 3892 ?       Ss   20:13   0:00 postgres: autovacuum worker process    waiting                  
postgres  8904  0.0  0.0 33314116 3888 ?       Ss   20:14   0:00 postgres: autovacuum worker process    waiting                  
postgres  8922  0.0  0.0 33314112 3900 ?       Ss   20:15   0:00 postgres: autovacuum worker process    waiting                  
postgres  9021  0.0  0.0 108464  1868 pts/3    S    20:25   0:00 -bash
postgres  9057 11.0  0.0 110232  1164 pts/3    R+   20:25   0:00 ps -aux
postgres  9058  0.0  0.0 103248   880 pts/3    S+   20:25   0:00 grep ^postgres
-bash-4.1$ gdb /usr/pgsql-9.2/bin/postmaster
GNU gdb (GDB) Red Hat Enterprise Linux (7.2-60.el6_4.1)
Copyright (C) 2010 Free Software Foundation, Inc.
License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html>
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.  Type "show copying"
and "show warranty" for details.
This GDB was configured as "x86_64-redhat-linux-gnu".
For bug reporting instructions, please see:
<http://www.gnu.org/software/gdb/bugs/>...
Reading symbols from /usr/pgsql-9.2/bin/postmaster...(no debugging symbols found)...done.
Missing separate debuginfos, use: debuginfo-install postgresql92-server-9.2.4-1PGDG.rhel6.x86_64
(gdb) attach 8882
Attaching to program: /usr/pgsql-9.2/bin/postmaster, process 8882
Reading symbols from /usr/lib64/libxml2.so.2...(no debugging symbols found)...done.
Loaded symbols for /usr/lib64/libxml2.so.2
Reading symbols from /lib64/libpam.so.0...(no debugging symbols found)...done.
Loaded symbols for /lib64/libpam.so.0
Reading symbols from /usr/lib64/libssl.so.10...(no debugging symbols found)...done.
Loaded symbols for /usr/lib64/libssl.so.10
Reading symbols from /usr/lib64/libcrypto.so.10...(no debugging symbols found)...done.
Loaded symbols for /usr/lib64/libcrypto.so.10
Reading symbols from /lib64/libgssapi_krb5.so.2...(no debugging symbols found)...done.
Loaded symbols for /lib64/libgssapi_krb5.so.2
Reading symbols from /lib64/libdl.so.2...(no debugging symbols found)...done.
Loaded symbols for /lib64/libdl.so.2
Reading symbols from /lib64/libm.so.6...(no debugging symbols found)...done.
Loaded symbols for /lib64/libm.so.6
Reading symbols from /lib64/libldap-2.4.so.2...(no debugging symbols found)...done.
Loaded symbols for /lib64/libldap-2.4.so.2
Reading symbols from /lib64/libc.so.6...(no debugging symbols found)...done.
Loaded symbols for /lib64/libc.so.6
Reading symbols from /lib64/libkrb5.so.3...(no debugging symbols found)...done.
Loaded symbols for /lib64/libkrb5.so.3
Reading symbols from /lib64/libcom_err.so.2...(no debugging symbols found)...done.
Loaded symbols for /lib64/libcom_err.so.2
Reading symbols from /lib64/libz.so.1...(no debugging symbols found)...done.
Loaded symbols for /lib64/libz.so.1
Reading symbols from /lib64/libaudit.so.1...(no debugging symbols found)...done.
Loaded symbols for /lib64/libaudit.so.1
Reading symbols from /lib64/libcrypt.so.1...(no debugging symbols found)...done.
Loaded symbols for /lib64/libcrypt.so.1
Reading symbols from /lib64/libk5crypto.so.3...(no debugging symbols found)...done.
Loaded symbols for /lib64/libk5crypto.so.3
Reading symbols from /lib64/libkrb5support.so.0...(no debugging symbols found)...done.
Loaded symbols for /lib64/libkrb5support.so.0
Reading symbols from /lib64/libkeyutils.so.1...(no debugging symbols found)...done.
Loaded symbols for /lib64/libkeyutils.so.1
Reading symbols from /lib64/libresolv.so.2...(no debugging symbols found)...done.
Loaded symbols for /lib64/libresolv.so.2
Reading symbols from /lib64/ld-linux-x86-64.so.2...(no debugging symbols found)...done.
Loaded symbols for /lib64/ld-linux-x86-64.so.2
Reading symbols from /lib64/liblber-2.4.so.2...(no debugging symbols found)...done.
Loaded symbols for /lib64/liblber-2.4.so.2
Reading symbols from /usr/lib64/libssl3.so...(no debugging symbols found)...done.
Loaded symbols for /usr/lib64/libssl3.so
Reading symbols from /usr/lib64/libsmime3.so...(no debugging symbols found)...done.
Loaded symbols for /usr/lib64/libsmime3.so
Reading symbols from /usr/lib64/libnss3.so...(no debugging symbols found)...done.
Loaded symbols for /usr/lib64/libnss3.so
Reading symbols from /usr/lib64/libnssutil3.so...(no debugging symbols found)...done.
Loaded symbols for /usr/lib64/libnssutil3.so
Reading symbols from /lib64/libplds4.so...(no debugging symbols found)...done.
Loaded symbols for /lib64/libplds4.so
Reading symbols from /lib64/libplc4.so...(no debugging symbols found)...done.
Loaded symbols for /lib64/libplc4.so
Reading symbols from /lib64/libnspr4.so...(no debugging symbols found)...done.
Loaded symbols for /lib64/libnspr4.so
Reading symbols from /usr/lib64/libsasl2.so.2...(no debugging symbols found)...done.
Loaded symbols for /usr/lib64/libsasl2.so.2
Reading symbols from /lib64/libpthread.so.0...(no debugging symbols found)...done.
[Thread debugging using libthread_db enabled]
Loaded symbols for /lib64/libpthread.so.0
Reading symbols from /lib64/libfreebl3.so...(no debugging symbols found)...done.
Loaded symbols for /lib64/libfreebl3.so
Reading symbols from /lib64/libselinux.so.1...(no debugging symbols found)...done.
Loaded symbols for /lib64/libselinux.so.1
Reading symbols from /lib64/librt.so.1...(no debugging symbols found)...done.
Loaded symbols for /lib64/librt.so.1
Reading symbols from /usr/pgsql-9.2/lib/pg_hint_plan.so...done.
Loaded symbols for /usr/pgsql-9.2/lib/pg_hint_plan.so
Reading symbols from /lib64/libnss_files.so.2...(no debugging symbols found)...done.
Loaded symbols for /lib64/libnss_files.so.2
0x000000000072a959 in hash_search_with_hash_value ()
(gdb) detach 8882
Undefined detach command: "8882".  Try "help detach".
(gdb) quit
A debugging session is active.
 
        Inferior 1 [process 8882] will be detached.
 
Quit anyway? (y or n) y
Detaching from program: /usr/pgsql-9.2/bin/postmaster, process 8882
-bash-4.1$ 
-bash-4.1$ 
이성필(splee75)님이 2015-04-29 20:27에 작성한 댓글입니다.

 안녕하세요. 장애 원인이 밝혀져서 처리 및 후속조치에 들어가 결과를 공유하고자 글을 남깁니다.

 

원인은 공유 스토리지의 마운트가 잘못되어 있었던 것으로 밝혀졌습니다.

 

현재 두대의 postgresql에 하나의 스토리지를 영역이 다르게 NAS로 연결해서 데이터 디렉토리(테이블 스페이스)로 사용하고 있습니다. 이런 구성의 효율 및 안정성이 좋지 못하다는 것은 알고 있지만... 최초 프로젝트 출발시에는 이런 저런 환경을 따져서가면서 시작을 할 수 없는 상황이었지요. 구성에 대한 히스토리 및 우여곡절은 넘어가고...

 

두대중 한대는 항상 서비스 온 상태이고, 한대는 필요시만 서비스가 온 되는 상황입니다.

두대의 데이터베이스 인스턴스에서 각각 일부의 마운트 포인트를 공유하면서 다른쪽 데이터베이스가 서비스 온 상태이거나 해당 데이터 디렉토리에 무언가 작업을 할 때 다른 쪽 데이터베이스가 파일을 점유하고 있어서 대기 상태에 빠진것으로 추측됩니다. 그래서 제가 복구하려고 시도한 데이터베이스에서 테이블 을 생성하려고 하면 시스템 카탈로그를 갱신해야 하고 그럴러면 모든 테이블스페이스를 다 뒤지는 것 같은데... 그 때 대기상태로 계속 기다리는 것 같습니다.

 

다행히도 그쪽 테이블 스페이스에 직접적으로 데이터 변경행위를 한 것이 없어서 복구에 큰 문제는 없어보입니다.  복구 방법은 각각 독립된 데이터디렉토리로 다시 마운트되도록 설정하고, reindex system 으로 시스템 카탈로그를 리인덱스 했습니다. 그 후 테이블 생성이 잘 되었습니다. 혹시 몰라 해당 데이터디렉토리의 테이블 전체를 리인덱스 중입니다.

 

여러가지 방법을 제안해 주신 김상기(ioseph)님께 다시 한번 감사드립니다.

이성필(splee75)님이 2015-05-04 17:02에 작성한 댓글입니다.
이 댓글은 2015-05-04 17:04에 마지막으로 수정되었습니다.

 전통적으로 DB의 자료는 NAS에 저장하지 않습니다. 

처음부터 디스크 I/O쪽이다고 말씀드렸는데, 그 부분부터 출발했다면, 디스크 I/O 쪽 설계부터 살펴보았다면 빨리 찾았을텐데 말이죠.

NAS 장애쪽은 OS 입장에서 찾기가 참 힘듭니다. 증상이 그 장치쪽으로 접근 할 때, 그 접근하는 프로세스만 딱 먹통이 되거든요. 그래서 OS 입장에서는 아무런 오류가 없는 것 처럼 보입니다.

여튼 잘 푸셨다니 다행이네요. 여건이 되면 DB 스토리지 관련을 정리하셔야겠네요.

김상기(ioseph)님이 2015-05-06 09:01에 작성한 댓글입니다.

 디스크 I/O 쪽이라고 언급을 주셔서 그나마 찾은 것이라 생각되어 김상기님께 정말 고맙게 생각합니다.  저도 NAS에서 Database를 운영하는 것은 처음이라서 이래저래 어려움이 있습니다. 다시한번 정말 감사드립니다.

 

이성필(splee75)님이 2015-05-06 15:26에 작성한 댓글입니다.
[Top]
No.
제목
작성자
작성일
조회
9553pg_dump 후 다른 스키마로 restore 하는 법 [3]
정성헌
2015-05-27
10479
9550postgreSQL DB 용량을 제한할 수 있는지 궁금합니다. [3]
박종혁
2015-05-15
11031
9549Postgresql Query 종료 방법 문의 [3]
이성필
2015-05-14
11487
9548[해결] table create 가 안됩니다. [9]
이성필
2015-04-29
15055
9547pgpool-II 구성중에 질문드립니다. [3]
한승현
2015-04-28
15273
9546이전 까지의 누적 횟수? 구하기 쿼리 [1]
김재영
2015-04-24
11004
9545desc 권한 질문입니다. [1]
입문자
2015-04-23
10162
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2024 DSN, All rights reserved.
작업시간: 0.020초, 이곳 서비스는
	PostgreSQL v16.4로 자료를 관리합니다