Ubuntu 20.04 に Posgtgres12 をインストール後 C# + Npgsql でアクセスしてみる

#目的
Ubuntu 20.04 に Posgtgres をインストールする
VsCode + C# + Npgsql + .NET Core SDK 3.1 でアクセスしてみる

##インストールの準備
・必要なパッケージをインストールする

$ sudo apt-get install curl ca-certificates gnupg

・鍵を入れる

$ sudo curl https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

・リポジトリの参照リストに追記する。
aptのリポジトリを順にみていくとv13まであるようだ
※http://apt.postgresql.org/pub/repos/apt/dists/focal-pgdg/13/

$ sudo apt-add-repository 'deb http://apt.postgresql.org/pub/repos/apt focal-pgdg main'

/etc/apt/sources.list (の最後の行)に

deb http://apt.postgresql.org/pub/repos/apt focal-pgdg main
# deb-src http://apt.postgresql.org/pub/repos/apt focal-pgdg main

が追加されるが apt update の時に以下のエラーが発生する

N: リポジトリ 'http://apt.postgresql.org/pub/repos/apt focal-pgdg InRelease' がアーキテクチャ 
'i386' をサポートしないため設定ファイル 'main/binary-i386/Packages' の取得をスキップ

これを避けるために以下の様に [arch=amd64] を追加する

deb [arch=amd64] http://apt.postgresql.org/pub/repos/apt focal-pgdg main
# deb-src [arch=amd64] http://apt.postgresql.org/pub/repos/apt focal-pgdg main

##リポジトリを更新後にPostgresをインストールする
※postgresql-13 のレポジトリは今日現在では使用不可のようだ

$ sudo apt update
$ sudo apt install postgresql-12 pgadmin4

※RDT + Virtual Box 環境では pgadmin4 は起動しない
※エラーが出た事だけ確認

##インストールログでメモったほうが良さげな項目
設定ファイルの位置 -> /etc/postgresql/12/main/
ログの位置 -> /var/log/postgresql/
pg_ctlcluster 12 main start
※ pg_ctlcluster - start/stop/restart/reload a PostgreSQL cluster
デフォルトのmax_connectionsを選択しています ... 100
デフォルトの shared_buffers を選択しています ... 128MB
selecting default time zone ... Asia/Tokyo

##設定ファイルの修正

/etc/postgresql/12/main/postgresql.conf の修正
#コメントアウト
#listen_addresses = 'localhost' # what IP address(es) to listen on;

#以下に修正
listen_addresses = '*'           # what IP address(es) to listen on;

※IPアドレスは環境に合わせてください・・・

/etc/postgresql/12/main/pg_hba.conf を以下に修正
# "local" is for Unix domain socket connections only
#local   all             all                                     peer
local   all             all                                       md5
# IPv4 local connections:
#host    all             all             127.0.0.1/32            md5
host    all             all             192.168.5.0/24            md5
# IPv6 local connections:
#host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
#replication privilege.
#local   replication     all                                     peer
#host    replication     all             127.0.0.1/32            md5
#host    replication     all             ::1/128                 md5

上記２ファイルを修正後pgsqlを再起動する

$ sudo pg_ctlcluster 12 main restart
※ man pg_ctlcluster より
※ pg_ctlcluster - start/stop/restart/reload a PostgreSQL cluster

##サンプルDBの作成
PostgreSQL Sample Databaseより
PostgreSQL DVD Rental sample databaseをDL後

$ su - postgres
$ unzip dvdrental.zip
$ createdb --locale=C --encoding=UTF-8 --template=template0 -U postgres dvdrental
$ pg_restore -U postgres -d dvdrental dvdrental.tar
$ psql -l
                                         データベース一覧
   名前    |  所有者  | エンコーディング |  照合順序   | Ctype(変換演算子) |     アクセス権限
-----------+----------+------------------+-------------+-------------------+-----------------------
 dvdrental | postgres | UTF8             | C           | C                 |
 postgres  | postgres | UTF8             | ja_JP.UTF-8 | ja_JP.UTF-8       |
 template0 | postgres | UTF8             | ja_JP.UTF-8 | ja_JP.UTF-8       | =c/postgres          +
           |          |                  |             |                   | postgres=CTc/postgres
 template1 | postgres | UTF8             | ja_JP.UTF-8 | ja_JP.UTF-8       | =c/postgres          +
           |          |                  |             |                   | postgres=CTc/postgres

$ psql -U postgres
ユーザ postgres のパスワード:
psql (12.3 (Ubuntu 12.3-1.pgdg20.04+1))
"help"でヘルプを表示します。

postgres=# \c dvdrental
データベース"dvdrental"にユーザ"postgres"として接続しました。
dvdrental=# \d
                       リレーション一覧
 スキーマ |            名前            |     型     |  所有者
----------+----------------------------+------------+----------
 public   | actor                      | テーブル   | postgres
 public   | actor_actor_id_seq         | シーケンス | postgres
 public   | actor_info                 | ビュー     | postgresｑ
 public   | address                    | テーブル   | postgres
 public   | address_address_id_seq     | シーケンス | postgres
 public   | category                   | テーブル   | postgres
 ～

##アクセス環境の設定

$ su - postgres
$ psql -U postgres
ユーザ postgres のパスワード:
postgres=# create role demo with login password 'passwd';
postgres=# grant connect on database dvdrental to demo;
postgres=# \c dvdrental
※サンプル用のために、全権限を全テーブルに許可する
postgres=# grant all on all tables in schema public to demo;
postgres=# grant all on all sequences in schema public to demo;
postgres=# grant all on all functions in schema public to demo;

postgres=# grant select on actor to demo;
postgres=# grant all on database dvdrental to demo;

##サンプルを試してみる
※ Ubuntu2004、Win10 共に同一コードで動作を確認
・VSCode を立ちあげて適当なフォルダを選択する
・コンソールから作成したフォルダ以下で dotnet new console
~/code/pg0001$ ls
Program.cs bin pg0001.csproj obj
##System.Data.SqlClient&Npgsqlを追加する
表示メニュー
　-> コマンドパレット
　　-> nuget
　　　-> Nuget package Manager:Add Pckage
　　　　-> System.Data.SqlClient を入力後 enter
　　　　　-> System.Data.SqlClient を選択
　　　　　　-> 4.8.1 を選択

　　　　-> Npgsqlを入力後 enter
　　　　　-> Npgsql を選択
　　　　　　-> 4.1.3.1 を選択

pg0001.csproj に以下を追加する(上記で追加後 restore　ボタン で追加される)

\
\
\
\

※launch.json
実行->デバックなしで実行　を選択したときに作成される

using System;
using Npgsql;
using System.Data;
using System.Data.SqlClient;
namespace pg0001
{
    class Program
    {
        static void Main(string[] args)
        {
            var constr = @"Server=192.168.5.44;Port=5432;User Id=demo;Password=passwd;Database=dvdrental";
            NpgsqlConnection con = new NpgsqlConnection(constr);
            con.Open();

            try {
                    NpgsqlDataAdapter da = new NpgsqlDataAdapter("select count(*) as cnt  from actor;", con);
                    DataSet ds = new DataSet();
                    da.Fill(ds, "actor");
                    DataTable tbl = ds.Tables["actor"];           

                    foreach(DataRow row in tbl.Rows)
                    {
                        Console.WriteLine(row[0].ToString());
                    }
            }
            finally {
                con.Close();
            }
        }
    }
}

#参考にしたサイトはこちら
Ubuntu 12.10 > Repository
PostgreSQL packages for Debian and Ubuntu
PostgreSQL データベース接続ユーザーの作成
[PostgreSQL] よく使うコマンドまとめ
ロールにテーブルやビューなどに対する権限を追加する(GRANT)
18．アクセス権の付与
PostgreSQLで全てのテーブルにGRANT ALLする方法
Npgsql 4.1.3.1
Npgsql/Installation
.NETライブラリ「Npgsql」によるPostgreSQLの活用
Ubuntu 18.04 に .NET Core SDK 3.1 をインストールする