暂无图片
暂无图片
暂无图片
暂无图片
暂无图片

使用.NET(C#)语言连接PostgreSQL数据库,Npgsql 也提供了故障转移和负载均衡的功能

原创 张玉龙 2021-11-09
5336

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.

其他语言客户端接口:

Name Language Comments Website
DBD::Pg Perl Perl DBI driver https://metacpan.org/release/DBD-Pg
JDBC Java Type 4 JDBC driver https://jdbc.postgresql.org/
libpqxx C++ C++ interface https://pqxx.org/
node-postgres JavaScript Node.js driver https://node-postgres.com/
Npgsql .NET .NET data provider https://www.npgsql.org/
pgtcl Tcl - https://github.com/flightaware/Pgtcl
pgtclng Tcl - https://sourceforge.net/projects/pgtclng/
pq Go Pure Go driver for Go’s database/sql https://github.com/lib/pq
psqlODBC ODBC ODBC driver https://odbc.postgresql.org/
psycopg Python DB API 2.0-compliant https://www.psycopg.org/

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/

image.png

新建项目

image.png

选择 Windows 窗体应用,对于我这种小白,不适合直接搞代码,整个窗体拖一拖还是挺舒服

image.png
image.png
image.png

可以使用 visual stdio 2019 连接 PostgreSQL 数据库

本次测试用不到这个,仅作为知识点,visual stdio 2019 中并未自带 PostgreSQL 的连接功能,我们需要手动配置

image.png
image.png

还需要关闭程序

image.png

关闭程序后执行配置,点击 Modify

image.png
image.png
image.png
image.png
image.png
image.png

在项目中添加 Npgsql,这个是必须的,为了在代码中引用Npgsql(using Npgsql;) 实现对数据库的操作功能

右击项目名,选择管理 NuGet 程序包

image.png
image.png
image.png
image.png

在窗体中拖动添加控件,按钮控件(button),标签控件(Label),列表框控件(ListBox)

image.png
image.png
image.png

为按钮控件和标签控件设置 Name 和 Text 属性,为列表框控件设置 Name 属性

image.png
image.png

为按钮控件设置单击(Click)事件

image.png

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

image.png

在源代码里按钮的单击(Click)事件中加入以下代码

image.png

// 编写数据库连接串 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); }

测试运行

image.png
image.png
image.png

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";

image.png

# 关闭主库 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

image.png

  此时连到只读库 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";

image.png

  在真实的故障转移场景中,如果主库出现故障,备库通常会提升为新的主服务器。但是,原来的主库可能会恢复并承担备用角色,此时服务器将切换角色,并且 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 | | +---------+---------------+---------+---------+----+-----------+

image.png

读写分离

  有这种情况,应用程序的某些部分只需要从数据库中读取数据,而其他部分则需要写入数据。如果您有一台或多台备用服务器,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 支持以下选项:

Option Description
any Any successful connection is acceptable.
primary Server must not be in hot standby mode (pg_is_in_recovery() must return false).
standby Server must be in hot standby mode (pg_is_in_recovery() must return true).
prefer-primary First try to find a primary server, but if none of the listed hosts is a primary server, try again in Any mode.
prefer-standby First try to find a standby server, but if none of the listed hosts is a standby server, try again in Any mode.
read-write Session must accept read-write transactions by default (that is, the server must not be in hot standby mode and the default_transaction_read_only parameter must be off).
read-only Session must not accept read-write transactions by default (the converse).
# 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 | +---------+---------------+---------+---------+----+-----------+

image.png

负载均衡

  我们已经了解了如何根据要执行的工作负载类型选择服务器。但是,在上面的例子中,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 | +---------+---------------+---------+---------+----+-----------+

image.png

  再点击一个点击测试 创建数据库连接,就返回另一个只读备库的连接,但是在我的测试中负载均衡Load Balance Hosts=true不咋好使,偶尔能连另一个只读备库,不想JDBC的负载均衡那样丝滑,可能是刚出来的技术,还有待改进。

image.png

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

image.png

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

image.png

「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论