This is the multi-page printable view of this section. Click here to print.
应用
- 1: Applet 数据分析应用"
- 1.1: PGLOG:PG自带日志分析应用
- 1.2: NOAA ISD 全球气象站历史数据查询
- 1.3: WHO COVID-19 疫情大盘
- 1.4: AWS 阿里云 服务器价格
- 1.5: 使用一条 SQL 计算扑克24点
- 1.6: DB-Engine 数据库热度趋势分析
- 1.7: StackOverflow 全球开发者调研
- 2: Docker 软件应用工具
- 2.1: 使用PGAdmin4进行数据库管理
- 2.2: 使用Gitea搭建您自己的代码托管服务
- 2.3: 使用Wiki.js搭建百科网站
- 2.4: 使用Minio存储本地对象与备份
- 2.5: 使用ByteBase对PG模式做版本控制
- 2.6: 使用PGWeb从网页浏览PostgreSQL数据
- 2.7: 使用PostgREST自动生成RESTful API
- 2.8: KONG API Gateway
- 3: 使用JupyterLab进行数据分析
- 4: 使用 PostgreSQL 作为 Grafana 后端数据库
- 5: 使用 TimescaleDB + Promscale 存储 Prometheus 时序指标数据
1 - Applet 数据分析应用"
Applet的结构
Applet,是一种自包含的,运行于Pigsty基础设施中的数据小应用。
一个Pigsty应用通常包括以下内容中的至少一样或全部:
- 图形界面(Grafana Dashboard定义) 放置于
ui
目录 - 数据定义(PostgreSQL DDL File),放置于
sql
目录 - 数据文件(各类资源,需要下载的文件),放置于
data
目录 - 逻辑脚本(执行各类逻辑),放置于
bin
目录
Pigsty默认提供了几个样例应用:
pglog
, 分析PostgreSQL CSV日志样本。covid
, 可视化WHO COVID-19数据,查阅各国疫情数据。pglog
, NOAA ISD,可以查询全球30000个地表气象站从1901年来的气象观测记录。
应用的结构
一个Pigsty应用会在应用根目录提供一个安装脚本:install
或相关快捷方式。您需要使用管理用户在 管理节点 执行安装。安装脚本会检测当前的环境(获取 METADB_URL
, PIGSTY_HOME
,GRAFANA_ENDPOINT
等信息以执行安装)
通常,带有APP
标签的面板会被列入Pigsty Grafana首页导航中App下拉菜单中,带有APP
和Overview
标签的面板则会列入首页面板导航中。
您可以从 https://github.com/Vonng/pigsty/releases/download/v1.5.1/app.tgz 下载带有基础数据的应用进行安装。
1.1 - PGLOG:PG自带日志分析应用
PGLOG是Pigsty自带的一个样例应用,固定使用MetaDB中pglog.sample
表作为数据来源。您只需要将日志灌入该表,然后访问相关Dashboard即可。
Pigsty提供了一些趁手的命令,用于拉取csv日志,并灌入样本表中。在元节点上,默认提供下列快捷命令:
catlog [node=localhost] [date=today] # 打印CSV日志到标准输出
pglog # 从标准输入灌入CSVLOG
pglog12 # 灌入PG12格式的CSVLOG
pglog13 # 灌入PG13格式的CSVLOG
pglog14 # 灌入PG14格式的CSVLOG (=pglog)
catlog | pglog # 分析当前节点当日的日志
catlog node-1 '2021-07-15' | pglog # 分析node-1在2021-07-15的csvlog
接下来,您可以访问以下的连接,查看样例日志分析界面。
- PGLOG Overview: 呈现整份CSV日志样本详情,按多种维度聚合。
- PGLOG Session: 呈现日志样本中一条具体连接的详细信息。
catlog
命令从特定节点拉取特定日期的CSV数据库日志,写入stdout
默认情况下,catlog
会拉取当前节点当日的日志,您可以通过参数指定节点与日期。
组合使用pglog
与catlog
,即可快速拉取数据库CSV日志进行分析。
catlog | pglog # 分析当前节点当日的日志
catlog node-1 '2021-07-15' | pglog # 分析node-1在2021-07-15的csvlog
1.2 - NOAA ISD 全球气象站历史数据查询
如果您拥有数据库后不知道干点什么,不妨参试试这个开源项目:Vonng/isd
您可以直接复用监控系统Grafana,以交互式的方式查阅近30000个地面气象站过去120年间的亚小时级气象数据。
这是一个功能完成的数据应用,可以查询全球30000个地表气象站从1901年来的气象观测记录。
项目地址:https://github.com/Vonng/isd
在线Demo地址:https://demo.pigsty.cc/d/isd-overview
快速上手
克隆本仓库
git clone https://github.com/Vonng/isd.git; cd isd;
准备一个 PostgreSQL 实例
该 PostgreSQL 实例应当启用了 PostGIS 扩展。使用 PGURL
环境变量传递数据库连接信息:
# Pigsty 默认使用的管理员账号是 dbuser_dba,密码是 DBUser.DBA
export PGURL=postgres://dbuser_dba:DBUser.DBA@127.0.0.1:5432/meta?sslmode=disable
psql "${PGURL}" -c 'SELECT 1' # 检查连接是否可用
获取并导入ISD气象站元数据
这是一份每日更新的气象站元数据,包含了气象站的经纬度、海拔、名称、国家、省份等信息,使用以下命令下载并导入。
make reload-station # 相当于先下载最新的Station数据再加载:get-station + load-station
获取并导入最新的 isd.daily
数据
isd.daily
是一个每日更新的数据集,包含了全球各气象站的日观测数据摘要,使用以下命令下载并导入。
请注意,直接从 NOAA 网站下载的原始数据需要经过解析方可入库,所以你需要下载或构建一个 ISD 数据 Parser。
make get-parser # 从 Github 下载 Parser 二进制,当然你也可以用 make build 直接用 go 构建。
make reload-daily # 下载本年度最新的 isd.daily 数据并导入数据库中
加载解析好的 CSV 数据集
ISD Daily 数据集有一些脏数据与重复数据,如果你不想手工解析处理清洗,这里也提供了一份解析好的稳定CSV数据集。
该数据集包含了截止到 2023-06-24 的 isd.daily
数据,你可以直接下载并导入 PostgreSQL 中,不需要 Parser,
make get-stable # 从 Github 上获取稳定的 isd.daily 历史数据集。
make load-stable # 将下载好的稳定历史数据集加载到 PostgreSQL 数据库中。
更多数据
ISD数据集有两个部分是每日更新的,气象站元数据,以及最新年份的 isd.daily
(如 2023 年的 Tarball)。
你可以使用以下命令下载并刷新这两个部分。如果数据集没有更新,那么这些命令不会重新下载同样的数据包
make reload # 实际上是:reload-station + reload-daily
你也可以使用以下命令下载并加载特定年份的 isd.daily
数据:
bin/get-daily 2022 # 获取 2022 年的每日气象观测摘要 (1900-2023)
bin/load-daily "${PGURL}" 2022 # 加载 2022 年的每日气象观测摘要 (1900-2023)
除了每日摘要 isd.daily
, ISD 还提供了一份更详细的亚小时级原始观测记录 isd.hourly
,下载与加载的方式与前者类似:
bin/get-hourly 2022 # 下载特定某一年的小时级观测记录(例如2022年,可选 1900-2023)
bin/load-hourly "${PGURL}" 2022 # 加载特定某一年的小时级观测记录
数据
数据集概要
ISD提供了四个数据集:亚小时级原始观测数据,每日统计摘要数据,月度统计摘要,年度统计摘要
数据集 | 备注 |
---|---|
ISD Hourly | 亚小时级观测记录 |
ISD Daily | 每日统计摘要 |
ISD Monthly | 没有用到,因为可以从 isd.daily 计算生成 |
ISD Yearly | 没有用到,因为可以从 isd.daily 计算生成 |
每日摘要数据集
- 压缩包大小 2.8GB (截止至 2023-06-24)
- 表大小 24GB,索引大小 6GB,PostgreSQL 中总大小约为 30GB
- 如果启用了 timescaledb 压缩,总大小可以压缩到 4.5 GB。
亚小时级观测数据级
- 压缩包总大小 117GB
- 灌入数据库后表大小 1TB+ ,索引大小 600GB+,总大小 1.6TB
数据库模式
气象站元数据表
CREATE TABLE isd.station
(
station VARCHAR(12) PRIMARY KEY,
usaf VARCHAR(6) GENERATED ALWAYS AS (substring(station, 1, 6)) STORED,
wban VARCHAR(5) GENERATED ALWAYS AS (substring(station, 7, 5)) STORED,
name VARCHAR(32),
country VARCHAR(2),
province VARCHAR(2),
icao VARCHAR(4),
location GEOMETRY(POINT),
longitude NUMERIC GENERATED ALWAYS AS (Round(ST_X(location)::NUMERIC, 6)) STORED,
latitude NUMERIC GENERATED ALWAYS AS (Round(ST_Y(location)::NUMERIC, 6)) STORED,
elevation NUMERIC,
period daterange,
begin_date DATE GENERATED ALWAYS AS (lower(period)) STORED,
end_date DATE GENERATED ALWAYS AS (upper(period)) STORED
);
每日摘要表
CREATE TABLE IF NOT EXISTS isd.daily
(
station VARCHAR(12) NOT NULL, -- station number 6USAF+5WBAN
ts DATE NOT NULL, -- observation date
-- 气温 & 露点
temp_mean NUMERIC(3, 1), -- mean temperature ℃
temp_min NUMERIC(3, 1), -- min temperature ℃
temp_max NUMERIC(3, 1), -- max temperature ℃
dewp_mean NUMERIC(3, 1), -- mean dew point ℃
-- 气压
slp_mean NUMERIC(5, 1), -- sea level pressure (hPa)
stp_mean NUMERIC(5, 1), -- station pressure (hPa)
-- 可见距离
vis_mean NUMERIC(6), -- visible distance (m)
-- 风速
wdsp_mean NUMERIC(4, 1), -- average wind speed (m/s)
wdsp_max NUMERIC(4, 1), -- max wind speed (m/s)
gust NUMERIC(4, 1), -- max wind gust (m/s)
-- 降水 / 雪深
prcp_mean NUMERIC(5, 1), -- precipitation (mm)
prcp NUMERIC(5, 1), -- rectified precipitation (mm)
sndp NuMERIC(5, 1), -- snow depth (mm)
-- FRSHTT (Fog/Rain/Snow/Hail/Thunder/Tornado) 雾/雨/雪/雹/雷/龙卷
is_foggy BOOLEAN, -- (F)og
is_rainy BOOLEAN, -- (R)ain or Drizzle
is_snowy BOOLEAN, -- (S)now or pellets
is_hail BOOLEAN, -- (H)ail
is_thunder BOOLEAN, -- (T)hunder
is_tornado BOOLEAN, -- (T)ornado or Funnel Cloud
-- 统计聚合使用的记录数
temp_count SMALLINT, -- record count for temp
dewp_count SMALLINT, -- record count for dew point
slp_count SMALLINT, -- record count for sea level pressure
stp_count SMALLINT, -- record count for station pressure
wdsp_count SMALLINT, -- record count for wind speed
visib_count SMALLINT, -- record count for visible distance
-- 气温标记
temp_min_f BOOLEAN, -- aggregate min temperature
temp_max_f BOOLEAN, -- aggregate max temperature
prcp_flag CHAR, -- precipitation flag: ABCDEFGHI
PRIMARY KEY (station, ts)
); -- PARTITION BY RANGE (ts);
亚小时级原始观测数据表
ISD Hourly
CREATE TABLE IF NOT EXISTS isd.hourly
(
station VARCHAR(12) NOT NULL, -- station id
ts TIMESTAMP NOT NULL, -- timestamp
-- air
temp NUMERIC(3, 1), -- [-93.2,+61.8]
dewp NUMERIC(3, 1), -- [-98.2,+36.8]
slp NUMERIC(5, 1), -- [8600,10900]
stp NUMERIC(5, 1), -- [4500,10900]
vis NUMERIC(6), -- [0,160000]
-- wind
wd_angle NUMERIC(3), -- [1,360]
wd_speed NUMERIC(4, 1), -- [0,90]
wd_gust NUMERIC(4, 1), -- [0,110]
wd_code VARCHAR(1), -- code that denotes the character of the WIND-OBSERVATION.
-- cloud
cld_height NUMERIC(5), -- [0,22000]
cld_code VARCHAR(2), -- cloud code
-- water
sndp NUMERIC(5, 1), -- mm snow
prcp NUMERIC(5, 1), -- mm precipitation
prcp_hour NUMERIC(2), -- precipitation duration in hour
prcp_code VARCHAR(1), -- precipitation type code
-- sky
mw_code VARCHAR(2), -- manual weather observation code
aw_code VARCHAR(2), -- auto weather observation code
pw_code VARCHAR(1), -- weather code of past period of time
pw_hour NUMERIC(2), -- duration of pw_code period
-- misc
-- remark TEXT,
-- eqd TEXT,
data JSONB -- extra data
) PARTITION BY RANGE (ts);
解析器
NOAA ISD 提供的原始数据是高度压缩的专有格式,需要通过解析器加工,才能转换为数据库表的格式。
针对 Daily 与 Hourly 两份数据集,这里提供了两个 Parser: isdd
and isdh
。
这两个解析器都以年度数据压缩包作为输入,产生 CSV 结果作为输出,以管道的方式工作,如下所示:
NAME
isd -- Intergrated Surface Dataset Parser
SYNOPSIS
isd daily [-i <input|stdin>] [-o <output|stout>] [-v]
isd hourly [-i <input|stdin>] [-o <output|stout>] [-v] [-d raw|ts-first|hour-first]
DESCRIPTION
The isd program takes noaa isd daily/hourly raw tarball data as input.
and generate parsed data in csv format as output. Works in pipe mode
cat data/daily/2023.tar.gz | bin/isd daily -v | psql ${PGURL} -AXtwqc "COPY isd.daily FROM STDIN CSV;"
isd daily -v -i data/daily/2023.tar.gz | psql ${PGURL} -AXtwqc "COPY isd.daily FROM STDIN CSV;"
isd hourly -v -i data/hourly/2023.tar.gz | psql ${PGURL} -AXtwqc "COPY isd.hourly FROM STDIN CSV;"
OPTIONS
-i <input> input file, stdin by default
-o <output> output file, stdout by default
-p <profpath> pprof file path, enable if specified
-d de-duplicate rows for hourly dataset (raw, ts-first, hour-first)
-v verbose mode
-h print help
用户界面
这里提供了几个使用 Grafana 制作的 Dashboard,可以用于探索 ISD 数据集,查询气象站与历史气象数据。
ISD Overview
全局概览,总体指标与气象站导航。
ISD Country
展示单个国家/地区内所有的气象站。
ISD Station
展示单个气象站的详细信息,元数据,天/月/年度汇总指标。
ISD Station Dashboard
ISD Detail
展示一个气象站原始亚小时级观测指标数据,需要 isd.hourly
数据集。
ISD Station Dashboard
1.3 - WHO COVID-19 疫情大盘
Covid 是 Pigsty 自带的,用于展示世界卫生组织官方疫情数据大盘的一个样例 Applet。
您可以查阅每个国家与地区 COVID-19 的感染与死亡案例,以及全球的疫情趋势。
概览
GitHub 仓库地址:https://github.com/Vonng/pigsty-app/tree/master/covid
在线Demo地址:https://demo.pigsty.cc/d/covid
安装
在管理节点上进入应用目录,执行make
以完成安装。
make # 完成所有配置
其他一些子任务:
make reload # download latest data and pour it again
make ui # install grafana dashboards
make sql # install database schemas
make download # download latest data
make load # load downloaded data into database
make reload # download latest data and pour it into database
1.4 - AWS 阿里云 服务器价格
概览
GitHub 仓库地址:https://github.com/Vonng/pigsty-app/tree/master/cloud
在线Demo地址:https://demo.pigsty.cc/d/ecs
文章地址:《剖析算力成本:阿里云真降价了吗?》
数据源
Aliyun ECS 价格可以在 价格计算器 - 定价详情 - 价格下载 中获取 CSV 原始数据。
模式
下载 阿里云 价格明细并导入分析
CREATE EXTENSION file_fdw;
CREATE SERVER fs FOREIGN DATA WRAPPER file_fdw;
DROP FOREIGN TABLE IF EXISTS aliyun_ecs CASCADE;
CREATE FOREIGN TABLE aliyun_ecs
(
"region" text,
"system" text,
"network" text,
"isIO" bool,
"instanceId" text,
"hourlyPrice" numeric,
"weeklyPrice" numeric,
"standard" numeric,
"monthlyPrice" numeric,
"yearlyPrice" numeric,
"2yearPrice" numeric,
"3yearPrice" numeric,
"4yearPrice" numeric,
"5yearPrice" numeric,
"id" text,
"instanceLabel" text,
"familyId" text,
"serverType" text,
"cpu" text,
"localStorage" text,
"NvmeSupport" text,
"InstanceFamilyLevel" text,
"EniTrunkSupported" text,
"InstancePpsRx" text,
"GPUSpec" text,
"CpuTurboFrequency" text,
"InstancePpsTx" text,
"InstanceTypeId" text,
"GPUAmount" text,
"InstanceTypeFamily" text,
"SecondaryEniQueueNumber" text,
"EniQuantity" text,
"EniPrivateIpAddressQuantity" text,
"DiskQuantity" text,
"EniIpv6AddressQuantity" text,
"InstanceCategory" text,
"CpuArchitecture" text,
"EriQuantity" text,
"MemorySize" numeric,
"EniTotalQuantity" numeric,
"PhysicalProcessorModel" text,
"InstanceBandwidthRx" numeric,
"CpuCoreCount" numeric,
"Generation" text,
"CpuSpeedFrequency" numeric,
"PrimaryEniQueueNumber" text,
"LocalStorageCategory" text,
"InstanceBandwidthTx" text,
"TotalEniQueueQuantity" text
) SERVER fs OPTIONS ( filename '/tmp/aliyun-ecs.csv', format 'csv',header 'true');
AWS EC2 同理,可以从 Vantage 下载价格清单:
DROP FOREIGN TABLE IF EXISTS aws_ec2 CASCADE;
CREATE FOREIGN TABLE aws_ec2
(
"name" TEXT,
"id" TEXT,
"Memory" TEXT,
"vCPUs" TEXT,
"GPUs" TEXT,
"ClockSpeed" TEXT,
"InstanceStorage" TEXT,
"NetworkPerformance" TEXT,
"ondemand" TEXT,
"reserve" TEXT,
"spot" TEXT
) SERVER fs OPTIONS ( filename '/tmp/aws-ec2.csv', format 'csv',header 'true');
DROP VIEW IF EXISTS ecs;
CREATE VIEW ecs AS
SELECT "region" AS region,
"id" AS id,
"instanceLabel" AS name,
"familyId" AS family,
"CpuCoreCount" AS cpu,
"MemorySize" AS mem,
round("5yearPrice" / "CpuCoreCount" / 60, 2) AS ycm5, -- ¥ / (core·month)
round("4yearPrice" / "CpuCoreCount" / 48, 2) AS ycm4, -- ¥ / (core·month)
round("3yearPrice" / "CpuCoreCount" / 36, 2) AS ycm3, -- ¥ / (core·month)
round("2yearPrice" / "CpuCoreCount" / 24, 2) AS ycm2, -- ¥ / (core·month)
round("yearlyPrice" / "CpuCoreCount" / 12, 2) AS ycm1, -- ¥ / (core·month)
round("standard" / "CpuCoreCount", 2) AS ycmm, -- ¥ / (core·month)
round("hourlyPrice" / "CpuCoreCount" * 720, 2) AS ycmh, -- ¥ / (core·month)
"CpuSpeedFrequency"::NUMERIC AS freq,
"CpuTurboFrequency"::NUMERIC AS freq_turbo,
"Generation" AS generation
FROM aliyun_ecs
WHERE system = 'linux';
DROP VIEW IF EXISTS ec2;
CREATE VIEW ec2 AS
SELECT id,
name,
split_part(id, '.', 1) as family,
split_part(id, '.', 2) as spec,
(regexp_match(split_part(id, '.', 1), '^[a-zA-Z]+(\d)[a-z0-9]*'))[1] as gen,
regexp_substr("vCPUs", '^[0-9]+')::int as cpu,
regexp_substr("Memory", '^[0-9]+')::int as mem,
CASE spot
WHEN 'unavailable' THEN NULL
ELSE round((regexp_substr("spot", '([0-9]+.[0-9]+)')::NUMERIC * 7.2), 2) END AS spot,
CASE ondemand
WHEN 'unavailable' THEN NULL
ELSE round((regexp_substr("ondemand", '([0-9]+.[0-9]+)')::NUMERIC * 7.2), 2) END AS ondemand,
CASE reserve
WHEN 'unavailable' THEN NULL
ELSE round((regexp_substr("reserve", '([0-9]+.[0-9]+)')::NUMERIC * 7.2), 2) END AS reserve,
"ClockSpeed" AS freq
FROM aws_ec2;
可视化
1.5 - 使用一条 SQL 计算扑克24点
题目
题目如下: 《数据库编程大赛:一条SQL计算扑克牌24点》
有一张表 cards,id 是自增字段的数字主键,另外有4个字段 c1,c2,c3,c4 ,每个字段随机从 1~10 之间选择一个整数 要求选手使用一条 SQL 给出 24 点的计算公式,返回的内容示例如右图:
其中 result 字段是计算的表达式,只需返回1个解,如果没有解,result 返回null
-
24 点的计算规则:只能使用加减乘除四则运算,不能使用阶乘、指数等运算符,每个数字最少使用一次,且只能使用一次,可以使用小括号改变优先级
-
只能使用一条 SQL ,可以使用数据库内置函数,但是不能使用存储过程/自定义函数和代码块。
-
SQL 正确性大家在 NineData 平台 demo 数据库自己验证,或在自己的数据库上验证,组委会评测服务器是 4 核 CPU ,32 GB 内存
-
选手个人诚信参赛,不允许提交别人的比赛代码,如果发现有类似代码,工作组以第一个提交的为有效参赛
-
每个选手最多提交 3 次比赛代码
-
提交的 SQL 不能超过 10 KB大小
作为 MySQL 老司机,NineData 搞的这个比赛暗吹 MySQL 的水平比姜高到不知道哪里去了 —— 为什么这么说呢?
因为 10KB 的大小限制非常猥琐 —— 最快的解法都是质数查表,而这种方式所有解的文本拼接大小大约是 10018 个字符。要想压缩这个表到 10KB 以内,必须要用到一些压缩技巧。
MySQL 是带有 COMPRESS 和 UNCOMPRESS 函数的,而 PostgreSQL 原生是没带的,需要用到 pgsql-gzip
扩展,而这个扩展在 NineData 比赛的平台上是不提供的。
下面是使用 PostgreSQL 的解法:
创建随机测试数据表
CREATE SCHEMA poker24;
DROP TABLE IF EXISTS poker24.cards;
CREATE TABLE poker24.cards AS
SELECT i AS id,
ceil(random() * 10) AS c1,
ceil(random() * 10) AS c2,
ceil(random() * 10) AS c3,
ceil(random() * 10) AS c4
FROM generate_series(1, 1000000) i;
ALTER TABLE poker24.cards ADD PRIMARY KEY (id);
解法
基本思想是是使用质数编码,将所有可能的结果分配唯一主键编号,快速计算 24 点:
EXPLAIN ANALYZE
WITH a(i, result) AS (
SELECT (split_part(kv, ':', 1))::INTEGER AS i, split_part(kv, ':', 2) AS result
FROM regexp_split_to_table('152:((1+1)+1)*8,156:(6*2)*(1+1),204:(7+1)*(2+1),228:((1*1)+2)*8,276:(9-1)*(2+1),348:(10+2)*(1+1),140:(4*3)*(1+1),220:(5+1)*(3+1),260:((1+1)+6)*3,340:((1*1)+7)*3,380:(8*3)+(1-1),460:(9+3)*(1+1),580:(10-(1+1))*3,196:((1+1)+4)*4,308:((1*1)+5)*4,364:(6*4)+(1-1),476:(7-(1*1))*4,532:(8+4)*(1+1),644:(9-1)*(4-1),812:((1+1)*10)+4,484:(5*5)-(1*1),572:(5-(1*1))*6,748:(7+5)*(1+1),836:(5-(1+1))*8,676:(6+6)*(1+1),988:(8*6)/(1+1),1196:((1+1)*9)+6,1972:((1+1)*7)+10,1444:((1+1)*8)+8,126:(4*2)*(2+1),198:(2+2)*(5+1),234:(6+2)*(2+1),306:(2+2)*(7-1),342:((2-1)+2)*8,414:((2+1)+9)*2,522:(10-2)*(2+1),150:(3*2)*(3+1),210:((2+1)+3)*4,330:(5+3)*(2+1),390:((2-1)+3)*6,510:(7*3)+(2+1),570:(8*3)*(2-1),690:(9*3)-(2+1),870:(10-(2*1))*3,294:(4+4)*(2+1),462:((2-1)+5)*4,546:(6*4)*(2-1),714:(7-(2-1))*4,798:(4-(2-1))*8,966:(9-(2+1))*4,1218:((2*1)*10)+4,726:(5*5)-(2-1),858:(5-(2-1))*6,1122:(7+5)*(2*1),1254:(5-(2*1))*8,1518:((2+1)*5)+9,1914:(10*2)+(5-1),1014:((2+1)*6)+6,1326:(7-(2+1))*6,1482:(6-(2+1))*8,1794:((2*1)*9)+6,2262:((2+1)*10)-6,1734:((7*7)-1)/2,1938:(8*2)+(7+1),2346:(9*2)+(7-1),2958:((2*1)*7)+10,2166:((2*1)*8)+8,2622:(9*8)/(2+1),3306:((8-1)*2)+10,250:(3+3)*(3+1),350:((3+1)+4)*3,550:(5+3)*(3*1),650:((3-1)+6)*3,850:(7*3)+(3*1),950:((8+1)*3)-3,1150:(9-3)*(3+1),1450:(10-(3-1))*3,490:((3-1)+4)*4,770:(5*4)+(3+1),910:6/(1-(3/4)),1190:(7*4)-(3+1),1330:((3+1)*4)+8,1610:(9-(3*1))*4,2030:(10-4)*(3+1),1430:(6*3)+(5+1),1870:(7+5)*(3-1),2090:(5-(3-1))*8,2530:((3*1)*5)+9,3190:(10*3)-(5+1),1690:(6+6)*(3-1),2210:(7-(3*1))*6,2470:(8-(3+1))*6,2990:((3-1)*9)+6,3770:((3*1)*10)-6,2890:(7-3)*(7-1),3230:(7-(3+1))*8,3910:(9/3)*(7+1),4930:((3-1)*7)+10,3610:((3+1)*8)-8,4370:(9*8)/(3*1),5510:(8/3)*(10-1),5290:(9/3)*(9-1),6670:((10+1)*3)-9,8410:(10+10)+(3+1),686:((4+1)*4)+4,1078:(5*4)+(4*1),1274:((6+1)*4)-4,1666:(7*4)-(4*1),1862:((4*1)*4)+8,2254:(9-(4-1))*4,2842:(10-4)*(4*1),1694:(5*4)+(5-1),2002:6/((5/4)-1),2618:(7*4)-(5-1),2926:(8-4)*(5+1),3542:((4-1)*5)+9,4466:(10-4)*(5-1),2366:((4+1)*6)-6,3094:(7-(4-1))*6,3458:(6-(4-1))*8,4186:(9-(4+1))*6,5278:((4-1)*10)-6,4046:(7-4)*(7+1),4522:(7-(4*1))*8,5474:(7-4)*(9-1),5054:(8-(4+1))*8,6118:(9*8)/(4-1),9338:(10+9)+(4+1),11774:(10+10)+(4*1),2662:(5-(1/5))*5,3146:(6*5)-(5+1),5566:(9-5)*(5+1),7018:((10-5)*5)-1,3718:((5*1)*6)-6,4862:(6*5)-(7-1),5434:(8-(5-1))*6,6578:(9-(5*1))*6,8294:(10-6)*(5+1),7106:(7-(5-1))*8,8602:(9-5)*(7-1),10846:(7*5)-(10+1),7942:((5-1)*8)-8,9614:(9-(5+1))*8,12122:(10+8)+(5+1),11638:(9+9)+(5+1),14674:(10+9)+(5*1),18502:(10+10)+(5-1),4394:((6-1)*6)-6,6422:6/(1-(6/8)),7774:(9-(6-1))*6,9802:(10-6)*(6*1),10166:(9-6)*(7+1),12818:(10+7)+(6+1),9386:(8-(6-1))*8,11362:(9+8)+(6+1),14326:(10-(6+1))*8,13754:(9+9)+(6*1),17342:(10+9)+(6-1),13294:(9+7)+(7+1),16762:(10-7)*(7+1),12274:(8+8)+(7+1),14858:(9-(7-1))*8,18734:(10+8)+(7-1),17986:(9+9)+(7-1),22678:(10-7)*(9-1),13718:(8+8)+(8*1),16606:(9+8)+(8-1),20938:(10-(8-1))*8,135:(3*2)*(2+2),189:(4+2)*(2+2),297:((5*2)+2)*2,459:((7*2)-2)*2,513:(8-2)*(2+2),621:((9+2)*2)+2,783:(10*2)+(2+2),225:(3+3)*(2+2),315:((2+2)+4)*3,495:((5*2)-2)*3,585:((2/2)+3)*6,765:((2/2)+7)*3,855:(8*3)+(2-2),1035:(9-3)*(2+2),1305:((10+3)*2)-2,441:((4*2)-2)*4,693:(5*4)+(2+2),819:(6*4)+(2-2),1071:(7*4)-(2+2),1197:((2+2)*4)+8,1449:(9*2)+(4+2),1827:(10-4)*(2+2),1089:(5*5)-(2/2),1287:(5-(2/2))*6,1683:(7*2)+(5*2),1881:((8+5)*2)-2,2277:((5-2)+9)*2,2871:((5+2)*2)+10,1521:(6/2)*(6+2),1989:((7+2)*2)+6,2223:(8-(2+2))*6,2691:((6/2)+9)*2,3393:(10*2)+(6-2),2601:((7-2)+7)*2,2907:(7-(2+2))*8,4437:((10/2)+7)*2,3249:((2+2)*8)-8,3933:(9*2)+(8-2),4959:(10-2)+(8*2),6003:((9-2)*2)+10,7569:(10+10)+(2+2),375:((3+2)+3)*3,825:((5+2)*3)+3,975:((3-2)+3)*6,1275:((3-2)+7)*3,1425:(8*3)*(3-2),1725:((3+2)*3)+9,2175:(10*3)-(3*2),735:((3+2)*4)+4,1155:((3-2)+5)*4,1365:(6*4)*(3-2),1785:(7-(3-2))*4,1995:(4-(3-2))*8,2415:(9*4)/(3/2),3045:(10*3)-(4+2),1815:(5*5)-(3-2),2145:(5-(3-2))*6,2805:(7*3)+(5-2),3135:(5+3)+(8*2),3795:(9-5)*(3*2),4785:(5-3)*(10+2),2535:((3+2)*6)-6,3315:(7*3)+(6/2),3705:((8+2)*3)-6,4485:(9-(3+2))*6,5655:(10-6)*(3*2),4335:(7+3)+(7*2),4845:(8/3)*(7+2),5865:(9+7)*(3/2),7395:(7-3)+(10*2),5415:(8-(3+2))*8,6555:(9-(3*2))*8,8265:(10+8)+(3*2),7935:(9+9)+(3*2),10005:(10+9)+(3+2),12615:((10-3)*2)+10,1029:((4-2)+4)*4,1617:((5+2)*4)-4,1911:((4*2)-4)*6,2499:(7-4)*(4*2),2793:(8-4)*(4+2),3381:((9-2)*4)-4,4263:((4-2)*10)+4,2541:((5+5)*2)+4,3003:(6*5)-(4+2),3927:(7+5)*(4-2),4389:(5-(4-2))*8,5313:(9-5)*(4+2),6699:(10+4)+(5*2),3549:(6+6)*(4-2),4641:(7-4)*(6+2),5187:(8*6)/(4-2),6279:((4-2)*9)+6,7917:(10-6)*(4+2),6069:((7+7)*2)-4,6783:((7*2)-8)*4,8211:(9+7)+(4*2),10353:((4-2)*7)+10,7581:((4-2)*8)+8,9177:(9-(4+2))*8,11571:(10+8)+(4+2),11109:(9+9)+(4+2),14007:(10-4)+(9*2),17661:((4/10)+2)*10,6171:(5+5)+(7*2),6897:((5/5)+2)*8,8349:((5-2)*5)+9,10527:(5-(2/10))*5,5577:((5-2)*6)+6,7293:(7-(5-2))*6,8151:(6-(5-2))*8,9867:((5/2)*6)+9,12441:((5-2)*10)-6,9537:(7+7)+(5*2),10659:((5*2)-7)*8,12903:(7*5)-(9+2),16269:(10+7)+(5+2),11913:(8*5)-(8*2),14421:(9+8)+(5+2),18183:(10-(5+2))*8,22011:(9-5)+(10*2),27753:(10/5)*(10+2),6591:(6+6)+(6*2),8619:(7-(6/2))*6,9633:(8-(6-2))*6,11661:(9-6)*(6+2),14703:(10+6)+(6+2),12597:(7-(6-2))*8,15249:(9+7)+(6+2),19227:(10-7)*(6+2),14079:(8+8)+(6+2),17043:((6*2)-9)*8,21489:(10-8)*(6*2),20631:((9-6)+9)*2,26013:(9-6)*(10-2),32799:(10+10)+(6-2),16473:(8+7)+(7+2),25143:((10/7)+2)*7,18411:(8-(7-2))*8,22287:((9+7)*2)-8,34017:(10+9)+(7-2),42891:(10-7)*(10-2),20577:((8/2)*8)-8,24909:(9-(8-2))*8,31407:(10+8)+(8-2),30153:(9+9)+(8-2),38019:(10-(9-2))*8,47937:(10+10)+(8/2),58029:(10+9)+(10/2),625:((3*3)*3)-3,875:((3*3)-3)*4,1375:(5*3)+(3*3),1625:(6*3)+(3+3),2125:(7-3)*(3+3),2375:((3+3)-3)*8,2875:(9-(3/3))*3,3625:(10*3)-(3+3),1225:(4*3)+(4*3),1925:((3/3)+5)*4,2275:(6*4)+(3-3),2975:(7-(3/3))*4,3325:(8-4)*(3+3),4025:(9-(4-3))*3,3025:(5*5)-(3/3),3575:(6*5)-(3+3),4675:((5*3)-7)*3,6325:(9-5)*(3+3),7975:(10+5)+(3*3),4225:((6/3)+6)*3,5525:(7*3)+(6-3),6175:((3*3)-6)*8,7475:(9+6)+(3*3),9425:(10-6)*(3+3),7225:((3/7)+3)*7,8075:(8+7)+(3*3),9775:(9-3)*(7-3),9025:8/(3-(8/3)),10925:(9-(3+3))*8,13775:(10+8)+(3+3),13225:(9+9)+(3+3),16675:(10*3)-(9-3),1715:((4+3)*4)-4,2695:((4-3)+5)*4,3185:(6*4)*(4-3),4165:(7-(4-3))*4,4655:((4+3)-4)*8,5635:(9*4)-(4*3),7105:((10-3)*4)-4,4235:(5*5)-(4-3),5005:(5-(4-3))*6,6545:(7+5)+(4*3),7315:(8*4)-(5+3),8855:((5*3)-9)*4,11165:(10/5)*(4*3),5915:(6+6)+(4*3),8645:(8-6)*(4*3),10465:(9-(6-3))*4,13195:(10-4)+(6*3),10115:(7*4)-(7-3),11305:((7-3)*4)+8,13685:(9-7)*(4*3),17255:(10+7)+(4+3),15295:(9+8)+(4+3),19285:(10-(4+3))*8,18515:(9+9)*(4/3),29435:(10*3)-(10-4),7865:((5+5)*3)-6,10285:(7+5)*(5-3),11495:(8-5)*(5+3),13915:(9-(5/5))*3,9295:(6+6)*(5-3),12155:(7+5)*(6/3),13585:(8*6)/(5-3),16445:(9-6)*(5+3),20735:(10+6)+(5+3),17765:(8-5)+(7*3),21505:(9+7)+(5+3),27115:(10-7)*(5+3),19855:(8+8)+(5+3),24035:(9*3)-(8-5),29095:((5/3)*9)+9,36685:(10/5)*(9+3),46255:(10-(10/5))*3,10985:((6-3)*6)+6,14365:(7-(6-3))*6,16055:((6+3)-6)*8,19435:(9+6)+(6+3),24505:((6-3)*10)-6,18785:((7-6)+7)*3,20995:(8+7)+(6+3),25415:(9-6)+(7*3),32045:((6/3)*7)+10,23465:((6/3)*8)+8,28405:(9*8)/(6-3),35815:(10-(8-6))*3,34385:(9*3)-(9-6),43355:(10-6)*(9-3),54665:(3-(6/10))*10,24565:(7+7)+(7+3),27455:((7+3)-7)*8,33235:(9-(7/7))*3,41905:(10-7)+(7*3),30685:((7-3)*8)-8,37145:(9-(8-7))*3,44965:(9-7)*(9+3),56695:(9*3)-(10-7),71485:(10+10)+(7-3),34295:((8+3)-8)*8,41515:(9-8)*(8*3),52345:((10*8)-8)/3,50255:(9-9)+(8*3),63365:(10+9)+(8-3),79895:(10-10)+(8*3),60835:(9+9)+(9-3),76705:((9+9)-10)*3,96715:(9-(10/10))*3,2401:(4*4)+(4+4),3773:((4/4)+5)*4,4459:((4+4)-4)*6,5831:(7-4)*(4+4),6517:(8*4)-(4+4),7889:((9-4)*4)+4,9947:(10*4)-(4*4),5929:(5*5)-(4/4),7007:(5-(4/4))*6,9163:(7-(5-4))*4,10241:(8-5)*(4+4),15631:((10-5)*4)+4,12103:(8+4)*(6-4),14651:(9-6)*(4+4),18473:(10+6)+(4+4),14161:(4-(4/7))*7,15827:(7*4)-(8-4),19159:(9+7)+(4+4),24157:(10-7)*(4+4),17689:(8+8)+(4+4),21413:(9*4)-(8+4),26999:(10-4)*(8-4),41209:((10*10)-4)/4,9317:(5*5)-(5-4),11011:((5+4)-5)*6,14399:(7-(5/5))*4,16093:(4-(5/5))*8,19481:(9-5)+(5*4),24563:(10+5)+(5+4),13013:(6-5)*(6*4),17017:(7+5)*(6-4),19019:((5+4)-6)*8,23023:(9+6)+(5+4),29029:(10-6)+(5*4),22253:(7*5)-(7+4),24871:(8+7)+(5+4),30107:((7-4)*5)+9,37961:((7-5)*10)+4,27797:(5-(8/4))*8,33649:(9-(8-5))*4,42427:(10/5)*(8+4),40733:((9/9)+5)*4,51359:(9-5)*(10-4),64757:((10/5)*10)+4,15379:((6+4)-6)*6,20111:(7-6)*(6*4),22477:(8+6)+(6+4),27209:((6-4)*9)+6,34307:(10+6)*(6/4),26299:(7+7)+(6+4),29393:((6+4)-7)*8,35581:(9+7)*(6/4),44863:((6-4)*7)+10,32851:((6-4)*8)+8,39767:(9-8)*(6*4),50141:((8-6)*10)+4,48139:(9-9)+(6*4),60697:(10-9)*(6*4),76531:(10-10)+(6*4),34391:(7-(7/7))*4,38437:((7+7)-8)*4,42959:((7+4)-8)*8,52003:(9*8)/(7-4),65569:((7/4)*8)+10,62951:(7-(9/9))*4,79373:(10*4)-(9+7),100079:(7-(10/10))*4,48013:((8-4)*8)-8,58121:((8+4)-9)*8,73283:(10-8)*(8+4),70357:(4-(9/9))*8,88711:((9+4)-10)*8,111853:(10+10)+(8-4),107387:(10+9)+(9-4),14641:(5*5)-(5/5),17303:(5*5)-(6-5),30613:(9+5)+(5+5),20449:((5+5)-6)*6,26741:(5*5)-(7-6),29887:(8+6)+(5+5),34969:(7+7)+(5+5),39083:((5+5)-7)*8,59653:(10/5)*(7+5),43681:(5*5)-(8/8),52877:(5*5)-(9-8),66671:(10+5)*(8/5),64009:(5*5)-(9/9),80707:(5*5)-(10-9),101761:(5*5)-(10/10),24167:(5-(6/6))*6,31603:(7+6)+(6+5),35321:((8-5)*6)+6,42757:(9*6)-(6*5),53911:((10-5)*6)-6,41327:(5-(7/7))*6,46189:(8-6)*(7+5),55913:((7-5)*9)+6,51623:((6+5)-8)*8,62491:((8+5)-9)*6,78793:(6*5)/(10/8),75647:((9-6)*5)+9,95381:((9+5)-10)*6,120263:(10+10)*(6/5),73117:(9-7)*(7+5),92191:((7-5)*7)+10,67507:((7-5)*8)+8,81719:((7+5)-9)*8,103037:(10-8)*(7+5),124729:((10-7)*5)+9,157267:((7/5)*10)+10,75449:(8*5)-(8+8),91333:(9*8)/(8-5),115159:((8+5)-10)*8,212773:(10+10)+(9-5),28561:(6+6)+(6+6),41743:(8-6)*(6+6),50531:(6*6)/(9/6),63713:(10*6)-(6*6),66079:(9-7)*(6+6),83317:((10-7)*6)+6,61009:(8*6)/(8-6),73853:((6+6)-9)*8,93119:(10-8)*(6+6),112723:((9-6)*10)-6,108953:((7+7)-10)*6,96577:(8*6)/(9-7),121771:((7+6)-10)*8,116909:(7*6)-(9+9),185861:((10-7)*10)-6,89167:((8-6)*8)+8,107939:(9*8)-(8*6),136097:(8*6)/(10-8),130663:(9+9)*(8/6),164749:((10-8)*9)+6,199433:((9/6)*10)+9,317057:(10+10)+(10-6),192763:((9-7)*7)+10,141151:((9-7)*8)+8,177973:(10*8)-(8*7),215441:(9*8)/(10-7),271643:((10-8)*7)+10,198911:((10-8)*8)+8', ',') AS kv
)
SELECT c.id, c1, c2, c3, c4, result
FROM poker24.cards c LEFT JOIN a a ON a.i =
( CASE c1 WHEN 1 THEN 2 WHEN 2 THEN 3 WHEN 3 THEN 5 WHEN 4 THEN 7 WHEN 5 THEN 11 WHEN 6 THEN 13 WHEN 7 THEN 17 WHEN 8 THEN 19 WHEN 9 THEN 23 WHEN 10 THEN 29 END
* CASE c2 WHEN 1 THEN 2 WHEN 2 THEN 3 WHEN 3 THEN 5 WHEN 4 THEN 7 WHEN 5 THEN 11 WHEN 6 THEN 13 WHEN 7 THEN 17 WHEN 8 THEN 19 WHEN 9 THEN 23 WHEN 10 THEN 29 END
* CASE c3 WHEN 1 THEN 2 WHEN 2 THEN 3 WHEN 3 THEN 5 WHEN 4 THEN 7 WHEN 5 THEN 11 WHEN 6 THEN 13 WHEN 7 THEN 17 WHEN 8 THEN 19 WHEN 9 THEN 23 WHEN 10 THEN 29 END
* CASE c4 WHEN 1 THEN 2 WHEN 2 THEN 3 WHEN 3 THEN 5 WHEN 4 THEN 7 WHEN 5 THEN 11 WHEN 6 THEN 13 WHEN 7 THEN 17 WHEN 8 THEN 19 WHEN 9 THEN 23 WHEN 10 THEN 29 END);
当然,这里的字符串长度超过了 10000: 10896 个。我们可以用一些手段来压缩,比如把这个巨长的 CASE 弄成一个 inline 函数,然后再把主键从十进制数字字面值换成十六进制,其实长度就在 10KB 以内了。 不过规则禁止我们使用存储过程,这就要想其他办法了。主要就是如何压缩中间那个长字符串。
压缩优化
当然,这里的字符串长度超过了 10000: 10896 个。所以需要用到额外的压缩功能,来满足题目要求。 Pigsty 原生提供了 pgsql-gzip
扩展:
CREATE EXTENSION IF NOT EXISTS gzip;
然后我们把上面的结果表压缩一下,10018个字符压缩到 7796 个,总长度 8796,满足题目要求
WITH a(i, result) AS (SELECT (split_part(kv, ':', 1))::INTEGER AS i, split_part(kv, ':', 2) AS result
FROM regexp_split_to_table(encode(gunzip('\x1F8B08000000000000034D5A5B722B2B0CDC4EE278CABC24E0EE7F6157DD2DF0A9CA47860109F468B518576BFFFDFCD4BFFA1B7FAFF5AEE6FFFDF8ABFDBE38F86E65FCF733F1EEA7F1B92DCC7FC5FC86F96DC6FCFDDCF77DC4FB5AFEAE803ACA7F3FE3D5AFC016CF46819DCF5ECE06FCF7D54340390A269F573CAF58FFF75343CD7B60FEFEBBF20CEF6B79F8840575FB11387E5FE3DDCBDDB1F1D9074E38AE409C603E9C81F7D6C3220B6BA5C0C738271C98BFEAB1D8AB96D0F11E2B26D8CB7E25E36D3326D811E8EF09934C2897C0D55DEFB9E1F5766CC0717ABDDF6BE1C4FEFB490B7E4FF4DA61A538E1BC5B98E1B71246C62635B27EFFC28DCD61F576DC5277C86CF48AD1EA1D46F8BBEF7BF1F37E3E742334B4E7B87954C8C7D4BFFDFB6A6F6B8D56FF2AB07043A742B9B596B3A0D3EA9D6EEF57E12E474187910CF327DDCCF736D3ED2F4E7A3BE6EF787EF47ECD747B7BC9ED6DC70E07DDC609C3EF09E8761B2EB7A7C0891385DBF180F713161AE779BDB733B0290CEF6BAB8823A84BBF4FD8587EA7C4658B7E958470538591E4782C0B113634E3251DD524136EB3B06CB809BBAA25ECF81713B1A65CCB4744C0F9BD295EB5B118182BD4F81908A9738FB353C64B6BB24586EC136B26FC1FF69EBFA1E4D3427167D041EFCC00C1F935808DB46DF7FC0ABA5661228D30E8424DC39A15912B2733AC7E1692A7690DC38461C030E978E6BFF05C7F9BDD30E93099EBFD73D061D90D13BEDF7CBF70B2088D487EC6E17EAE823A2C03A53F0A94B1AF48E2C3442419F1802B7644A247EAC58ACFF865FA51E7083F4B246399FF635518DC2B95724B10D94A97D2F1DD06469C1B67025606B082A3D3BE056AECEC33AC6952F33AC1D1B991080E248184302B041D12C2B49B6727E1FAC13CD2CE39B0EFF1151C9DE7971A05475B3C306D2830683DA56684F5CD037F3883C9B6FB95AAE0E8B4898CB47E9F40903ECB090EBACE98F28B42C2541869FB8ADD4C7AE7DEA29CC8BFFBBD46A50DFE908232AD2FC4D8486F44A296B98E4387D26E22D85D339E98E1085C795433161304FFCBA38D991A1E1D890E6D8D763DAA35BEC75163726069089C1F8BB0E18023BBA54633365277518629FA09B35022178F819DA51AADE97E8FE7F04E2F5BC0351266FA4062FA1900562F41D7489F5B8345A4462E1E651044C67520017DCA1E1062638E3383BEB00293AC2335CA56C5F1E45016C6DDBB6AFF86E555B9E61C5F77D16ECD3DCBE3C7428E45580B99ED44B599A0D78E9966214C86590C767A6A042D47EC75AC32E8410961CCDAE8DAAEA599DC6084B08A656A2C568C10EA574F2D82564B4B2E2FEDEC640A8D170DA7625FB868D38758A240DF5E153B76F0B85555CBBF75B3BF3A6CB5692A8D0C4F5371485169A57DADC770189DD8EECF39B88FD612AEFCB302AE264D1EEA3D0FBE97A4F09CFE524D9185FDB8BFB655E5BBC85E664A78733158534E1CA376D878F3149EA0D614AE7CE6A43E99393C8594CDAED4D110ADD869FA4D65D21F1C489B9CDF2D316D17D56964B0C26E7998DA16EB585A561E8A3AEE67032A5CCDE7BAB2B736C0F891ECA56CF6E2E7702BF158E1FCF05987B3C371409542FD06E5B8CF6D4F066523696A91549B45B6FD3E7CB6DA61D13BDF5B8DF79B9363C97BAE7E8BBF04363BD592CFBD1AEB78CB6A39B6A542088DEAB9F8FED392544DBFCF53D5D30E976E0F0E507022554B9DA81713E0F65F4A0D44AA4446A918C1C3FA413DAE58751F369D22673DA02791955621B754B51C631F663164C6362FE8694D8165935A7D1AE3738A39C513498FC356533CE94521AB9209586E3CC287DE884D89B28608CCB0343758B3C101FE81439C7AF7A2C7720A9853A3CBB82D964FDF10823592DAFBFE3ACD6181686830653EB27A28DE6526636B02E8A885B4F2E74CE90D369191082221B51F232162E0EA9D8CFB8F3CEDEDA57484CF73CF33CDF7172F1431D3588515111101CD8E0D220AFA7BEBFD7322266AE51D60C8D4D1EC10F14E07CF764442C40E1A8825494B901DEFD9EF0C55E46A5728B97820891D329E4211B960184F13DBDE08ED7106A2220FC4FE8E25411F1012BD8CAFDA8C234C51D4506AAB98624708980DC25BF41181110985AD826FA651FBDC76109F6719DC993D62294C4AFB1E4F15996929A9CEAD4D66D19289509DC63211C40C2373B38BC9D2D32175722793030B9B5FC9B11A1A5D180DA0F9920566DF269EF6A7008CA2879DACA3276AB4592A7E696035B70B9872D62606102F39504B297601BBD3B24165840B4FBFC953DA26A968C9A38305CF135BA259BB5EEC1822A37B1F4E81D1779BBB1F4244170683A827A6296334EFA925BBAE60664A6326FA1FFA7BE4814ABF846CE089A8F560EE74C2C9C327929B0E249697B9C47D2B73C6C193A066FB506B0971E8D5E60916D1BBCDD3A77386C771CE5E49ADE7AEF37A597A8A0B6026512AE09498AF1AB160C5D5603495C6F14A8CBE269899E7B41247D878859E998CAF65AD36805DFA59D9516BD9C7D11A19A51CE0FD23D6441EBA53F407C6A6CD83E74114EC9D91E94B752EF89B2E0756277A21A3B28D2DD60E5E8720B03CB30BC7EA636783EF49B6941391BD953CD6D24B51C8A5474B426C5335B28C86C8AC6D9DBE9EB70E1467D565519CA25FBBF4C3D9360FEE2D8192CBB24AB13873129120CA54AB871158E28B0AF4C367A2522B74D7633707A3EC18677DEC42466CA92A98FE78B716C4B2321388172469DE7BB2AD2C70959E104753718A568E82254679695BA5C5D36651D2585D93C7B1A6B52CAFF32BA92052573239FABD0C041936F76C9E2CD8960ACE226DC4C98A7765A79F921AA5AE9F4DB23645259BFB9F22C48A587D4C9C2EF91E31B4525F58693288665877C094EB61E5947159F505798D5571146554B23BA46574ABF51E4F7B6845C1B63EA79C865118FC0F8B295B5818E166084B6C2F159E53866864952A23109258BB03B2E6F77C50812BC8B6EFB658D6030C582450377931B1E6797EBA4AE064B1D24D466750DAB92100E50B0F343B6DB8064E31978C054693E81E558277A594714A31D65452C841A9836AB636162B548B1B2BBE185C7F3A596CD6624AC5D51D29405E66C48C519A65779C7A3990953756057A4AA89D7D447723AADA9995FDEDBD7D0B2D66CC2D1A419CA14506F71E29D2F3F2C7AC7D0B2DB6EAF56B558745E6A045982194B147FBA7D0524F4B034C529EF95E056B149C5A33E765C530FF7BE3782B78C7C37A0C90D9ED14F47EFA9EF94F61A5E93B356565E588FB3F54090A22F15858072F495110029938F01CFFF4BA2E57C268B4F76E790120FF0C9209CA808FA2BC794FAEF4C8E9D1D87ECB77D6D57E3D46A9C6A26F472AFAE561AAA21939933467E93A03C7596C27E4D3CD98AE55EC82D0C745017C76908F03CB496B5412199065B865C3AAF3D45EB7DDBAE49A54C5B106FB7B8C64AB327524F415DDC5B2E6151D54D52ECE0FBAC01A095ED64525C492363EABADB49A9E0B491FE6C4E85FCF7167D1ADB91D224296178C68D9211EA64DB2435B7995C1A0A041703BC0EB8F60E0DC9088861635D2658941F0A3EF5C76A886E6F818768097825B99DA214D2D5D93FDDF7A54B9092956EC54072D9B346CC2A7966DB58959F83069A84FE4E1210E2D8D5A4FD0D3CDCB49F7F5F5FD56CEA7F91F0DB39D289B3D2A7C9DF7D9A3673C7B465EF5C2C0F2BF93D655E6DF59F9B8259E4472F24E7B91AA8724CFDE255AF87D535BCBC49059C16492DED847D0D0E75EBB332235A48BED356837DE751179C2236937C6B23E5CF575AD040DE4F45FF461BEDB70C8EEA8FC6446D0378C16C8F248AF0C5A000F223181C16AD57F6600177BFFBACBF1DC394BF17573426DE4AC8A93D8652E1BDB6F96D04DE6849C7D427BF2DB889CA922C784EB83811AD6ECA4AAB867549A90212C667B984E4043F5BF9FC0ECD2D482B0A86252301DFFF617EB21F6AFCC7815554E2BEBDB98D076D3D557610813913C3E339D22C268CF8E68AD2879BCFF0D428F1B6E12E8CF48481DBA98C14B3526B6FAE5F65CE256E7C13A0ECCC59B818D29EC69F71EA40109B20350D7EEA50574BD27E9B5E98924AFFAA1BC434857DAA8071FA8A79A3896EE3AD53DB75AFAF922E9409E1AF179B9A1962D32ACCC7E0D459DA86CA10723261896F1A24520BA2828C0E8B245AE429BFD658B12347D5DB6A84921BB9F02B33812FDD3BE7738943D6A03E58289909FD1B787D13ACC2A13193750C99F03664294E96B565793980089BEB2A05318678470B02EEBC65D1453A85FF660DC76273775DA16E51324B7DEC65086DCE477524FA4694165FA411ACA09A813B92366485B14F6DB514C998D974B2B8175904CC2FB0A2A7DBE99DB753164B7979D732B4416430479EEE310551D7FB421FE4E60A5B583B9765EFD7CF6F9B81925621F3AA5F2149CDBF296E9EA0B7ECB16D5F3BCD19287FD19FA7EACD4DA00795E89B51899F2244C96DF8C464FF2CC651F4640A3DF126B69385E8D499940CCD8B8EA0A83ABC658ECEF293A3F1C4511AD6788E8DBF7F47970867BB452D90892469C8FF0515A0FCE70127A6D85F23EEBA21EF6FAC5198EC559362D90C81A8C6BE97E0E6751530EE853DF3E12FBACF1D6411561D2DE66EAED3FDA373AE75826D1F0943E3277E529530786E075CB54C41F0CC36918BC22DD44F2B05CD305E7C80E6D86A5FAEDD01818D120C2E7E3288CD63CE25297CC439889BB81E037FD9F1686991021B5BEADD54E988195335D238A70975FFA194166A1E4100A32EF8C3F18D16D403C648CF9FCCA41A44568AC75638CAB7A94A51B3E1AD985572394C3F0B1A85CDFCE1A691C15D6D715BD3E0B2568CD8B310899B707EBAE890D61279CC3472917AB612A3401E52E63C880734EAFDF31F806F8E8CA58FFB83EBF053E010C325FB073EB7215110DF912190CBF6CDC17B22B7A5BD7E598705E9FB0A261906805628C38BF30ACFC4E8365C66B0A610853D1A26F54965986A647B37BAEC211291EC58BF76C50FCC141C228D37CCCECE5054FDBF2EE0DCB1029B80C2ECD6FA420658D6D409D87407053BBD57D814D491C7D4EA29F6512AFE874944296F11B55ADA895660053540DF069AA1A90AFCB249B8D1741F30019AFC01865795F13A5298A6BEF372349522BF8C93E9650F047533DE73FC1BFC96697F9F77EE60FCCADCED18FE539127413D0E1E4E03B7C1F3C6656E5B2BC8A21672AEFBC6A71FCD687252FCFC360F0CAE0139B8786302913922B649B2894F59FDB1748AAB1F3D68FCB92F296E04DFD40959C164932EFBD2476020231F9E903317A41C0792332B979302A743DCBEBDDAB34ACCD789725F4CBA238229116B84435E8BCCABE3AB969945FF77E9AA80583E11A68A473D7FD2953507BD5B28472FCCE2178DE3F772C2CBDE8D3A6EBFCF3FBB3A7CA4B438D697B28A9728BF637D9F6F0E250B1218A1B8D8FE715143693F282879EB45C12F83F3248B10120270000'), 'escape'), ',') AS kv)
SELECT c.id, c1, c2, c3, c4, result FROM poker24.cards c LEFT JOIN a a ON a.i =
(CASE c1 WHEN 1 THEN 2 WHEN 2 THEN 3 WHEN 3 THEN 5 WHEN 4 THEN 7 WHEN 5 THEN 11 WHEN 6 THEN 13 WHEN 7 THEN 17 WHEN 8 THEN 19 WHEN 9 THEN 23 WHEN 10 THEN 29 END
*CASE c2 WHEN 1 THEN 2 WHEN 2 THEN 3 WHEN 3 THEN 5 WHEN 4 THEN 7 WHEN 5 THEN 11 WHEN 6 THEN 13 WHEN 7 THEN 17 WHEN 8 THEN 19 WHEN 9 THEN 23 WHEN 10 THEN 29 END
*CASE c3 WHEN 1 THEN 2 WHEN 2 THEN 3 WHEN 3 THEN 5 WHEN 4 THEN 7 WHEN 5 THEN 11 WHEN 6 THEN 13 WHEN 7 THEN 17 WHEN 8 THEN 19 WHEN 9 THEN 23 WHEN 10 THEN 29 END
*CASE c4 WHEN 1 THEN 2 WHEN 2 THEN 3 WHEN 3 THEN 5 WHEN 4 THEN 7 WHEN 5 THEN 11 WHEN 6 THEN 13 WHEN 7 THEN 17 WHEN 8 THEN 19 WHEN 9 THEN 23 WHEN 10 THEN 29 END);
结果
在本地 M1 Macbook Pro 上单核执行时间大约是 0.58 秒,比第一名 0.67s 稍微快一点。
当然,因为 NineData 上面那个 PostgreSQL 没有 gzip 扩展,所以我也没用他们的平台(4c 32G)去提交成绩。
Merge Right Join (cost=118104.17..768224.17 rows=5000000 width=68) (actual time=457.485..555.265 rows=1000000 loops=1)
Merge Cond: (((split_part(kv.kv, ':'::text, 1))::integer) = ((((CASE c.c1 WHEN '1'::double precision THEN 2 WHEN '2'::double precision THEN 3 WHEN '3'::double precision THEN 5 WHEN '4'::double precision THEN 7 WHEN '5'::double precision THEN 11 WHEN '6'::double precision THEN 13 WHEN '7'::double precision THEN 17 WHEN '8'::double precision THEN 19 WHEN '9'::double precision THEN 23 WHEN '10'::double precision THEN 29 ELSE NULL::integer END * CASE c.c2 WHEN '1'::double precision THEN 2 WHEN '2'::double precision THEN 3 WHEN '3'::double precision THEN 5 WHEN '4'::double precision THEN 7 WHEN '5'::double precision THEN 11 WHEN '6'::double precision THEN 13 WHEN '7'::double precision THEN 17 WHEN '8'::double precision THEN 19 WHEN '9'::double precision THEN 23 WHEN '10'::double precision THEN 29 ELSE NULL::integer END) * CASE c.c3 WHEN '1'::double precision THEN 2 WHEN '2'::double precision THEN 3 WHEN '3'::double precision THEN 5 WHEN '4'::double precision THEN 7 WHEN '5'::double precision THEN 11 WHEN '6'::double precision THEN 13 WHEN '7'::double precision THEN 17 WHEN '8'::double precision THEN 19 WHEN '9'::double precision THEN 23 WHEN '10'::double precision THEN 29 ELSE NULL::integer END) * CASE c.c4 WHEN '1'::double precision THEN 2 WHEN '2'::double precision THEN 3 WHEN '3'::double precision THEN 5 WHEN '4'::double precision THEN 7 WHEN '5'::double precision THEN 11 WHEN '6'::double precision THEN 13 WHEN '7'::double precision THEN 17 WHEN '8'::double precision THEN 19 WHEN '9'::double precision THEN 23 WHEN '10'::double precision THEN 29 ELSE NULL::integer END)))
-> Sort (cost=62.33..64.83 rows=1000 width=64) (actual time=0.851..0.872 rows=566 loops=1)
Sort Key: ((split_part(kv.kv, ':'::text, 1))::integer)
Sort Method: quicksort Memory: 59kB
-> Function Scan on regexp_split_to_table kv (cost=0.00..12.50 rows=1000 width=64) (actual time=0.491..0.654 rows=566 loops=1)
-> Sort (cost=118041.84..120541.84 rows=1000000 width=36) (actual time=456.629..494.693 rows=1000000 loops=1)
Sort Key: ((((CASE c.c1 WHEN '1'::double precision THEN 2 WHEN '2'::double precision THEN 3 WHEN '3'::double precision THEN 5 WHEN '4'::double precision THEN 7 WHEN '5'::double precision THEN 11 WHEN '6'::double precision THEN 13 WHEN '7'::double precision THEN 17 WHEN '8'::double precision THEN 19 WHEN '9'::double precision THEN 23 WHEN '10'::double precision THEN 29 ELSE NULL::integer END * CASE c.c2 WHEN '1'::double precision THEN 2 WHEN '2'::double precision THEN 3 WHEN '3'::double precision THEN 5 WHEN '4'::double precision THEN 7 WHEN '5'::double precision THEN 11 WHEN '6'::double precision THEN 13 WHEN '7'::double precision THEN 17 WHEN '8'::double precision THEN 19 WHEN '9'::double precision THEN 23 WHEN '10'::double precision THEN 29 ELSE NULL::integer END) * CASE c.c3 WHEN '1'::double precision THEN 2 WHEN '2'::double precision THEN 3 WHEN '3'::double precision THEN 5 WHEN '4'::double precision THEN 7 WHEN '5'::double precision THEN 11 WHEN '6'::double precision THEN 13 WHEN '7'::double precision THEN 17 WHEN '8'::double precision THEN 19 WHEN '9'::double precision THEN 23 WHEN '10'::double precision THEN 29 ELSE NULL::integer END) * CASE c.c4 WHEN '1'::double precision THEN 2 WHEN '2'::double precision THEN 3 WHEN '3'::double precision THEN 5 WHEN '4'::double precision THEN 7 WHEN '5'::double precision THEN 11 WHEN '6'::double precision THEN 13 WHEN '7'::double precision THEN 17 WHEN '8'::double precision THEN 19 WHEN '9'::double precision THEN 23 WHEN '10'::double precision THEN 29 ELSE NULL::integer END))
Sort Method: external sort Disk: 56760kB
-> Seq Scan on cards c (cost=0.00..18384.00 rows=1000000 width=36) (actual time=0.028..213.760 rows=1000000 loops=1)
Planning Time: 0.363 ms
Execution Time: 581.782 ms
以上就是使用 PostgreSQL 一条SQL计算扑克牌24点的解法。
其实,如果在用上并行优化也许还能再快点,然后 PostgreSQL 还有一种其他数据库做不到的解法。那就是直接把这个查表动作封装成一个扩展,然后用C语言直接暴露存储过程给 SQL 调用。这样就能把这个计算过程优化到极致了。当然,这种我们也懒得折腾了。
1.6 - DB-Engine 数据库热度趋势分析
概览
GitHub 仓库地址:https://github.com/Vonng/pigsty-app/tree/master/db
在线Demo地址:https://demo.pigsty.cc/d/db-engine
1.7 - StackOverflow 全球开发者调研
概览
GitHub 仓库地址:https://github.com/Vonng/pigsty-app/tree/master/db
在线Demo地址:https://demo.pigsty.cc/d/sf-survey
2 - Docker 软件应用工具
您可以使用Docker,快速部署启动软件应用,在容器中,您可以直接使用连接串访问部署于宿主机上的PostgreSQL/Redis数据库。
- PgAdmin4 : 一个用于管理PostgreSQL数据库实例的GUI工具
- PGWeb:一个自动根据PG数据库模式生成后端API服务的工具
- PostgREST:一个自动根据PG数据库模式生成后端API服务的工具
- ByteBase : 一个用于进行PostgreSQL模式变更的GUI工具
- Jupyter Lab:一个开箱即用的数据分析与处理Python实验环境
您也可以用Docker拉起一些开箱即用的开源软件服务:
您也可以使用Docker执行一些随用随抛的命令工具,例如:
您也可以用Docker拉起一些开箱即用的开源软件服务:
- Gitlab:开源代码托管平台。
- Habour:开源镜像仓库
- Jira:开源项目管理平台。
- Confluence:开源知识托管平台。
- Odoo:开源ERP
- Mastodon:基于PG的社交网络
- Discourse:基于PG与Redis的开源论坛
PGADMIN
PgAdmin4 是一个实用的PostgreSQL管理工具,执行以下命令可在管理节点拉起 pgadmin服务:
cd ~/pigsty/app/pgadmin ; docker-compose up -d
默认分配 8885 端口,使用域名: http://adm.pigsty 访问, Demo:http://adm.pigsty.cc。
默认用户名:admin@pigsty.cc
,密码:pigsty
。
PGWeb客户端工具
PGWeb是一款基于浏览器的PG客户端工具,使用以下命令,在元节点上拉起PGWEB服务,默认为主机8886
端口。可使用域名: http://cli.pigsty 访问,公开Demo:http://cli.pigsty.cc。
# docker stop pgweb; docker rm pgweb
docker run --init --name pgweb --restart always --detach --publish 8886:8081 sosedoff/pgweb
用户需要自行填写数据库连接串,例如默认CMDB的连接串:
postgres://dbuser_dba:DBUser.DBA@10.10.10.10:5432/meta?sslmode=disable
ByteBase
ByteBase是一个进行数据库模式变更的工具,以下命令将在元节点 8887 端口启动一个ByteBase。
mkdir -p /data/bytebase/data;
docker run --init --name bytebase --restart always --detach --publish 8887:8887 --volume /data/bytebase/data:/var/opt/bytebase \
bytebase/bytebase:1.0.4 --data /var/opt/bytebase --host http://ddl.pigsty --port 8887
访问 http://10.10.10.10:8887/ 或 http://ddl.pigsty 即可使用 ByteBase,您需要依次创建项目、环境、实例、数据库,即可开始进行模式变更。 公开Demo地址: http://ddl.pigsty.cc
PostgREST
PostgREST是一个自动根据 PostgreSQL 数据库模式生成 REST API的二进制组件。
例如,以下命令将使用docker拉起 postgrest (本地 8884 端口,使用默认管理员用户,暴露Pigsty CMDB模式)
docker run --init --name postgrest --restart always --detach --publish 8884:8081 postgrest/postgrest
访问 http://10.10.10.10:8884 会展示所有自动生成API的定义,并自动使用 Swagger Editor 暴露API文档。
如果您想要进行增删改查,设计更精细的权限控制,请参考 Tutorial 1 - The Golden Key,生成一个签名JWT。
数据分析环境:Jupyter
Jupyter Lab 是一站式数据分析环境,下列命令将在 8887 端口启动一个Jupyter Server.
docker run -it --restart always --detach --name jupyter -p 8888:8888 -v "${PWD}":/tmp/notebook jupyter/scipy-notebook
docker logs jupyter # 打印日志,获取登陆的Token
访问 http://10.10.10.10:8888/ 即可使用 JupyterLab,(需要填入自动生成的Token)。
您也可以使用 infra-jupyter.yml 在管理节点裸机上启用Jupyter Notebook。
样例:数据库模式报表SchemaSPY
使用以下docker
生成数据库模式报表,以CMDB为例:
docker run -v /www/schema/pg-meta/meta/pigsty:/output andrewjones/schemaspy-postgres:latest -host 10.10.10.10 -port 5432 -u dbuser_dba -p DBUser.DBA -db meta -s pigsty
然后访问 http://pigsty/schema/pg-meta/meta/pigsty 即可访问Schema报表
样例:开源代码仓库:Gitlab
请参考Gitlab Docker部署文档 完成Docker部署。
export GITLAB_HOME=/data/gitlab
sudo docker run --detach \
--hostname gitlab.example.com \
--publish 443:443 --publish 80:80 --publish 23:22 \
--name gitlab \
--restart always \
--volume $GITLAB_HOME/config:/etc/gitlab \
--volume $GITLAB_HOME/logs:/var/log/gitlab \
--volume $GITLAB_HOME/data:/var/opt/gitlab \
--shm-size 256m \
gitlab/gitlab-ee:latest
sudo docker exec -it gitlab grep 'Password:' /etc/gitlab/initial_root_password
样例:开源技术论坛:Discourse
搭建开源论坛Discourse,需要调整配置 app.yml
,重点是SMTP部分的配置
Discourse配置样例
templates:
- "templates/web.china.template.yml"
- "templates/postgres.template.yml"
- "templates/redis.template.yml"
- "templates/web.template.yml"
- "templates/web.ratelimited.template.yml"
## Uncomment these two lines if you wish to add Lets Encrypt (https)
# - "templates/web.ssl.template.yml"
# - "templates/web.letsencrypt.ssl.template.yml"
expose:
- "80:80" # http
- "443:443" # https
params:
db_default_text_search_config: "pg_catalog.english"
db_shared_buffers: "768MB"
env:
LC_ALL: en_US.UTF-8
LANG: en_US.UTF-8
LANGUAGE: en_US.UTF-8
EMBER_CLI_PROD_ASSETS: 1
UNICORN_WORKERS: 4
DISCOURSE_HOSTNAME: forum.pigsty
DISCOURSE_DEVELOPER_EMAILS: 'fengruohang@outlook.com,rh@vonng.com'
DISCOURSE_SMTP_ENABLE_START_TLS: false
DISCOURSE_SMTP_AUTHENTICATION: login
DISCOURSE_SMTP_OPENSSL_VERIFY_MODE: none
DISCOURSE_SMTP_ADDRESS: smtpdm.server.address
DISCOURSE_SMTP_PORT: 80
DISCOURSE_SMTP_USER_NAME: no_reply@mail.pigsty.cc
DISCOURSE_SMTP_PASSWORD: "<password>"
DISCOURSE_SMTP_DOMAIN: mail.pigsty.cc
volumes:
- volume:
host: /var/discourse/shared/standalone
guest: /shared
- volume:
host: /var/discourse/shared/standalone/log/var-log
guest: /var/log
hooks:
after_code:
- exec:
cd: $home/plugins
cmd:
- git clone https://github.com/discourse/docker_manager.git
run:
- exec: echo "Beginning of custom commands"
# - exec: rails r "SiteSetting.notification_email='no_reply@mail.pigsty.cc'"
- exec: echo "End of custom commands"
然后,执行以下命令,拉起Discourse即可。
./launcher rebuild app
2.1 - 使用PGAdmin4进行数据库管理
公开Demo地址:http://adm.pigsty.cc
默认用户名与密码: admin@pigsty.cc
/ pigsty
太长;不看
cd ~/pigsty/app/pgadmin # 进入应用目录
make up # 拉起pgadmin容器
make conf view # 加载Pigsty服务器列表文件至Pgadmin容器内并加载
Pigsty的Pgadmin应用模板默认使用8885端口,您可以通过以下地址访问:
http://adm.pigsty 或 http://10.10.10.10:8885
默认用户名与密码: admin@pigsty.cc
/ pigsty
make up # pull up pgadmin with docker-compose
make run # launch pgadmin with docker
make view # print pgadmin access point
make log # tail -f pgadmin logs
make info # introspect pgadmin with jq
make stop # stop pgadmin container
make clean # remove pgadmin container
make conf # provision pgadmin with pigsty pg servers list
make dump # dump servers.json from pgadmin container
make pull # pull latest pgadmin image
make rmi # remove pgadmin image
make save # save pgadmin image to /tmp/pgadmin.tgz
make load # load pgadmin image from /tmp
2.2 - 使用Gitea搭建您自己的代码托管服务
公开Demo地址:http://git.pigsty.cc
太长;不看
cd ~/pigsty/app/gitea; make up
在本例中,Gitea 默认使用 8889 端口,您可以访问以下位置:
http://git.pigsty 或 http://10.10.10.10:8889
make up # pull up gitea with docker-compose in minimal mode
make run # launch gitea with docker , local data dir and external PostgreSQL
make view # print gitea access point
make log # tail -f gitea logs
make info # introspect gitea with jq
make stop # stop gitea container
make clean # remove gitea container
make pull # pull latest gitea image
make rmi # remove gitea image
make save # save gitea image to /tmp/gitea.tgz
make load # load gitea image from /tmp
使用外部的PostgreSQL
Pigsty默认使用容器内的 Sqlite 作为元数据存储,您可以让 Gitea 通过连接串环境变量使用外部的PostgreSQL
# postgres://dbuser_gitea:DBUser.gitea@10.10.10.10:5432/gitea
db: { name: gitea, owner: dbuser_gitea, comment: gitea primary database }
user: { name: dbuser_gitea , password: DBUser.gitea, roles: [ dbrole_admin ] }
2.3 - 使用Wiki.js搭建百科网站
公开Demo地址:http://wiki.pigsty.cc
太长;不看
cd app/wiki ; docker-compose up -d
准备数据库
# postgres://dbuser_wiki:DBUser.Wiki@10.10.10.10:5432/wiki
- { name: wiki, owner: dbuser_wiki, revokeconn: true , comment: wiki the api gateway database }
- { name: dbuser_wiki, password: DBUser.Wiki , pgbouncer: true , roles: [ dbrole_admin ] }
bin/createuser pg-meta dbuser_wiki
bin/createdb pg-meta wiki
容器配置
version: "3"
services:
wiki:
container_name: wiki
image: requarks/wiki:2
environment:
DB_TYPE: postgres
DB_HOST: 10.10.10.10
DB_PORT: 5432
DB_USER: dbuser_wiki
DB_PASS: DBUser.Wiki
DB_NAME: wiki
restart: unless-stopped
ports:
- "9002:3000"
Access
- Default Port for wiki: 9002
# add to nginx_upstream
- { name: wiki , domain: wiki.pigsty.cc , endpoint: "127.0.0.1:9002" }
./infra.yml -t nginx_config
ansible all -b -a 'nginx -s reload'
2.4 - 使用Minio存储本地对象与备份
公开Demo地址:http://sss.pigsty.cc
默认用户名: admin
/ pigsty.minio
太长;不看
Launch minio (s3) service on 9000 & 9001
cd ~/pigsty/app/minio ; docker-compose up -d
docker run -p 9000:9000 -p 9001:9001 \
-e "MINIO_ROOT_USER=admin" \
-e "MINIO_ROOT_PASSWORD=pigsty.minio" \
minio/minio server /data --console-address ":9001"
visit http://10.10.10.10:9000 with user admin
and password pigsty.minio
make up # pull up minio with docker-compose
make run # launch minio with docker
make view # print minio access point
make log # tail -f minio logs
make info # introspect minio with jq
make stop # stop minio container
make clean # remove minio container
make pull # pull latest minio image
make rmi # remove minio image
make save # save minio image to /tmp/minio.tgz
make load # load minio image from /tmp
2.5 - 使用ByteBase对PG模式做版本控制
公开Demo地址:http://ddl.pigsty.cc
默认用户名与密码: admin
/ pigsty
Bytebase概览
Schema Migrator for PostgreSQL
cd app/bytebase; make up
Visit http://ddl.pigsty or http://10.10.10.10:8887
make up # pull up bytebase with docker-compose in minimal mode
make run # launch bytebase with docker , local data dir and external PostgreSQL
make view # print bytebase access point
make log # tail -f bytebase logs
make info # introspect bytebase with jq
make stop # stop bytebase container
make clean # remove bytebase container
make pull # pull latest bytebase image
make rmi # remove bytebase image
make save # save bytebase image to /tmp/bytebase.tgz
make load # load bytebase image from /tmp
使用外部的PostgreSQL
Bytebase use its internal PostgreSQL database by default, You can use external PostgreSQL for higher durability.
# postgres://dbuser_bytebase:DBUser.Bytebase@10.10.10.10:5432/bytebase
db: { name: bytebase, owner: dbuser_bytebase, comment: bytebase primary database }
user: { name: dbuser_bytebase , password: DBUser.Bytebase, roles: [ dbrole_admin ] }
if you wish to user an external PostgreSQL, drop monitor extensions and views & pg_repack
DROP SCHEMA monitor CASCADE;
DROP EXTENSION pg_repack;
After bytebase initialized, you can create them back with /pg/tmp/pg-init-template.sql
psql bytebase < /pg/tmp/pg-init-template.sql
2.6 - 使用PGWeb从网页浏览PostgreSQL数据
公开Demo地址:http://cli.pigsty.cc
使用Docker Compose拉起PGWEB容器:
cd ~/pigsty/app/pgweb ; docker-compose up -d
接下来,访问您本机的 8886 端口,即可看到 PGWEB 的UI界面: http://10.10.10.10:8886
您可以尝试使用下面的URL连接串,通过 PGWEB 连接至数据库实例并进行探索。
postgres://dbuser_meta:DBUser.Meta@10.10.10.10:5432/meta?sslmode=disable
postgres://test:test@10.10.10.11:5432/test?sslmode=disable
快捷方式
make up # pull up pgweb with docker-compose
make run # launch pgweb with docker
make view # print pgweb access point
make log # tail -f pgweb logs
make info # introspect pgweb with jq
make stop # stop pgweb container
make clean # remove pgweb container
make pull # pull latest pgweb image
make rmi # remove pgweb image
make save # save pgweb image to /tmp/pgweb.tgz
make load # load pgweb image from /tmp
2.7 - 使用PostgREST自动生成RESTful API
This is an example of creating pigsty cmdb API with PostgREST
cd ~/pigsty/app/postgrest ; docker-compose up -d
http://10.10.10.10:8884 is the default endpoint for PostgREST
http://10.10.10.10:8883 is the default api docs for PostgREST
make up # pull up postgrest with docker-compose
make run # launch postgrest with docker
make ui # run swagger ui container
make view # print postgrest access point
make log # tail -f postgrest logs
make info # introspect postgrest with jq
make stop # stop postgrest container
make clean # remove postgrest container
make rmui # remove swagger ui container
make pull # pull latest postgrest image
make rmi # remove postgrest image
make save # save postgrest image to /tmp/postgrest.tgz
make load # load postgrest image from /tmp
Swagger UI
Launch a swagger OpenAPI UI and visualize PostgREST API on 8883 with:
docker run --init --name postgrest --name swagger -p 8883:8080 -e API_URL=http://10.10.10.10:8884 swaggerapi/swagger-ui
# docker run -d -e API_URL=http://10.10.10.10:8884 -p 8883:8080 swaggerapi/swagger-editor # swagger editor
Check http://10.10.10.10:8883/
2.8 - KONG API Gateway
TL;DR
cd app/kong ; docker-compose up -d
make up # pull up kong with docker-compose
make ui # run swagger ui container
make log # tail -f kong logs
make info # introspect kong with jq
make stop # stop kong container
make clean # remove kong container
make rmui # remove swagger ui container
make pull # pull latest kong image
make rmi # remove kong image
make save # save kong image to /tmp/kong.tgz
make load # load kong image from /tmp
Scripts
- Default Port: 8000
- Default SSL Port: 8443
- Default Admin Port: 8001
- Default Postgres Database:
postgres://dbuser_kong:DBUser.Kong@10.10.10.10:5432/kong
# postgres://dbuser_kong:DBUser.Kong@10.10.10.10:5432/kong
- { name: kong, owner: dbuser_kong, revokeconn: true , comment: kong the api gateway database }
- { name: dbuser_kong, password: DBUser.Kong , pgbouncer: true , roles: [ dbrole_admin ] }
3 - 使用JupyterLab进行数据分析
Jupyter Lab 是基于 IPython Notebook 的完整数据科学研发环境,可用于数据分析与可视化。
因为JupyterLab提供了Web Terminal功能,因此在默认安装中不启用,需要主动使用 infra-jupyter.yml
在元节点上进行部署。
太长不看
./infra-jupyter.yml # 在管理节点上安装 Jupyter Lab,使用8888端口,OS用户jupyter,默认密码 pigsty
./infra-jupyter.yml -e jupyter_domain=lab.pigsty.cc # 使用另一个域名(默认为lab.pigsty)
./infra-jupyter.yml -e jupyter_port=8887 # 使用另一个端口(默认为8888)
./infra-jupyter.yml -e jupyter_username=osuser_jupyter jupyter_password=pigsty2 # 使用不同的操作系统用户与密码
Jupyter配置
Name | Type | Level | Comment |
---|---|---|---|
jupyter_port |
integer | G | Jupyter端口 |
jupyter_domain |
string | G | Jupyter端口 |
jupyter_username |
string | G | Jupyter使用的操作系统用户 |
jupyter_password |
string | G | Jupyter Lab的密码 |
默认值
jupyter_username: jupyter # os user name, special names: default|root (dangerous!)
jupyter_password: pigsty # default password for jupyter lab (important!)
jupyter_port: 8888 # default port for jupyter lab
jupyter_domain: lab.pigsty # domain name used to distinguish jupyter
jupyter_username
Jupyter使用的操作系统用户, 类型:bool
,层级:G,默认值为:"jupyter"
其他用户名亦同理,但特殊用户名default
会使用当前执行安装的用户(通常为管理员)运行 Jupyter Lab,这会更方便,但也更危险。
jupyter_password
Jupyter Lab的密码, 类型:bool
,层级:G,默认值为:"pigsty"
如果启用Jupyter,强烈建议修改此密码。加盐混淆的密码默认会写入~jupyter/.jupyter/jupyter_server_config.json
。
jupyter_port
Jupyter监听端口, 类型:int
,层级:G,默认值为:8888
。
启用JupyterLab时,Pigsty会使用jupyter_username
参数指定的用户运行本地Notebook服务器。
此外,需要确保配置node_packages_meta_pip
参数包含默认值 'jupyterlab'
。
Jupyter Lab可以从Pigsty首页导航进入,或通过默认域名 lab.pigsty
访问,默认监听于8888端口。
jupyter_domain
Jupyter域名, 类型:string
,层级:G,默认值为:lab.pigsty
。
该域名会被写入 /etc/nginx/conf.d/jupyter.conf
中,作为Jupyter服务的监听域名。
Jupyter剧本
infra-jupyter
infra-jupyter.yml
剧本用于在元节点上加装 Jupyter Lab服务
Jupyter Lab 是非常实用的Python数据分析环境,但自带Web Shell,风险较大,需要使用专用剧本显式安装。
使用说明:参照 Jupyter配置 中的说明调整配置清单,然后执行此剧本即可。
如果您在生产环境中启用了Jupyter,请务必修改Jupyter的密码
在Jupyter中访问PostgreSQL数据库
您可以直接使用 psycopg2
驱动访问 PostgreSQL 数据库
import psycopg2
conn = psycopg2.connect('postgres://dbuser_meta:DBUser.Meta@:5432/meta')
cursor = conn.cursor()
cursor.execute("""SELECT date, new_cases FROM covid.country_history WHERE country_code = 'CN';""")
data = cursor.fetchall()
4 - 使用 PostgreSQL 作为 Grafana 后端数据库
您可以使用 postgres 作为Grafana后端使用的数据库。
这是了解Pigsty部署系统使用方式的好机会,完成此教程,您会了解:
- 如何创建新数据库集群
- 如何在已有数据库集群中创建新业务用户
- 如何在已有数据库集群中创建新业务数据库
- 如何访问Pigsty所创建的数据库
- 如何管理Grafana中的监控面板
- 如何管理Grafana中的PostgreSQL数据源
- 如何一步到位完成Grafana数据库升级
太长不看
vi pigsty.yml # 取消注释DB/User定义:dbuser_grafana grafana
bin/createuser pg-meta dbuser_grafana
bin/createdb pg-meta grafana
psql postgres://dbuser_grafana:DBUser.Grafana@meta:5436/grafana -c \
'CREATE TABLE t(); DROP TABLE t;' # 检查连接串可用性
vi /etc/grafana/grafana.ini # 修改 [database] type url
systemctl restart grafana-server
创建数据库集群
我们可以在pg-meta
上定义一个新的数据库grafana
,
也可以在新的机器节点上创建一个专用于Grafana的数据库集群:pg-grafana
定义集群
如果需要创建新的专用数据库集群pg-grafana
,部署在10.10.10.11
,10.10.10.12
两台机器上,可以使用以下配置文件:
pg-grafana:
hosts:
10.10.10.11: {pg_seq: 1, pg_role: primary}
10.10.10.12: {pg_seq: 2, pg_role: replica}
vars:
pg_cluster: pg-grafana
pg_databases:
- name: grafana
owner: dbuser_grafana
revokeconn: true
comment: grafana primary database
pg_users:
- name: dbuser_grafana
password: DBUser.Grafana
pgbouncer: true
roles: [dbrole_admin]
comment: admin user for grafana database
创建集群
使用以下命令完成数据库集群pg-grafana
的创建:pgsql.yml
。
bin/createpg pg-grafana # 初始化pg-grafana集群
该命令实际上调用了Ansible Playbook pgsql.yml
创建数据库集群。
./pgsql.yml -l pg-grafana # 实际执行的等效Ansible剧本命令
定义在 pg_users
与 pg_databases
中的业务用户与业务数据库会在集群初始化时自动创建,因此使用该配置时,集群创建完毕后,(在没有DNS支持的情况下)您可以使用以下连接串访问数据库(任一即可):
postgres://dbuser_grafana:DBUser.Grafana@10.10.10.11:5432/grafana # 主库直连
postgres://dbuser_grafana:DBUser.Grafana@10.10.10.11:5436/grafana # 直连default服务
postgres://dbuser_grafana:DBUser.Grafana@10.10.10.11:5433/grafana # 连接串读写服务
postgres://dbuser_grafana:DBUser.Grafana@10.10.10.12:5432/grafana # 主库直连
postgres://dbuser_grafana:DBUser.Grafana@10.10.10.12:5436/grafana # 直连default服务
postgres://dbuser_grafana:DBUser.Grafana@10.10.10.12:5433/grafana # 连接串读写服务
因为默认情况下Pigsty安装在单个元节点上,接下来的步骤我们会在已有的pg-meta
数据库集群上创建Grafana所需的用户与数据库,而并非使用这里创建的pg-grafana
集群。
创建Grafana业务用户
通常业务对象管理的惯例是:先创建用户,再创建数据库。
因为如果为数据库配置了owner
,数据库对相应的用户存在依赖。
定义用户
要在pg-meta
集群上创建用户dbuser_grafana
,首先将以下用户定义添加至pg-meta
的集群定义中:
添加位置:all.children.pg-meta.vars.pg_users
- name: dbuser_grafana
password: DBUser.Grafana
comment: admin user for grafana database
pgbouncer: true
roles: [ dbrole_admin ]
如果您在这里定义了不同的密码,请在后续步骤中将相应参数替换为新密码
创建用户
使用以下命令完成dbuser_grafana
用户的创建(任一均可)。
bin/createuser pg-meta dbuser_grafana # 在pg-meta集群上创建`dbuser_grafana`用户
实际上调用了Ansible Playbook pgsql-createuser.yml
创建用户
./pgsql-createuser.yml -l pg-meta -e pg_user=dbuser_grafana # Ansible
dbrole_admin
角色具有在数据库中执行DDL变更的权限,这正是Grafana所需要的。
创建Grafana业务数据库
定义数据库
创建业务数据库的方式与业务用户一致,首先在pg-meta
的集群定义中添加新数据库grafana
的定义。
添加位置:all.children.pg-meta.vars.pg_databases
- { name: grafana, owner: dbuser_grafana, revokeconn: true }
创建数据库
使用以下命令完成grafana
数据库的创建(任一均可)。
bin/createdb pg-meta grafana # 在`pg-meta`集群上创建`grafana`数据库
实际上调用了Ansible Playbook pgsql-createdb.yml
创建数据库
./pgsql-createdb.yml -l pg-meta -e pg_database=grafana # 实际执行的Ansible剧本
使用Grafana业务数据库
检查连接串可达性
postgres://dbuser_grafana:DBUser.Grafana@meta:5432/grafana # 直连
postgres://dbuser_grafana:DBUser.Grafana@meta:5436/grafana # default服务
postgres://dbuser_grafana:DBUser.Grafana@meta:5433/grafana # primary服务
这里,我们将使用通过负载均衡器直接访问主库的default服务访问数据库。
首先检查连接串是否可达,以及是否有权限执行DDL命令。
psql postgres://dbuser_grafana:DBUser.Grafana@meta:5436/grafana -c \
'CREATE TABLE t(); DROP TABLE t;'
直接修改Grafana配置
为了让Grafana使用 Postgres 数据源,您需要编辑 /etc/grafana/grafana.ini
,并修改配置项:
[database]
;type = sqlite3
;host = 127.0.0.1:3306
;name = grafana
;user = root
# If the password contains # or ; you have to wrap it with triple quotes. Ex """#password;"""
;password =
;url =
将默认的配置项修改为:
[database]
type = postgres
url = postgres://dbuser_grafana:DBUser.Grafana@meta/grafana
随后重启Grafana即可:
systemctl restart grafana-server
从监控系统中看到新增的 grafana
数据库已经开始有活动,则说明Grafana已经开始使用Postgres作为首要后端数据库了。但一个新的问题是,Grafana中原有的Dashboards与Datasources都消失了!这里需要重新导入监控面板与Postgres数据源
管理Grafana监控面板
您可以使用管理用户前往 Pigsty 目录下的files/ui
目录,执行grafana.py init
重新加载Pigsty监控面板。
cd ~/pigsty/files/ui
./grafana.py init # 使用当前目录下的Dashboards初始化Grafana监控面板
执行结果:
vagrant@meta:~/pigsty/files/ui
$ ./grafana.py init
Grafana API: admin:pigsty @ http://10.10.10.10:3000
init dashboard : home.json
init folder pgcat
init dashboard: pgcat / pgcat-table.json
init dashboard: pgcat / pgcat-bloat.json
init dashboard: pgcat / pgcat-query.json
init folder pgsql
init dashboard: pgsql / pgsql-replication.json
init dashboard: pgsql / pgsql-table.json
init dashboard: pgsql / pgsql-activity.json
init dashboard: pgsql / pgsql-cluster.json
init dashboard: pgsql / pgsql-node.json
init dashboard: pgsql / pgsql-database.json
init dashboard: pgsql / pgsql-xacts.json
init dashboard: pgsql / pgsql-overview.json
init dashboard: pgsql / pgsql-session.json
init dashboard: pgsql / pgsql-tables.json
init dashboard: pgsql / pgsql-instance.json
init dashboard: pgsql / pgsql-queries.json
init dashboard: pgsql / pgsql-alert.json
init dashboard: pgsql / pgsql-service.json
init dashboard: pgsql / pgsql-persist.json
init dashboard: pgsql / pgsql-proxy.json
init dashboard: pgsql / pgsql-query.json
init folder pglog
init dashboard: pglog / pglog-instance.json
init dashboard: pglog / pglog-analysis.json
init dashboard: pglog / pglog-session.json
该脚本会侦测当前的环境(安装时定义于~/pigsty
),获取Grafana的访问信息,并将监控面板中的URL连接占位符域名(*.pigsty
)替换为真实使用的域名。
export GRAFANA_ENDPOINT=http://10.10.10.10:3000
export GRAFANA_USERNAME=admin
export GRAFANA_PASSWORD=pigsty
export NGINX_UPSTREAM_YUMREPO=yum.pigsty
export NGINX_UPSTREAM_CONSUL=c.pigsty
export NGINX_UPSTREAM_PROMETHEUS=p.pigsty
export NGINX_UPSTREAM_ALERTMANAGER=a.pigsty
export NGINX_UPSTREAM_GRAFANA=g.pigsty
export NGINX_UPSTREAM_HAPROXY=h.pigsty
题外话,使用grafana.py clean
会清空目标监控面板,使用grafana.py load
会加载当前目录下所有监控面板,当Pigsty的监控面板发生变更,可以使用这两个命令升级所有的监控面板。
管理Postgres数据源
当使用 pgsql.yml
创建新PostgreSQL集群,或使用pgsql-createdb.yml
创建新业务数据库时,Pigsty会在Grafana中注册新的PostgreSQL数据源,您可以使用默认的监控用户通过Grafana直接访问目标数据库实例。应用pgcat
的绝大部分功能有赖于此。
要注册Postgres数据库,可以使用pgsql.yml
中的register_grafana
任务:
./pgsql.yml -t register_grafana # 重新注册当前环境中所有Postgres数据源
./pgsql.yml -t register_grafana -l pg-test # 重新注册 pg-test 集群中所有的数据库
一步到位更新Grafana
您可以直接通过修改Pigsty配置文件,更改Grafana使用的后端数据源,一步到位的完成切换Grafana后端数据库的工作。编辑pigsty.yml
中grafana_database
与grafana_pgurl
参数,将其修改为:
grafana_database: postgres
grafana_pgurl: postgres://dbuser_grafana:DBUser.Grafana@meta:5436/grafana
然后重新执行 infral.yml
中的grafana
任务,即可完成Grafana升级
./infra.yml -t grafana
5 - 使用 TimescaleDB + Promscale 存储 Prometheus 时序指标数据
虽然这并不是推荐的行为,但这是了解Pigsty部署系统使用方式的好机会。
注意,使用 Promscale 存储 Prometheus 指标占用的存储空间大约是 Prometheus 的 4 倍,但是可以使用 SQL 来查询分析 Prometheus 监控指标。
准备Postgres数据库
vi pigsty.yml # 取消注释DB/User定义:dbuser_prometheus prometheus
pg_databases: # define business users/roles on this cluster, array of user definition
- { name: prometheus, owner: dbuser_prometheus , revokeconn: true, comment: prometheus primary database }
pg_users: # define business users/roles on this cluster, array of user definition
- {name: dbuser_prometheus , password: DBUser.Prometheus ,pgbouncer: true , createrole: true, roles: [dbrole_admin], comment: admin user for prometheus database }
创建 Prometheus 业务数据库与业务用户。
bin/createuser pg-meta dbuser_prometheus
bin/createdb pg-meta prometheus
检查数据库可用性并创建扩展
psql postgres://dbuser_prometheus:DBUser.Prometheus@10.10.10.10:5432/prometheus -c 'CREATE EXTENSION timescaledb;'
配置Promscale
在元节点上执行以下命令安装 promscale
yum install -y promscale
如果默认软件包中没有,可以直接下载:
wget https://github.com/timescale/promscale/releases/download/0.6.1/promscale_0.6.1_Linux_x86_64.rpm
sudo rpm -ivh promscale_0.6.1_Linux_x86_64.rpm
编辑 promscale
的配置文件 /etc/sysconfig/promscale.conf
PROMSCALE_DB_HOST="127.0.0.1"
PROMSCALE_DB_NAME="prometheus"
PROMSCALE_DB_PASSWORD="DBUser.Prometheus"
PROMSCALE_DB_PORT="5432"
PROMSCALE_DB_SSL_MODE="disable"
PROMSCALE_DB_USER="dbuser_prometheus"
最后启动promscale,它会访问安装有 timescaledb
的数据库实例,并创建所需的schema
# launch
cat /usr/lib/systemd/system/promscale.service
systemctl start promscale && systemctl status promscale
配置Prometheus
Prometheus可以使用Remote Write/ Remote Read的方式,通过Promscale,使用Postgres作为远程存储。
编辑Prometheus配置文件:
vi /etc/prometheus/prometheus.yml
添加以下记录:
remote_write:
- url: "http://127.0.0.1:9201/write"
remote_read:
- url: "http://127.0.0.1:9201/read"
重启Prometheus后,监控数据即可放入Postgres中。
systemctl restart prometheus