postgresql 11 and zabbix 5.4 database connection issues

[xnobodyx]

hello,

just set up a fresh zabbix 5.4 install on ubuntu (mate) 18.04 with ngninx 1.14.0, PHP Version 7.2.24, timescaledb-2-2.3.1, and postgresql 11.13 (full install log at the end of this post)

everything seemed to go well until after logging into the web interface for the first time after setup. the system information panel showed: zabbix server is running - no and the dreaded warning zabbix server is not running:the information displayed may not be current showed up at the bottom of the web interface (i have read through https://www.zabbix.com/forum/zabbix-...ot-be-current/ for the record)

zabbix_server.log showed:

connection to database 'zabbix' failed: [0] connection to server at "localhost" (127.0.0.1), port 5432 failed: FATAL: password authentication failed for user "zabbix"
connection to server at "localhost" (127.0.0.1), port 5432 failed: FATAL: password authentication failed for user "zabbix"

password seemed to be entered correctly in conf files since the web installer loaded and was able to connect to the database to get to the main GUI interface. nonetheless double and triple checked DBPassword="test" in /etc/zabbix/zabbix_server.conf and $DB['PASSWORD'] = 'test'; in /var/www/html/zabbix/conf/zabbix.conf.php

then began dabbling with /etc/postgresql/11/main/pg_hba.conf which by default is

# Database administrative login by Unix domain socket
local all postgres peer

# TYPE DATABASE USER ADDRESS METHOD

# "local" is for Unix domain socket connections only
local all all peer
# IPv4 local connections:
host all all 127.0.0.1/32 md5
# IPv6 local connections:
host all all ::1/128 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all peer
host replication all 127.0.0.1/32 md5
host replication all ::1/128 md5

changed all the peer and md5 entries to trust for testing . this fixed the 'password authentication failed for user "zabbix"'error, the zabbix web interface loads and the server shows as running, but after a few seconds the database connection drops, with a new error in the zabbix_server.log :

connection to database 'zabbix' failed: [0] connection to server at "localhost" (127.0.0.1), port 5432 failed: FATAL: remaining connection slots are reserved for non-replication superuser connections

the only fix was to stop the zabbix-server service, log into psql ( sudo -u postgres psql ) and drop all connections

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'zabbix'
AND pid <> pg_backend_pid();

any help figuring out how to get this final config tweaked correctly is greatly appreciated.

-----------------------------------

here is the full install log if it is of use to anyone - a few things had to be tweaked from the official guide/other guides online (maybe i tweaked them wrong):

1. Install PostgreSQL 11

sudo apt install postgresql-common && sudo sh /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh && sudo apt update
sudo apt install postgresql-11 postgresql-client-11
sudo systemctl enable --now postgresql

sudo -u postgres psql -V

returns:
psql (PostgreSQL) 11.13 (Ubuntu 11.13-1.pgdg18.04+1)


2. Install TimescaleDB

sudo add-apt-repository ppa:timescale/timescaledb-ppa
sudo apt update
sudo apt install timescaledb-2-2.3.1-postgresql-11

3. Configure PostgresSQL

sudo nano /etc/postgresql/11/main/postgresql.conf

add line 617 shared_preload_libraries = 'timescaledb'

sudo systemctl restart postgresql

4. timescaledb-tune

Install Go (32-bit in this case - find link for x64 version at https://golang.org/dl/ if necessary)

cd ~
wget https://golang.org/dl/go1.17.2.linux-386.tar.gz
sudo tar -C /usr/local -xzf go1.17.2.linux-386.tar.gz
export PATH=$PATH:/usr/local/go/bin
go install github.com/timescale/timescaledb-tune/cmd/timescaledb-tune@latest
cd go/bin
sudo ./timescaledb-tune

y for correct if postgresql conf is listed correctly
y to tune memory
y to all prompts

sudo service postgresql restart

5. Install Zabbix

cd ~
wget https://repo.zabbix.com/zabbix/5.4/ubuntu/pool/main/z/zabbix-release/zabbix-release_5.4-1+ubuntu18.04_all.deb
sudo dpkg -i zabbix-release_5.4-1+ubuntu18.04_all.deb
sudo apt update
sudo apt install zabbix-server-pgsql zabbix-frontend-php php7.2-pgsql zabbix-nginx-conf zabbix-sql-scripts zabbix-agent
sudo -u postgres createuser --pwprompt zabbix

entered password: test

sudo -u postgres createdb -O zabbix zabbix
sudo zcat /usr/share/doc/zabbix-sql-scripts/postgresql/create.sql.gz | sudo -u zabbix psql zabbix
echo "CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;" | sudo -u postgres psql zabbix

Fix timescaledb.sql (wasn't a gz file for some reason) -

cd /usr/share/doc/zabbix-sql-scripts/postgresql/
sudo gzip timescaledb.sql
zcat /usr/share/doc/zabbix-sql-scripts/postgresql/timescaledb.sql.gz | sudo -u zabbix psql zabbix

6. Edit various conf files

sudo nano /etc/zabbix/zabbix_server.conf

Line 129 Add DBPassword (needs to be in quotes)
Line 434 CacheSize=32M

sudo nano /etc/zabbix/nginx.conf

uncomment and set 'listen' and 'server_name' directives.

# listen 80;
# server_name example.com;

sudo nano /etc/nginx/nginx.conf

add client_max_body_size 32M; to basic settings

sudo nano /etc/nginx/sites-available/default

line 44 add index.php
uncomment line 56, 57, 60 and 63 change php7.0 on line 60 to php7.2

sudo nano /etc/php/7.2/fpm/php.ini

# line 380
max_execution_time = 300
# line 401
memory_limit = 256M
# line 669
post_max_size = 32M
# line 822
upload_max_filesize = 32M
# line 390
max_input_time = 300

7. Enable and start services

sudo systemctl enable --now zabbix-server zabbix-agent nginx php7.2-fpm

8. Copy frontend files

sudo cp -pr /usr/share/zabbix /var/www/html/zabbix

9. Check and enable firewall access

sudo ufw status verbose
sudo ufw allow from [ip] to any port 80

10. restart all services just to make sure

sudo systemctl restart zabbix-server zabbix-agent nginx php7.2-fpm postgresql

11. Web Interface setup

Go to http://localhost/zabbix

Choose language
makes sure prerequisites are ok
Configure DB Connection
type: postgreSQL
use defaults
Zabbix server details
name: zabbix
Set time zone and theme

[xnobodyx]

was able to get the remaining connection slots are reserved for non-replication superuser connections error resolved by changing max_connections from 25 to 100:

`sudo nano /etc/postgresql/11/main/postgresql.conf`

line 64 - change max_connections to 100

everything seems to be working now but not sure if this is the ideal fix, or what the ideal settings in pg_hba.conf should be, if anyone has any tips.

[splitek]

Fix is ok. There is no universal settings (default is 100).
25 connections? https://github.com/timescale/timescaledb-tune/issues/47

[xnobodyx]

thank you, wasn't paying enough attention when timescaledb-tune prompted for that change.

will do further research on correct config for pg_hba.conf