This is the multi-page printable view of this section.
Click here to print.
Return to the regular view of this page.
Application
Docker compose template for software using PostgreSQL, and data visualization Apps
Lots of software using PostgreSQL. Pigsty has some docker compose template for some popular software.
You can launch stateless software with docker easily and using external HA PostgreSQL for higher availability & durability.
Docker is not installed by default, You can install docker with docker.yml
playbook, e.g.: ./docker.yml -l infra
Available software and their docker-compose template can be found in pigsty/app
PostgreSQL Administration
Use more advanced tools to manage PostgreSQL instances / clusters.
- PgAdmin4: A GUI tool for managing PostgreSQL instances.
- ByteBase: A GUI IaC tool for PostgreSQL schema migration.
- PGWeb: A tool automatically generates back-end API services based on PG database schema.
- SchemaSPY: Generates detailed visual reports of database schemas.
- Pgbadger: Generate PostgreSQL summary report from log samples.
Application Development
Scaffold your application with PostgreSQL and its ecosystem.
- Supabase: Supabase is an open source Firebase alternative based on PostgreSQL
- FerretDB: FerretDB, a truly open source MongoDB alternative in PostgreSQL.
- EdgeDB: EdgeDB, open source graph-like database based on PostgreSQL
- PostgREST: PostgREST, serve a RESTful API from any Postgres database automatically.
- Kong: Kong, a scalable, open source API Gateway with Redis/PostgreSQL/OpenResty
- DuckDB: DuckDB, in-process SQL olap DBMS that works well with PostgreSQL
Business Software
Launch open-source software with PostgreSQL at ease.
- Wiki.js: Wiki.js, the most powerful and extensible open source wiki software
- Gitea: Gitea, a painless self-hosted git service
- NocoDB: NocoDB, Open source AirTable alternative.
- Gitlab: open-source code hosting platform.
- Harbour: open-source mirror repo
- Jira: open-source project management platform.
- Confluence: open-source knowledge hosting platform.
- Odoo: open-source ERP
- Mastodon: PG-based social network
- Discourse: open-source forum based on PG and Redis
- Jupyter Lab: A battery-included Python lab environment for data analysis and processing.
- Grafana: use postgres as backend storage
- Promscale: use postgres/timescaledb as prometheus metrics storage
Visualization
Perform data visualization with PostgreSQL, Grafana & Echarts.
- isd: noaa weather data visualization: github.com/Vonng/isd, Demo
- pglog: PostgreSQL CSVLOG sample analyzer. Demo
- covid: Covid-19 data visualization
- dbeng: Database popularity visualization
- price: RDS, ECS price comparison
1 - Data Visualization Applet
使用 Pigsty Grafana & Echarts 工具箱进行数据分析与可视化
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 - Analyse CSVLOG Sample with the built-in PGLOG
Analyse CSVLOG Sample with the built-in PGLOG
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
接下来,您可以访问以下的连接,查看样例日志分析界面。
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 Station
Fetch, Parse, Analyze, and Visualize Integrated Surface Weather Station Dataset
Including 30000 meteorology station, daily, sub-hourly observation records, from 1900-2023. https://github.com/Vonng/isd
It is recommended to use with Pigsty, the battery-included PostgreSQL distribution with Grafana & echarts for visualization. It will setup everything for your with make all
;
Otherwise, you’ll have to provide your own PostgreSQL instance, and setup grafana dashboards manually.
Quick Start
Clone this repo
git clone https://github.com/Vonng/isd.git; cd isd;
Prepare a PostgreSQL Instance
Export PGURL
in your environment to specify the target postgres database:
# the default PGURL for pigsty's meta database, change that accordingly
export PGURL=postgres://dbuser_dba:[email protected]:5432/meta?sslmode=disable
psql "${PGURL}" -c 'SELECT 1' # check if connection is ok
then init database schema with:
make sql # setup postgres schema on target database
Get isd station metadata
The basic station metadata can be downloaded and loaded with:
make reload-station # equivalent to get-station + load-station
Fetch and load isd.daily
To load isd.daily
dataset, which is organized by yearly tarball files.
You can download the raw data from noaa and parse with isd parser
make get-parser # download parser binary from github, you can just build with: make build
make reload-daily # download and reload latest daily data and re-calculates monthly/yearly data
Load Parsed Stable CSV Data
Or just load the pre-parsed stable part from GitHub.
Which is well-formatted CSV that does not require an isd parser.
make get-stable # download stable isd.daily dataset from Github
make load-stable # load downloaded stable isd.daily dataset into database
More Data
There are two parts of isd datasets needs to be regularly updated: station metadata & isd.daily
of the latest year, you can reload them with:
make reload # reload-station + reload-daily
You can download and load isd.daily
in a specific year with:
bin/get-daily 2022 # get daily observation summary of a specific year (1900-2023)
bin/load-daily "${PGURL}" 2022 # load daily data of a specific year
You can also download and load isd.hourly
in a specific year with:
bin/get-hourly 2022 # get hourly observation record of a specific year (1900-2023)
bin/load-hourly "${PGURL}" 2022 # load hourly data of a specific year
Data
Dataset
There are four official datasets
Daily Dataset
- Tarball size 2.8GB (until 2023-06-24)
- Table size 24GB, Index size 6GB, Total size in PostgreSQL = 30GB
- If timescaledb compression is used, it will be compressed to around 4.5GB
Hourly dataset
- Tarball size 117GB
- Table size 1TB+ , Index size 600GB+
Schema
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
-- temperature & dew point
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 ℃
-- pressure
slp_mean NUMERIC(5, 1), -- sea level pressure (hPa)
stp_mean NUMERIC(5, 1), -- station pressure (hPa)
-- visible distance
vis_mean NUMERIC(6), -- visible distance (m)
-- wind speed
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)
-- precipitation / snow depth
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
-- record count
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 marks
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);
Parser
There are two parsers: isdd
and isdh
, which takes noaa original yearly tarball as input, generate CSV as output (which could be directly consumed by PostgreSQL COPY
command).
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
UI
ISD Overview
Show all stations on a world map.
ISD Country
Show all stations among a country.
ISD Station
Visualize station metadata and daily/monthly/yearly summary
ISD Station Dashboard
ISD Detail
Visualize hourly observation raw metrics.
ISD Station Dashboard
License
MIT License
1.3 - WHO COVID-19 Data Analysis
Pigsty built-in application which visualize WHO covid-19 data
The on-line demo:https://demo.pigsty.cc/d/covid-overview
Installation
在管理节点上进入应用目录,执行make
以完成安装。
make # 如果本地数据可用
make all # 完整安装,从WHO官网下载数据
makd reload # 重新下载并加载最新数据
其他一些子任务:
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
Dashboards
2 - Docker Compose Template
Software and tools that use PostgreSQL can be managed by the docker daemon
您可以使用Docker,快速部署启动软件应用,在容器中,您可以直接使用连接串访问部署于宿主机上的PostgreSQL/Redis数据库。
您也可以用Docker拉起一些开箱即用的开源软件服务:
您也可以使用Docker执行一些随用随抛的命令工具,例如:
您也可以用Docker拉起一些开箱即用的开源软件服务:
PGADMIN
PgAdmin4 是一个实用的PostgreSQL管理工具,执行以下命令可在管理节点拉起 pgadmin服务:
cd ~/pigsty/app/pgadmin ; docker-compose up -d
默认分配 8885 端口,使用域名: http://adm.pigsty 访问, Demo:http://adm.pigsty.cc。
默认用户名:[email protected]
,密码: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:[email protected]: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: '[email protected],[email protected]'
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: [email protected]
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='[email protected]'"
- exec: echo "End of custom commands"
然后,执行以下命令,拉起Discourse即可。
2.1 - 使用PGAdmin4进行数据库管理
使用Docker拉起PgAdmin4,并加载Pigsty服务器列表
公开Demo地址:http://adm.pigsty.cc
默认用户名与密码: [email protected]
/ 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
默认用户名与密码: [email protected]
/ 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搭建您自己的代码托管服务
使用Docker拉起Gitea,并使用Pigsty的PG作为外部的元数据库
公开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:[email protected]: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搭建百科网站
使用Docker拉起Wiki,并使用Pigsty的PG作为持久数据存储
公开Demo地址:http://wiki.pigsty.cc
太长;不看
cd app/wiki ; docker-compose up -d
准备数据库
# postgres://dbuser_wiki:[email protected]: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存储本地对象与备份
使用Docker拉起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模式做版本控制
使用Docker拉起Bytebase,对PG的模式进行版本化管理
公开Demo地址:http://ddl.pigsty.cc
默认用户名与密码: admin
/ pigsty
Bytebase概览
Schema Migrator for PostgreSQL
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:[email protected]: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数据
使用Docker拉起PGWEB,以便从浏览器进行小批量在线数据查询
公开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 连接至数据库实例并进行探索。
快捷方式
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
使用Docker拉起PostgREST,自动根据PostgreSQL模式生成后端REST 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
Launch kong with docker and use postgres as metadb
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:[email protected]:5432/kong
# postgres://dbuser_kong:[email protected]: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 ] }