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, ¤t_database, ¤t_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進行舉報,并提供相關證據,一經查實,墨天輪將立刻刪除相關內容。




