성태의 닷넷 이야기
홈 주인
모아 놓은 자료
프로그래밍
질문/답변
사용자 관리
사용자
메뉴
아티클
외부 아티클
유용한 코드
온라인 기능
MathJax 입력기
최근 덧글
[정성태] Working with Rust Libraries from C#...
[정성태] Detecting blocking calls using asyn...
[정성태] 아쉽게도, 커뮤니티는 아니고 개인 블로그입니다. ^^
[정성태] 질문이 잘 이해가 안 됩니다. 우선, 해당 소스코드에서 ILis...
[양승조
] var대신 dinamic으로 선언해서 해결은 했습니다. 맞는 해...
[양승조
] 또 막혔습니다. ㅠㅠ var list = props[i].Ge...
[양승조
] 아. 감사합니다. 어제는 안됐던것 같은데....정신을 차려야겠네...
[정성태] "props[i].GetValue(props[i])" 코드에서 ...
[정성태] 저렇게 조각 코드 말고, 실제로 재현이 되는 예제 프로젝트를 압...
[정성태] Modules 창(Ctrl+Shift+U)을 띄워서, 해당 Op...
글쓰기
제목
이름
암호
전자우편
HTML
홈페이지
유형
제니퍼 .NET
닷넷
COM 개체 관련
스크립트
VC++
VS.NET IDE
Windows
Team Foundation Server
디버깅 기술
오류 유형
개발 환경 구성
웹
기타
Linux
Java
DDK
Math
Phone
Graphics
사물인터넷
부모글 보이기/감추기
내용
<div style='display: inline'> <div style='font-family: 맑은 고딕, Consolas; font-size: 20pt; color: #006699; text-align: center; font-weight: bold'>닷넷에서 접근해보는 PostgreSQL DB</div> <br /> 드디어, "PostgreSQL" 데이터베이스를 사용하고 있는 예비 고객사에서 ^^ "<a target='_tab' href='https://jennifersoft.com/ko/product/dotnet/'>제니퍼 닷넷 APM 솔루션</a>"에서 PostgreSQL DB 접근에 대한 모니터링이 가능한지 문의가 들어왔습니다. 그래서 이렇게 PostgreSQL을 처음 설치하고 만져보았는데요. 오호~~~ "관계형 데이터베이스"들이 SQL 쿼리를 지원하고 닷넷의 경우에는 .NET Data Provider를 위한 인터페이스들이 강제되어 있다 보니 기본적인 사용법이 그다지 어렵지 않더군요.<br /> <br /> 물론, 제가 바닥부터 ^^ PostgreSQL 접근을 알아본 것은 아니고 아래의 글에서부터 시작을 했습니다.<br /> <br /> <pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; width: 800px; background-color: #fbedbb; overflow-x: scroll; font-family: Consolas, Verdana;' > Using PostgreSQL in your C# (.NET) application (An introduction) ; <a target='_tab' href='http://www.codeproject.com/KB/database/afppostgresqlintro.aspx'>http://www.codeproject.com/KB/database/afppostgresqlintro.aspx</a> </pre> <br /> 당연히 PostgreSQL 데이터베이스를 서버에 설치부터 해야할텐데, 다음의 경로에서 윈도우 버전의 설치 파일을 다운로드 할 수 있습니다.<br /> <br /> <pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; width: 800px; background-color: #fbedbb; overflow-x: scroll; font-family: Consolas, Verdana;' > Download PostgreSQL ; <a target='_tab' href='http://www.enterprisedb.com/products/pgdownload.do#windows'>http://www.enterprisedb.com/products/pgdownload.do#windows</a> </pre> <br /> 제 경우에는 x64 운영체제에 설치하기 때문에 "Win x86-64" 아이콘을 눌러서 다운로드 받은 후 설치를 했습니다. 다행히 설치는 무척 간단합니다. ^^ 여느 DB처럼, superuser를 위한 암호를 묻고, 기본 접속 포트는 5432로 설치를 마친 후 SQL 서버의 "Management Studio"와 같은 도구와 유사한 "시작" / "PostgreSQL 9.0" / "pgAdmin III" 프로그램을 실행해서 GUI 환경에서 관리를 해줄 수 있습니다.<br /> <br /> 관리방식도 꽤 직관적입니다. 누구나 한번쯤 아래와 같이 "오른쪽 마우스 버튼"을 눌러서 "Connect"를 실행해 볼 것이고,<br /> <br /> <img alt='postgresql_dotnet_1.png' src='/SysWebRes/bbs/postgresql_dotnet_1.png' /><br /> <br /> 설치 시 입력했던 관리자 암호를 입력한 후,<br /> <br /> <img alt='postgresql_dotnet_2.png' src='/SysWebRes/bbs/postgresql_dotnet_2.png' /><br /> <br /> 연결 문자열에 사용할 테스트용 사용자 계정(ID: testuser)을 아래와 같이 "Login Roles" 노드에서 생성할 수 있습니다.<br /> <br /> <img alt='postgresql_dotnet_3.png' src='/SysWebRes/bbs/postgresql_dotnet_3.png' /><br /> <br /> "Database" 노드에서는 새로운 DB를 생성(Name: testdb, Owner: testuser)해 줄 수 있고,<br /> <br /> <img alt='postgresql_dotnet_4.png' src='/SysWebRes/bbs/postgresql_dotnet_4.png' /><br /> <br /> 생성된 "testdb" 하위 노드의 "Schemas / public / Tables" 에서 테이블을 생성할 수 있습니다.<br /> <br /> <img alt='postgresql_dotnet_5.png' src='/SysWebRes/bbs/postgresql_dotnet_5.png' /><br /> <br /> <div style='BACKGROUND-COLOR: #ccffcc; padding: 10px 10px 5px 10px; width: 700px; MARGIN: 0px 10px 10px 10px; FONT-FAMILY: 맑은 고딕, Consolas, Verdana; COLOR: #005555'> Name: TestTable<br /> Owner: testuser<br /> </div><br /> <br /> "New Table..." 대화창의 "Columns" 탭에서 "Add" 버튼을 눌러 다음과 같이 "칼럼"을 추가해 주는 것으로 테스트 환경 구축은 거의 완료가 됩니다.<br /> <br /> <img alt='postgresql_dotnet_6.png' src='/SysWebRes/bbs/postgresql_dotnet_6.png' /><br /> <br /> <div style='BACKGROUND-COLOR: #ccffcc; padding: 10px 10px 5px 10px; width: 700px; MARGIN: 0px 10px 10px 10px; FONT-FAMILY: 맑은 고딕, Consolas, Verdana; COLOR: #005555'> tid: bigserial NOT NULL<br /> name: character varying(150) NOT NULL<br /> age: integer NOT NULL<br /> </div><br /> <br /> Primary Key는 "TestTable" 노드를 마우스 오른쪽 버튼 클릭해서 "New Object" / "New Primary Key" 메뉴 선택으로 지정해 줄 수 있습니다. 그 외에 SQL Server와의 변환 체계는 다음의 글을 참고하시면 되겠습니다.<br /> <br /> <pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; width: 800px; background-color: #fbedbb; overflow-x: scroll; font-family: Consolas, Verdana;' > Conversion of Microsoft SQL/ASP applications to PostgreSQL ; <a target='_tab' href='http://wiki.postgresql.org/images/e/e4/5.pdf'>http://wiki.postgresql.org/images/e/e4/5.pdf</a> </pre> <br /> <hr style='width: 50%' /><br /> <br /> 설치 및 테스트용 DB 구성을 정상적으로 서버에서 마쳤으면, 이제 개발자 PC에 PostgreSQL 서버를 위한 ".NET Data Provider"를 다운로드 받아야 하는데, 다음의 경로에서 C#으로 구현된 오픈소스 "Npgsql"을 구할 수 있습니다.<br /> <br /> <pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; width: 800px; background-color: #fbedbb; overflow-x: scroll; font-family: Consolas, Verdana;' > Npgsql - a .Net data provider for Postgresql ; <a target='_tab' href='http://pgfoundry.org/projects/npgsql'>http://pgfoundry.org/projects/npgsql</a> 다운로드 Npgsql ; <a target='_tab' href='http://pgfoundry.org/frs/?group_id=1000140&release_id=958'>http://pgfoundry.org/frs/?group_id=1000140&release_id=958</a> </pre> <br /> Microsoft .NET Framework을 위해서 3가지 링크가 제공되는데요.<br /> <br /> <ol> <li>Npgsql2.0.11-bin-ms.net.zip 464 KB 1,540 Any .zip </li> <li>Npgsql2.0.11-bin-ms.net3.5sp1.zip 511 KB 1,393 Any .zip </li> <li>Npgsql2.0.11-bin-ms.net4.0.zip </li> </ol> <br /> 음... 2번과 3번의 차이는 쉽게 알겠는데 1번은 왜 있는지 잘 모르겠군요. 그냥 무시하고 ^^ 2번과 3번 중에서 원하는 것을 받아 압축을 해제한 후, 다음의 2개 파일만 여러분들의 웹 사이트 프로젝트에 복사해서 참조를 하시면 됩니다. (또는 GAC에 등록을 해주거나.)<br /> <br /> <ul> <li>Mono.Security.dll</li> <li>Npgsql.dll</li> </ul> <br /> 이렇게 마치고, 처음 연결문자열을 다음과 같이 설정하고 접속을 했는데,<br /> <br /> <pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; width: 800px; background-color: #fbedbb; overflow-x: scroll; font-family: Consolas, Verdana;' > string connectionString = "Server=testpc;Port=5432;User Id=testuser;Password=testuser;Database=testdb"; using (NpgsqlConnection connection = new NpgsqlConnection()) { connection.ConnectionString = connectionString; connection.Open(); </pre> <br /> 지금까지 너무 쉽긴 했지요. ^^; 아래와 같이 오류가 발생합니다.<br /> <br /> <pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; width: 800px; background-color: #fbedbb; overflow-x: scroll; font-family: Consolas, Verdana;' > System.IO.IOException occurred Message=<b style='COLOR: blue'>Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host.</b> Source=System StackTrace: at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size) at System.IO.BufferedStream.ReadByte() at Npgsql.NpgsqlState.<ProcessBackendResponses_Ver_3>d__a.MoveNext() in C:\...\NpgsqlState.cs:line 665 InnerException: System.Net.Sockets.SocketException Message=An existing connection was forcibly closed by the remote host Source=System ErrorCode=10054 NativeErrorCode=10054 StackTrace: at System.Net.Sockets.Socket.Receive(Byte[] buffer, Int32 offset, Int32 size, SocketFlags socketFlags) at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size) InnerException: </pre> <br /> 기본값이 SSL 연결만 허용하기 때문인데, "pgAdmin III" 도구에서 "Tools" / "Server Configuration" / "postgresql.conf" 메뉴를 선택하고 아래와 같이 "SSL off" 옵션을 허용시켜 주면 됩니다.<br /> <br /> <img onclick='toggle_img(this)' class='imgView' alt='postgresql_dotnet_7.png' src='/SysWebRes/bbs/postgresql_dotnet_7.png' /><br /> <br /> 아니... ^^; 그래도 다음과 같이 오류가 발생하더군요.<br /> <br /> <pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; width: 800px; background-color: #fbedbb; overflow-x: scroll; font-family: Consolas, Verdana;' > Npgsql.NpgsqlException was unhandled by user code Message=<b style='COLOR: blue'>FATAL: 28000: no pg_hba.conf entry for host "192.168.0.132", user "testuser", database "testdb", SSL off</b> Source=Npgsql ErrorCode=-2147467259 BaseMessage=<b style='COLOR: blue'>no pg_hba.conf entry for host "192.168.0.132", user "testuser", database "testdb", SSL off</b> Code=28000 Detail="" ErrorSql="" File=.\src\backend\libpq\auth.c Hint="" Line=464 Position="" Routine=ClientAuthentication Severity=FATAL Where="" StackTrace: at Npgsql.NpgsqlState.<ProcessBackendResponses_Ver_3>d__a.MoveNext() in C:\...\NpgsqlState.cs:line 686 at Npgsql.NpgsqlState.IterateThroughAllResponses(IEnumerable`1 ienum) in C:\...\NpgsqlState.cs:line 319 at Npgsql.NpgsqlState.ProcessBackendResponses(NpgsqlConnector context) in C:\...\NpgsqlState.cs:line 314 at Npgsql.NpgsqlConnectedState.Startup(NpgsqlConnector context) in C:\...\NpgsqlConnectedState.cs:line 52 at Npgsql.NpgsqlConnector.Open() in C:\...\NpgsqlConnector.cs:line 656 at Npgsql.NpgsqlConnectorPool.GetPooledConnector(NpgsqlConnection Connection) in C:\...\NpgsqlConnectorPool.cs:line 423 at Npgsql.NpgsqlConnectorPool.RequestPooledConnectorInternal(NpgsqlConnection Connection) in C:\...\NpgsqlConnectorPool.cs:line 226 at Npgsql.NpgsqlConnectorPool.RequestPooledConnector(NpgsqlConnection Connection) in C:\...\NpgsqlConnectorPool.cs:line 178 at Npgsql.NpgsqlConnectorPool.RequestConnector(NpgsqlConnection Connection) in C:\...\NpgsqlConnectorPool.cs:line 158 at Npgsql.NpgsqlConnection.Open() in C:\...\NpgsqlConnection.cs:line 543 at Jennifer40.WebSiteTest.postgreSQLTest.Page_Load(Object sender, EventArgs e) in D:\workshop\Jennifer\Sources\Agent\UnitTest\Jennifer40.WebSiteTest\postgreSQLTest.aspx.cs:line 23 at System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) at System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) at System.Web.UI.Control.OnLoad(EventArgs e) at System.Web.UI.Control.LoadRecursive() at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) InnerException: </pre> <br /> 음... 용서해줄 수 있습니다. 기본적인 "Lockdown" 보안 정책은 충분히 이해해 줄 수 있거든요. ^^<br /> <br /> 검색을 해보니까, PostgreSQL 서버는 접속하려는 측의 IP를 별도로 등록해 주거나, 모든 IP로부터의 접속을 허용한다는 식의 설정을 명시적으로 해주어야 한다고 나옵니다.<br /> <br /> 그래서, "pgAdmin III" 도구에서 "Tools" / "Server Configuration" / "pg_hba.conf" 메뉴를 선택하고 다음과 같이 NpgsqlConnection 개체를 사용하는 클라이언트 측의 컴퓨터 IP를 등록해 주었습니다.<br /> <br /> <img onclick='toggle_img(this)' class='imgView' alt='postgresql_dotnet_8.png' src='/SysWebRes/bbs/postgresql_dotnet_8.png' /><br /> <br /> <div style='BACKGROUND-COLOR: #ccffcc; padding: 10px 10px 5px 10px; width: 700px; MARGIN: 0px 10px 10px 10px; FONT-FAMILY: 맑은 고딕, Consolas, Verdana; COLOR: #005555'> host testdb all 192.168.0.132/<b style='COLOR: blue'>32</b> md5<br /> </div><br /> <br /> 보시는 것처럼 CIDR 방식으로 지정하는 것이 가능하기 때문에, 일정한 IP 대역을 선택하는 것도 가능합니다. 예를 들어, 다음과 같이 해주면 192.168.0.*의 모든 컴퓨터로부터 접속을 허용합니다.<br /> <br /> <pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; width: 800px; background-color: #fbedbb; overflow-x: scroll; font-family: Consolas, Verdana;' > host testdb all 192.168.0.<b style='COLOR: blue'>0/24</b> md5 </pre> <br /> 설정을 변경한 후에는 "서비스 관리자"에서 "postgresql-x64-9.0" NT 서비스를 재시작 해주어야 합니다.<br /> <br /> 기타의 보다 자세한 사항은 다음의 문서를 보시면 도움이 될 것입니다. ^^<br /> <br /> <pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; width: 800px; background-color: #fbedbb; overflow-x: scroll; font-family: Consolas, Verdana;' > PostgreSQL 8.1.22 Documentation - Chapter 20. Client Authentication ; <a target='_tab' href='http://www.postgresql.org/docs/8.1/interactive/client-authentication.html'>http://www.postgresql.org/docs/8.1/interactive/client-authentication.html</a> </pre> <br /> 물론, 이렇게 적용하고 난 후에는 정상적으로 NpgsqlConnection 연결이 되었고 그 외의 NpgsqlCommand, NpgsqlParameter, NpgsqlDataReader 개체는 아래의 예제 코드에서 보는 것처럼, 예의 "@MyParam"이 아닌 ":MyParam"과 같이 파라미터를 지원하는 차이만을 제외하고는 늘 해오던 수준에서 해결이 되었습니다.<br /> <br /> <pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; width: 800px; background-color: #fbedbb; overflow-x: scroll; font-family: Consolas, Verdana;' > using (NpgsqlConnection connection = new NpgsqlConnection()) { connection.ConnectionString = connectionString; connection.Open(); <b style='COLOR: blue'>NpgsqlCommand</b> command = new NpgsqlCommand(); command.Connection = connection; command.CommandText = "SELECT * FROM TestTable WHERE <b style='COLOR: blue'>:MyParam</b> is not null"; command.Parameters.Add(new <b style='COLOR: blue'>NpgsqlParameter</b>(<b style='COLOR: blue'>"MyParam"</b>, "test")); <b style='COLOR: blue'>NpgsqlDataReader</b> reader = command.ExecuteReader(); if (reader != null) { while (reader.Read()) { } reader.Close(); } } </pre> <br /> <hr style='width: 50%' /><br /> <br /> 한가지, 시행착오를 더 설명드려야겠군요. ^^<br /> <br /> "pgAdmin III" 도구에서 테이블 명을 "TestTable"과 같이 대소문자를 섞어서 지정한 경우 다음과 같은 쿼리를 NpgsqlCommand 개체에 지정해서 실행하면 오류가 발생합니다.<br /> <br /> <pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; width: 800px; background-color: #fbedbb; overflow-x: scroll; font-family: Consolas, Verdana;' > SELECT * FROM TestTable </pre> <br /> 오류 내용은 다음과 같습니다.<br /> <br /> <pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; width: 800px; background-color: #fbedbb; overflow-x: scroll; font-family: Consolas, Verdana;' > Npgsql.NpgsqlException was unhandled by user code Message=<b style='COLOR: blue'>ERROR: 42P01: relation "testtable" does not exist</b> Source=Npgsql ErrorCode=-2147467259 BaseMessage=relation "testtable" does not exist Code=42P01 Detail="" ErrorSql=SELECT * FROM TestTable WHERE ((E'test')) is not null AND 1 = 1 AND 'A' = 'A' File=.\src\backend\parser\parse_relation.c Hint="" Line=857 Position=15 Routine=parserOpenTable Severity=ERROR Where="" StackTrace: ...[생략]... at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) InnerException: </pre> <br /> 해답은 아래에서 찾아보니 나오는데요.<br /> <br /> <pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; width: 800px; background-color: #fbedbb; overflow-x: scroll; font-family: Consolas, Verdana;' > Re: Database, Table Names are resulting in lowercase ; <a target='_tab' href='http://archives.postgresql.org/pgsql-admin/2004-06/msg00324.php'>http://archives.postgresql.org/pgsql-admin/2004-06/msg00324.php</a> </pre> <br /> 명시적으로 "쿼리문"에 포함된 테이블명을 대소문자로 인식시키려면 다음과 같이 쿼리를 바꿔주어야 합니다.<br /> <br /> <pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; width: 800px; background-color: #fbedbb; overflow-x: scroll; font-family: Consolas, Verdana;' > SELECT * FROM "TestTable" C#의 경우, 아래와 같이 설정됩니다. command.CommandText = "SELECT * FROM \"TestTable\""; </pre> <br /> 아니면, 그냥 속편하게 "pgAdmin III" 도구에서 테이블 생성할 때 소문자로만 이름을 줘도 되겠고.<br /> <br /> <br /><br /><hr /><span style='color: Maroon'>[이 글에 대해서 여러분들과 의견을 공유하고 싶습니다. 틀리거나 미흡한 부분 또는 의문 사항이 있으시면 언제든 댓글 남겨주십시오.]</span> </div>
첨부파일
스팸 방지용 인증 번호
1156
(왼쪽의 숫자를 입력해야 합니다.)