下载postgresql

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

sudo yum install -y timescaledb-postgresql-11

可初始配置(基本不用执行)

sudo timescaledb-tune

修改默认数据目录

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

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

设置环境变量

clipboard

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

使配置生效

source /etc/profile 

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

clipboard (1)

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

clipboard (2)

image-20220707141523295

设置监听所有地址

clipboard (4)

添加timescaledb库

clipboard (5)

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

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

clipboard (6)

修改用户密码

passwd postgres

clipboard (7)

修改数据库密码

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

clipboard (8)

创建用户数据库并指定所有者(bfprod为数据库名,postgres为用户名)

GRANT DATABASE bfprod OWNER postgres;

clipboard (9)

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

GRANT ALL PRIVILEGES ON DATABASE bfprod to postgres;

clipboard (10)

进入数据库

psql -s bf_prod

clipboard (11)

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,
);

创建时序表

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

高效写入

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)
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

# 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

远程访问配置

clipboard

添加配置

host    all             all             0.0.0.0/0            	md5

注意事项

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

clipboard (2)

常见错误

错误内容

 [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.

解决方式

/usr/pgsql-11/bin/postgresql-11-setup initdb