PostgreSQL的客戶端接口

在PostgreSQL發行版中只包含兩個客戶端接口: libpq 和 ECPG

• libpq is included because it is the primary C language interface, and because many other client interfaces are built on top of it.
• ECPG is included because it depends on the server-side SQL grammar, and is therefore sensitive to changes in PostgreSQL itself.

其他語言客戶端接口：

C# 語言連接PostgreSQL數據庫

??至于 C# 語言和 .NET 是啥關系，這里就不提了，百度一搜一大把。

??Npgsql 是一個用于 PostgreSQL 的開源 ADO.NET 數據提供程序，它允許用 C#、Visual Basic、F# 編寫的程序訪問 PostgreSQL 數據庫服務器。它是用 100% C# 代碼實現的，是免費的并且是開源的。

??Npgsql 也不用單獨下載，直接在 Visual Studio 2019(.NET 的編程工具) 就能直接安裝。

??Npgsql的官方手冊： https://www.npgsql.org/index.html

下面開始測試

測試這個還挺麻煩，需要下載安裝.NET開發工具 Visual Studio 2019

下載地址： https://visualstudio.microsoft.com/zh-hans/vs/

新建項目

選擇 Windows 窗體應用，對于我這種小白，不適合直接搞代碼，整個窗體拖一拖還是挺舒服

可以使用 visual stdio 2019 連接 PostgreSQL 數據庫

本次測試用不到這個，僅作為知識點，visual stdio 2019 中并未自帶 PostgreSQL 的連接功能，我們需要手動配置

還需要關閉程序

關閉程序后執行配置，點擊 Modify

在項目中添加 Npgsql，這個是必須的，為了在代碼中引用Npgsql(using Npgsql;) 實現對數據庫的操作功能

右擊項目名，選擇管理 NuGet 程序包

在窗體中拖動添加控件，按鈕控件(button)，標簽控件(Label)，列表框控件(ListBox)

為按鈕控件和標簽控件設置 Name 和 Text 屬性，為列表框控件設置 Name 屬性

為按鈕控件設置單擊(Click)事件

在源代碼中引用 Npgsql(using Npgsql;)

在源代碼里按鈕的單擊(Click)事件中加入以下代碼

// 編寫數據庫連接串
string connString = "Host=192.168.58.10;Port=5432;Username=postgres;Password=postgres;Database=postgres";
try
{
    // 創建數據庫連接
    using (NpgsqlConnection conn = new NpgsqlConnection(connString))
    {
        // 打開數據庫連接
        conn.Open();
        // 定義查詢SQL
        string sql = "select inet_server_addr(),pg_is_in_recovery(),current_database(),current_user";
        // 創建 NpgsqlDataAdapter 類的對象
        NpgsqlDataAdapter sda = new NpgsqlDataAdapter(sql, conn);
        // 創建 DataSet 類的對象
        DataSet ds = new DataSet();
        // 使用 NpgsqlDataAdapter 對象 sda 將查詢結果填充到 Dataset 對象 ds 中
        sda.Fill(ds);
        // 設置列表控件的數據源（DataSource）屬性
        ip_b.DataSource = ds.Tables[0];
        recover_b.DataSource = ds.Tables[0];
        db_b.DataSource = ds.Tables[0];
        user_b.DataSource = ds.Tables[0];
        // 在列表控件中顯示列值
        ip_b.DisplayMember = ds.Tables[0].Columns[0].ToString();
        recover_b.DisplayMember = ds.Tables[0].Columns[1].ToString();
        db_b.DisplayMember = ds.Tables[0].Columns[2].ToString();
        user_b.DisplayMember = ds.Tables[0].Columns[3].ToString();
        // 釋放資源，關閉數據庫連接
        ds.Dispose();
        sda.Dispose();
        conn.Close();
        conn.Dispose();
    }
}
catch (Exception ex)
{
    MessageBox.Show("查詢失敗！" + ex.Message);
}

測試運行

C# 連接數據庫思路和代碼的學習受益于：http://c.biancheng.net/csharp/150/

Npgsql 也提供了故障轉移和負載均衡的功能，但是這個功能在 Npgsql 6.0 中引入，該版本仍處于測試階段。

Npgsql 最新的版本信息： https://www.nuget.org/packages/Npgsql/
Npgsql 關于故障轉移和負載均衡的介紹：https://www.npgsql.org/doc/failover-and-load-balancing.html
Visual Studio 2019 上下載不到 Npgsql 6.0 的版本，需要在Windows CMD中使用 dotnet 命令指定版本下載：

# cd 到項目文件夾中
C:\Users\Administrator# cd C:\Users\Administrator\source\repos\pg_conn\pg_conn
C:\Users\Administrator\source\repos\pg_conn\pg_conn# dotnet add pg_conn.csproj package Npgsql --version 6.0.0-rc.2

多臺服務器和故障轉移

Npgsql 允許連接字符串中指定多個服務器，如下所示：

Host=server1:port,server2:port;Username=test;Password=test

默認情況下，Npgsql 將嘗試按照指定的順序連接到服務器。

# 192.168.58.10是主節點，192.168.58.11/12是只讀從節點
# 按順序連接，當192.168.58.10不可用時，連接192.168.58.11
string connString = "Host=192.168.58.10:5432,192.168.58.11:5432,192.168.58.12:5432;Username=postgres;Password=postgres;Database=postgres";

# 關閉主庫 192.168.58.10 ，不進行主備切換
[root@pgtest1 ~]# patronictl pause
Success: cluster management is paused
[root@pgtest1 ~]# su - postgres
Last login: Sat Nov  6 17:02:07 CST 2021 on pts/0
[postgres@pgtest1 ~]$ pg_ctl stop
waiting for server to shut down..... done
server stopped
[postgres@pgtest1 ~]$ 
[postgres@pgtest1 ~]$ patronictl list
+---------+---------------+---------+---------+----+-----------+
| Member  | Host          | Role    | State   | TL | Lag in MB |
+ Cluster: pg_cluster (7025023477017500881) --+----+-----------+
| pgtest1 | 192.168.58.10 | Replica | stopped |    |   unknown |
| pgtest2 | 192.168.58.11 | Replica | running | 26 |         0 |
| pgtest3 | 192.168.58.12 | Replica | running | 26 |         0 |
+---------+---------------+---------+---------+----+-----------+
 Maintenance mode: on

??此時連到只讀庫 192.168.58.11，且多次 點擊測試 創建數據庫連接，連接的都是 192.168.58.11，如果是只讀業務還好，此時不能進行寫業務，Npgsql 又提供了Target Session Attributes參數指定服務器類型。

指定服務器類型 Target Session Attributes

# 192.168.58.10是主節點，192.168.58.11/12是只讀從節點
# 關閉主庫 192.168.58.10 ，不進行主備切換的情況下，以下連接報錯，找不到 primary
string connString = "Host=192.168.58.10:5432,192.168.58.11:5432,192.168.58.12:5432;Username=postgres;Password=postgres;Database=postgres;Target Session Attributes=primary";

??在真實的故障轉移場景中，如果主庫出現故障，備庫通常會提升為新的主服務器。但是，原來的主庫可能會恢復并承擔備用角色，此時服務器將切換角色，并且 Npgsql 將盡可能繼續連接原來的主庫。為了緩解這種情況，也可以告訴 Npgsql 希望連接到哪種服務器類型：

# 192.168.58.10是主節點，192.168.58.11/12是只讀從節點
# 關閉主庫進行主備切換，當192.168.58.10不可用時，連接到切換后的主庫 192.168.58.12(primary)
# Npgsql 只返回到 primary 的連接，無論 primary 位于 connString 的主機列表中的哪個位置，192.168.58.12處于最后位置，它的角色是primary，所以就沒有連192.168.58.11。
string connString = "Host=192.168.58.10:5432,192.168.58.11:5432,192.168.58.12:5432;Username=postgres;Password=postgres;Database=postgres;Target Session Attributes=primary";

[root@pgtest1 ~]# patronictl resume
Success: cluster management is resumed
[root@pgtest1 ~]# patronictl list
+---------+---------------+---------+---------+----+-----------+
| Member  | Host          | Role    | State   | TL | Lag in MB |
+ Cluster: pg_cluster (7025023477017500881) --+----+-----------+
| pgtest1 | 192.168.58.10 | Leader  | running | 27 |           |
| pgtest2 | 192.168.58.11 | Replica | running | 27 |         0 |
| pgtest3 | 192.168.58.12 | Replica | running | 27 |         0 |
+---------+---------------+---------+---------+----+-----------+
[root@pgtest1 ~]# systemctl stop patroni
[root@pgtest1 ~]# patronictl list
+---------+---------------+---------+---------+----+-----------+
| Member  | Host          | Role    | State   | TL | Lag in MB |
+ Cluster: pg_cluster (7025023477017500881) --+----+-----------+
| pgtest1 | 192.168.58.10 | Replica | stopped |    |   unknown |
| pgtest2 | 192.168.58.11 | Replica | running | 27 |         0 |
| pgtest3 | 192.168.58.12 | Leader  | running | 28 |           |
+---------+---------------+---------+---------+----+-----------+

讀寫分離

??有這種情況，應用程序的某些部分只需要從數據庫中讀取數據，而其他部分則需要寫入數據。如果您有一臺或多臺備用服務器，Npgsql 可以向這些服務器發送只讀查詢，以減少主服務器的負載。雖然上述故障轉移設置提高了可靠性，但負載均衡這種技術提高了性能。

# 寫讀業務
Host=server1:port,server2:port;Username=test;Password=test;Target Session Attributes=primary";
# 只讀業務
Host=server1:port,server2:port;Username=test;Password=test;Target Session Attributes=prefer-standby

??使用 prefer-standby，只要至少有一個備用服務器可用，Npgsql 就會返回與該服務器的連接。但是，如果所有備用服務器都關閉（或已用完它們的Max Pool Size設置），則將返回到主服務器的連接。

Target Session Attributes 支持以下選項：

# 192.168.58.10是主節點，192.168.58.11/12是只讀從節點
# 在主備庫正常的情況下，prefer-standby 控制連接只讀備庫，多個只讀備庫的情況下，也是按主機列表順序連接，多次“點擊測試”，只連一個只讀備庫192.168.58.11
string connString = "Host=192.168.58.10:5432,192.168.58.11:5432,192.168.58.12:5432;Username=postgres;Password=postgres;Database=postgres;Target Session Attributes=prefer-standby";

[root@pgtest1 ~]# patronictl list
+---------+---------------+---------+---------+----+-----------+
| Member  | Host          | Role    | State   | TL | Lag in MB |
+ Cluster: pg_cluster (7025023477017500881) --+----+-----------+
| pgtest1 | 192.168.58.10 | Leader  | running | 29 |           |
| pgtest2 | 192.168.58.11 | Replica | running | 29 |         0 |
| pgtest3 | 192.168.58.12 | Replica | running | 29 |         0 |
+---------+---------------+---------+---------+----+-----------+

負載均衡

??我們已經了解了如何根據要執行的工作負載類型選擇服務器。但是，在上面的例子中，Npgsql 仍然嘗試根據連接字符串中指定的主機順序返回連接；這將負載集中在單個主服務器和可能的單個輔助服務器上，并且不會在多個相同類型的服務器之間平衡負載。
??您可以在連接字符串中指定 Load Balance Hosts=true 以指示 Npgsql 在所有服務器之間進行負載平衡，通過以循環方式返回連接：

# 寫讀業務
Host=server1:port,server2:port;Username=test;Password=test;Target Session Attributes=primary";
# 只讀業務
Host=server1:port,server2:port,server3:port;Username=test;Password=test;Load Balance Hosts=true;Target Session Attributes=prefer-standby

# 192.168.58.10是主節點，192.168.58.11/12是只讀從節點
# 在主備庫正常的情況下，prefer-standby 控制連接只讀備庫，Load Balance Hosts 控制只讀備庫之間的負載均衡
string connString = "Host=192.168.58.10:5432,192.168.58.11:5432,192.168.58.12:5432;Username=postgres;Password=postgres;Database=postgres;Load Balance Hosts=true;Target Session Attributes=prefer-standby";

[root@pgtest1 ~]# patronictl list
+---------+---------------+---------+---------+----+-----------+
| Member  | Host          | Role    | State   | TL | Lag in MB |
+ Cluster: pg_cluster (7025023477017500881) --+----+-----------+
| pgtest1 | 192.168.58.10 | Leader  | running | 29 |           |
| pgtest2 | 192.168.58.11 | Replica | running | 29 |         0 |
| pgtest3 | 192.168.58.12 | Replica | running | 29 |         0 |
+---------+---------------+---------+---------+----+-----------+

??再點擊一個點擊測試 創建數據庫連接，就返回另一個只讀備庫的連接，但是在我的測試中負載均衡Load Balance Hosts=true不咋好使，偶爾能連另一個只讀備庫，不想JDBC的負載均衡那樣絲滑，可能是剛出來的技術，還有待改進。

# 192.168.58.10是主節點，192.168.58.11/12是只讀從節點
# 停到一個只讀備庫，多次連接始終返回另一個只讀庫的連接
string connString = "Host=192.168.58.10:5432,192.168.58.11:5432,192.168.58.12:5432;Username=postgres;Password=postgres;Database=postgres;Load Balance Hosts=true;Target Session Attributes=prefer-standby";

[root@pgtest2 ~]# systemctl stop patroni
[root@pgtest1 ~]# patronictl list
+---------+---------------+---------+---------+----+-----------+
| Member  | Host          | Role    | State   | TL | Lag in MB |
+ Cluster: pg_cluster (7025023477017500881) --+----+-----------+
| pgtest1 | 192.168.58.10 | Leader  | running | 29 |           |
| pgtest2 | 192.168.58.11 | Replica | stopped |    |   unknown |
| pgtest3 | 192.168.58.12 | Replica | running | 29 |         0 |
+---------+---------------+---------+---------+----+-----------+

# 192.168.58.10是主節點，192.168.58.11/12是只讀從節點
# 停到所有只讀備庫，返回主庫的連接
[root@pgtest3 ~]# systemctl stop patroni
[root@pgtest1 ~]# patronictl list
+---------+---------------+--------+---------+----+-----------+
| Member  | Host          | Role   | State   | TL | Lag in MB |
+ Cluster: pg_cluster (7025023477017500881) -+----+-----------+
| pgtest1 | 192.168.58.10 | Leader | running | 29 |           |
+---------+---------------+--------+---------+----+-----------+