Microsoft MVP성태의 닷넷 이야기
개발 환경 구성: 90. 닷넷에서 접근해보는 PostgreSQL DB [링크 복사], [링크+제목 복사]
조회: 29529
글쓴 사람
정성태 (techsharer at outlook.com)
홈페이지
첨부 파일
 
닷넷에서 접근해보는 PostgreSQL DB

드디어, "PostgreSQL" 데이터베이스를 사용하고 있는 예비 고객사에서 ^^ "제니퍼 닷넷 APM 솔루션"에서 PostgreSQL DB 접근에 대한 모니터링이 가능한지 문의가 들어왔습니다. 그래서 이렇게 PostgreSQL을 처음 설치하고 만져보았는데요. 오호~~~ "관계형 데이터베이스"들이 SQL 쿼리를 지원하고 닷넷의 경우에는 .NET Data Provider를 위한 인터페이스들이 강제되어 있다 보니 기본적인 사용법이 그다지 어렵지 않더군요.

물론, 제가 바닥부터 ^^ PostgreSQL 접근을 알아본 것은 아니고 아래의 글에서부터 시작을 했습니다.

Using PostgreSQL in your C# (.NET) application (An introduction)
; http://www.codeproject.com/KB/database/afppostgresqlintro.aspx

당연히 PostgreSQL 데이터베이스를 서버에 설치부터 해야할텐데, 다음의 경로에서 윈도우 버전의 설치 파일을 다운로드 할 수 있습니다.

Download PostgreSQL 
; http://www.enterprisedb.com/products/pgdownload.do#windows

제 경우에는 x64 운영체제에 설치하기 때문에 "Win x86-64" 아이콘을 눌러서 다운로드 받은 후 설치를 했습니다. 다행히 설치는 무척 간단합니다. ^^ 여느 DB처럼, superuser를 위한 암호를 묻고, 기본 접속 포트는 5432로 설치를 마친 후 SQL 서버의 "Management Studio"와 같은 도구와 유사한 "시작" / "PostgreSQL 9.0" / "pgAdmin III" 프로그램을 실행해서 GUI 환경에서 관리를 해줄 수 있습니다.

관리방식도 꽤 직관적입니다. 누구나 한번쯤 아래와 같이 "오른쪽 마우스 버튼"을 눌러서 "Connect"를 실행해 볼 것이고,

postgresql_dotnet_1.png

설치 시 입력했던 관리자 암호를 입력한 후,

postgresql_dotnet_2.png

연결 문자열에 사용할 테스트용 사용자 계정(ID: testuser)을 아래와 같이 "Login Roles" 노드에서 생성할 수 있습니다.

postgresql_dotnet_3.png

"Database" 노드에서는 새로운 DB를 생성(Name: testdb, Owner: testuser)해 줄 수 있고,

postgresql_dotnet_4.png

생성된 "testdb" 하위 노드의 "Schemas / public / Tables" 에서 테이블을 생성할 수 있습니다.

postgresql_dotnet_5.png

Name: TestTable
Owner: testuser


"New Table..." 대화창의 "Columns" 탭에서 "Add" 버튼을 눌러 다음과 같이 "칼럼"을 추가해 주는 것으로 테스트 환경 구축은 거의 완료가 됩니다.

postgresql_dotnet_6.png

tid: bigserial NOT NULL
name: character varying(150) NOT NULL
age: integer NOT NULL


Primary Key는 "TestTable" 노드를 마우스 오른쪽 버튼 클릭해서 "New Object" / "New Primary Key" 메뉴 선택으로 지정해 줄 수 있습니다. 그 외에 SQL Server와의 변환 체계는 다음의 글을 참고하시면 되겠습니다.

Conversion of Microsoft SQL/ASP applications to PostgreSQL
; http://wiki.postgresql.org/images/e/e4/5.pdf




설치 및 테스트용 DB 구성을 정상적으로 서버에서 마쳤으면, 이제 개발자 PC에 PostgreSQL 서버를 위한 ".NET Data Provider"를 다운로드 받아야 하는데, 다음의 경로에서 C#으로 구현된 오픈소스 "Npgsql"을 구할 수 있습니다.

Npgsql - a .Net data provider for Postgresql
; http://pgfoundry.org/projects/npgsql

다운로드 Npgsql
; http://pgfoundry.org/frs/?group_id=1000140&release_id=958

Microsoft .NET Framework을 위해서 3가지 링크가 제공되는데요.

  1. Npgsql2.0.11-bin-ms.net.zip 464 KB 1,540 Any .zip
  2. Npgsql2.0.11-bin-ms.net3.5sp1.zip 511 KB 1,393 Any .zip
  3. Npgsql2.0.11-bin-ms.net4.0.zip

음... 2번과 3번의 차이는 쉽게 알겠는데 1번은 왜 있는지 잘 모르겠군요. 그냥 무시하고 ^^ 2번과 3번 중에서 원하는 것을 받아 압축을 해제한 후, 다음의 2개 파일만 여러분들의 웹 사이트 프로젝트에 복사해서 참조를 하시면 됩니다. (또는 GAC에 등록을 해주거나.)

  • Mono.Security.dll
  • Npgsql.dll

이렇게 마치고, 처음 연결문자열을 다음과 같이 설정하고 접속을 했는데,

string connectionString = "Server=testpc;Port=5432;User Id=testuser;Password=testuser;Database=testdb";
using (NpgsqlConnection connection = new NpgsqlConnection())
{
    connection.ConnectionString = connectionString;
    connection.Open();

지금까지 너무 쉽긴 했지요. ^^; 아래와 같이 오류가 발생합니다.

System.IO.IOException occurred
  Message=Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host.
  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: 

기본값이 SSL 연결만 허용하기 때문인데, "pgAdmin III" 도구에서 "Tools" / "Server Configuration" / "postgresql.conf" 메뉴를 선택하고 아래와 같이 "SSL off" 옵션을 허용시켜 주면 됩니다.

postgresql_dotnet_7.png

아니... ^^; 그래도 다음과 같이 오류가 발생하더군요.

Npgsql.NpgsqlException was unhandled by user code
  Message=FATAL: 28000: no pg_hba.conf entry for host "192.168.0.132", user "testuser", database "testdb", SSL off
  Source=Npgsql
  ErrorCode=-2147467259
  BaseMessage=no pg_hba.conf entry for host "192.168.0.132", user "testuser", database "testdb", SSL off
  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: 

음... 용서해줄 수 있습니다. 기본적인 "Lockdown" 보안 정책은 충분히 이해해 줄 수 있거든요. ^^

검색을 해보니까, PostgreSQL 서버는 접속하려는 측의 IP를 별도로 등록해 주거나, 모든 IP로부터의 접속을 허용한다는 식의 설정을 명시적으로 해주어야 한다고 나옵니다.

그래서, "pgAdmin III" 도구에서 "Tools" / "Server Configuration" / "pg_hba.conf" 메뉴를 선택하고 다음과 같이 NpgsqlConnection 개체를 사용하는 클라이언트 측의 컴퓨터 IP를 등록해 주었습니다.

postgresql_dotnet_8.png

host testdb all 192.168.0.132/32 md5


보시는 것처럼 CIDR 방식으로 지정하는 것이 가능하기 때문에, 일정한 IP 대역을 선택하는 것도 가능합니다. 예를 들어, 다음과 같이 해주면 192.168.0.*의 모든 컴퓨터로부터 접속을 허용합니다.

host     testdb  all     192.168.0.0/24  md5

설정을 변경한 후에는 "서비스 관리자"에서 "postgresql-x64-9.0" NT 서비스를 재시작 해주어야 합니다.

기타의 보다 자세한 사항은 다음의 문서를 보시면 도움이 될 것입니다. ^^

PostgreSQL 8.1.22 Documentation
- Chapter 20. Client Authentication
; http://www.postgresql.org/docs/8.1/interactive/client-authentication.html

물론, 이렇게 적용하고 난 후에는 정상적으로 NpgsqlConnection 연결이 되었고 그 외의 NpgsqlCommand, NpgsqlParameter, NpgsqlDataReader 개체는 아래의 예제 코드에서 보는 것처럼, 예의 "@MyParam"이 아닌 ":MyParam"과 같이 파라미터를 지원하는 차이만을 제외하고는 늘 해오던 수준에서 해결이 되었습니다.

using (NpgsqlConnection connection = new NpgsqlConnection())
{
    connection.ConnectionString = connectionString;
    connection.Open();

    NpgsqlCommand command = new NpgsqlCommand();

    command.Connection = connection;
    command.CommandText = "SELECT * FROM TestTable WHERE :MyParam is not null";
    command.Parameters.Add(new NpgsqlParameter("MyParam", "test"));

    NpgsqlDataReader reader = command.ExecuteReader();
    if (reader != null)
    {
        while (reader.Read())
        {
        }

        reader.Close();
    }
}




한가지, 시행착오를 더 설명드려야겠군요. ^^

"pgAdmin III" 도구에서 테이블 명을 "TestTable"과 같이 대소문자를 섞어서 지정한 경우 다음과 같은 쿼리를 NpgsqlCommand 개체에 지정해서 실행하면 오류가 발생합니다.

SELECT * FROM TestTable

오류 내용은 다음과 같습니다.

Npgsql.NpgsqlException was unhandled by user code
  Message=ERROR: 42P01: relation "testtable" does not exist
  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: 

해답은 아래에서 찾아보니 나오는데요.

Re: Database, Table Names are resulting in lowercase
; http://archives.postgresql.org/pgsql-admin/2004-06/msg00324.php

명시적으로 "쿼리문"에 포함된 테이블명을 대소문자로 인식시키려면 다음과 같이 쿼리를 바꿔주어야 합니다.

SELECT * FROM "TestTable"

C#의 경우, 아래와 같이 설정됩니다.

command.CommandText = "SELECT * FROM \"TestTable\"";

아니면, 그냥 속편하게 "pgAdmin III" 도구에서 테이블 생성할 때 소문자로만 이름을 줘도 되겠고.




[이 글에 대해서 여러분들과 의견을 공유하고 싶습니다. 틀리거나 미흡한 부분 또는 의문 사항이 있으시면 언제든 댓글 남겨주십시오.]

[연관 글]


donaricano-btn



[최초 등록일: ]
[최종 수정일: 7/10/2021

Creative Commons License
이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이센스에 따라 이용하실 수 있습니다.
by SeongTae Jeong, mailto:techsharer at outlook.com

비밀번호

댓글 쓴 사람
 



2012-02-14 11시37분
위의 글에서 "Npgsql2.0.11-bin-ms.net.zip" 파일의 용도를 모르겠다고 했는데... 차이점을 알았습니다. ^^ 그 파일은 순수하게 .NET 2.0 Framework 만 설치된 경우에 사용할 수 있습니다. "2.Npgsql2.0.11-bin-ms.net3.5sp1.zip"에 포함된 Npgsql.dll 은 .NET 3.5 에서만 배포되는 System.Data.Entity.dll 어셈블리를 참조하고 있다는 차이가 있습니다.
정성태
2015-12-01 12시33분
[gwise] 테이블 대소문자 관련 내용이 "PostgreSQL for Data Architects" 책의 210 page에도 나와 있어서 덧글 남기고 갑니다.
테이블 생성할때 대문자나 소문자 하나로 통일하고 쿼리로 사용할때는 대소문자 섞어서 사용해도 됩니다.(이게 좀 귀찮긴 하네요)
닷넷 관련 내용 잘 보고 있습니다.
[손님]
2015-12-01 01시01분
그러게요. 살짝 귀찮긴 하지만 저 정도는 감수해도 무방할 듯 합니다. ^^
정성태
2015-12-01 04시08분
[gwise] GUI툴로 테이블 만들어 내는 쿼리를 보니 대소문자 구분할때는 위에 말씀하신 바와 같이 쌍따옴표가 있습니다.
그러나 쿼리에서 직접 쌍따옴표 없이 대소문자 같이 있는 스크립트로 테이블을 만드니 전부 소문자로 생성이 되네요.
결국 원인은 테이블 생성할때의 차이때문인거 같습니다.

그리고 아래와 같이 두개 테이블 쿼리에서 생성하면 두개 다 생성이 됩니다.
CREATE TABLE Mytb (Id integer ) <- 얘는 실제로는 전부 소문자로 생성됨.
CREATE TABLE "Mytb" ("Id" integer ) <- GUI에서 테이블 생성하면 이렇게 query됨.

Postgresql 테이블이나 컬럼 생성할때 맘편하게 소문자로 하는게 정신건강에 좋을거 같습니다. ^^
[손님]
2021-08-06 09시37분
정성태

[1]  2  3  4  5  6  7  8  9  10  11  12  13  14  15  ...
NoWriterDateCnt.TitleFile(s)
12839정성태9/15/2021218.NET Framework: 1118. C# 10 - (17) 제네릭 타입의 특성 적용파일 다운로드1
12838정성태9/13/2021220.NET Framework: 1117. C# - Task에 전달한 Action, Func 유형에 따라 달라지는 async/await 비동기 처리 [2]파일 다운로드1
12837정성태9/11/2021122VC++: 151. Golang - fmt.Errorf, errors.Is, errors.As 설명
12836정성태9/10/202184Linux: 45. 리눅스 - 실행 중인 다른 프로그램의 출력을 확인하는 방법
12835정성태9/7/2021159.NET Framework: 1116. C# 10 - (16) CallerArgumentExpression 특성 추가파일 다운로드1
12834정성태9/7/202178오류 유형: 762. Visual Studio 2019 Build Tools - 'C:\Program' is not recognized as an internal or external command, operable program or batch file.
12833정성태9/6/2021131VC++: 150. Golang - TCP client/server echo 예제 코드파일 다운로드1
12832정성태9/6/202181VC++: 149. Golang - 인터페이스 포인터가 의미 있을까요?
12831정성태9/6/2021114VC++: 148. Golang - 채널에 따른 다중 작업 처리파일 다운로드1
12830정성태9/6/202178오류 유형: 761. Internet Explorer에서 파일 다운로드 시 "Your current security settings do not allow this file to be downloaded." 오류
12829정성태9/5/2021204.NET Framework: 1115. C# 10 - (15) 구조체 타입에 기본 생성자 정의 가능파일 다운로드1
12828정성태9/4/2021195.NET Framework: 1114. C# 10 - (14) 단일 파일 내에 적용되는 namespace 선언파일 다운로드1
12827정성태9/4/2021113스크립트: 27. 파이썬 - 웹 페이지 데이터 수집을 위한 scrapy Crawler 사용법 요약
12826정성태9/3/2021168.NET Framework: 1113. C# 10 - (13) 문자열 보간 성능 개선파일 다운로드1
12825정성태9/3/202169개발 환경 구성: 603. GoLand - WSL 환경과 연동
12824정성태9/2/2021143오류 유형: 760. 파이썬 tensorflow - Dst tensor is not initialized. 오류 메시지
12823정성태9/2/2021164스크립트: 26. 파이썬 - PyCharm을 이용한 fork 디버그 방법
12822정성태9/1/202197오류 유형: 759. 파이썬 tensorflow - ValueError: Shapes (...) and (...) are incompatible
12821정성태9/1/2021150.NET Framework: 1112. C# - .NET 6부터 공개된 ISpanFormattable 사용법
12820정성태9/1/202187VC++: 147. Golang - try/catch에 대응하는 panic/recover파일 다운로드1
12819정성태8/31/2021182.NET Framework: 1111. C# - FormattableString 타입
12818정성태8/31/2021118Windows: 198. 윈도우 - 작업 관리자에서 (tensorflow 등으로 인한) GPU 연산 부하 보는 방법
12817정성태8/31/202177스크립트: 25. 파이썬 - 윈도우 환경에서 directml을 이용한 tensorflow의 AMD GPU 사용 방법
12816정성태8/30/2021323스크립트: 24. 파이썬 - tensorflow 2.6 NVidia GPU 사용 방법 [2]
12815정성태8/30/2021199개발 환경 구성: 602. WSL 2 - docker-desktop-data, docker-desktop (%LOCALAPPDATA%\Docker\wsl\data\ext4.vhdx) 파일을 다른 디렉터리로 옮기는 방법
[1]  2  3  4  5  6  7  8  9  10  11  12  13  14  15  ...