TimescaleDB安装与配置 #

系统要求：

操作系统
├── Linux（推荐）
│   ├── Ubuntu 18.04+
│   ├── Debian 10+
│   ├── CentOS 7+
│   └── RHEL 7+
├── macOS 10.14+
└── Windows 10+

硬件要求
├── CPU: 2核心以上
├── 内存: 4GB以上
├── 磁盘: SSD推荐
└── 网络: 稳定连接

PostgreSQL版本支持：

PostgreSQL版本    TimescaleDB支持
─────────────────────────────────
PostgreSQL 11     有限支持
PostgreSQL 12     支持
PostgreSQL 13     支持
PostgreSQL 14     支持
PostgreSQL 15     推荐
PostgreSQL 16     支持

# 拉取镜像
docker pull timescale/timescaledb:latest-pg15

# 启动容器
docker run -d --name timescaledb \
    -p 5432:5432 \
    -e POSTGRES_PASSWORD=password \
    -e POSTGRES_USER=postgres \
    -e POSTGRES_DB=tsdb \
    timescale/timescaledb:latest-pg15

# 查看容器状态
docker ps | grep timescaledb

# 创建数据目录
mkdir -p ~/timescaledb/data

# 启动容器（持久化数据）
docker run -d --name timescaledb \
    -p 5432:5432 \
    -e POSTGRES_PASSWORD=password \
    -v ~/timescaledb/data:/var/lib/postgresql/data \
    timescale/timescaledb:latest-pg15

# 创建配置目录
mkdir -p ~/timescaledb/config

# 复制默认配置
docker cp timescaledb:/etc/postgresql/postgresql.conf ~/timescaledb/config/

# 启动容器（挂载配置）
docker run -d --name timescaledb \
    -p 5432:5432 \
    -e POSTGRES_PASSWORD=password \
    -v ~/timescaledb/data:/var/lib/postgresql/data \
    -v ~/timescaledb/config/postgresql.conf:/etc/postgresql/postgresql.conf \
    timescale/timescaledb:latest-pg15

# docker-compose.yml
version: '3.8'

services:
  timescaledb:
    image: timescale/timescaledb:latest-pg15
    container_name: timescaledb
    restart: always
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: password
      POSTGRES_DB: tsdb
    ports:
      - "5432:5432"
    volumes:
      - timescaledb_data:/var/lib/postgresql/data
      - ./postgresql.conf:/etc/postgresql/postgresql.conf

volumes:
  timescaledb_data:

# 启动服务
docker-compose up -d

# 停止服务
docker-compose down

# 查看日志
docker-compose logs -f timescaledb

# 更新包列表
sudo apt-get update

# 安装依赖
sudo apt-get install -y wget gnupg2 lsb-release

# 添加TimescaleDB仓库
echo "deb [signed-by=/usr/share/keyrings/timescale.keyring] https://packagecloud.io/timescale/timescaledb/ubuntu/ $(lsb_release -c -s) main" | sudo tee /etc/apt/sources.list.d/timescaledb.list

# 添加GPG密钥
wget --quiet -O - https://packagecloud.io/timescale/timescaledb/gpgkey | sudo gpg --dearmor -o /usr/share/keyrings/timescale.keyring

# 更新包列表
sudo apt-get update

# 安装PostgreSQL（如果未安装）
sudo apt-get install -y postgresql-15

# 安装TimescaleDB
sudo apt-get install -y timescaledb-2-postgresql-15

# 启动PostgreSQL服务
sudo systemctl start postgresql
sudo systemctl enable postgresql

# 运行配置脚本
sudo timescaledb-tune --quiet --yes

# 重启PostgreSQL
sudo systemctl restart postgresql

# 切换到postgres用户
sudo -u postgres psql

# 在psql中执行
CREATE EXTENSION IF NOT EXISTS timescaledb;

-- 查看版本
\dx timescaledb

# 安装EPEL仓库
sudo yum install -y epel-release

# 添加TimescaleDB仓库
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 makecache

# 安装PostgreSQL（如果未安装）
sudo yum install -y postgresql15-server

# 初始化数据库
sudo /usr/pgsql-15/bin/postgresql-15-setup initdb

# 安装TimescaleDB
sudo yum install -y timescaledb_15

# 启动服务
sudo systemctl start postgresql-15
sudo systemctl enable postgresql-15

# 配置TimescaleDB
sudo timescaledb-tune --quiet --yes

# 重启服务
sudo systemctl restart postgresql-15

# 安装PostgreSQL
brew install postgresql@15

# 启动PostgreSQL
brew services start postgresql@15

# 安装TimescaleDB
brew tap timescale/tap
brew install timescaledb

# 安装扩展
cd /opt/homebrew/Cellar/timescaledb/<version>/bin
./timescaledb_move.sh

# 连接数据库
psql postgres

# 创建扩展
CREATE EXTENSION IF NOT EXISTS timescaledb;

-- 查看版本
SELECT extversion FROM pg_extension WHERE extname = 'timescaledb';

Windows安装步骤：

1. 下载PostgreSQL
   └── https://www.postgresql.org/download/windows/

2. 安装PostgreSQL
   └── 运行安装程序，按提示完成安装

3. 下载TimescaleDB
   └── https://timescale.com/downloads

4. 安装TimescaleDB
   └── 解压到PostgreSQL安装目录

5. 配置postgresql.conf
   └── shared_preload_libraries = 'timescaledb'

# postgresql.conf

# 添加TimescaleDB到预加载库
shared_preload_libraries = 'timescaledb'

# 其他推荐配置
max_connections = 100
shared_buffers = 256MB
effective_cache_size = 768MB
maintenance_work_mem = 64MB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 2621kB
min_wal_size = 1GB
max_wal_size = 4GB

# 自动配置优化
timescaledb-tune

# 交互式配置
timescaledb-tune --interactive

# 静默模式（使用默认值）
timescaledb-tune --quiet --yes

# 指定配置文件
timescaledb-tune --conf-path=/etc/postgresql/15/main/postgresql.conf

# postgresql.conf

# 内存配置
shared_buffers = 4GB              # 共享缓冲区
effective_cache_size = 12GB       # 有效缓存大小
work_mem = 64MB                   # 工作内存
maintenance_work_mem = 1GB        # 维护工作内存

# WAL配置
wal_buffers = 64MB
checkpoint_completion_target = 0.9
max_wal_size = 2GB
min_wal_size = 1GB

# 并行查询
max_worker_processes = 8
max_parallel_workers_per_gather = 4
max_parallel_workers = 8

# TimescaleDB特定配置
timescaledb.max_background_workers = 8
timescaledb.last_tuned = '2024-01-01 00:00:00'
timescaledb.last_tuned_version = '2.0.0'

# postgresql.conf

# 监听地址
listen_addresses = '*'            # 监听所有地址
port = 5432                       # 端口号

# 最大连接数
max_connections = 200

# 连接保持
tcp_keepalives_idle = 60
tcp_keepalives_interval = 10
tcp_keepalives_count = 6

# postgresql.conf

# 日志设置
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1d
log_rotation_size = 100MB

# 日志内容
log_min_duration_statement = 1000  # 记录超过1秒的查询
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on

-- 连接数据库
psql -U postgres -d mydb

-- 创建扩展
CREATE EXTENSION IF NOT EXISTS timescaledb;

-- 查看扩展信息
\dx timescaledb

-- 查看版本
SELECT extversion FROM pg_extension WHERE extname = 'timescaledb';

-- 检查TimescaleDB函数
SELECT proname 
FROM pg_proc 
WHERE proname LIKE 'timescaledb%'
LIMIT 10;

-- 检查超表信息
SELECT * FROM timescaledb_information.hypertables;

-- 检查后台工作进程
SELECT * FROM timescaledb_information.background_workers;

# 备份数据
pg_dump -U postgres mydb > mydb_backup.sql

# 或者使用pg_dumpall
pg_dumpall -U postgres > all_backup.sql

# 检查当前版本
psql -U postgres -c "SELECT extversion FROM pg_extension WHERE extname = 'timescaledb';"

# 停止服务
sudo systemctl stop postgresql

# 升级包
sudo apt-get update
sudo apt-get install timescaledb-2-postgresql-15

# 启动服务
sudo systemctl start postgresql

# 升级扩展
psql -U postgres -d mydb -c "ALTER EXTENSION timescaledb UPDATE;"

-- 检查版本
SELECT extversion FROM pg_extension WHERE extname = 'timescaledb';

-- 检查超表
SELECT hypertable_name, num_chunks 
FROM timescaledb_information.hypertables;

-- 检查连续聚合
SELECT * FROM timescaledb_information.continuous_aggregates;

-- 错误：could not open extension control file

-- 解决方案：检查shared_preload_libraries
SHOW shared_preload_libraries;

-- 如果没有timescaledb，需要修改配置
-- postgresql.conf:
-- shared_preload_libraries = 'timescaledb'

-- 重启PostgreSQL后重试

-- 授权用户创建扩展
GRANT CREATE ON DATABASE mydb TO myuser;

-- 授权使用TimescaleDB函数
GRANT USAGE ON SCHEMA public TO myuser;

# 检查端口占用
netstat -tlnp | grep 5432

# 修改端口
# postgresql.conf:
# port = 5433

# 重启服务
sudo systemctl restart postgresql