닷넷에서 접근해보는 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"를 실행해 볼 것이고,
설치 시 입력했던 관리자 암호를 입력한 후,
연결 문자열에 사용할 테스트용 사용자 계정(ID: testuser)을 아래와 같이 "Login Roles" 노드에서 생성할 수 있습니다.
"Database" 노드에서는 새로운 DB를 생성(Name: testdb, Owner: testuser)해 줄 수 있고,
생성된 "testdb" 하위 노드의 "Schemas / public / Tables" 에서 테이블을 생성할 수 있습니다.
Name: TestTable
Owner: testuser
"New Table..." 대화창의 "Columns" 탭에서 "Add" 버튼을 눌러 다음과 같이 "칼럼"을 추가해 주는 것으로 테스트 환경 구축은 거의 완료가 됩니다.
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가지 링크가 제공되는데요.
- Npgsql2.0.11-bin-ms.net.zip 464 KB 1,540 Any .zip
- Npgsql2.0.11-bin-ms.net3.5sp1.zip 511 KB 1,393 Any .zip
- 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" 옵션을 허용시켜 주면 됩니다.
아니... ^^; 그래도 다음과 같이 오류가 발생하더군요.
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를 등록해 주었습니다.
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" 도구에서 테이블 생성할 때 소문자로만 이름을 줘도 되겠고.
[이 글에 대해서 여러분들과 의견을 공유하고 싶습니다. 틀리거나 미흡한 부분 또는 의문 사항이 있으시면 언제든 댓글 남겨주십시오.]