记一次 Centos7 postgresql v11 安装时序数据库 TimescaleDB

蔚落 2023-02-12 15:22 2阅读 0赞

一、数据库安装

根据自身环境需要选择安装

1、yum 指定目录安装

https://blog.csdn.net/llwy1428/article/details/105143053

2、yum 直接安装

https://blog.csdn.net/llwy1428/article/details/102486414

3、编译安装

https://blog.csdn.net/llwy1428/article/details/95444151

4、PostgreSql 基本操作

https://blog.csdn.net/llwy1428/article/details/102598732

5、Centos7 yum 安装、配置 PgAdmin4

[https://blog.csdn.net/llwy1428/article/details/102486511][https_blog.csdn.net_llwy1428_article_details_102486511]

6、Centos7 PostgreSql 数据库安装扩展

[https://blog.csdn.net/llwy1428/article/details/105167524][https_blog.csdn.net_llwy1428_article_details_105167524]

7、Centos7 PostgreSql 数据库使用FDW扩展

[https://blog.csdn.net/llwy1428/article/details/106291669][https_blog.csdn.net_llwy1428_article_details_106291669]

二、TimescaleDB  安装配置

1、安装数据库：

https://blog.csdn.net/llwy1428/article/details/102486414

2、制作 timescaledb.repo 文件

[root@localhost ~]# sudo vi /etc/yum.repos.d/timescaledb.repo

[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

3、安装 timescaledb-postgresql-11

[root@localhost ~]# sudo yum install -y timescaledb-postgresql-11

4、配置数据库

[root@localhost ~]# sudo timescaledb-tune --pg-config=/usr/pgsql-11/bin/pg_config

![20200526155737457.png][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xsd3kxNDI4_size_16_color_FFFFFF_t_70][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xsd3kxNDI4_size_16_color_FFFFFF_t_70 1][]

注：如果使用默认配置，可直接使用如下命令

[root@localhost ~]# sudo timescaledb-tune --pg-config=/usr/pgsql-11/bin/pg_config --quiet --yes

5、重启数据库服务

[root@localhost ~]# sudo systemctl restart postgresql-11.service

6、测试：

切换用户：

[root@localhost ~]# su - postgres

进入命令窗口：

-bash-4.2$ psql

创建数据库 timeseries

postgres=# CREATE DATABASE timeseries;
    postgres=# \l

进入创建的数据库 timeseries

postgres=# \c timeseries
    timeseries=# CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xsd3kxNDI4_size_16_color_FFFFFF_t_70 2][]

创建表：

timeseries=# CREATE TABLE conditions (time TIMESTAMP WITH TIME ZONE NOT NULL,device_id TEXT,temperature NUMERIC,humidity NUMERIC);
    timeseries=# SELECT create_hypertable('conditions', 'time');

插入数据：

timeseries=# INSERT INTO conditions(time, device_id, temperature, humidity) VALUES (NOW(), 'weather-pro-000000', 84.1, 84.1);
    timeseries=# INSERT INTO conditions VALUES (NOW(), 'weather-pro-000002', 71.0, 51.0),(NOW(), 'weather-pro-000003', 70.5, 50.5),(NOW(), 'weather-pro-000004', 70.0, 50.2);

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xsd3kxNDI4_size_16_color_FFFFFF_t_70 3][]

查询数据

timeseries=# SELECT * FROM conditions LIMIT 10;

![20200526161028246.png][]

查询数据

timeseries=# SELECT * FROM conditions ORDER BY time DESC LIMIT 3;

![20200526161116335.png][]

参考地址：

[https://www.digitalocean.com/community/tutorials/how-to-install-and-use-timescaledb-on-centos-7][https_www.digitalocean.com_community_tutorials_how-to-install-and-use-timescaledb-on-centos-7]

至此， Centos7  postgresql v11 安装时序数据库  TimescaleDB 操作完毕！

[https_blog.csdn.net_llwy1428_article_details_102486511]: https://blog.csdn.net/llwy1428/article/details/102486511
[https_blog.csdn.net_llwy1428_article_details_105167524]: https://blog.csdn.net/llwy1428/article/details/105167524
[https_blog.csdn.net_llwy1428_article_details_106291669]: https://blog.csdn.net/llwy1428/article/details/106291669
[20200526155737457.png]: https://img-blog.csdnimg.cn/20200526155737457.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xsd3kxNDI4_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200526155802403.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xsd3kxNDI4,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xsd3kxNDI4_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200526155857845.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xsd3kxNDI4,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xsd3kxNDI4_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200526160347491.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xsd3kxNDI4,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xsd3kxNDI4_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20200526160903998.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xsd3kxNDI4,size_16,color_FFFFFF,t_70
[20200526161028246.png]: https://img-blog.csdnimg.cn/20200526161028246.png
[20200526161116335.png]: https://img-blog.csdnimg.cn/20200526161116335.png
[https_www.digitalocean.com_community_tutorials_how-to-install-and-use-timescaledb-on-centos-7]: https://www.digitalocean.com/community/tutorials/how-to-install-and-use-timescaledb-on-centos-7