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 |           |
+---------+---------------+--------+---------+----+-----------+