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!