PostgreSQL 10 + PostGIS Bundle 3.0 + TimeScaleDB 10 在Windows 10环境下安装整理

一、安装环境

• 电脑系统

   Windows 10 x64 bit 

• PostgreSQL 10 Windows Installer

   postgresql-10.12-3-windows-x64.exe

  下载地址: https://www.enterprisedb.com/thank-you-downloading-postgresql?cid=62

• PostGIS Bundle 3.0 Windows Installer

   postgis-bundle-pg10x64-setup-3.0.1-1.exe

  下载地址: https://winnie.postgis.net/download/windows/pg10/buildbot/postgis-bundle-pg10x64-setup-3.0.1-1.exe

• TimeScaleDB 10 Windows Installer

   timescaledb-postgresql-10_1.7.0-windows-amd64.zip

  下载地址: https://timescalereleases.blob.core.windows.net/windows/timescaledb-postgresql-10_1.7.0-windows-amd64.zip

二、安装步骤

• PostgreSQL 10

   运行postgresql-10.12-3-windows-x64.exe正常安装完成即可，如果无法正常安装请使用管理员权限安装。

   值得注意的是，由于这里已经提供了PostGIS Bundle 3.0的扩展插件，安装最后无需通过StackBuilder再次下载了。

• 配置 IP地址远程访问

   找到安装\PostgreSQL\10\data目录下的postgresql.conf文件:

   # - Connection Settings -
   
   listen_addresses = '*'		# what IP address(es) to listen on;
                       # comma-separated list of addresses;
                       # defaults to 'localhost'; use '*' for all
                       # (change requires restart)
   port = 5432				# (change requires restart)
   max_connections = 100			# (change requires restart)
   ...
   ...

   确认监听地址为：***listen_addresses = ‘*’***

   找到安装\PostgreSQL\10\data目录下的pg_hba.conf文件:

   # IPv4 local connections:
   host    all             all             127.0.0.1/32            md5
   # IPv6 local connections:
   host    all             all             ::1/128                 md5
   ...
   ...

   新增一行IPv4的连接地址：***    host    all    all    0.0.0.0/0    md5   ***

   # IPv4 local connections:
   host    all             all             127.0.0.1/32            md5
   host    all             all             0.0.0.0/0               md5
   # IPv6 local connections:
   host    all             all             ::1/128                 md5
   ...
   ...

   完成之后可使用本地IP进行连接测试。

• PostGIS Bundle 3.0

   运行postgis-bundle-pg10x64-setup-3.0.1-1.exe，勾选创建postgis_30_sample等待安装，有弹窗提示，偷懒的可以一路yes就可以了。

   如果出现错误应该是Postgre SQL版本与PostGis Bundle版本不对应，或者安装包下载出错。

   完成之后，连接数据库，选择默认数据库postgres 新建查询：

CREATE EXTENSION postgis;

CREATE EXTENSION postgis
> OK
> 时间: 2.156s

• TimeScaleDB 10

• 第一步：关闭PostgreSQL 10服务

   右键我的电脑 > 管理 >  服务和应用程序 >  服务 > postgresql-x64-10 > 属性 > 停止

• 第二步：配置环境变量 Path

   右键我的电脑 > 属性 >  高级系统设置 >  环境变量 > Path > 编辑 > 新建

   添加两行数据(根据个人安装路径)：

\PostgreSQL\10\bin

\PostgreSQL\10\lib

• 第三步：解压timescaledb-postgresql-10_1.7.0-windows-amd64.zip，安装

   打开解压文件timescaledb，右键setup.exe以管理员身份运行：

2020/05/14 17:11:06 WELCOME to TimescaleDB Windows installer!
2020/05/14 17:11:06 timescaledb-tune is a program that modifies your postgresql.conf configuration to be optimized for your machine's resources.
Do you want to run timescaledb-tune.exe now? [(y)es / (n)o]: y
Please enter the path to your postgresql.conf:

   这里把自己的postgresql.conf的路径复制上去，如：D:\PostgreSQL\10\data\postgresql.conf

   剩下的一路yes即可。

• 第四步：验证安装

   完成之后，首先开启postgresql-x64-10服务

   连接数据库，选择默认数据库postgres 新建查询：

CREATE EXTENSION timescaledb;

CREATE EXTENSION timescaledb;
> 警告:  
  WELCOME TO
   _____ _                               _     ____________  
  |_   _(_)                             | |    |  _  \ ___ \ 
    | |  _ _ __ ___   ___  ___  ___ __ _| | ___| | | | |_/ / 
    | | | |  _ ` _ \ / _ \/ __|/ __/ _` | |/ _ \ | | | ___ \ 
    | | | | | | | | |  __/\__ \ (_| (_| | |  __/ |/ /| |_/ /
    |_| |_|_| |_| |_|\___||___/\___\__,_|_|\___|___/ \____/
                 Running version 1.7.0
  For more information on TimescaleDB, please visit the following links:
  
   1. Getting started: https://docs.timescale.com/getting-started
   2. API reference documentation: https://docs.timescale.com/api
   3. How TimescaleDB is designed: https://docs.timescale.com/introduction/architecture
  
  Note: TimescaleDB collects anonymous reports to better understand and assist our users.
  For more information and how to disable, please see our docs https://docs.timescaledb.com/using-timescaledb/telemetry.
  
  
  
> 时间: 0.528s

   也可参考网上教程 Windows安装TimescaleDB

• TimeScaleDB 10 使用测试

   这里建议官方的案例【How to explore TimescaleDB using simulated IoT sensor data】

   地址：https://blog.timescale.com/tutorials/how-to-explore-timescaledb-using-simulated-iot-sensor-data/

e-timescaledb-using-simulated-iot-sensor-data/)】

   地址：https://blog.timescale.com/tutorials/how-to-explore-timescaledb-using-simulated-iot-sensor-data/

   也可以参考我整理过的文档timescaledb_official_simple.sql

• timescaledb_official_simple.sql

-- 创建一个 timescaledb的测试库便于进行官方案例实验

CREATE DATABASE "time_scale_db";


-- 创建timescaledb扩展：
-- 1、第一种方式：
-- CREATE EXTENSION IF NOT EXISTS "timescaledb" CASCADE;

-- 2、第二种方式：
CREATE EXTENSION "timescaledb";

-- 官方测试 案例 https://blog.timescale.com/tutorials/how-to-explore-timescaledb-using-simulated-iot-sensor-data/

-- The Internet of Things (IoT) describes a trend where computing is becoming ubiquitous
-- and is embedded in more and more physical things. For many of these things, the purpose
-- of IoT is to collect sensor data about the environment in which it exists: e.g., oil wells,
-- factories, power plants, farms, moving vehicles, office buildings, homes.
-- 物联网（Internet of Things，IoT）描述了一种趋势，即计算变得无处不在，并嵌入到越来越多的物理事物中。
-- 对于其中许多方面，物联网的目的是收集有关其存在环境的传感器数据：例如油井、工厂、发电厂、农场、移动车辆、办公楼、住宅
-- In other words, IoT is all about the data. And the datasets generated by these things are
-- generally time-series in nature, with relational metadata to describe those things.
-- 换句话说，物联网就是数据。而由这些事物生成的数据集本质上是时间序列，用关系元数据来描述这些事物。
-- In this tutorial we explore some of the features and capabilities of TimescaleDB using an IoT
-- sensor dataset that is meant to simulate a real-world IoT deployment.
-- 在本教程中，我们将探索TimescaleDB的一些特性和功能，它使用一个IoT传感器数据集来模拟真实的IoT部署。

------ Step 1: Set up your tables
------