0%

cmake安装

# 到官网 https://cmake.org/download/ 下载最新的包
wget https://github.com/Kitware/CMake/releases/download/v3.30.0/cmake-3.30.0-linux-x86_64.tar.gz

# 解压
tar -zxvf cmake-3.30.0-linux-x86_64.tar.gz

# 改个名字并移动到/usr/local目录下
mv cmake-3.30.0-linux-x86_64 cmake
mv cmake /usr/local/

# 添加bin到环境变量
export PATH=/usr/local/cmake/bin:$PATH

# 测试
cmake --version

安装

# Add the TimescaleDB package
echo "deb https://packagecloud.io/timescale/timescaledb/ubuntu/ $(lsb_release -c -s) main" | sudo tee /etc/apt/sources.list.d/timescaledb.list
# Install the TimescaleDB GPG key
wget --quiet -O - https://packagecloud.io/timescale/timescaledb/gpgkey | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/timescaledb.gpg
# Update your local repository list
sudo apt update
# Install TimescaleDB
sudo apt install timescaledb-2-postgresql-16 postgresql-client-16

转换

# Tune your PostgreSQL instance for TimescaleDB
sudo timescaledb-tune
# Restart PostgreSQL
sudo systemctl restart postgresql

添加拓展

重启pg后,登录到控制台:

-- 添加
CREATE EXTENSION IF NOT EXISTS timescaledb;
-- \dx 命令检查安装的拓展
postgres=# \dx
                                                List of installed extensions
    Name     | Version |   Schema   |                                      Description                                      
-------------+---------+------------+---------------------------------------------------------------------------------------
 plpgsql     | 1.0     | pg_catalog | PL/pgSQL procedural language
 timescaledb | 2.15.3  | public     | Enables scalable inserts and complex queries for time-series data (Community Edition)
(2 rows)

修改的配置详细过程

postgres@deri:~$ sudo timescaledb-tune
Using postgresql.conf at this path:
/etc/postgresql/16/main/postgresql.conf

Is this correct? [(y)es/(n)o]: y
Writing backup to:
/tmp/timescaledb_tune.backup202407030214

shared_preload_libraries needs to be updated
Current:
#shared_preload_libraries = ''
Recommended:
shared_preload_libraries = 'timescaledb'
Is this okay? [(y)es/(n)o]: y
success: shared_preload_libraries will be updated

Tune memory/parallelism/WAL and other settings? [(y)es/(n)o]: y
Recommendations based on 7.76 GB of available memory and 4 CPUs for PostgreSQL 16

Memory settings recommendations
Current:
shared_buffers = 128MB
#effective_cache_size = 4GB
#maintenance_work_mem = 64MB
#work_mem = 4MB
Recommended:
shared_buffers = 1985MB
effective_cache_size = 5956MB
maintenance_work_mem = 1016498kB
work_mem = 5082kB
Is this okay? [(y)es/(s)kip/(q)uit]: y
success: memory settings will be updated

Parallelism settings recommendations
Current:
missing: timescaledb.max_background_workers
#max_worker_processes = 8
#max_parallel_workers_per_gather = 2
#max_parallel_workers = 8
Recommended:
timescaledb.max_background_workers = 16
max_worker_processes = 23
max_parallel_workers_per_gather = 2
max_parallel_workers = 4
Is this okay? [(y)es/(s)kip/(q)uit]: y
success: parallelism settings will be updated

WAL settings recommendations
Current:
#wal_buffers = -1
min_wal_size = 80MB
Recommended:
wal_buffers = 16MB
min_wal_size = 512MB
Is this okay? [(y)es/(s)kip/(q)uit]: y
success: WAL settings will be updated

Background writer settings recommendations
Current:
Recommended:
Is this okay? [(y)es/(s)kip/(q)uit]: y
success: background writer settings will be updated

Miscellaneous settings recommendations
Current:
#default_statistics_target = 100
#random_page_cost = 4.0
#checkpoint_completion_target = 0.9
#max_locks_per_transaction = 64
#autovacuum_max_workers = 3
#autovacuum_naptime = 1min
#effective_io_concurrency = 1
Recommended:
default_statistics_target = 100
random_page_cost = 1.1
checkpoint_completion_target = 0.9
max_locks_per_transaction = 128
autovacuum_max_workers = 10
autovacuum_naptime = 10
effective_io_concurrency = 256
Is this okay? [(y)es/(s)kip/(q)uit]: y
success: miscellaneous settings will be updated
Saving changes to: /etc/postgresql/16/main/postgresql.conf

安装

# 安装 16 版本
sudo apt -y install postgresql-16
# 启动
systemctl start postgresql

默认目录

切换到 postgres 用户: sudo su postgres.

  • 配置文件目录: /etc/postgresql/16/main/
  • 数据目录:/var/lib/postgresql/16/main/
  • 可执行文件:/usr/lib/postgresql/16/bin/
  • 日志目录:/var/log/postgresql/

可以通过 pg_config 命令查看 PostgreSQL 的具体安装路径和配置信息.

网络配置

  • sudo vim /etc/netplan/xxx.yaml
network:
    version: 2
    ethernets:
        ens160: # 网卡
            dhcp4: no
            addresses:
              - 172.16.0.176/24
            routes: # 配置网关
              - to: default
                via: 172.16.0.1
            nameservers:
              addresses:
                - 223.5.5.5
                - 223.6.6.6
  • 应用:sudo netplan apply

关闭防火墙

# 检查防火墙状态
sudo ufw status
# 关闭防火墙
sudo ufw disable
# 开通防火墙
sudo ufw allow 5432/tcp

包管理

# 更新软件包列表
sudo apt update
# 升级已安装的软件包
sudo apt upgrade
# 安装新软件包
sudo apt install <package_name>
# 删除软件包
sudo apt remove <package_name>
# 清理不再需要的包
sudo apt autoremove
# 查看包的信息
apt show <package_name>

apt-get用法与apt基本一致,apt 结合了 apt-getapt-cache 的一些常用功能,使其更加简洁和高效,如果你在编写脚本,使用 apt-get 可能会更好.

# 列出已安装的软件包
apt list --installed

安装docker

sudo apt update
# 安装必要的包
sudo apt install apt-transport-https ca-certificates curl software-properties-common
# 添加 Docker 的官方 GPG 密钥
curl -fsSL https://download.docker.com/linux/ubuntu/gpg | sudo gpg --dearmor -o /usr/share/keyrings/docker-archive-keyring.gpg
# 添加 Docker 仓库到 APT 源
echo "deb [arch=amd64 signed-by=/usr/share/keyrings/docker-archive-keyring.gpg] https://download.docker.com/linux/ubuntu $(lsb_release -cs) stable" | sudo tee /etc/apt/sources.list.d/docker.list > /dev/null
sudo apt update
# 安装 Docker
sudo apt install docker-ce
# 启动
sudo systemctl start docker
# 开机启动
sudo systemctl enable docker

# 将当前用户添加到 Docker 组,这样就不用加sudo了
sudo usermod -aG docker ${USER}
# 立即生效
newgrp docker
# 验证是否成功
groups $USER
  • 设置docker数据目录及日志大小
{
"data-root": "/data/docker",
"registry-mirrors": [
    "https://7x3mic2t.mirror.aliyuncs.com"
  ],
"log-opts": {
    "max-size": "100m",
    "max-file":"1"
  }
}
sudo systemctl daemon-reload
sudo systemctl restart docker

普通用户添加sudo

# 将用户添加到 sudo 组
sudo usermod -aG sudo username
# 验证用户是否已被添加到 sudo 组
groups username
# 测试 sudo 权限
sudo apt update

概述

基于patroni搭建了三台服务器的postgres集群,本文通过haproxy实现postgres的动态负载。

3台机器都需要安装haproxy,三个haproxy上面可以由负载均衡器或者其他工具提供服务。

安装

  1. haproxy的安装
# 基于CentOS7系统,通过yum只能安装haproxy-1.5或haproxy-1.8,这两个版本都已EOL
yum install haproxy # haproxy-1.5
vim /etc/haproxy/haproxy.cfg

yum install haproxy18 # haproxy-1.8,需要具有epel-release软件包
vim /etc/haproxy18/haproxy.cfg

# 源码安装方式:省略
  1. /etc/haproxy18/haproxy.conf
defaults     
    mode tcp
    log global
    option tcplog
    option dontlognull
    retries 3 
    option redispatch
    timeout queue 1m
    timeout client 30m # 1h
    timeout connect 4s # 10s
    timeout server 30m # 1h
    timeout check 5s
    #option clitcpka # haproxy与client端的keepalive配置
    #option srvtcpka # haproxy与server端的keepalive配置
    option tcpka # haproxy与双方都建立长连接
    #clitcpka-cnt 3
    #clitcpka-idle 180
    #clitcpka-intvl 10

listen primary
    bind 0.0.0.0:15432
    mode tcp
    balance roundrobin
    option httpchk HEAD /primary
    http-check expect status 200
    default-server inter 3s fall 2 rise 1 on-marked-down shutdown-sessions
    server DB01 192.168.86.101:5432 maxconn 2000 check port 8008
    server DB02 192.168.86.102:5432 maxconn 2000 check port 8008
    server DB03 192.168.86.103:5432 maxconn 2000 check port 8008
    # acl is_lb_healthcheck src 192.168.0.10 # LB探活机制的源IP
    # tcp-request connection reject if is_lb_healthcheck # 阻止探活转发到后端PG,避免造成PG频繁fork

listen replicas
    bind 0.0.0.0:15433
    mode tcp
    balance roundrobin
    option httpchk HEAD /replica
    # option tcpka
    http-check expect status 200
    default-server inter 3s fall 2 rise 1 on-marked-down shutdown-sessions
    server DB01 192.168.86.101:5432 maxconn 2000 check port 8008
    server DB02 192.168.86.102:5432 maxconn 2000 check port 8008
    server DB03 192.168.86.103:5432 maxconn 2000 check port 8008
    # acl is_lb_healthcheck src 192.168.0.10 # LB探活机制的源IP
    # tcp-request connection reject if is_lb_healthcheck # 阻止探活转发到后端PG,避免造成PG频繁fork

listen monitor
    bind 0.0.0.0:8888
    mode http
    option httplog
    stats enable
    stats uri /haproxy_stats
    stats realm Haproxy\ Statistics
    # stats auth admin:123456 # haproxy web管理用户名密码,自行设置
    stats refresh 30s
    stats hide-version

通过15432始终与主节点建立连接,通过15433始终与备节点建立连接,主节点可读可写,备节点只能读,实现读写分离。检测8008端口由patroni提供。

  1. 启动
systemctl start haproxy18.service

TimescaleDB

TimescaleDB 是基于postgres的时序数据库,安装TimescaleDB需要先安装postgres,然后将postgres转成TimescaleDB.

参考前文安装 postgres-15.

安装TimescaleDB

  1. 安装yum源
sudo yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-$(rpm -E %{rhel})-x86_64/pgdg-redhat-repo-latest.noarch.rpm
  1. 安装TimescaleDB资源库
sudo tee /etc/yum.repos.d/timescale_timescaledb.repo <<EOL
[timescale_timescaledb]
name=timescale_timescaledb
baseurl=https://packagecloud.io/timescale/timescaledb/el/$(rpm -E %{rhel})/\$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
  1. 更新
sudo yum update
  1. YUM安装TimescaleDB
sudo yum install timescaledb-2-postgresql-15
# 同时安装postgres15
# sudo yum install timescaledb-2-postgresql-16 postgresql16
  1. 初始化数据库(已经安装过了就跳过)
sudo /usr/pgsql-16/bin/postgresql-15-setup initdb
  1. 将PostgreSQL调成TimescaleDB
# 如果修改过data目录可以通过 -conf-path 指定位置,否在找不到。
sudo timescaledb-tune --pg-config=/usr/pgsql-15/bin/pg_config -conf-path=/home/pgsql/data/postgresql.conf
  1. 重启数据库
systemctl restart postgresql-15
  1. 添加数据库拓展
# 登录
psql -U postgres -h 127.0.0.1 -p 5432
-- Add TimescaleDB to the database
CREATE EXTENSION IF NOT EXISTS timescaledb;

-- Check that TimescaleDB is installed
postgres=# \dx
                                               List of installed extensions
    Name     | Version |   Schema   |                                     Description                                      
-------------+---------+------------+--------------------------------------------------------------------------------------
 plpgsql     | 1.0     | pg_catalog | PL/pgSQL procedural language
 timescaledb | 2.15.2  | public     | Enables scalable inserts and complex queries for time-series data (Apache 2 Edition)
(2 rows)