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

使用Go语言连接PostgreSQL数据库,pq可能要被pgx取代

原创 张玉龙 2021-11-10
6961

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/

Go语言连接PostgreSQL数据库

之前研究PostgreSQL高可用 Patroni + etcd,其中 etcd 就是基于Go语言编写的,关于 etcd 和高可用请参考:
PostgreSQL高可用测试系列之Patroni + etcd + HAProxy + Keepalived 离线部署(三)

Go 的下载地址:https://golang.google.cn/dl/
pq 驱动的文档:https://pkg.go.dev/github.com/lib/pq
pq 通过 go 命令下载,所以运行 Go 的操作系统需要连接互联网

在一个可以连接互联网的 Linux 系统安装 Go

# 解压即是安装 [root@proxy ~]# tar -zxvf /enmo/soft/go1.17.3.linux-amd64.tar.gz -C /enmo/app/ [root@proxy ~]# mv /enmo/app/go /enmo/app/go-1.17.3 [root@proxy ~]# ln -s /enmo/app/go-1.17.3 /enmo/app/go # 配置环境变量 [root@proxy ~]# vi /etc/profile export GOROOT=/enmo/app/go # Go的软件目录 export GOPATH=$HOME/go # 写代码的项目目录 export PATH=$PATH:$GOROOT/bin:$GOPATH/bin [root@proxy ~]# source /etc/profile # 检查安装 [root@proxy ~]# go version go version go1.17.3 linux/amd64

创建项目的目录结构

[root@proxy ~]# mkdir go [root@proxy ~]# cd go [root@proxy go]# mkdir bin [root@proxy go]# mkdir pkg [root@proxy go]# mkdir src [root@proxy go]# cd src [root@proxy src]# go mod init test

安装pq驱动

# 安装之前需要先设置一下 GOPROXY ,默认是从 https://proxy.golang.org 下载驱动,但是这个网站被墙了,建议换成 https://proxy.golang.org [root@proxy ~]# go env |grep GOPROXY GOPROXY="https://proxy.golang.org,direct" [root@proxy ~]# go env -w GOPROXY=https://goproxy.cn,direct # 安装pq驱动 [root@proxy ~]# cd go/src/ [root@proxy src]# go get github.com/lib/pq go: downloading github.com/lib/pq v1.10.4

连接PostgreSQL数据库

[root@proxy src]# vi test.go package main import ( "database/sql" "fmt" "log" _ "github.com/lib/pq" ) func main() { connStr := "host=192.168.58.10 port=5432 dbname=postgres user=postgres password=postgres sslmode=disable" db, err := sql.Open("postgres", connStr) if err != nil { log.Fatal(err) } rows, err := db.Query("select inet_server_addr(),pg_is_in_recovery(),current_database(),current_user") if err != nil { log.Fatal(err) } for rows.Next() { var inet_server_addr string var pg_is_in_recovery string var current_database string var current_user string err = rows.Scan(&inet_server_addr, &pg_is_in_recovery, &current_database, &current_user) if err != nil { log.Fatal(err) } fmt.Println("inet_server_addr: " + inet_server_addr) fmt.Println("pg_is_in_recovery: " + pg_is_in_recovery) fmt.Println("current_database: " + current_database) fmt.Println("current_user: " + current_user) } }
# 编译 [root@proxy src]# go build # 执行 [root@proxy src]# ./test inet_server_addr: 192.168.58.10 pg_is_in_recovery: false current_database: postgres current_user: postgres

将编译后的文件传给其他服务器就可以直接执行

[root@proxy src]# scp test 192.168.58.10:/root/ # 在 192.168.58.10 服务器上直接就能执行 [root@pgtest1 ~]# ./test inet_server_addr: 192.168.58.10 pg_is_in_recovery: false current_database: postgres current_user: postgres

pq 可能要被 pgx 取代

  pq 驱动的文档:https://pkg.go.dev/github.com/lib/pq 中提到:

This package is currently in maintenance mode, which means: 1. It generally does not accept new features. 2. It does accept bug fixes and version compatability changes provided by the community. 3. Maintainers usually do not resolve reported issues. 4. Community members are encouraged to help each other with reported issues. For users that require new features or reliable resolution of reported bugs, we recommend using pgx which is under active development.

  pgx 驱动的文档:https://pkg.go.dev/github.com/jackc/pgx/v4

# 创建项目目录 [root@proxy ~]# cd go/src/ [root@proxy src]# mkdir test_pgx [root@proxy src]# cd test_pgx/ [root@proxy test_pgx]# go mod init go: creating new go.mod: module test_pgx # 安装 pgx [root@proxy test_pgx]# go get github.com/jackc/pgx/v4 # 脚本 [root@proxy test_pgx]# vi test_pgx.go package main import ( "context" "fmt" "os" "github.com/jackc/pgx/v4" ) func main() { conn, err := pgx.Connect(context.Background(), os.Getenv("DATABASE_URL")) if err != nil { fmt.Fprintf(os.Stderr, "Unable to connect to database: %v\n", err) os.Exit(1) } defer conn.Close(context.Background()) var greeting string err = conn.QueryRow(context.Background(), "select 'Hello, world!'").Scan(&greeting) if err != nil { fmt.Fprintf(os.Stderr, "QueryRow failed: %v\n", err) os.Exit(1) } fmt.Println(greeting) } # 定义环境变量,编译执行 [root@proxy test_pgx]# export DATABASE_URL="postgres://postgres:postgres@192.168.58.10:5432/postgres?sslmode=disable" [root@proxy test_pgx]# go build [root@proxy test_pgx]# ./test_pgx Hello, world!
最后修改时间:2021-11-11 10:35:56
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论