任务

高可用演练，数据库试用，一些可以在Pigsty中探索的任务

1: 访问数据库
2: 加载数据
3: 慢查询优化
4: 高可用演练

在配置完Pigsty后，您可以用它做一些有趣的探索与实验。

1 - 访问数据库

Pigsty提供多种多样的策略来控制对数据库的访问

Database Access [DRAFT]

You can access provisioned database cluster via different approach. Take standard demo as an example:

If you want ultimate performance and complete database features, connect to 5432 (or haproxy, routes to 5432)
If you have thousands of perishable connections, using pgbouncer via 6432 (or haproxy, routes to 6432, default).
If you wish to connect to cluster primary via any cluster member, using haproxy primary via 5433 (to primary 5432/6432).
If you wish to connect to cluster replica via any cluster member, using haproxy replica via 5434 (to replicas 5432/6432)

VIP:              10.10.10.3 → active_primary([10.10.10.11, 10.10.10.12, 10.10.10.13])
DNS:              pg-test  → 10.10.10.3 (VIP)
                  pg-test-primary → active_primary([10.10.10.11, 10.10.10.12, 10.10.10.13])
                  pg-test-replica → active_replicas([10.10.10.11, 10.10.10.12, 10.10.10.13]) 
 
Primary Raw:      postgres://10.10.10.11
Primary Raw Pool: postgres://10.10.10.11:6432
Primary Raw Auto: postgres://10.10.10.11,10.10.10.12,10.10.10.13?target_session_attrs=read-write
Primary VIP:      postgres://10.10.10.2
Primary Proxy:    postgres://10.10.10.11:5433 , postgres://10.10.10.12:5433, postgres://10.10.10.13:5433
Primary DNS:      postgres://pg-test ,  postgres://pg-test:5433, postgres://pg-test-primary

Replica Raw:      postgres://10.10.10.12,      postgres://10.10.10.13
Replica Raw Pool: postgres://10.10.10.12:6432, postgres://10.10.10.13:6432
Replica Raw Auto: postgres://10.10.10.11,10.10.10.12,10.10.10.13:5432
Replica Proxy:    postgres://10.10.10.11:5434 , postgres://10.10.10.12:5434, postgres://10.10.10.13:5434
Replica DNS:      postgres://pg-test:5434, postgres://pg-test-replica

Default VIP for pg-meta is 10.10.10.2 , and default VIP for pg-test is 10.10.10.3.

2 - 加载数据

以ISD数据集为例，展现如何将数据导入数据库中

ISD数据集简介

3 - 慢查询优化

利用监控系统定位数据库系统中的慢查询

一个典型的慢查询

4 - 高可用演练

做好准备，测试Pigsty在系统故障时的表现！

HA Guide [DRAFT]

Quick Start

Use patronictl to trigger failover or switchover.

alias pt='patronictl -c /pg/bin/patroni.yml'

Failover

# run as postgres @ any member of cluster `pg-test`
$ pt failover
Candidate ['pg-test-2', 'pg-test-3'] []: pg-test-3
Current cluster topology
+ Cluster: pg-test (6886641621295638555) -----+----+-----------+-----------------+
| Member    | Host        | Role    | State   | TL | Lag in MB | Tags            |
+-----------+-------------+---------+---------+----+-----------+-----------------+
| pg-test-1 | 10.10.10.11 | Leader  | running |  1 |           | clonefrom: true |
| pg-test-2 | 10.10.10.12 | Replica | running |  1 |         0 | clonefrom: true |
| pg-test-3 | 10.10.10.13 | Replica | running |  1 |         0 | clonefrom: true |
+-----------+-------------+---------+---------+----+-----------+-----------------+
Are you sure you want to failover cluster pg-test, demoting current master pg-test-1? [y/N]: y
+ Cluster: pg-test (6886641621295638555) -----+----+-----------+-----------------+
| Member    | Host        | Role    | State   | TL | Lag in MB | Tags            |
+-----------+-------------+---------+---------+----+-----------+-----------------+
| pg-test-1 | 10.10.10.11 | Replica | running |  2 |         0 | clonefrom: true |
| pg-test-2 | 10.10.10.12 | Replica | running |  2 |         0 | clonefrom: true |
| pg-test-3 | 10.10.10.13 | Leader  | running |  2 |           | clonefrom: true |
+-----------+-------------+---------+---------+----+-----------+-----------------+

Switchover

$ pt switchover
Master [pg-test-3]: pg-test-3
Candidate ['pg-test-1', 'pg-test-2'] []: pg-test-1
When should the switchover take place (e.g. 2020-10-23T17:06 )  [now]: now
Current cluster topology
+ Cluster: pg-test (6886641621295638555) -----+----+-----------+-----------------+
| Member    | Host        | Role    | State   | TL | Lag in MB | Tags            |
+-----------+-------------+---------+---------+----+-----------+-----------------+
| pg-test-1 | 10.10.10.11 | Replica | running |  2 |         0 | clonefrom: true |
| pg-test-2 | 10.10.10.12 | Replica | running |  2 |         0 | clonefrom: true |
| pg-test-3 | 10.10.10.13 | Leader  | running |  2 |           | clonefrom: true |
+-----------+-------------+---------+---------+----+-----------+-----------------+
Are you sure you want to switchover cluster pg-test, demoting current master pg-test-3? [y/N]: y
2020-10-23 16:06:11.76252 Successfully switched over to "pg-test-1"

Maintenance Mode

https://patroni.readthedocs.io/en/latest/pause.html

pt pause <cluster>

HA Procedure

Failure Detection

https://patroni.readthedocs.io/en/latest/SETTINGS.html#dynamic-configuration-settings

任务