usage: inventory_load [-h] [-p PATH] [-d CMDB_URL]

load config arguments

optional arguments:
  -h, --help            show this help message and exit„
  -p PATH, --path PATH  config path, ${PIGSTY_HOME}/pigsty.yml by default
  -d DATA, --data DATA  postgres cmdb pgurl, ${METADB_URL} by default

bin/inventory_load
bin/inventory_load -p files/conf/pigsty-demo.yml
bin/inventory_load -p files/conf/pigsty-dcs3.yml -d postgresql://dbuser_meta:DBUser.Meta@10.10.10.10:5432/meta

bin/inventory_cmdb

bin/inventory_conf

---
inventory = pigsty.yml
+++
inventory = inventory.sh

bin/createpg   pg-test       # 初始化PGSQL集群 pg-test
bin/createpg   10.10.10.13   # 初始化PGSQL实例 10.10.10.13
bin/reloadha   pg-test       # 调整PGSQL集群 pg-test 的负载均衡配置
bin/reloadhba  pg-test       # 调整PGSQL集群 pg-test 的认证白名单配置
bin/createuser pg-test -e pg_user=test     # 在PG集群pg-test创建用户test
bin/createdb   pg-test -e pg_database=test # 在PG集群pg-test创建数据库test

# NODES集群创建/集群扩容
./nodes.yml -l pg-test       # 集群初始化
./nodes.yml -l 10.10.10.13   # 实例初始化

# PGSQL集群创建/集群扩容
./pgsql.yml -l pg-test       # 集群初始化
./pgsql.yml -l 10.10.10.13   # 实例初始化

# PGSQL集群销毁/实例销毁
./pgsql-remove.yml -l pg-test      # 集群销毁
./pgsql-remove.yml -l 10.10.10.13  # 实例销毁

# NODES集群销毁/实例销毁
./nodes-remove.yml -l pg-test      # 集群销毁
./nodes-remove.yml -l 10.10.10.13  # 实例销毁

# PGSQL业务数据库/用户创建
./pgsql-createuser.yml -l pg-test -e pg_user=test
./pgsql-createdb.yml   -l pg-test -e pg_database=test

# 成员身份调整
./pgsql.yml -l pg-test -t pg_hba   # 调整IP白名单
./pgsql.yml -l pg-test -t haproxy_config,haproxy_reload

# 服务注册信息调整
./pgsql.yml -l pg-test -t register_prometheus
./pgsql.yml -l pg-test -t register_grafana

pg list        [cluster]             # 打印集群信息
pg edit-config [cluster]             # 编辑某个集群的配置文件 

pg reload      [cluster] [instance]  # 重载某个集群或实例的配置
pg restart     [cluster] [instance]  # 重启某个集群或实例 
pg reinit      [cluster] [instance]  # 重置某个集群中的实例（重新制作从库）

pg pause       [cluster]             # 进入维护模式（不会触发自动故障切换）
pg resume      [cluster]             # 退出维护模式

pg failover    [cluster]             # 手工触发某集群的Failover
pg switchover  [cluster]             # 手工触发某集群的Switchover

systemctl stop patroni            # 关闭 Patroni & Postgres
systemctl stop pgbouncer          # 关闭 Pgbouncer 
systemctl stop pg_exporter        # 关闭 PG Exporter
systemctl stop pgbouncer_exporter # 关闭 Pgbouncer Exporter
systemctl stop node_exporter      # 关闭 Node Exporter
systemctl stop haproxy            # 关闭 Haproxy
systemctl stop vip-manager        # 关闭 Vip-Manager
systemctl stop consul             # 关闭 Consul
systemctl stop postgres           # 关闭 Postgres (patroni_mode = remove ）

systemctl reload patroni             # 重载配置： Patroni
systemctl reload postgres            # 重载配置： Postgres (patroni_mode = remove)
systemctl reload pgbouncer           # 重载配置： Pgbouncer 
systemctl reload pg_exporter         # 重载配置： PG Exporter
systemctl reload pgbouncer_exporter  # 重载配置： Pgbouncer Exporter
systemctl reload haproxy             # 重载配置： Haproxy
systemctl reload vip-manager         # 重载配置： vip-manager
systemctl reload consul              # 重载配置： Consul

systemctl reload nginx          # 重载配置： Nginx （更新Haproxy管理界面索引，以及外部访问域名）
systemctl reload prometheus     # 重载配置： Prometheus （更新预计算指标计算逻辑与告警规则）
systemctl reload alertmanager   # 重载配置： Alertmanager
systemctl reload grafana-server # 重载配置： Grafana

./infra.yml -t environ             # 重新在元节点上配置环境变量与访问凭证
./infra.yml -t repo_upstream       # 重新在元节点上添加上游repo
./infra.yml -t repo_download       # 重新在元节点上下载软件包
./infra.yml -t nginx_home          # 重新生成Nginx首页内容
./infra.yml -t nginx_config,nginx_restart # 重新生成Nginx配置文件并重启应用
./infra.yml -t prometheus_config   # 重置Prometheus配置
./infra.yml -t grafana_provision   # 重置Grafana监控面板

./pgsql.yml -l pg-test -t=pgsql       # 完成数据库部署：数据库、监控、服务
./pgsql.yml -l pg-test -t=postgres    # 完成数据库部署
./pgsql.yml -l pg-test -t=service     # 完成负载均衡的部署，（Haproxy & VIP）
./pgsql.yml -l pg-test -t=pg-exporter # 完成监控部署
./pgsql.yml -l pg-test -t=pg-register # 将服务注册至基础设施
./pgsql.yml -l pg-test -t=register_prometheus # 将监控对象注册至Prometheus
./pgsql.yml -l pg-test -t=register_grafana    # 将监控目标数据源注册至Grafana

./nodes.yml -l pg-test      # 初始化 pg-test 包含的机器节点
./pgsql.yml -l pg-test      # 初始化 pg-test 数据库集群

bin/createpg pg-test

pg-test:
  hosts:
    10.10.10.11: { pg_seq: 1, pg_role: primary }
    10.10.10.12: { pg_seq: 2, pg_role: replica }
    10.10.10.13: { pg_seq: 3, pg_role: replica, pg_offline_query: true } # 新实例
  vars: { pg_cluster: pg-test }

./nodes.yml -l 10.10.10.13      # 初始化 pg-test 机器节点 10.10.10.13
./pgsql.yml -l 10.10.10.13      # 初始化 pg-test 的实例 pg-test-3

# 上述两命令可简化为：
bin/createpg 10.10.10.13

# 销毁 pg-test 集群：先销毁所有非主库实例，最后销毁主库实例
./pgsql-remove.yml -l pg-test

# 销毁集群时，一并移除数据目录与软件包
./pgsql-remove.yml -l pg-test -e rm_pgdata=true -e rm_pgpkgs=true

# 移除 pg-test 包含的节点，可选
./nodes-remove.yml -l pg-test 

./pgsql-remove.yml -l 10.10.10.13  # 实例销毁（缩容）：销毁 pg-test 集群中的 10.10.10.13 节点 
./nodes-remove.yml -l 10.10.10.13  # 从Pigsty中移除 10.10.10.13 节点（可选）

pg restart [cluster] [instance]  # 重启某个集群或实例 

# 在 pg-test 集群创建名为 test 的用户
./pgsql-createuser.yml -l pg-test -e pg_user=test

bin/createuser pg-test test  # 在 pg-test 集群创建名为 test 的用户

# 在 pg-test 集群创建名为 test 的数据库
./pgsql-createdb.yml   -l pg-test -e pg_database=test

bin/createdb   pg-test test  # 在 pg-test 集群创建名为 test 的数据库

./pgsql.yml -t register_grafana -l pg-test

- title: allow internal infra service direct access
  role: common
  rules:
    - host putong-confluence     dbuser_confluence     10.0.0.0/8  md5
    - host putong-jira           dbuser_jira           10.0.0.0/8  md5
    - host putong-newjira        dbuser_newjira        10.0.0.0/8  md5
    - host putong-gitlab         dbuser_gitlab         10.0.0.0/8  md5

./pgsql.yml -t pg_hba -l pg-test

bin/reloadhba pg-test

# 重新生成 pg-test 的HAProxy配置（但没有应用）
./pgsql.yml -l pg-test -t haproxy_config 

# 重新加载 pg-test 的HAProxy配置并启用生效
./pgsql.yml -l pg-test -t haproxy_config,haproxy_reload -e haproxy_reload=true 

bin/reloadha pg-test # 调整pg-test集群所有HAPROXY并重载配置，通常不会影响现有流量。

10.10.10.11: { pg_seq: 1, pg_role: replica, pg_offline_query: true }
10.10.10.12: { pg_seq: 2, pg_role: replica }
10.10.10.13: { pg_seq: 3, pg_role: primary }

./pgsql.yml -l pg-test -t pg_hba,haproxy_config,haproxy_reload

bin/reloadhba pg-test # 调整集群HBA配置
bin/reloadha  pg-test # 调整集群HAProxy配置

# 将 pg-test 集群所有成员注册为 prometheus 监控对象
./pgsql.yml -t register_prometheus -l pg-test

# pg-meta-1 [primary] @ 172.21.0.11
- labels: { cls: pg-meta, ins: pg-meta-1 }
  targets: [172.21.0.11:9630, 172.21.0.11:9100, 172.21.0.11:9631, 172.21.0.11:9101]

# 移除监控对象文件
rm -rf /etc/prometheus/targets/pgsql/pg-test-*.yml

# 将 pg-test 集群中的每一个数据库对象，注册为 grafana 的数据源
./pgsql.yml -t register_grafana -l pg-test

./infra.yml -t repo_upstream       # 重新在元节点上添加上游repo
./infra.yml -t repo_download       # 重新在元节点上下载软件包
./infra.yml -t nginx_home          # 重新生成Nginx首页内容
./infra.yml -t prometheus_config   # 重置Prometheus配置
./infra.yml -t grafana_provision   # 重置Grafana监控面板

./infra.yml -t nginx      # 重新配置Nginx
./infra.yml -t prometheus # 重新配置Prometheus
./infra.yml -t grafana    # 重新配置Grafana
./infra-jupyter.yml       # 重置Jupyterlab
./infra-pgweb.yml         # 重置PGWeb

# 较为常用，安全的重置命令，重装监控与重新注册不会影响服务
./pgsql.yml -l pg-test -t=monitor  # 重新部署监控
./pgsql.yml -l pg-test -t=register # 重新将服务注册至基础设施（Nginx, Prometheus, Grafana, CMDB...）
./nodes.yml -l pg-test -t=consul -e dcs_clean=clean # 在维护模式下重置DCS Agent

# 略有风险的重置操作
./pgsql.yml -l pg-test -t=service    # 重新部署负载均衡，可能导致服务闪断
./pgsql.yml -l pg-test -t=pgbouncer  # 重新部署连接池，可能导致服务闪断

# 非常危险的重置任务
./pgsql.yml -l pg-test -t=postgres # 重置数据库（包括Patroni，Postgres，Pgbouncer）
./pgsql.yml -l pg-test -t=pgsql    # 重新完成完整的数据库部署：数据库、监控、服务
./nodes.yml -l pg-test -t=consul   # 当高可用自动切换模式启用时，直接重置DCS服务器

# 极度危险的重置任务
./nodes.yml -l pg-test -t=consul -e rm_dcs_servers=true  # 强制抹除DCS服务器，可能导致所有DB集群不可写入

./pgsql.yml -l pg-test -t=pgbouncer # 重装连接池（所有用户与DB会重新生成），手工修改的配置会丢失

# 使目标集群进入维护模式
pg pause pg-test

# 将目标集群恢复为自动故障切换模式（可选）
pg resume pg-test

# 强制重置目标集群上的Consul Agent（因为HA处于维护模式，不会影响新数据库集群）
./nodes.yml -l pg-test -t consul -e dcs_clean=clean

# 重要！首先重启目标集群主库的Patroni，然后重启其余从库的Patroni
ansible pg-test-1 -b -a 'sudo systemctl reload patroni'
ansible pg-test-2,pg-test-3 -b -a 'sudo systemctl restart patroni'

git clone git@github.com:electrum/tpch-dbgen.git
cd tpch-dbgen/
make

SCALE_FACTOR=1
export DSS_QUERY="${PWD}/queries"
export OUT_QUERY="/tmp/queries"
mkdir -p ${OUT_QUERY}

./dbgen -vf -s ${SCALE_FACTOR}



for i in {1..22}
do
    
    diff $i.sql ../queries/$i.sql  
done

psql postgres -c 'DROP DATABASE tpch;';
psql postgres  -c 'CREATE DATABASE tpch';
psql postgres  -c 'TRUNCATE TABLE REGION,PART CASCADE;';
# psql tpc -f dss.ddl ; psql tpc -c '\d+' ; ls -alh *.tbl

CREATE TABLE REGION
(
    R_REGIONKEY INTEGER  NOT NULL PRIMARY KEY,
    R_NAME      CHAR(25) NOT NULL,
    R_COMMENT   VARCHAR(152)
);

CREATE TABLE NATION
(
    N_NATIONKEY INTEGER  NOT NULL PRIMARY KEY,
    N_NAME      CHAR(25) NOT NULL,
    N_REGIONKEY INTEGER  NOT NULL,
    N_COMMENT   VARCHAR(152),
    FOREIGN KEY (N_REGIONKEY) REFERENCES REGION (R_REGIONKEY)
);

CREATE TABLE PART
(
    P_PARTKEY     INTEGER        NOT NULL PRIMARY KEY,
    P_NAME        VARCHAR(55)    NOT NULL,
    P_MFGR        CHAR(25)       NOT NULL,
    P_BRAND       CHAR(10)       NOT NULL,
    P_TYPE        VARCHAR(25)    NOT NULL,
    P_SIZE        INTEGER        NOT NULL,
    P_CONTAINER   CHAR(10)       NOT NULL,
    P_RETAILPRICE DECIMAL(15, 2) NOT NULL,
    P_COMMENT     VARCHAR(23)    NOT NULL
);

CREATE TABLE SUPPLIER
(
    S_SUPPKEY   INTEGER        NOT NULL PRIMARY KEY,
    S_NAME      CHAR(25)       NOT NULL,
    S_ADDRESS   VARCHAR(40)    NOT NULL,
    S_NATIONKEY INTEGER        NOT NULL,
    S_PHONE     CHAR(15)       NOT NULL,
    S_ACCTBAL   DECIMAL(15, 2) NOT NULL,
    S_COMMENT   VARCHAR(101)   NOT NULL,
    FOREIGN KEY (S_NATIONKEY) REFERENCES NATION (N_NATIONKEY)
);

CREATE TABLE PARTSUPP
(
    PS_PARTKEY    INTEGER        NOT NULL,
    PS_SUPPKEY    INTEGER        NOT NULL,
    PS_AVAILQTY   INTEGER        NOT NULL,
    PS_SUPPLYCOST DECIMAL(15, 2) NOT NULL,
    PS_COMMENT    VARCHAR(199)   NOT NULL,
    PRIMARY KEY (PS_PARTKEY, PS_SUPPKEY),
    FOREIGN KEY (PS_SUPPKEY) REFERENCES SUPPLIER (S_SUPPKEY),
    FOREIGN KEY (PS_PARTKEY) REFERENCES PART (P_PARTKEY)
);

CREATE TABLE CUSTOMER
(
    C_CUSTKEY    INTEGER        NOT NULL PRIMARY KEY,
    C_NAME       VARCHAR(25)    NOT NULL,
    C_ADDRESS    VARCHAR(40)    NOT NULL,
    C_NATIONKEY  INTEGER        NOT NULL,
    C_PHONE      CHAR(15)       NOT NULL,
    C_ACCTBAL    DECIMAL(15, 2) NOT NULL,
    C_MKTSEGMENT CHAR(10)       NOT NULL,
    C_COMMENT    VARCHAR(117)   NOT NULL,
    FOREIGN KEY (C_NATIONKEY) REFERENCES NATION (N_NATIONKEY)
);

CREATE TABLE ORDERS
(
    O_ORDERKEY      INTEGER        NOT NULL PRIMARY KEY,
    O_CUSTKEY       INTEGER        NOT NULL,
    O_ORDERSTATUS   CHAR(1)        NOT NULL,
    O_TOTALPRICE    DECIMAL(15, 2) NOT NULL,
    O_ORDERDATE     DATE           NOT NULL,
    O_ORDERPRIORITY CHAR(15)       NOT NULL,
    O_CLERK         CHAR(15)       NOT NULL,
    O_SHIPPRIORITY  INTEGER        NOT NULL,
    O_COMMENT       VARCHAR(79)    NOT NULL,
    FOREIGN KEY (O_CUSTKEY) REFERENCES CUSTOMER (C_CUSTKEY)
);

CREATE TABLE LINEITEM
(
    L_ORDERKEY      INTEGER        NOT NULL,
    L_PARTKEY       INTEGER        NOT NULL,
    L_SUPPKEY       INTEGER        NOT NULL,
    L_LINENUMBER    INTEGER        NOT NULL,
    L_QUANTITY      DECIMAL(15, 2) NOT NULL,
    L_EXTENDEDPRICE DECIMAL(15, 2) NOT NULL,
    L_DISCOUNT      DECIMAL(15, 2) NOT NULL,
    L_TAX           DECIMAL(15, 2) NOT NULL,
    L_RETURNFLAG    CHAR(1)        NOT NULL,
    L_LINESTATUS    CHAR(1)        NOT NULL,
    L_SHIPDATE      DATE           NOT NULL,
    L_COMMITDATE    DATE           NOT NULL,
    L_RECEIPTDATE   DATE           NOT NULL,
    L_SHIPINSTRUCT  CHAR(25)       NOT NULL,
    L_SHIPMODE      CHAR(10)       NOT NULL,
    L_COMMENT       VARCHAR(44)    NOT NULL,
    PRIMARY KEY (L_ORDERKEY, L_LINENUMBER),
    FOREIGN KEY (L_ORDERKEY) REFERENCES ORDERS (O_ORDERKEY),
    FOREIGN KEY (L_PARTKEY, L_SUPPKEY) REFERENCES PARTSUPP (PS_PARTKEY, PS_SUPPKEY)
);

CREATE INDEX ON ORDERS (O_ORDERDATE);
CREATE INDEX ON LINEITEM (L_SHIPDATE);
CREATE INDEX ON LINEITEM (L_PARTKEY, L_SUPPKEY);
-- ALTER TABLE lineitem ADD FOREIGN KEY(l_partkey, l_suppkey) REFERENCES partsupp(ps_partkey, ps_suppkey);

sed 's/|$//' region.tbl    | psql tpch -c "COPY region    FROM STDIN DELIMITER '|';"
sed 's/|$//' nation.tbl    | psql tpch -c "COPY nation    FROM STDIN DELIMITER '|';"       
sed 's/|$//' supplier.tbl  | psql tpch -c "COPY supplier  FROM STDIN DELIMITER '|';"
sed 's/|$//' part.tbl      | psql tpch -c "COPY part      FROM STDIN DELIMITER '|';"
sed 's/|$//' customer.tbl  | psql tpch -c "COPY customer  FROM STDIN DELIMITER '|';"       
sed 's/|$//' partsupp.tbl  | psql tpch -c "COPY partsupp  FROM STDIN DELIMITER '|';"
sed 's/|$//' orders.tbl    | psql tpch -c "COPY orders    FROM STDIN DELIMITER '|';"
sed 's/|$//' lineitem.tbl  | psql tpch -c "COPY lineitem  FROM STDIN DELIMITER '|';"

git clone https://github.com/2ndQuadrant/pg-tpch.git
cd pg-tpch/queries
psql tpc

\i q01.analyze.sql
\i q02.analyze.sql
\i q03.analyze.sql
\i q04.analyze.sql
\i q05.analyze.sql
\i q06.analyze.sql
\i q07.analyze.sql
\i q08.analyze.sql
\i q09.analyze.sql
\i q10.analyze.sql
\i q11.analyze.sql
\i q12.analyze.sql
\i q13.analyze.sql
\i q14.analyze.sql
\i q15.analyze.sql
\i q16.analyze.sql
\i q17.analyze.sql
\i q18.analyze.sql
\i q19.analyze.sql
\i q20.analyze.sql
\i q21.analyze.sql
\i q22.analyze.sql

\i q01.explain.sql
\i q02.explain.sql
\i q03.explain.sql
\i q04.explain.sql
\i q05.explain.sql
\i q06.explain.sql
\i q07.explain.sql
\i q08.explain.sql
\i q09.explain.sql
\i q10.explain.sql
\i q11.explain.sql
\i q12.explain.sql
\i q13.explain.sql
\i q14.explain.sql
\i q15.explain.sql
\i q16.explain.sql
\i q17.explain.sql
\i q18.explain.sql
\i q19.explain.sql
\i q20.explain.sql
\i q21.explain.sql
\i q22.explain.sql

-- enhance with index
ALTER TABLE region ADD PRIMARY KEY (r_regionkey);
ALTER TABLE nation ADD PRIMARY KEY (n_nationkey);
ALTER TABLE customer ADD PRIMARY KEY (c_custkey);
ALTER TABLE supplier ADD PRIMARY KEY (s_suppkey);
ALTER TABLE part ADD PRIMARY KEY (p_partkey);
ALTER TABLE orders ADD PRIMARY KEY (o_orderkey);
ALTER TABLE partsupp ADD PRIMARY KEY (ps_partkey, ps_suppkey);
ALTER TABLE lineitem ADD PRIMARY KEY (l_orderkey, l_linenumber);

ALTER TABLE nation ADD FOREIGN KEY(n_regionkey) REFERENCES region(r_regionkey);
ALTER TABLE customer ADD FOREIGN KEY(c_nationkey) REFERENCES nation(n_nationkey);
ALTER TABLE supplier ADD FOREIGN KEY(s_nationkey) REFERENCES nation(n_nationkey);
ALTER TABLE orders ADD FOREIGN KEY(o_custkey) REFERENCES customer(c_custkey);
ALTER TABLE customer ADD FOREIGN KEY(c_suppkey) REFERENCES supplier(s_suppkey);
ALTER TABLE partsupp ADD FOREIGN KEY(ps_partkey) REFERENCES part(p_partkey);
ALTER TABLE lineitem ADD FOREIGN KEY(l_orderkey) REFERENCES orders(o_orderkey);
ALTER TABLE lineitem ADD FOREIGN KEY(l_partkey, l_suppkey) REFERENCES partsupp(ps_partkey, ps_suppkey);

/etc/pki              # CA与证书目录
/etc/pki/ca           # CA证书目录

/etc/pki/ca/cert           # CA证书目录

openssl x509 -text -in /etc/pigsty/ca/ca.crt

pg-test:
  hosts:
    10.10.10.11: { pg_seq: 1, pg_role: primary } # 主库
    10.10.10.12: { pg_seq: 2, pg_role: replica } # 热备（承载在线只读流量）
    10.10.10.13: { pg_seq: 3, pg_role: offline } # 温备（不承载在线流量）
  vars:
    pg_cluster: pg-test

# pg-test是原始数据库
pg-test:
  hosts:
    10.10.10.11: { pg_seq: 1, pg_role: primary }
  vars:
    pg_cluster: pg-test
    pg_version: 14

# pg-testdelay 将作为 pg-test 库的延时从库
pg-testdelay:
  hosts:
    10.10.10.12: { pg_seq: 1, pg_role: primary , pg_upstream: 10.10.10.11 } # 实际角色为 Standby Leader
  vars:
    pg_cluster: pg-testdelay
    pg_version: 14          

 standby_cluster:
   create_replica_methods:
   - basebackup
   host: 10.10.10.11
   port: 5432
+  recovery_min_apply_delay: 1h

$ pg-backup # 不带任何参数执行备份脚本
[2021-08-05 17:41:35][INFO] ================================================================
[2021-08-05 17:41:35][INFO] [INIT] pg-backup begin, checking parameters
[2021-08-05 17:41:35][DEBUG] [INIT] #====== BINARY
[2021-08-05 17:41:35][DEBUG] [INIT] pg_basebackup     :   /usr/pgsql/bin/pg_basebackup
[2021-08-05 17:41:35][DEBUG] [INIT] openssl           :   /bin/openssl
[2021-08-05 17:41:35][DEBUG] [INIT] #====== PARAMETER
[2021-08-05 17:41:35][DEBUG] [INIT] filename  (-f)    :   backup_pg-meta_20210805.tar.lz4
[2021-08-05 17:41:35][DEBUG] [INIT] src       (-s)    :   postgres:///
[2021-08-05 17:41:35][DEBUG] [INIT] dst       (-d)    :   /pg/backup
[2021-08-05 17:41:35][DEBUG] [INIT] tag       (-t)    :   pg-meta
[2021-08-05 17:41:35][DEBUG] [INIT] key       (-k)    :   pg-meta
[2021-08-05 17:41:35][DEBUG] [INIT] encrypt   (-e)    :   false
[2021-08-05 17:41:35][DEBUG] [INIT] upload    (-u)    :   false
[2021-08-05 17:41:35][DEBUG] [INIT] remove    (-r)    :   -mmin +1200
[2021-08-05 17:41:35][INFO] [LOCK] acquire lock @ /tmp/backup.lock
[2021-08-05 17:41:35][INFO] [LOCK] lock acquired success on /tmp/backup.lock, pid=25438
[2021-08-05 17:41:35][INFO] [BKUP] backup begin, from postgres:/// to /pg/backup/backup_pg-meta_20210805.tar.lz4
[2021-08-05 17:41:35][INFO] [BKUP] backup in normal mode
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/6B000028 on timeline 1
pg_basebackup: write-ahead log end point: 0/6B000138
pg_basebackup: syncing data to disk ...
pg_basebackup: base backup completed
[2021-08-05 17:41:45][INFO] [BKUP] backup complete!
[2021-08-05 17:41:45][INFO] [RMBK] remove local obsolete backup: 1200
[2021-08-05 17:41:45][INFO] [BKUP] find obsolete backups: find /pg/backup/ -maxdepth 1 -type f -mmin +1200 -name 'backup*.lz4'
[2021-08-05 17:41:45][WARN] [BKUP] remove obsolete backups:
[2021-08-05 17:41:45][INFO] [RMBK] remove old backup complete
[2021-08-05 17:41:45][INFO] [LOCK] release lock @ /tmp/backup.lock
[2021-08-05 17:41:45][INFO] [DONE] backup procdure complete!
[2021-08-05 17:41:45][INFO] ================================================================

node_crontab: # 每日凌晨1点执行一次全量备份
  - '00 01 * * * postgres /pg/bin/pg-backup 2>>/pg/log/backup.log'

# 找到最新的备份文件并打印信息
backup_dir="/pg/backup"
data_dir=/pg/data
backup_latest=$(ls -t ${backup_dir} | head -n1)
echo "backup ${backup_latest} will be used"

# 暂停Patroni，关停数据库，移除数据目录（危险）
pg pause pg-meta
pg_ctl -D /pg/data stop
rm -rf /pg/data/*     # 清空数据目录（危险）

# 解压备份至数据库目录
echo "unlz4 -d -c ${backup_dir}/${backup_latest} | tar -xC ${data_dir}"
unlz4 -d -c ${backup_dir}/${backup_latest} | tar -xC ${data_dir}    # 解压至数据库目录
# 可选：如果加密时设置了密码，则需要先解密再解压
openssl enc -rc4 -d -k ${PASSWORD} -in ${backup_latest} | unlz4 -d -c | tar -xC ${data_dir}

# 重新拉起数据库
systemctl restart patroni

# 重做集群的其他从库
pg reinit <cluster> # 依次重置集群的其他实例成员

make test-ri     # 在 pg-test集群初始化 pgbench 表
make test-rw     # 生成 pgbench 写入流量
make test-ro     # 生成 pgbench 只读流量

# 4条连接，总计64读写TPS
while true; do pgbench -nv -P1 -c4 --rate=64 -T10 postgres://test:test@pg-test:5433/test; done

# 8条连接，总计512只读TPS
while true; do pgbench -nv -P1 -c8 --select-only --rate=512 -T10 postgres://test:test@pg-test:5434/test; done

pg list pg-test          # 查看 pg-test 集群状态（在单独的窗口中）
pg list pg-test -w 0.1   # 查看 pg-test 集群状态，每0.1s刷新一次

ssh 10.10.10.3 sudo reboot    # 直接将 pg-test-1 主节点重启（VIP指向实际主节点）

# 关停主库上的Postgres主进程
ssh 10.10.10.3 'sudo -iu postgres /usr/pgsql/bin/pg_ctl -D /pg/data stop'

# 查询主库PID并强行Kill
ssh 10.10.10.3 'sudo kill -9 $(sudo cat /pg/data/postmaster.pid | head -n1)'

# 关停主库上的Postgres主进程
ssh 10.10.10.3 'sudo systemctl stop patroni'

# 关停主库上的Patroni主进程
ssh 10.10.10.3 "ps aux | grep /usr/bin/patroni | grep -v grep | awk '{print $2}'"
ssh 10.10.10.3 'sudo kill -9 723'

/usr/pgsql/bin/pg_ctl -D /pg/data stop
systemctl restart patroni

2021-12-03 14:16:18 +0800 INFO:  stderr=2021-12-03 14:16:18.752 HKT [7852] FATAL:  lock file "postmaster.pid" already exists
2021-12-03 14:16:18.752 HKT [7852] HINT:  Is another postmaster (PID 887) running in data directory "/pg/data"?

ssh 10.10.10.3 sudo reboot    # 直接将 pg-test-1 主节点重启（VIP指向实际主节点）

# 关停从库上的Postgres主进程
ssh 10.10.10.3 'sudo -iu postgres /usr/pgsql/bin/pg_ctl -D /pg/data stop'

# 查询从库PID并强行Kill
ssh 10.10.10.3 'sudo kill -9 $(sudo cat /pg/data/postmaster.pid | head -n1)'

systemctl stop consul

ALTER TABLE pgbench_accounts DROP CONSTRAINT pgbench_accounts_pkey ;

SELECT abalance FROM pgbench_accounts WHERE aid = $1

CREATE USER dbuser_admin SUPERUSER;              -- 超级用户用于创建发布与订阅
CREATE USER replicator REPLICATION BYPASSRLS;    -- 复制用户用于订阅变更

wal_level: logical                      # MANDATORY!	
max_worker_processes: 64                # default 8 -> 64, set to CPU CORE 64
max_parallel_workers: 32                # default 8 -> 32, limit by max_worker_processes
max_parallel_maintenance_workers: 16    # default 2 -> 16, limit by parallel worker
max_parallel_workers_per_gather: 0      # default 2 -> 0,  disable parallel query on OLTP instance
# max_parallel_workers_per_gather: 16   # default 2 -> 16, enable parallel query on OLAP instance

max_wal_senders: 24                     # 10 -> 24
max_replication_slots: 16               # 10 -> 16 
max_logical_replication_workers: 8      # 4 -> 8, 6 sync worker + 1~2 apply worker
max_sync_workers_per_subscription: 6    # 2 -> 6, 6 sync worker

PGSRC='postgres://dbuser_admin@10.10.10.10/meta'        # 源端发布者 (SU)
PGDST='postgres://dbuser_admin@10.10.10.11/test'        # 宿端订阅者 (SU)

pgbench -is64 ${PGSRC}

psql ${PGSRC} -qAXtw <<-EOF
DROP TABLE IF EXISTS pgbench_extras;
CREATE TABLE IF NOT EXISTS pgbench_extras
  (id BIGSERIAL PRIMARY KEY,v  TIMESTAMP NOT NULL UNIQUE);
EOF

SELECT quote_ident(nspname) || '.' || quote_ident(relname) AS name
FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE relkind = 'r' AND nspname NOT IN ('pg_catalog', 'information_schema', 'monitor', 'repack', 'pg_toast')

SELECT quote_ident(nspname) || '.' || quote_ident(relname) AS name, con.ri AS keys,
       CASE relreplident WHEN 'd' THEN 'default' WHEN 'n' THEN 'nothing' WHEN 'f' THEN 'full' WHEN 'i' THEN 'index' END AS identity
FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid, LATERAL (SELECT array_agg(contype) AS ri FROM pg_constraint WHERE conrelid = c.oid) con
WHERE relkind = 'r' AND nspname NOT IN ('pg_catalog', 'information_schema', 'monitor', 'repack', 'pg_toast')
ORDER BY 2,3;

          name           | keys  | identity
-------------------------+-------+----------
 public.spatial_ref_sys  | {c,p} | default
 public.pgbench_accounts | {p}   | default
 public.pgbench_branches | {p}   | default
 public.pgbench_tellers  | {p}   | default
 public.pgbench_extras   | {p,u} | default
 public.pgbench_history  | NULL  | default

-- 一个例子：即使pgbench_extras上有主键，但也可以使用唯一索引作为身份标识
ALTER TABLE pgbench_extras REPLICA IDENTITY USING INDEX pgbench_extras_v_key;

ALTER TABLE pgbench_history REPLICA IDENTITY FULL;

          name           | keys  | identity
-------------------------+-------+----------
 public.spatial_ref_sys  | {c,p} | default
 public.pgbench_accounts | {p}   | default
 public.pgbench_branches | {p}   | default
 public.pgbench_tellers  | {p}   | default
 public.pgbench_extras   | {p,u} | index
 public.pgbench_history  | NULL  | full

pg_dump ${PGSRC} --schema-only -n public | psql ${PGDST}

pg_dump ${PGSRC} --schema-only -n public -t 'pgbench_*' | psql ${PGDST}

CREATE PUBLICATION name
    [ FOR TABLE [ ONLY ] table_name [ * ] [, ...]
      | FOR ALL TABLES ]
    [ WITH ( publication_parameter [= value] [, ... ] ) ]

CREATE PUBLICATION "pg_meta_pub" FOR ALL TABLES;

CREATE PUBLICATION "pg_meta_pub" FOR ALL TABLES 
WITH(publish = 'insert', publish_via_partition_root = true);

SELECT E'CREATE PUBLICATION pg_meta_pub FOR TABLE\n' ||
       string_agg(quote_ident(nspname) || '.' || quote_ident(relname), E',\n') || ';' AS sql
FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE relkind = 'r' AND nspname NOT IN ('pg_catalog', 'information_schema', 'monitor', 'repack', 'pg_toast')
AND relname ~ 'pgbench'; -- 只复制表名形如 pgbench* 的表
\gexec    -- 在psql中执行上面命令生成的SQL语句

psql ${PGSRC} -Xtw <<-EOF
  CREATE PUBLICATION pg_meta_pub FOR TABLE
    public.pgbench_accounts,
    public.pgbench_branches,
    public.pgbench_tellers,
    public.pgbench_history,
    public.pgbench_extras;
EOF

$ psql ${PGSRC} -Xxwc 'table pg_publication;'
-[ RECORD 1 ]+------------
oid          | 24679
pubname      | pg_meta_pub
pubowner     | 10
puballtables | f
pubinsert    | t
pubupdate    | t
pubdelete    | t
pubtruncate  | t
pubviaroot   | f

$ psql ${PGSRC} -Xwc 'table pg_publication_tables;'
   pubname   | schemaname |    tablename
-------------+------------+------------------
 pg_meta_pub | public     | pgbench_history
 pg_meta_pub | public     | pgbench_tellers
 pg_meta_pub | public     | pgbench_accounts
 pg_meta_pub | public     | pgbench_branches
 pg_meta_pub | public     | pgbench_extras

CREATE SUBSCRIPTION subscription_name
    CONNECTION 'conninfo'
    PUBLICATION publication_name [, ...]
    [ WITH ( subscription_parameter [= value] [, ... ] ) ]

psql ${PGDST} -Xtw <<-EOF
    CREATE SUBSCRIPTION "pg_test_sub" 
      CONNECTION 'host=10.10.10.10 user=replicator dbname=meta' 
      PUBLICATION "pg_meta_pub";
EOF

$ psql ${PGDST} -Xxwc 'TABLE pg_subscription;'
-[ RECORD 1 ]---+---------------------------------------------
oid             | 20759
subdbid         | 19351
subname         | pg_test_sub
subowner        | 16390
subenabled      | t
subconninfo     | host=10.10.10.10 user=replicator dbname=meta
subslotname     | pg_test_sub
subsynccommit   | off
subpublications | {pg_meta_pub}

$ psql ${PGDST} -Xwc 'table pg_subscription_rel;'
 srsubid | srrelid | srsubstate |  srsublsn
---------+---------+------------+------------
   20759 |   20742 | r          | 0/B0BC1FB8
   20759 |   20734 | r          | 0/B0BC20B0
   20759 |   20737 | r          | 0/B0BC20B0
   20759 |   20745 | r          | 0/B0BC20B0
   20759 |   20731 | r          | 0/B0BC20B0

psql ${PGDST} -Xxw <<-'EOF'
    SELECT subname, json_object_agg(srsubstate, cnt) FROM
    pg_subscription s JOIN
      (SELECT srsubid, srsubstate, count(*) AS cnt FROM pg_subscription_rel 
       GROUP BY srsubid, srsubstate) sr
    ON s.oid = sr.srsubid GROUP BY subname;
EOF

   subname   | json_object_agg
-------------+-----------------
 pg_test_sub | { "r" : 5 }

psql ${PGSRC} -Xxwc 'CHECKPOINT;ANALYZE;CHECKPOINT;'
psql ${PGSRC} -Xxwc 'CHECKPOINT;ANALYZE;CHECKPOINT;'

psql ${PGSRC} -Xxw <<-EOF
    SELECT application_name AS name,
           pg_current_wal_lsn() AS lsn,
           pg_current_wal_lsn() - replay_lsn AS lag 
    FROM pg_stat_replication;
EOF

-[ RECORD 1 ]-----
name | pg_test_sub
lsn  | 0/B0C24918
lag  | 0

psql ${PGDST} -qAXtwc 'ALTER SUBSCRIPTION pg_test_sub DISABLE;'

# 在宿端创建发布：pg_test_pub
psql ${PGDST} -Xtw <<-EOF
  CREATE PUBLICATION pg_test_pub FOR TABLE
    public.pgbench_accounts,
    public.pgbench_branches,
    public.pgbench_tellers,
    public.pgbench_history,
    public.pgbench_extras;
  TABLE pg_publication;
EOF

# 在源端创建订阅
psql ${PGSRC} -Xtw <<-EOF
    CREATE SUBSCRIPTION "pg_meta_sub" 
      CONNECTION 'host=10.10.10.11 user=replicator dbname=test' 
      PUBLICATION "pg_test_pub";
    TABLE pg_subscription;
EOF

# 清空源端所有相关表（危险），等待/或者不等待同步完成
psql ${PGSRC} -Xtw <<-EOF
  TRUNCATE TABLE
    public.pgbench_accounts,
    public.pgbench_branches,
    public.pgbench_tellers,
    public.pgbench_history,
    public.pgbench_extras;
  TABLE pg_publication;
EOF