前言
这里介绍一款Restful的面向PostgreSQL的工具：PostgREST。看它的github上的project，还非常活跃。
对于一心想提供纯REST API的面向PG的服务，无疑是非常有价值的。也可以用于一些快速原型或性能分析。
postgrest = PostgreSQL + Restful
我们来体会一下它的简单用途吧。
实例
1、下载安装
从网站：https://github.com/PostgREST/postgrest 找到release tag, 下载最新的：
https://github.com/PostgREST/postgrest/releases/tag/v10.2.0
-- 我这里用的是CentOS7.9，就从上边下载这个通用的static版本。
wget https://github.com/PostgREST/postgrest/releases/download/v10.2.0/postgrest-v10.2.0-linux-static-x64.tar.xz
cd /var/lib/pgsql
tar -xf postgrest-v10.2.0-linux-static-x64.tar.xz
# 于是在子目录postgrest下边多了个可执行程序：postgrest
2、简单配置
在下载安装的相同目录：/var/lib/pgsql/postgrest下边，创建一个配置文件：
vi db.conf
db-uri = "postgres://mydb:test123@127.0.0.1:5555/mydb"
db-schema = "public"
db-anon-role = "mydb"
server-host = "0.0.0.0"
server-port = 6666
当然你可以使用 ./postgrest -e 得到一个样例配置文件如下：
## Enable or disable the notification channel
db-channel-enabled = true
## Enable in-database configuration
db-config = true
## Extra schemas to add to the search_path of every request
db-extra-search-path = "public"
## Limit rows in response
# db-max-rows = 1000
## Allow getting the EXPLAIN plan through the `Accept: application/vnd.pgrst.plan` header
# db-plan-enabled = false
## Number of open connections in the pool
db-pool = 10
## Time in seconds to wait to acquire a slot from the connection pool
# db-pool-acquisition-timeout = 10
## Time in seconds after which to recycle pool connections
# db-pool-max-lifetime = 1800
## Stored proc to exec immediately after auth
# db-pre-request = "stored_proc_name"
## Enable or disable prepared statements. disabling is only necessary when behind a connection pooler.
## When disabled, statements will be parametrized but won't be prepared.
db-prepared-statements = true
## The name of which database schema to expose to REST clients
db-schemas = "public"
## How to terminate database transactions
## Possible values are:
## commit (default)
## Transaction is always committed, this can not be overriden
## commit-allow-override
## Transaction is committed, but can be overriden with Prefer tx=rollback header
## rollback
## Transaction is always rolled back, this can not be overriden
## rollback-allow-override
## Transaction is rolled back, but can be overriden with Prefer tx=commit header
db-tx-end = "commit"
## The standard connection URI format, documented at
## https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING
db-uri = "postgresql://"
## Determine if GUC request settings for headers, cookies and jwt claims use the legacy names (string with dashes, invalid starting from PostgreSQL v14) with text values instead of the new names (string without dashes, valid on all PostgreSQL versions) with json values.
## For PostgreSQL v14 and up, this setting will be ignored.
db-use-legacy-gucs = true
# jwt-aud = "your_audience_claim"
## Jspath to the role claim key
jwt-role-claim-key = ".role"
## Choose a secret, JSON Web Key (or set) to enable JWT auth
## (use "@filename" to load from separate file)
# jwt-secret = "secret_with_at_least_32_characters"
jwt-secret-is-base64 = false
## Logging level, the admitted values are: crit, error, warn and info.
log-level = "error"
## Determine if the OpenAPI output should follow or ignore role privileges or be disabled entirely.
## Admitted values: follow-privileges, ignore-privileges, disabled
openapi-mode = "follow-privileges"
## Base url for the OpenAPI output
openapi-server-proxy-uri = ""
## Content types to produce raw output
# raw-media-types="image/png, image/jpg"
server-host = "!4"
server-port = 3000
## Unix socket location
## if specified it takes precedence over server-port
# server-unix-socket = "/tmp/pgrst.sock"
## Unix socket file mode
## When none is provided, 660 is applied by default
# server-unix-socket-mode = "660"
3、启动
nohup ./postgrest db.conf &
[09:36:14-postgres@centos1:/var/lib/pgsql/postgrest]$ ls
db.conf nohup.out postgrest
[09:36:15-postgres@centos1:/var/lib/pgsql/postgrest]$ tail -f nohup.out
15/Apr/2023:09:36:12 +0800: Attempting to connect to the database...
15/Apr/2023:09:36:12 +0800: Connection successful
15/Apr/2023:09:36:12 +0800: Listening on port 6666
15/Apr/2023:09:36:12 +0800: Config reloaded
15/Apr/2023:09:36:12 +0800: Listening for notifications on the pgrst channel
15/Apr/2023:09:36:12 +0800: Schema cache loaded
4、验证及使用
4.1)、首先，我们建一个测试表test，并插入两个简单的数据：
create table test (
id bigserial primary key,
info text,
ctime timestamptz
);
insert into test (info,ctime) values ('Zhao',now()), ('Qian',now());
mydb=> select * from test;
id | info | ctime
----+------+-------------------------------
1 | Zhao | 2023-04-15 09:38:42.371544+08
2 | Qian | 2023-04-15 09:38:42.371544+08
(2 rows)
4.2)、用Postman验证下结果：
因为暴露出来的是6666端口:
4.2.1、查询所有的数据
4.2.2、创建一个Entity
4.2.3、再取一下刚才的值：(id=eq.3)
4.2.4、综合查询
再来看看请求：
http://192.168.0.6:6666/test?select=id,info,ctime&id=gt.1&order=id.desc&limit=2
这些操作符的完整描述，可以参考：
http://postgrest.org/en/latest/api.html#operators
4.3)、用k6简单试下加压
既然这么有意思，我们可以尝试多个线程并发请求试试。
创建测试的简单脚本script.js
import http from 'k6/http';
import { sleep } from 'k6';
export default function () {
http.get('http://192.168.0.6:6666/test');
sleep(0.05);
}
运行下k6, 模拟20个用户，并发60秒，看看结果，同时附带观察PostgreSQL的内存消耗和连接情况
k6 run --vus 20 --duration 30s script.js
其结果如下：
C:\tools\k6>k6 run --vus 20 --duration 30s script.js
/\ |‾‾| /‾‾/ /‾‾/
/\ / \ | |/ / / /
/ \/ \ | ( / ‾‾\
/ \ | |\ \ | (‾) |
/ __________ \ |__| \__\ \_____/ .io
execution: local
script: script.js
output: -
scenarios: (100.00%) 1 scenario, 20 max VUs, 1m0s max duration (incl. graceful stop):
* default: 20 looping VUs for 30s (gracefulStop: 30s)
running (0m30.0s), 00/20 VUs, 9699 complete and 0 interrupted iterations
default ✓ [======================================] 20 VUs 30s
data_received..................: 4.0 MB 134 kB/s
data_sent......................: 834 kB 28 kB/s
http_req_blocked...............: avg=15.81µs min=0s med=0s max=9.53ms p(90)=0s p(95)=0s
http_req_connecting............: avg=12.54µs min=0s med=0s max=9.53ms p(90)=0s p(95)=0s
http_req_duration..............: avg=9.04ms min=527.29µs med=8ms max=471ms p(90)=13.99ms p(95)=16.99ms
{ expected_response:true }...: avg=9.04ms min=527.29µs med=8ms max=471ms p(90)=13.99ms p(95)=16.99ms
http_req_failed................: 0.00% ✓ 0 ✗ 9699
http_req_receiving.............: avg=99.3µs min=0s med=0s max=1.47ms p(90)=514µs p(95)=994.72µs
http_req_sending...............: avg=15.68µs min=0s med=0s max=13ms p(90)=0s p(95)=0s
http_req_tls_handshaking.......: avg=0s min=0s med=0s max=0s p(90)=0s p(95)=0s
http_req_waiting...............: avg=8.92ms min=518.2µs med=7.99ms max=470ms p(90)=13.77ms p(95)=16.93ms
http_reqs......................: 9699 322.804546/s
iteration_duration.............: avg=61.92ms min=50.96ms med=60.91ms max=529.54ms p(90)=65.6ms p(95)=68.75ms
iterations.....................: 9699 322.804546/s
vus............................: 20 min=20 max=20
vus_max........................: 20 min=20 max=20
PG那边的连接数及内存消耗：
[10:11:41-postgres@centos1:/var/lib/pgsql/script/postgrest]$ smem -k
PID User Command Swap USS PSS RSS
2026 postgres tail -f nohup.out 0 164.0K 175.0K 668.0K
1889 postgres postgres: archiver 0 148.0K 226.0K 2.2M
1883 postgres postgres: logger 0 200.0K 266.0K 2.1M
1890 postgres postgres: stats collector 0 284.0K 363.0K 2.3M
1886 postgres postgres: background writer 0 212.0K 387.0K 2.7M
1891 postgres postgres: logical replicati 0 444.0K 544.0K 2.9M
1888 postgres postgres: autovacuum launch 0 568.0K 691.0K 3.2M
1885 postgres postgres: checkpointer 0 368.0K 772.0K 3.6M
1887 postgres postgres: walwriter 0 212.0K 774.0K 3.3M
2023 postgres postgres: mydb mydb 127.0.0 0 944.0K 1.1M 4.8M
2595 postgres postgres: mydb mydb 127.0.0 0 1.2M 1.6M 6.8M
2622 postgres postgres: mydb mydb 127.0.0 0 1.2M 1.6M 6.8M
2625 postgres postgres: mydb mydb 127.0.0 0 1.2M 1.6M 6.8M
2623 postgres postgres: mydb mydb 127.0.0 0 1.2M 1.6M 6.8M
2626 postgres postgres: mydb mydb 127.0.0 0 1.2M 1.6M 6.8M
2628 postgres postgres: mydb mydb 127.0.0 0 1.2M 1.6M 6.8M
2631 postgres postgres: mydb mydb 127.0.0 0 1.2M 1.6M 6.8M
2630 postgres postgres: mydb mydb 127.0.0 0 1.2M 1.6M 6.8M
2632 postgres postgres: mydb mydb 127.0.0 0 1.2M 1.6M 6.8M
2634 postgres postgres: mydb mydb 127.0.0 0 1.2M 1.6M 6.8M
2098 postgres -bash 0 1.8M 2.0M 3.5M
1653 postgres -bash 0 1.8M 2.0M 3.6M
1767 postgres -bash 0 1.8M 2.0M 3.6M
1896 postgres psql -p 5555 mydb -U mydb - 0 1.8M 2.0M 4.6M
1898 postgres postgres: mydb mydb 127.0.0 0 3.2M 3.9M 9.4M
1882 postgres /usr/pgsql-14/bin/postgres 0 4.5M 5.6M 11.9M
2954 postgres python /usr/bin/smem -k 0 5.3M 6.0M 7.5M
2016 postgres ./postgrest db.conf 0 18.6M 18.6M 18.6M
你也能看出来，大概就是10个物理连接。因为我们没有配置连接数（Postgrest配置文件里），用的就是默认值。
当然，你也可以使用SoapUI工具或者用JMeter完成加压测试。有兴趣自行探索。
参考：
1、curl并发请求 https://www.jianshu.com/p/025e4f3cf668
2、running K6 (https://k6.io/docs/get-started/running-k6/)
3、Postgrest REST API (http://postgrest.org/en/latest/api.html#operators)
