在CenterOS中安装PostgreSql,并启用时序库timescaledb插件

下载postgresql

1	sudo yum install -y https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

添加repo

sudo tee /etc/yum.repos.d/timescale_timescaledb.repo <<EOL
[timescale_timescaledb]
name=timescale_timescaledb
baseurl=https://packagecloud.io/timescale/timescaledb/el/7/\$basearch
repo_gpgcheck=1
gpgcheck=0
enabled=1
gpgkey=https://packagecloud.io/timescale/timescaledb/gpgkey
sslverify=1
sslcacert=/etc/pki/tls/certs/ca-bundle.crt
metadata_expire=300
EOL
sudo yum update -y

下载timesacle

1	sudo yum install -y timescaledb-postgresql-11

可初始配置（基本不用执行）

1	sudo timescaledb-tune

修改默认数据目录

在/home下创建一个Postgresql的数据目录，指定所有者postgres同时分配权限

1
2
3

mkdir /home/postgresql_data
chown postgres:postgres /home/postgresql_data
chmod 750 /home/postgresql_data

设置环境变量

、

1
2
3

export PATH=/usr/pgsql-11/bin:$PATH
export LD_LIBRARY_PATH=/usr/pgsql-11/lib
export PGDATA=/home/postgresql_data

使配置生效

1	source /etc/profile

切换postgres用户并初始化数据库

修改配置内容指定正确的data路径

设置监听所有地址

添加timescaledb库

配置数据库开机启动并启动数据库服务

# 添加系统服务
systemctl enable postgresql-11.service
# 启动postgresql
service postgresql-11 start
# 查看状态
service postgresql-11 status
# 停止服务
service postgresql-11 stop
# 重启服务
service postgresql-11 restart

修改用户密码

1	passwd postgres

修改数据库密码

# 设置数据库密码
ALTER USER postgres WITH PASSWORD '密码';
# 登录
su postgres
psql
# 列出当前库
\l
# 添加timescaledb扩展
create extension timescaledb;

创建用户数据库并指定所有者（bfprod为数据库名，postgres为用户名）

1	GRANT DATABASE bfprod OWNER postgres;

将bfprod数据库的所有权限都赋予postgres。（必须加权限否则只能登陆不能操作）

1	GRANT ALL PRIVILEGES ON DATABASE bfprod to postgres;

进入数据库

1	psql -s bf_prod

timescaledb时序库操作示例

时序表创建

CREATE TABLE bf_equipment_alarm(
  time        TIMESTAMPTZ       NOT NULL,
  alarm_content    TEXT              NOT NULL,
  alarm_local TEXT  NOT NULL,
  equip_code    VARCHAR(10)  NOT NULL,
  point_code    VARCHAR(10)  NULL,
  alarm_num     DOUBLE PRECISION NOT NULL,
  alarm_unit    VARCHAR(10) NULL,
  alarm_start_time TIMESTAMPTZ NOT NULL,
  alarm_end_time TIMESTAMPTZ NULL,
  is_elimination BOOLEAN NOT NULL,
);

创建时序表

1	SELECT create_hypertable('bf_equipment_alarm', 'time');

高效写入

1
2
3

INSERT INTO public.bf_equipment_alarm(
	"time", alarm_content, alarm_local, equip_code, point_code, alarm_num, alarm_unit, alarm_start_time, alarm_end_time, is_elimination)
	VALUES (NOW(), '5号盐井鼓风机发出温度超标报警', '2号风险区生产区5号盐井', 'bfe0001', NULL , 50 , '摄氏度', to_timestamp('2020-03-26 11:29:42','yyyy-mm-dd hh24:mi:ss'), NULL, false);

使用高级SQL查询检索数据

过去3小时内，每15分钟采集一次数据，按时间排序。

SELECT time_bucket('15 minutes', time) AS fifteen_min,
    alarm_local, COUNT(*),
    MAX(alarm_num) AS max_num
  FROM bf_equipment_alarm
  WHERE time > NOW() - interval '10 hours'
  GROUP BY fifteen_min,alarm_local
  ORDER BY fifteen_min DESC;

均值查询（Median）

1
2
3

SELECT percentile_cont(0.5)
  WITHIN GROUP (ORDER BY temperature)
  FROM conditions;

移动平均数（Moving Average）

SELECT time, AVG(temperature) OVER(ORDER BY time
      ROWS BETWEEN 9 PRECEDING AND CURRENT ROW)
    AS smooth_temp
  FROM conditions
  WHERE location = 'garage' and time > NOW() - interval '1 day'
  ORDER BY time DESC;

一般sql

# 创建新表 
CREATE TABLE user_tbl(name VARCHAR(20), signup_date DATE);
# 插入数据 
INSERT INTO user_tbl(name, signup_date) VALUES('张三', '2013-12-22');
# 选择记录 
SELECT * FROM user_tbl;
# 更新数据 
UPDATE user_tbl set name = '李四' WHERE name = '张三';
# 删除记录 
DELETE FROM user_tbl WHERE name = '李四' ;
# 添加栏位 
ALTER TABLE user_tbl ADD email VARCHAR(40);
# 更新结构 
ALTER TABLE user_tbl ALTER COLUMN signup_date SET NOT NULL;
# 更名栏位 
ALTER TABLE user_tbl RENAME COLUMN signup_date TO signup;
# 删除栏位 
ALTER TABLE user_tbl DROP COLUMN email;
# 表格更名 
ALTER TABLE user_tbl RENAME TO backup_tbl;
# 删除表格 
DROP TABLE IF EXISTS backup_tbl;

创建时序表(hypertable)

# Create a schema for a new hypertable  
CREATE TABLE sensor_data (  
"time" timestamp with time zone NOT NULL,  
device_id TEXT NOT NULL,  
location TEXT NULL,  
temperature NUMERIC NULL,  
humidity NUMERIC NULL,  
pm25 NUMERIC  
);  
  
# Create a hypertable from this data  
SELECT create_hypertable  
('sensor_data', 'time', 'device_id', 16);

迁移数据到hyper table

1
2
3

# Migrate data from existing Postgres table into  
# a TimescaleDB hypertable  
INSERT INTO sensor_data (SELECT * FROM old_data);

查询hyper table

# Query hypertable like any SQL table  
SELECT device_id, AVG(temperature) from sensor_data  
WHERE temperature IS NOT NULL AND humidity > 0.5  
AND time > now() - interval '7 day'  
GROUP BY device_id;

查询最近异常的数据

# Metrics about resource-constrained devices  
SELECT time, cpu, freemem, battery FROM devops  
WHERE device_id='foo'  
AND cpu > 0.7 AND freemem < 0.2  
ORDER BY time DESC  
LIMIT 100;

计算最近7天，每小时的异常次数

# Calculate total errors by latest firmware versions  
# per hour over the last 7 days  
SELECT date_trunc('hour', time) as hour, firmware,  
COUNT(error_msg) as errno FROM data  
WHERE firmware > 50  
AND time > now() - interval '7 day'  
GROUP BY hour, firmware  
ORDER BY hour DESC, errno DESC;

计算巴士的每小时平均速度

# Find average bus speed in last hour  
# for each NYC borough  
SELECT loc.region, AVG(bus.speed) FROM bus  
INNER JOIN loc ON (bus.bus_id = loc.bus_id)  
WHERE loc.city = 'nyc'  
AND bus.time > now() - interval '1 hour'  
GROUP BY loc.region;

展示最近12小时，每小时的平均值

SELECT date_trunc('hour', time) AS hour, AVG(weight)  
    FROM logs  
    WHERE device_type = 'pressure-sensor' AND customer_id = 440  
      AND time > now() - interval '12 hours'  
    GROUP BY hour;  
  
 hour               | AVG(weight)  
--------------------+--------------  
 2017-01-04 12:00   | 170.0  
 2017-01-04 13:00   | 174.2  
 2017-01-04 14:00   | 174.0  
 2017-01-04 15:00   | 178.6  
 2017-01-04 16:00   | 173.0  
 2017-01-04 17:00   | 169.9  
 2017-01-04 18:00   | 168.1  
 2017-01-04 19:00   | 170.2  
 2017-01-04 20:00   | 167.4  
 2017-01-04 21:00   | 168.6

监控每分钟过载的设备数量

 SELECT date_trunc('minute', time) AS minute, COUNT(device_id)  
    FROM logs  
    WHERE cpu_level > 0.9 AND free_mem < 1024  
      AND time > now() - interval '24 hours'  
    GROUP BY minute  
    ORDER BY COUNT(device_id) DESC LIMIT 25;  
  
 minute             | heavy_load_devices  
--------------------+---------------------  
 2017-01-04 14:59   | 1653  
 2017-01-04 15:01   | 1650  
 2017-01-04 15:00   | 1605  
 2017-01-04 15:02   | 1594  
 2017-01-04 15:03   | 1594  
 2017-01-04 15:04   | 1561  
 2017-01-04 15:06   | 1499  
 2017-01-04 15:05   | 1460  
 2017-01-04 15:08   | 1459

最近7天，按固件版本，输出每个固件版本的报错次数

SELECT firmware_version, SUM(error_count) FROM logs  
    WHERE time > now() - interval '7 days'  
    GROUP BY firmware_version  
    ORDER BY SUM(error_count) DESC LIMIT 10;  
  
 firmware_version  | SUM(error_count)  
-------------------+-------------------  
 1.0.10            | 191  
 1.1.0             | 180  
 1.1.1             | 179  
 1.0.8             | 164  
 1.1.3             | 161  
 1.1.2             | 152  
 1.2.1             | 144  
 1.2.0             | 137  
 1.0.7             | 130  
 1.0.5             | 112  
 1.2.2             | 110

某个范围，每小时，温度高于90度的设备数量。

SELECT date_trunc('hour', time) AS hour, COUNT(logs.device_id)  
    FROM logs  
    JOIN devices ON logs.device_id = devices.id  
    WHERE logs.temperature > 90 AND devices.location = 'SITE-1'  
    GROUP BY hour;  
  
 hour               | COUNT(logs.device_id)  
--------------------+------------------------  
 2017-01-04 12:00   | 994  
 2017-01-04 13:00   | 905  
 2017-01-04 14:00   | 875  
 2017-01-04 15:00   | 910  
 2017-01-04 16:00   | 905  
 2017-01-04 17:00   | 840  
 2017-01-04 18:00   | 801  
 2017-01-04 19:00   | 813  
 2017-01-04 20:00   | 798

连接PostgreSQL实例

连接工具：pgAdmin、postico(mac)、navicat、DbEaverEE

https://www.pgadmin.org/download/?spm=a2c4g.11186623.2.18.49db15878F5Ojd

远程访问配置

添加配置

1	host all all 0.0.0.0/0 md5

注意事项

必须开启服务器5432端口并设置用户权限alter role postgres with password ‘123’;才能正常使用工具远程访问

常见错误

错误内容

 [root@localhost data]# systemctl status postgresql-11
● postgresql-11.service - PostgreSQL 11 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-11.service; enabled; vendor preset: disabled)
   Active: failed (Result: exit-code) since 四 2021-01-21 23:32:22 CST; 18s ago
     Docs: https://www.postgresql.org/docs/11/static/
  Process: 26811 ExecStartPre=/usr/pgsql-11/bin/postgresql-11-check-db-dir ${PGDATA} (code=exited, status=1/FAILURE)

1月 21 23:32:22 localhost.localdomain systemd[1]: Starting PostgreSQL 11 database server...
1月 21 23:32:22 localhost.localdomain systemd[1]: postgresql-11.service: control process exited, code=exited status=1
1月 21 23:32:22 localhost.localdomain systemd[1]: Failed to start PostgreSQL 11 database server.
1月 21 23:32:22 localhost.localdomain systemd[1]: Unit postgresql-11.service entered failed state.
1月 21 23:32:22 localhost.localdomain systemd[1]: postgresql-11.service failed.