Ubuntu 24.04 源码部署timescaledb

环境准备

操作系统版本：24.04.2 LTS (Noble Numbat)
PostgreSQL版本：16.8
timescaledb版本：2.19.3

服务器设置

关闭防火墙

--查看防火墙状态
# ufw status

--关闭防火墙
# ufw stop && ufw disable

--查看SELinux状态（Enforcing:安全策略正在执行； Permissive:不执行安全策略，但会记录警告； Disabled: 已关闭）
# getenforce

--关闭SELinux
setenforce 0 && sed -i 's/^SELINUX=.*$/SELINUX=disable/g' /etc/selinux/config

设置时区

时钟同步

设置字符集

# locale
# echo 'LANG="en_US.UTF-8"' > /etc/locale.conf

系统参数设置

安装依赖包

apt -y install gcc g++ libreadline-dev libreadline8t64 zlib1g-dev zlib1g libicu-dev

创建系统用户及目录

# groupadd postgres
# useradd -g postgres postgres -m -s /bin/bash
# mkdir -p /data/pgdata16 /opt/software /opt/app && chown -R postgres: /data/pgdata16 /opt/app

部署PG单机

官网现在PG源码安装包

本地解压编译

# tar -zxvf /opt/software/postgresql-16.8.tar.gz -C /opt/app
# cd /opt/app/postgresql-16.8/
# ./configure --prefix=/app/pg16
# make -j 8 && make install-world -j 8
# ln -s /opt/app/pg16 /opt/app/pgsql && chown -R postgres: pg16 pgsql

配置环境变量

# su - postgres
$ echo 'export PATH=$PATH:/opt/app/pgsql/bin' >> ~/.bashrc
$ echo 'export PGDATA=/data/pgdata16' >> ~/.bashrc
$ echo 'export PGHOST=/tmp' >> ~/.bashrc
$ source ~/.bashrc

初始化数据库

$ initdb -D /data/pgdata16/

配置数据库参数（参考）

listen_addresses = '*'
port = 5432
max_connections = 1000 --(按实际需要)
max_prepared_transactions=1000
shared_buffers = 32GB  --(数据库服务器内存的1/4)
work_mem = 64MB
maintenance_work_mem = 2GB
autovacuum_work_mem = -1
wal_level = replica
synchronous_commit = on
full_page_writes = on
wal_log_hints = on
archive_mode = on       --(必须打开)
archive_command = 'cp %p /data/pgarchive/%f'
autovacuum = on
logging_collector = on
log_min_duration_statement = 1000
log_checkpoints = on
log_connections = on
log_disconnections = on
log_duration = on
log_lock_waits = on
log_statement = 'mod'
log_line_prefix = '%m %d %u %r %a %p v%'
idle_in_transaction_session_timeout = 10000
lc_messages = 'en_US.UTF-8'
lc_monetary = 'en_US.UTF-8'
lc_numeric = 'en_US.UTF-8'
lc_time = 'en_US.UTF-8'

启动数据库

pg_ctl start -D /data/pgdata16/

添加timescaledb

官网下载timescaledb安装包

安装编译依赖包

apt -y install cmake

本地解压编译

# tar -zxvf timescaledb-2.19.3.tar.gz
# chown -R postgres: timescaledb-2.19.3
# cd timescaledb-2.19.3/
# su - postgres
$ cd /opt/software/timescaledb-2.19.3/
$ whereis pg_config
pg_config: /opt/app/pg16/bin/pg_config

$ ./bootstrap -DGP_CONFIG=/opt/app/pg16/bin/pg_config -DUSE_OPENSSL=0
$ cd ./build && make && make install

配置数据库参数

vi $PGDATA/postgresql.conf  
shared_preload_libraries = 'timescaledb'

重启数据库

$ pg_ctl -D /data/pgdata16 restart

安装扩展

$ psql
psql (16.8)
Type "help" for help.

postgres=# \dx
                 List of installed extensions
  Name   | Version |   Schema   |         Description
---------+---------+------------+------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(1 row)

postgres=# select * from pg_available_extension_versions() where name='timescaledb';
    name     | version | superuser | trusted | relocatable | schema | requires |                              comment
-------------+---------+-----------+---------+-------------+--------+----------+-------------------------------------------------------------------
 timescaledb | 2.19.3  | t         | t       | f           |        |          | Enables scalable inserts and complex queries for time-series data

postgres=# create extension timescaledb ;
CREATE EXTENSION
postgres=# \dx
                                                List of installed extensions
    Name     | Version |   Schema   |                                      Description
-------------+---------+------------+---------------------------------------------------------------------------------------
 plpgsql     | 1.0     | pg_catalog | PL/pgSQL procedural language
 timescaledb | 2.19.3  | public     | Enables scalable inserts and complex queries for time-series data (Community Edition)

添加备节点

请参考PostgreSQL 数据库安装部署