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.

其他语言客户端接口：

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!