在CenterOS中安装PostgreSql,并启用时序库timescaledb插件
下载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设置环境变量
、
export PATH=/usr/pgsql-11/bin:$PATH
export LD_LIBRARY_PATH=/usr/pgsql-11/lib
export PGDATA=/home/postgresql_data使配置生效
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
修改用户密码
passwd postgres
修改数据库密码
# 设置数据库密码
ALTER USER postgres WITH PASSWORD '密码';
# 登录
su postgres
psql
# 列出当前库
\l
# 添加timescaledb扩展
create extension timescaledb;
创建用户数据库并指定所有者（bfprod为数据库名，postgres为用户名）
GRANT DATABASE bfprod OWNER postgres;
将bfprod数据库的所有权限都赋予postgres。（必须加权限否则只能登陆不能操作）
GRANT ALL PRIVILEGES ON DATABASE bfprod to postgres;
进入数据库
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,
);创建时序表
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
远程访问配置
添加配置
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.
解决方式
/usr/pgsql-11/bin/postgresql-11-setup initdb本博客所有文章除特别声明外，均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来自 ALLBS！
- 支付宝
- 微信
- qq
相关推荐
评论