【Zabbixサーバー構築 #6】データベースサーバーとしてPostgreSQLとTImescaleDBをインストールする

概要

監視サーバーZabbixのデータの保存先として、データベースサーバーのPostgreSQLと、その拡張機能であるTimescaleDBをインストールします。

環境

ソフトウェア

インストール

PostgreSQLサーバー本体

PostgreSQLサーバーは、Debian標準のレポジトリにあるバージョン１３を利用します。

# apt install postgresql-13

TimescaleDB

TImescaleDBのサイトにある手順をもとにインストールします。

# sh -c "echo 'deb [signed-by=/usr/share/keyrings/timescale.keyring] https://packagecloud.io/timescale/timescaledb/debian/ $(lsb_release -c -s) main' > /etc/apt/sources.list.d/timescaledb.list"
# wget --quiet -O - https://packagecloud.io/timescale/timescaledb/gpgkey | sudo gpg --dearmor -o /usr/share/keyrings/timescale.keyring
# apt update
# apt install timescaledb-2-postgresql-13

PostgreSQLにTImescaleDBを組み込むための設定をします。

# timescaledb-tune -yes
Using postgresql.conf at this path:
/etc/postgresql/13/main/postgresql.conf

Writing backup to:
/tmp/timescaledb_tune.backup202111070825

shared_preload_libraries needs to be updated
Current:
#shared_preload_libraries = ''
Recommended:
shared_preload_libraries = 'timescaledb'
success: shared_preload_libraries will be updated

Recommendations based on 3.66 GB of available memory and 2 CPUs for PostgreSQL 13

Memory settings recommendations
Current:
shared_buffers = 128MB
#effective_cache_size = 4GB
#maintenance_work_mem = 64MB
#work_mem = 4MB
Recommended:
shared_buffers = 959609kB
effective_cache_size = 2811MB
maintenance_work_mem = 479804kB
work_mem = 9596kB
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 = 8
max_worker_processes = 13
max_parallel_workers_per_gather = 1
max_parallel_workers = 2
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
success: WAL settings will be updated

Miscellaneous settings recommendations
Current:
#default_statistics_target = 100
#random_page_cost = 4.0
#checkpoint_completion_target = 0.5
max_connections = 100
#max_locks_per_transaction = 64
#autovacuum_max_workers = 3
#autovacuum_naptime = 1min
#effective_io_concurrency = 1
Recommended:
default_statistics_target = 500
random_page_cost = 1.1
checkpoint_completion_target = 0.9
max_connections = 50
max_locks_per_transaction = 64
autovacuum_max_workers = 10
autovacuum_naptime = 10
effective_io_concurrency = 256
success: miscellaneous settings will be updated
Saving changes to: /etc/postgresql/13/main/postgresql.conf

有効にするためにPostgreSQLサーバーを再起動します。

# systemctl restart postgresql@13-main

以上で終了です。