Innovating today, leading tomorrow

OpenSQL_Technical Guide
홈
•
•
•
•
[OpenSQL] (Interface).NET – Npgsql

[OpenSQL] (Interface).NET – Npgsql

Npgsql 개요

Npgsql란?

Npgsql is an open source ADO.NET Data Provider for PostgreSQL, it allows programs written in C#, Visual Basic, F# to access the PostgreSQL database server. It is implemented in 100% C# code, is free and is open source. An Entity Framework Core provider is also available, and exposes some features unique to the PostgreSQL database to EF Core users.

Finally, a legacy Entity Framework 6.x (non-Core) provider is also available, but is no longer being actively maintained.

– Npgsql 공식 홈페이지 소개 내용-

Npgsql – .NET Access to PostgreSQL | Npgsql Documentation
Npgsql은 100% C# 코드로 만들어진 PostgreSQL 인터페이스로 C#, Visual Basic, F# 프로그래밍 언어에서 PostgreSQL 데이터베이스에 접속할 수 있도록 만들어주는 오픈소스 라이브러리 입니다.
Entitiy Framework Core에서도 사용 가능하며, Entitiy Framework 6 버전도 지원합니다.

.NET 이란?
.NET은 모든 운영체제에서 기본적으로 실행할 수 있는 데스크탑, 웹 및 모바일 어플리케이션 빌드를 위한 오픈 소스 플랫폼입니다. .NET Framework는 Windows에서 웹, 서비스, 데스크톱 등 앱을 실행할 수 있도록 지원합니다. .NET Core는 .NET 개발자를 위한 교차 플랫폼을 지원합니다.

Npgsql 버전 호환성 확인

PostgreSQL

버전 릴리즈 날짜를 기준으로 5년 이전의 PostgreSQL 버전(Supported 한정)까지 호환 됩니다.
아래의 표를 참고하면 현재 시점에서는 11버전 까지 지원된다고 볼 수 있습니다.

ADO.NET
Npgsql은 ADO.NET 호환을 준수하므로 다른 .NET 데이터베이스 드라이버와 동일한 표준 API를 가지고있습니다.

.NET Framework
.NET Framework 5버전 까지 Npgsql 5.x 버전이 지원되며, PostgreSQL 버전 호환성을 참고해야 합니다.

Npgsql 다운로드

Npgsql은 nuget 패키지를 이용하여 쉽게 설치할 수 있습니다.

nuget install npgsql

Npgsql 실습 환경 설치

Linux(CentOS7) 와 Windows 10 두 환경에서 설치를 해보도록 하겠습니다.

Linux (CentOS 7)에 설치

설치 시 필요한 패키지 목록

postgresql-14.2 기준

  • .NET Framework SDK 7.0

실습 환경은 .NET Framework 7.0을 아래와 같이 설치하도록 하겠습니다.

.NET Framework 7.0 설치

  1. .NET Framework 7.0 다운로드 Liunx Installers버전으로 다운로드 합니다.

https://dotnet.microsoft.com/en-us/download/dotnet/thank-you/sdk-7.0.304-linux-x64-binaries

Linux Installers 버전은 OS의 패키지 매니저를 사용하는 방식으로 가이드 되어있습니다.

실습 환경은 CentOS 7이므로, rpm을 이용해서 .NET 설치에 필요한 Repository를 설치하겠습니다.

sudo rpm -Uvh https://packages.microsoft.com/config/centos/7/packages-microsoft-prod.rpm

.NET SDK 및 nuget 패키지매니저를 설치합니다.

yum install dotnet-sdk-7.0 nuget

Binary로 받아서 빌드해서 사용하시려면 아래의 .NET 종속성 패키지를 확인 해주세요.

https://github.com/dotnet/core/blob/main/release-notes/7.0/linux-packages.md

2. nuget을 최신 버전으로 업데이트 합니다.

nuget update -self

3. nuget을 이용해서 npgsql을 설치합니다.

mkdir npgsql_test
cd npgsql_test
nuget install npgsql -Source <https://api.nuget.org/v3/index.json>

4. 실습 할 .NET 프로젝트를 생성하고 Program.cs를 삭제합니다.

dotnet new console
rm -f Program.cs

5. 프로젝트에 npgsql 모듈을 추가합니다.

dotnet add package npgsql

테스트 파일을 생성합니다.
npgsql.cs

using System;
using Npgsql;
namespace npgsql_test {
class Program {
static void Main(string[] args) {
string host = “”;
string database=””;
string userName=””;
string password=””;
string query=””;

        Console.WriteLine("==== npgsql connection test ====n");
        Console.WriteLine("1. Quick test  2.Custom test");
        Console.Write("Input Test number : ");
        int num = int.Parse(Console.ReadLine());
        if(num == 1) test_config(ref host, ref database, ref userName, ref password, ref query);
        else {
            configureConnection(ref host, ref database, ref userName, ref password);
            while((host == null || host.Equals("")) || (database == null || database.Equals("")) || (userName == null|| userName.Equals("")) || (password == null || password.Equals(""))){
                Console.WriteLine("입력 값에 공백이 있거나, 값이 잘못되었습니다.");
                configureConnection(ref host, ref database, ref userName, ref password);
            }
        }
        using (var conn = new NpgsqlConnection("Host="+host+";"+"Username="+userName+";"+"Password="+password+";"+"Database="+database+";")) { 
            try { 
                conn.Open();
                Console.WriteLine("Database "+database+" Connectedn");
                if(num == 2 ){
                    Console.Write("실행할 Query 입력 : ");
                    query = Console.ReadLine();
                    while((query == null || query.Equals(""))){
                        Console.WriteLine("공백이 입력되었습니다. 다시 입력해주세요.");
                        Console.Write("실행할 Query 입력 : ");
                        query = Console.ReadLine();
                    }
                }
                using (var cmd = new NpgsqlCommand()) { 
                    cmd.Connection = conn;
                    cmd.CommandText = query;
                    using ( var reader = cmd.ExecuteReader()) {
                        for(int idx=0; idx < reader.FieldCount; idx++) { 
                            Console.Write(reader.GetName(idx));
                            if(idx != reader.FieldCount-1) Console.Write("|");
                            else Console.WriteLine();
                        }
                        while (reader.HasRows && reader.Read()) {
                            for(int colnum=0; colnum < reader.FieldCount; colnum++){
                                if(reader.IsDBNull(colnum)) Console.Write("null");
                                else if(reader.GetPostgresType(colnum).ToString().Contains("character")) Console.Write(reader.GetString(colnum));
                                else if(reader.GetPostgresType(colnum).ToString().Contains("integer")) Console.Write(reader.GetInt32(colnum)); 

                                if(colnum != reader.FieldCount-1){
                                    Console.Write(", ");
                                }
                            }
                            Console.WriteLine();
                        } 
                    } 
                } 
            }
            catch (Exception ex) {
                Console.WriteLine("============== Error ==============");
                Console.WriteLine(ex.Message); 
            } finally {
                conn.Close();
                Console.WriteLine("nDatabase disconnected successfully");
            }
        } 
    }

    static void configureConnection(ref string host, ref string database, ref string userName, ref string password){
        try{
            Console.Write("DB 서버 HOST 입력 : ");
            host = Console.ReadLine();
            Console.Write("접속할 Database 입력 : ");
            database = Console.ReadLine();
            Console.Write("접속할 유저명 입력 : ");
            userName = Console.ReadLine();
            Console.Write("접속할 유저 패스워드 입력 : ");
            password = Console.ReadLine();
            Console.WriteLine();
        } catch(Exception e){
            throw e;
        }
    }

    static void test_config(ref string host, ref string database, ref string userName, ref string password, ref string query){
        try{
            host = "127.0.0.1";
            database = "postgres";
            userName = "postgres";
            password = "1234";
            query = "SELECT * FROM pg_database";
        } catch(Exception e){
            throw e;
        }
    }  
}

}

7. 빌드 및 실행합니다.

dotnet build
dotnet run

Windows 10 (64-bit)에 설치

설치 시 필요한 패키지 목록
postgresql-14.2 기준

  • dotnet SDK (7.0)

실습 환경은 dotnet SDK를 아래와 같이 설치하도록 하겠습니다.

Dotnet SDK설치

1. Dotnet SDK 다운로드
Microsoft Windows 버전으로 다운로드 합니다.
Download .NET 7.0 SDK (v7.0.400) – Windows x64 Installer

2. Dotnet SDK설치

3. 설치가 잘 되었는지 확인합니다.
윈도우 버전의 인스톨러에는 환경변수가 자동으로 잡혀있습니다.

#시작 – 실행 – cmd

dotnet –info

.NET SDK:
Version: 7.0.400
Commit: 73bf45718d

런타임 환경:
OS Name: Windows
OS Version: 10.0.19045
OS Platform: Windows
RID: win10-x64
Base Path: c:program filesdotnetsdk7.0.400

Host:
Version: 7.0.10
Architecture: x64
Commit: a6dbb800a4

.NET SDKs installed:
5.0.100 [c:program filesdotnetsdk]
7.0.400 [c:program filesdotnetsdk]

.NET runtimes installed:
Microsoft.AspNetCore.All 2.1.23 [c:program filesdotnetsharedMicrosoft.AspNetCore.All]
Microsoft.AspNetCore.App 2.1.23 [c:program filesdotnetsharedMicrosoft.AspNetCore.App]
Microsoft.AspNetCore.App 3.1.9 [c:program filesdotnetsharedMicrosoft.AspNetCore.App]
Microsoft.AspNetCore.App 5.0.0 [c:program filesdotnetsharedMicrosoft.AspNetCore.App]
Microsoft.AspNetCore.App 7.0.10 [c:program filesdotnetsharedMicrosoft.AspNetCore.App]
Microsoft.NETCore.App 2.1.23 [c:program filesdotnetsharedMicrosoft.NETCore.App]
Microsoft.NETCore.App 3.1.9 [c:program filesdotnetsharedMicrosoft.NETCore.App]
Microsoft.NETCore.App 5.0.0 [c:program filesdotnetsharedMicrosoft.NETCore.App]
Microsoft.NETCore.App 7.0.10 [c:program filesdotnetsharedMicrosoft.NETCore.App]
Microsoft.WindowsDesktop.App 3.1.9 [c:program filesdotnetsharedMicrosoft.WindowsDesktop.App]
Microsoft.WindowsDesktop.App 5.0.0 [c:program filesdotnetsharedMicrosoft.WindowsDesktop.App]
Microsoft.WindowsDesktop.App 7.0.10 [c:program filesdotnetsharedMicrosoft.WindowsDesktop.App]

Other architectures found:
x86 [C:Program Files (x86)dotnet]
registered at [HKLMSOFTWAREdotnetSetupInstalledVersionsx86InstallLocation]

Environment variables:
Not set

global.json file:
Not found

Learn more:
https://aka.ms/dotnet/info

Download .NET:
https://aka.ms/dotnet/download

4. nuget을 설치하고 테스트를 수행할 폴더에 옮겨줍니다.
환경변수에 등록하지 않고 진행하겠습니다.

mkdir npgsql_test
move nuget.exe npgsql_text

5. 최신 버전을 다운로드 받았지만, cmd 창에서 아래의 커맨드를 수행하여 nuget을 최신 버전인지 체크합니다.

nuget.exe update -self

6. nuget을 이용해서 npgsql을 설치합니다.

nuget.exe install npgsql -Source https://api.nuget.org/v3/index.json

7. npgsql이 제대로 설치되었는지 디렉토리를 확인합니다.

C:Userslhw56Downloadsnpgsql_test>dir
C 드라이브의 볼륨: Local Disk
볼륨 일련 번호: 045A-0E74

C:Userslhw56Downloadsnpgsql_test 디렉터리

2023-08-09 오후 04:23 .
2023-08-09 오후 04:19 ..
2023-08-09 오후 04:23 Microsoft.Extensions.Logging.Abstractions.6.0.0
2023-08-09 오후 04:23 Npgsql.7.0.4
2023-08-09 오후 04:15 7,333,336 nuget.exe
2023-08-09 오후 04:23 System.Buffers.4.5.1
2023-08-09 오후 04:23 System.Memory.4.5.4
2023-08-09 오후 04:23 System.Numerics.Vectors.4.5.0
2023-08-09 오후 04:23 System.Runtime.CompilerServices.Unsafe.4.5.3
1개 파일 7,333,336 바이트
8개 디렉터리 1,948,368,064,512 바이트 남음

Npgsql 사용방법

Linux(CentOS 7)와 Windows 10 모두 동일한 절차로 진행됩니다.

실습 환경
아래와 같은 환경에서 Npgsql을 사용 해보는 실습을 진행하겠습니다.

OSCentOS 7.9Windows 10
PostgreSQL VersionPostgreSQL 14.2PostgreSQL 14.2
Dotnet SDK Version7.0.47.0.4
Npgsql Version7.0.47.0.4

1.실습 할 .NET 프로젝트를 생성하고 Program.cs를 삭제합니다.

dotnet new console
rm -f Program.cs

2. 프로젝트에 npgsql 모듈을 추가합니다.

dotnet add package npgsql

3. 테스트 파일을 생성합니다.

npgsql.cs

using System;
using Npgsql;
namespace npgsql_test {
class Program {
static void Main(string[] args) {
string host = “”;
string database=””;
string userName=””;
string password=””;
string query=””;

        Console.WriteLine("==== npgsql connection test ====n");
        Console.WriteLine("1. Quick test  2.Custom test");
        Console.Write("Input Test number : ");
        int num = int.Parse(Console.ReadLine());
        if(num == 1) test_config(ref host, ref database, ref userName, ref password, ref query);
        else {
            configureConnection(ref host, ref database, ref userName, ref password);
            while((host == null || host.Equals("")) || (database == null || database.Equals("")) || (userName == null|| userName.Equals("")) || (password == null || password.Equals(""))){
                Console.WriteLine("입력 값에 공백이 있거나, 값이 잘못되었습니다.");
                configureConnection(ref host, ref database, ref userName, ref password);
            }
        }
        using (var conn = new NpgsqlConnection("Host="+host+";"+"Username="+userName+";"+"Password="+password+";"+"Database="+database+";")) { 
            try { 
                conn.Open();
                Console.WriteLine("Database "+database+" Connectedn");
                if(num == 2 ){
                    Console.Write("실행할 Query 입력 : ");
                    query = Console.ReadLine();
                    while((query == null || query.Equals(""))){
                        Console.WriteLine("공백이 입력되었습니다. 다시 입력해주세요.");
                        Console.Write("실행할 Query 입력 : ");
                        query = Console.ReadLine();
                    }
                }
                using (var cmd = new NpgsqlCommand()) { 
                    cmd.Connection = conn;
                    cmd.CommandText = query;
                    using ( var reader = cmd.ExecuteReader()) {
                        for(int idx=0; idx < reader.FieldCount; idx++) { 
                            Console.Write(reader.GetName(idx));
                            if(idx != reader.FieldCount-1) Console.Write("|");
                            else Console.WriteLine();
                        }
                        while (reader.HasRows && reader.Read()) {
                            for(int colnum=0; colnum < reader.FieldCount; colnum++){
                                if(reader.IsDBNull(colnum)) Console.Write("null");
                                else if(reader.GetPostgresType(colnum).ToString().Contains("character")) Console.Write(reader.GetString(colnum));
                                else if(reader.GetPostgresType(colnum).ToString().Contains("integer")) Console.Write(reader.GetInt32(colnum)); 

                                if(colnum != reader.FieldCount-1){
                                    Console.Write(", ");
                                }
                            }
                            Console.WriteLine();
                        } 
                    } 
                } 
            }
            catch (Exception ex) {
                Console.WriteLine("============== Error ==============");
                Console.WriteLine(ex.Message); 
            } finally {
                conn.Close();
                Console.WriteLine("nDatabase disconnected successfully");
            }
        } 
    }

    static void configureConnection(ref string host, ref string database, ref string userName, ref string password){
        try{
            Console.Write("DB 서버 HOST 입력 : ");
            host = Console.ReadLine();
            Console.Write("접속할 Database 입력 : ");
            database = Console.ReadLine();
            Console.Write("접속할 유저명 입력 : ");
            userName = Console.ReadLine();
            Console.Write("접속할 유저 패스워드 입력 : ");
            password = Console.ReadLine();
            Console.WriteLine();
        } catch(Exception e){
            throw e;
        }
    }

    static void test_config(ref string host, ref string database, ref string userName, ref string password, ref string query){
        try{
            host = "127.0.0.1";
            database = "postgres";
            userName = "postgres";
            password = "1234";
            query = "SELECT * FROM pg_database";
        } catch(Exception e){
            throw e;
        }
    }  
}

}

4. 빌드합니다.

dotnet build

5. 빌드한 프로젝트를 실행 합니다.

dotnet run

==== npgsql connection test ====

  1. Quick test 2.Custom test

주의 사항

pgbouncer 이슈

Npgsql 의 대부분의 기능은 pgbouncer와의 호환에 문제가 없지만 아래와 같은 권고사항이 있습니다.

  • Npgsql의 커넥션 풀링을 끄고 싶을 경우, connection string에 Pooling=false 를 명시해주셔야 합니다.
  • pgbouncer의 풀링과 npgsql의 풀링을 동시에 사용하며, pgbouncer의 pooling mode가 transaction 또는 statement일 경우, connection string에 No Reset On Close=true 를 명시해야 합니다.

이 옵션은 Npgsql 풀로 커넥션이 반환되었을 때 Npgsql의 connection 리셋 기능 (DISCARD ALL)을 비활성화 합니다.
의미 없는 동작이므로 비활성화가 필요합니다.

  • PgBouncer 1.12버전 이하는 SASL 인증을 지원하지 않습니다. </aside>

오류가 발생 한다면?

연결 오류

<aside> Unable to connect to database: failed to connect to host=192.168.200.151 user=postgres database=postgres: dial error (dial tcp 192.168.200.151:5432: connectex: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.) exit status 1

오류가 발생해요!

  • 답변 내용
    PostgreSQL 서버에 접속이 불가능 한 상태입니다!
    1. PostgreSQL 서버 기동 상태를 확인합니다.
    • PostgreSQL 데이터베이스 서버가 기동되어 있지 않다면 접속이 불가능합니다.기동이 되어 있다면 postgresql.conf와 postgresql.auto.conf의 listen_addresses 파라미터를 확인해주세요.
    2. Host와 Port를 한번 더 확인 해주시기 바랍니다.
    • Host와 Port가 잘못 입력 되어서 입력 받은 접속 정보로 연결이 불가능 할 경우 위와 같은 메시지가 출력됩니다.
    3. PostgreSQL 서버의 방화벽을 확인합니다.
    • 접속을 시도하는 서버가 PostgreSQL 서버의 방화벽에 막히지 않는지 방화벽 설정을 확인 해주시기 바랍니다.

Unable to connect to database: failed to connect to host=192.168.200.151 user=postgres database=postgres: server error (FATAL: no pg_hba.conf entry for host “192.168.200.135”, user “postgres”, database “postgres”, no encryption (SQLSTATE 28000)) exit status 1

오류가 발생해요!

  • 답변 내용
    PostgreSQL의 pg_hba.conf에 연결 허용 설정이 되어있지 않아서 그렇습니다!
    pg_hba.conf에 접속 정보를 추가 해주세요!
    아래는 TCP 연결을 통해 192.168.123.123 호스트로 부터 온 연결 요청에 대해서 모든 데이터베이스 및 모든 유저로 접속이 가능하고 패스워드는 입력하지 않아도 접속이 가능 하도록 설정하는 예시 입니다.

    TYPE DATABASE USER ADDRESS METHOD
    host all all 192.168.123.123/32 trust

쿼리 오류

Query Execution failed: ERROR: column “oid” does not exist (SQLSTATE 42703) exit status 1

오류가 발생해요!

답변 내용
수행한 쿼리에서 oid 컬럼을 SELECT 하려고 하지만, FROM절의 대상 테이블 또는 서브쿼리에 oid 컬럼이 존재하지 않습니다.

해결 방안 1. 조회하려고 하는 테이블에 해당 컬럼이 존재하는지 확인합니다.

  • 아래의 쿼리에서 WHERE절의 테이블명만 변경해서 조회 하시거나, psql 클라이언트의 d 명령어를 이용하는 방법이 있습니다. d 테이블명

SELECT a.attname,
pg_catalog.format_type(a.atttypid, a.atttypmod),
(SELECT pg_catalog.pg_get_expr(d.adbin, d.adrelid, true)
FROM pg_catalog.pg_attrdef d
WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
a.attnotnull,
(SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t
WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation <> t.typcollation) AS attcollation,
a.attidentity,
a.attgenerated,
a.attstorage,
a.attcompression AS attcompression,
CASE WHEN a.attstattarget=-1 THEN NULL ELSE a.attstattarget END AS attstattarget,
pg_catalog.col_description(a.attrelid, a.attnum)
FROM pg_catalog.pg_attribute a LEFT OUTER JOIN pg_catalog.pg_class c on a.attrelid=c.oid
WHERE a.attnum > 0 AND NOT a.attisdropped AND c.relname=’테이블명’
ORDER BY a.attnum;

QueryRow failed: can’t scan into dest[0]: cannot scan NULL into *string exit status 1

오류가 발생해요!

답변 내용
쿼리를 수행한 결과 값이 NULL이라서 그렇습니다.
전체가 NULL일 수도 있고, 특정 ROW만 NULL일 수 있어서 데이터를 확인 하시거나, NULL에 대한 에러 처리가 필요합니다.

npgsql 에 대한 더욱 자세한 정보는 아래의 홈페이지를 확인 해주시기 바랍니다!
https://www.npgsql.org/

지금까지 PostgreSQL의 (Interface) .NET – Npgsql에 관해 알아보았습니다

‘PostgreSQL의 pgbadger’을 바로 이어서 확인해보세요!

데이터 산업 트렌드와
티맥스티베로 소식을
만나보세요.

디비딥레터 구독하기

Tibero

OpenSQL

데이터 산업 트렌드와 티맥스티베로 소식을 뉴스레터로 만나보세요.

디비딥레터 구독하기

대표전화 : 031.8018.1700

I

구입문의 : 031.8018.1717

I

기술서비스센터 : 1544.8629

(주)티맥스티베로

대표이사 : 이희상, 박경희 / 사업자등록번호 : 306-86-01745 / 주소 : 경기도 성남시 분당구 정자일로 45, 티맥스소프트타워

Tibero Inside

Tibero Now

Tech Book

Trend Report

Partner

이용약관

I

개인정보처리방침

ⓒ TmaxTibero

광고성 정보 수신

개인정보 수집, 활용 목적 및 기간

(주)티맥스티베로의 개인정보 수집 및 이용 목적은 다음과 같습니다.
내용을 자세히 읽어보신 후 동의 여부를 결정해 주시기 바랍니다.

  • 수집 목적: 티맥스티베로 뉴스레터 발송 및 고객 관리
  • 수집 항목: 성함, 회사명, 회사 이메일, 연락처, 부서명, 직급, 산업, 담당업무, 관계사 여부, 방문 경로
  • 보유 및 이용 기간: 동의 철회 시까지

※ 위 개인정보 수집 및 이용에 대한 동의를 거부할 권리가 있습니다.
※ 필수 수집 항목에 대한 동의를 거부하는 경우 뉴스레터 구독이 제한될 수 있습니다.

개인정보의 처리 위탁 정보
  • 업체명: 스티비 주식회사
  • 위탁 업무 목적 및 범위: 광고가 포함된 뉴스레터 발송 및 수신자 관리
 

개인정보 수집 및 이용

개인정보 수집, 활용 목적 및 기간

(주)티맥스티베로의 개인정보 수집 및 이용 목적은 다음과 같습니다. 내용을 자세히 읽어보신 후 동의 여부를 결정해 주시기 바랍니다.

  • 수집 목적: 티맥스티베로 뉴스레터 발송 및 고객 관리
  • 수집 항목: 성함, 회사명, 회사 이메일, 연락처, 부서명, 직급, 산업, 담당업무, 관계사 여부, 방문 경로
  • 보유 및 이용 기간: 동의 철회 시까지

※ 위 개인정보 수집 및 이용에 대한 동의를 거부할 권리가 있습니다.
※ 필수 수집 항목에 대한 동의를 거부하는 경우 뉴스레터 구독이 제한될 수 있습니다.

개인정보의 처리 위탁 정보

  • 업체명: 스티비 주식회사
  • 위탁 업무 목적 및 범위: 광고가 포함된 뉴스레터 발송 및 수신자 관리
  •  
교육신청
문의하기
Docs
뉴스레터 구독