Problem installing Zabbix using Postgresql

[ktenney]

Howdy,

Ubuntu 14.04
Installed zabbix-server-pgsql, zabbix-frontend-php from repo.zabbix.com

http://host/zabbix/setup.php

At #3, 'Configure DB connection', only choice is 'MySQL'

What am I missing?

Thanks,
Kent

[Atsushi]

Please try install php5-pgsql package.

[ktenney]

Thank you, now the postgresql option is seen

next, get the db created and configured correctly ...

[Hal0n]

3.0.0 PostgreSQL installation is just plain broken

The manual points to three .sql schema files that just plain don't exist. Found the forum post that points out that you can:

cd /usr/share/doc/zabbix-server-pgsql
gunzip create.sql.gz
psql -U zabbix -h localhost zabbix < create.sql

I was thrilled to see that my http://<fqdn-hostname>/zabbix page was now available. I fixed the Timezone setting, and found myself with only a choice of MySQL.

Ok, so now I find myself on this thread, and I install the 'php5-pgsql' package. Great! Now I have PostgreSQL as an option. Except now I have:

Cannot connect to the database.
Details
The frontend does not match Zabbix database.

I have not ever even downloaded any version of Zabbix besides 3.0.0. There is no possible way that this is an artifact of a botched upgrade because it is a 100% clean install.

[Stefan_D]

The manual points to three .sql schema files that just plain don't exist. Found the forum post that points out that you can:

cd /usr/share/doc/zabbix-server-pgsql
gunzip create.sql.gz
psql -U zabbix -h localhost zabbix < create.sql

I was thrilled to see that my http://<fqdn-hostname>/zabbix page was now available. I fixed the Timezone setting, and found myself with only a choice of MySQL.

Ok, so now I find myself on this thread, and I install the 'php5-pgsql' package. Great! Now I have PostgreSQL as an option. Except now I have:

Cannot connect to the database.
Details
The frontend does not match Zabbix database.

I have not ever even downloaded any version of Zabbix besides 3.0.0. There is no possible way that this is an artifact of a botched upgrade because it is a 100% clean install.

Hi,

which distribution do you use? One of my test machines for example is a SLES12 box. The necessary .sql files are located at:

/usr/share/doc/packages/zabbix30-server/postgresql/

maybe you have to use find on your /usr directory.

Cheers,

SD

[Hal0n]

Missing SQL Import Statements for Debian + PostgreSQL on 3.0.0

Actually I did a find for all .sql files before.

root@zabbix-test:/usr/share/doc# find / -name *.sql*
/usr/share/postgresql/9.5/system_views.sql
/usr/share/postgresql/9.5/extension/intagg--unpackaged--1.0.sql
/usr/share/postgresql/9.5/extension/dict_int--1.0.sql
/usr/share/postgresql/9.5/extension/btree_gist--1.0--1.1.sql
/usr/share/postgresql/9.5/extension/dblink--1.0--1.1.sql
/usr/share/postgresql/9.5/extension/lo--1.0.sql
/usr/share/postgresql/9.5/extension/autoinc--1.0.sql
/usr/share/postgresql/9.5/extension/citext--unpackaged--1.0.sql
/usr/share/postgresql/9.5/extension/pg_freespacemap--unpackaged--1.0.sql
/usr/share/postgresql/9.5/extension/pg_buffercache--1.1.sql
/usr/share/postgresql/9.5/extension/fuzzystrmatch--1.0.sql
/usr/share/postgresql/9.5/extension/dblink--unpackaged--1.0.sql
/usr/share/postgresql/9.5/extension/pg_trgm--1.1.sql
/usr/share/postgresql/9.5/extension/pageinspect--1.3.sql
/usr/share/postgresql/9.5/extension/pg_stat_statements--unpackaged--1.0.sql
/usr/share/postgresql/9.5/extension/pg_prewarm--1.0.sql
/usr/share/postgresql/9.5/extension/pageinspect--1.2--1.3.sql
/usr/share/postgresql/9.5/extension/dblink--1.1.sql
/usr/share/postgresql/9.5/extension/pg_stat_statements--1.2--1.3.sql
/usr/share/postgresql/9.5/extension/sslinfo--1.0.sql
/usr/share/postgresql/9.5/extension/intarray--1.0.sql
/usr/share/postgresql/9.5/extension/hstore--1.2--1.3.sql
/usr/share/postgresql/9.5/extension/xml2--1.0.sql
/usr/share/postgresql/9.5/extension/pageinspect--1.0--1.1.sql
/usr/share/postgresql/9.5/extension/pgrowlocks--unpackaged--1.0.sql
/usr/share/postgresql/9.5/extension/cube--unpackaged--1.0.sql
/usr/share/postgresql/9.5/extension/tsm_system_time--1.0.sql
/usr/share/postgresql/9.5/extension/pgcrypto--unpackaged--1.0.sql
/usr/share/postgresql/9.5/extension/pgrowlocks--1.1.sql
/usr/share/postgresql/9.5/extension/unaccent--1.0.sql
/usr/share/postgresql/9.5/extension/ltree--1.0.sql
/usr/share/postgresql/9.5/extension/pgstattuple--1.2--1.3.sql
/usr/share/postgresql/9.5/extension/hstore--1.0--1.1.sql
/usr/share/postgresql/9.5/extension/file_fdw--1.0.sql
/usr/share/postgresql/9.5/extension/pageinspect--unpackaged--1.0.sql
/usr/share/postgresql/9.5/extension/chkpass--unpackaged--1.0.sql
/usr/share/postgresql/9.5/extension/earthdistance--1.0.sql
/usr/share/postgresql/9.5/extension/pg_trgm--1.0--1.1.sql
/usr/share/postgresql/9.5/extension/postgres_fdw--1.0.sql
/usr/share/postgresql/9.5/extension/isn--unpackaged--1.0.sql
/usr/share/postgresql/9.5/extension/moddatetime--unpackaged--1.0.sql
/usr/share/postgresql/9.5/extension/dict_xsyn--1.0.sql
/usr/share/postgresql/9.5/extension/refint--unpackaged--1.0.sql
/usr/share/postgresql/9.5/extension/intarray--unpackaged--1.0.sql
/usr/share/postgresql/9.5/extension/tsearch2--1.0.sql
/usr/share/postgresql/9.5/extension/pgstattuple--1.3.sql
/usr/share/postgresql/9.5/extension/insert_username--unpackaged--1.0.sql
/usr/share/postgresql/9.5/extension/pgcrypto--1.2.sql
/usr/share/postgresql/9.5/extension/pg_stat_statements--1.1--1.2.sql
/usr/share/postgresql/9.5/extension/pgstattuple--1.0--1.1.sql
/usr/share/postgresql/9.5/extension/autoinc--unpackaged--1.0.sql
/usr/share/postgresql/9.5/extension/adminpack--1.0.sql
/usr/share/postgresql/9.5/extension/pg_buffercache--1.0--1.1.sql
/usr/share/postgresql/9.5/extension/timetravel--1.0.sql
/usr/share/postgresql/9.5/extension/uuid-ossp--unpackaged--1.0.sql
/usr/share/postgresql/9.5/extension/hstore--1.1--1.2.sql
/usr/share/postgresql/9.5/extension/pg_trgm--unpackaged--1.0.sql
/usr/share/postgresql/9.5/extension/pgcrypto--1.1--1.2.sql
/usr/share/postgresql/9.5/extension/citext--1.0--1.1.sql
/usr/share/postgresql/9.5/extension/seg--unpackaged--1.0.sql
/usr/share/postgresql/9.5/extension/chkpass--1.0.sql
/usr/share/postgresql/9.5/extension/hstore--1.3.sql
/usr/share/postgresql/9.5/extension/insert_username--1.0.sql
/usr/share/postgresql/9.5/extension/plpgsql--1.0.sql
/usr/share/postgresql/9.5/extension/tablefunc--1.0.sql
/usr/share/postgresql/9.5/extension/moddatetime--1.0.sql
/usr/share/postgresql/9.5/extension/cube--1.0.sql
/usr/share/postgresql/9.5/extension/timetravel--unpackaged--1.0.sql
/usr/share/postgresql/9.5/extension/pgstattuple--1.1--1.2.sql
/usr/share/postgresql/9.5/extension/pg_buffercache--unpackaged--1.0.sql
/usr/share/postgresql/9.5/extension/btree_gin--1.0.sql
/usr/share/postgresql/9.5/extension/pgstattuple--unpackaged--1.0.sql
/usr/share/postgresql/9.5/extension/pg_stat_statements--1.3.sql
/usr/share/postgresql/9.5/extension/unaccent--unpackaged--1.0.sql
/usr/share/postgresql/9.5/extension/sslinfo--unpackaged--1.0.sql
/usr/share/postgresql/9.5/extension/seg--1.0.sql
/usr/share/postgresql/9.5/extension/ltree--unpackaged--1.0.sql
/usr/share/postgresql/9.5/extension/pgcrypto--1.0--1.1.sql
/usr/share/postgresql/9.5/extension/xml2--unpackaged--1.0.sql
/usr/share/postgresql/9.5/extension/intagg--1.0.sql
/usr/share/postgresql/9.5/extension/tablefunc--unpackaged--1.0.sql
/usr/share/postgresql/9.5/extension/dict_xsyn--unpackaged--1.0.sql
/usr/share/postgresql/9.5/extension/lo--unpackaged--1.0.sql
/usr/share/postgresql/9.5/extension/plpgsql--unpackaged--1.0.sql
/usr/share/postgresql/9.5/extension/uuid-ossp--1.0.sql
/usr/share/postgresql/9.5/extension/fuzzystrmatch--unpackaged--1.0.sql
/usr/share/postgresql/9.5/extension/pgrowlocks--1.0--1.1.sql
/usr/share/postgresql/9.5/extension/hstore--unpackaged--1.0.sql
/usr/share/postgresql/9.5/extension/btree_gist--unpackaged--1.0.sql
/usr/share/postgresql/9.5/extension/btree_gin--unpackaged--1.0.sql
/usr/share/postgresql/9.5/extension/dict_int--unpackaged--1.0.sql
/usr/share/postgresql/9.5/extension/tsm_system_rows--1.0.sql
/usr/share/postgresql/9.5/extension/isn--1.0.sql
/usr/share/postgresql/9.5/extension/pg_freespacemap--1.0.sql
/usr/share/postgresql/9.5/extension/tcn--1.0.sql
/usr/share/postgresql/9.5/extension/btree_gist--1.1.sql
/usr/share/postgresql/9.5/extension/citext--1.1.sql
/usr/share/postgresql/9.5/extension/pageinspect--1.1--1.2.sql
/usr/share/postgresql/9.5/extension/refint--1.0.sql
/usr/share/postgresql/9.5/extension/tsearch2--unpackaged--1.0.sql
/usr/share/postgresql/9.5/extension/earthdistance--unpackaged--1.0.sql
/usr/share/postgresql/9.5/extension/pg_stat_statements--1.0--1.1.sql
/usr/share/postgresql/9.5/conversion_create.sql
/usr/share/postgresql/9.5/information_schema.sql
/usr/share/postgresql/9.5/snowball_create.sql
/usr/share/postgresql/9.4/system_views.sql
/usr/share/postgresql/9.4/extension/pgstattuple--1.2.sql
/usr/share/postgresql/9.4/extension/intagg--unpackaged--1.0.sql
/usr/share/postgresql/9.4/extension/dict_int--1.0.sql
/usr/share/postgresql/9.4/extension/dblink--1.0--1.1.sql
/usr/share/postgresql/9.4/extension/lo--1.0.sql
/usr/share/postgresql/9.4/extension/autoinc--1.0.sql
/usr/share/postgresql/9.4/extension/citext--unpackaged--1.0.sql
/usr/share/postgresql/9.4/extension/pg_freespacemap--unpackaged--1.0.sql
/usr/share/postgresql/9.4/extension/fuzzystrmatch--1.0.sql
/usr/share/postgresql/9.4/extension/dblink--unpackaged--1.0.sql
/usr/share/postgresql/9.4/extension/pg_trgm--1.1.sql
/usr/share/postgresql/9.4/extension/pg_stat_statements--unpackaged--1.0.sql
/usr/share/postgresql/9.4/extension/pg_prewarm--1.0.sql
/usr/share/postgresql/9.4/extension/btree_gist--1.0.sql
/usr/share/postgresql/9.4/extension/dblink--1.1.sql
/usr/share/postgresql/9.4/extension/sslinfo--1.0.sql
/usr/share/postgresql/9.4/extension/intarray--1.0.sql
/usr/share/postgresql/9.4/extension/hstore--1.2--1.3.sql
/usr/share/postgresql/9.4/extension/xml2--1.0.sql
/usr/share/postgresql/9.4/extension/pageinspect--1.0--1.1.sql
/usr/share/postgresql/9.4/extension/pgrowlocks--unpackaged--1.0.sql
/usr/share/postgresql/9.4/extension/cube--unpackaged--1.0.sql
/usr/share/postgresql/9.4/extension/pgcrypto--unpackaged--1.0.sql
/usr/share/postgresql/9.4/extension/pgrowlocks--1.1.sql
/usr/share/postgresql/9.4/extension/unaccent--1.0.sql
/usr/share/postgresql/9.4/extension/ltree--1.0.sql
/usr/share/postgresql/9.4/extension/worker_spi--1.0.sql
/usr/share/postgresql/9.4/extension/hstore--1.0--1.1.sql
/usr/share/postgresql/9.4/extension/file_fdw--1.0.sql
/usr/share/postgresql/9.4/extension/pageinspect--unpackaged--1.0.sql
/usr/share/postgresql/9.4/extension/pg_buffercache--1.0.sql
/usr/share/postgresql/9.4/extension/chkpass--unpackaged--1.0.sql
/usr/share/postgresql/9.4/extension/earthdistance--1.0.sql
/usr/share/postgresql/9.4/extension/pg_trgm--1.0--1.1.sql
/usr/share/postgresql/9.4/extension/postgres_fdw--1.0.sql
/usr/share/postgresql/9.4/extension/isn--unpackaged--1.0.sql
/usr/share/postgresql/9.4/extension/moddatetime--unpackaged--1.0.sql
/usr/share/postgresql/9.4/extension/dict_xsyn--1.0.sql
/usr/share/postgresql/9.4/extension/refint--unpackaged--1.0.sql
/usr/share/postgresql/9.4/extension/intarray--unpackaged--1.0.sql
/usr/share/postgresql/9.4/extension/tsearch2--1.0.sql
/usr/share/postgresql/9.4/extension/pgcrypto--1.1.sql
/usr/share/postgresql/9.4/extension/insert_username--unpackaged--1.0.sql
/usr/share/postgresql/9.4/extension/pg_stat_statements--1.1--1.2.sql
/usr/share/postgresql/9.4/extension/pgstattuple--1.0--1.1.sql
/usr/share/postgresql/9.4/extension/autoinc--unpackaged--1.0.sql
/usr/share/postgresql/9.4/extension/adminpack--1.0.sql
/usr/share/postgresql/9.4/extension/timetravel--1.0.sql
/usr/share/postgresql/9.4/extension/uuid-ossp--unpackaged--1.0.sql
/usr/share/postgresql/9.4/extension/hstore--1.1--1.2.sql
/usr/share/postgresql/9.4/extension/pg_trgm--unpackaged--1.0.sql
/usr/share/postgresql/9.4/extension/citext--1.0--1.1.sql
/usr/share/postgresql/9.4/extension/seg--unpackaged--1.0.sql
/usr/share/postgresql/9.4/extension/chkpass--1.0.sql
/usr/share/postgresql/9.4/extension/hstore--1.3.sql
/usr/share/postgresql/9.4/extension/insert_username--1.0.sql
/usr/share/postgresql/9.4/extension/plpgsql--1.0.sql
/usr/share/postgresql/9.4/extension/tablefunc--1.0.sql
/usr/share/postgresql/9.4/extension/moddatetime--1.0.sql
/usr/share/postgresql/9.4/extension/cube--1.0.sql
/usr/share/postgresql/9.4/extension/timetravel--unpackaged--1.0.sql
/usr/share/postgresql/9.4/extension/pageinspect--1.2.sql
/usr/share/postgresql/9.4/extension/pgstattuple--1.1--1.2.sql
/usr/share/postgresql/9.4/extension/pg_stat_statements--1.2.sql
/usr/share/postgresql/9.4/extension/pg_buffercache--unpackaged--1.0.sql
/usr/share/postgresql/9.4/extension/btree_gin--1.0.sql
/usr/share/postgresql/9.4/extension/pgstattuple--unpackaged--1.0.sql
/usr/share/postgresql/9.4/extension/unaccent--unpackaged--1.0.sql
/usr/share/postgresql/9.4/extension/sslinfo--unpackaged--1.0.sql
/usr/share/postgresql/9.4/extension/seg--1.0.sql
/usr/share/postgresql/9.4/extension/ltree--unpackaged--1.0.sql
/usr/share/postgresql/9.4/extension/pgcrypto--1.0--1.1.sql
/usr/share/postgresql/9.4/extension/xml2--unpackaged--1.0.sql
/usr/share/postgresql/9.4/extension/intagg--1.0.sql
/usr/share/postgresql/9.4/extension/tablefunc--unpackaged--1.0.sql
/usr/share/postgresql/9.4/extension/citext--1.1--1.0.sql
/usr/share/postgresql/9.4/extension/dict_xsyn--unpackaged--1.0.sql
/usr/share/postgresql/9.4/extension/lo--unpackaged--1.0.sql
/usr/share/postgresql/9.4/extension/plpgsql--unpackaged--1.0.sql
/usr/share/postgresql/9.4/extension/uuid-ossp--1.0.sql
/usr/share/postgresql/9.4/extension/fuzzystrmatch--unpackaged--1.0.sql
/usr/share/postgresql/9.4/extension/pgrowlocks--1.0--1.1.sql
/usr/share/postgresql/9.4/extension/hstore--unpackaged--1.0.sql
/usr/share/postgresql/9.4/extension/btree_gist--unpackaged--1.0.sql
/usr/share/postgresql/9.4/extension/btree_gin--unpackaged--1.0.sql
/usr/share/postgresql/9.4/extension/dict_int--unpackaged--1.0.sql
/usr/share/postgresql/9.4/extension/citext--1.0.sql
/usr/share/postgresql/9.4/extension/isn--1.0.sql
/usr/share/postgresql/9.4/extension/pg_freespacemap--1.0.sql
/usr/share/postgresql/9.4/extension/test_shm_mq--1.0.sql
/usr/share/postgresql/9.4/extension/tcn--1.0.sql
/usr/share/postgresql/9.4/extension/citext--1.1.sql
/usr/share/postgresql/9.4/extension/pageinspect--1.1--1.2.sql
/usr/share/postgresql/9.4/extension/refint--1.0.sql
/usr/share/postgresql/9.4/extension/tsearch2--unpackaged--1.0.sql
/usr/share/postgresql/9.4/extension/earthdistance--unpackaged--1.0.sql
/usr/share/postgresql/9.4/extension/test_parser--1.0.sql
/usr/share/postgresql/9.4/extension/test_parser--unpackaged--1.0.sql
/usr/share/postgresql/9.4/extension/pg_stat_statements--1.0--1.1.sql
/usr/share/postgresql/9.4/conversion_create.sql
/usr/share/postgresql/9.4/information_schema.sql
/usr/share/postgresql/9.4/snowball_create.sql
/usr/share/doc/zabbix-server-pgsql/create.sql

I carefully documented my install so far:
sudo su -
wget http://repo.zabbix.com/zabbix/3.0/de...wheezy_all.deb
dpkg -i zabbix-release_3.0-1+wheezy_all.deb
apt-get update
apt-get install zabbix-server-pgsql
apt-get install zabbix-frontend-php
apt-get install zabbix-agent
apt-get install php5-pgsql

cd /etc/apt/sources.list.d
vi pgdg.list
### paste the following line into pgdg.list and then write quit ###
deb http://apt.postgresql.org/pub/repos/apt/ wheezy-pgdg main
### end of vi session (:wq) ###

vi ACCC4CF8.asc
### paste contents of clipboard (:wq) ###
apt-key add /root/ACCC4CF8.asc
apt-get update
apt-get install postgresql-client-9.4
apt-get install postgresql-9.4

su - postgres
psql
CREATE USER zabbix PASSWORD '<password>';
CREATE SCHEMA zabbix;
GRANT ALL ON ALL TABLES IN SCHEMA zabbix TO zabbix;
ALTER USER zabbix CREATEDB;
CREATE DATABASE zabbix;
\q

cd /usr/share/doc/zabbix-server-pgsql
gunzip create.sql.gz
psql -U zabbix -h localhost zabbix < create.sql

[Hal0n]

Solution my manager came up with for Debian + Postgres on 3.0.1

Made sure only Postgres 9.5 was installed. (.4 was still installed, hadn't been replaced.

user@zabbix-test:~$ sudo -u postgres psql
psql (9.5.1)
Type "help" for help.

postgres=# create role zabbix;
CREATE ROLE
postgres=# alter role zabbix with password 'xxxxx';
ALTER ROLE
postgres=# alter role zabbix with superuser;
ALTER ROLE
postgres=# alter role zabbix with CREATEDB;
ALTER ROLE
postgres=# alter role zabbix with CREATEROLE;
ALTER ROLE
postgres=# create database zabbix with owner zabbix;
CREATE DATABASE

user@zabbix-test:~$ wget http://downloads.sourceforge.net/pro...se_mirror=iweb
user@zabbix-test:~$ tar -xzf zabbix-3.0.1.tar.gz
user@zabbix-test:~$ cd zabbix-3.0.1


user@zabbix-test:~/zabbix-3.0.1$ cd database/postgresql
user@zabbix-test:~/zabbix-3.0.1/database/postgresql$ ls -l
total 2920
-rw-r--r-- 1 user users 893005 Feb 26 03:46 data.sql
-rw-r--r-- 1 user users 1979089 Feb 26 03:45 images.sql
-rw-r--r-- 1 user users 109366 Feb 26 03:46 schema.sql
user@zabbix-test:~/zabbix-3.0.1/database/postgresql$ sudo -u postgres psql zabbix < schema.sql
user@zabbix-test:~/zabbix-3.0.1/database/postgresql$ sudo -u postgres psql zabbix < images.sql
user@zabbix-test:~/zabbix-3.0.1/database/postgresql$ sudo -u postgres psql zabbix < data.sql

[pester]

Same problem here but turns out user zabbix wasn't permitted to login.

Found this in /var/log/postgresql/postgresql-9.4-main.log

2016-03-21 15:51:27 CDT [12582-1] zabbix@zabbix FATAL: role "zabbix" is not permitted to log in

Fix was simple:


postgres=# CREATE ROLE zabbix WITH LOGIN;
ERROR: role "zabbix" already exists
postgres=# ALTER ROLE zabbix LOGIN;
ALTER ROLE
postgres=#

[Karl_MCS]

For Ubuntu Bionic and Zabbix 3.4, this package is php-pgsql. Needs to be added to official documentation.

[dimir]

php-pgsql is automatically installed with zabbix-frontend-php as of version 3.2.3