Red Hat Enterprise Linux Server release 6.3 - 64 bit, Postgresql 9.2.4, Zabbix 2.2.1, php 5.3.3, repozitory EPEL - http://dl.fedoraproject.org/pub/epel/6/x86_64/

The schema name 'public' is also in php frontend file include/classes/db/PostgresqlDbBackend.php. Setting frontend parametr $DB["SCHEMA"] in zabbix.conf.php has no effect and is applicable for DB2 only. Is possible allow usage of this parametr for postgresql too?

I think more approach could be useful if you want to reproduce the error. Let's assume that I need new fresh installation of zabbix server 2.2.x.
Here are steps to be done to get described error above:

1. connect to postgresql by superuser 'postgres' and create db user zabbix_tester - CREATE ROLE zabbix_tester WITH NOSUPERUSER; ALTER ROLE zabbix_tester WITH LOGIN;\password zabbix_tester
2. create db called 'zabbix_test' - CREATE DATABASE zabbix_test TEMPLATE template0 OWNER postgres;
3. grant all rights - GRANT ALL ON DATABASE zabbix_test TO zabbix_tester;
4. logout from postgres and login again to newly created db - psql -U postgres -d zabbix_test
5. create schema with same name like user role 'zabbix_tester' - CREATE SCHEMA zabbix_tester AUTHORIZATION zabbix_tester;
6. drop unsecured public schema - DROP SCHEMA public;
7. probably will be necessary modification of access file pg_hba.conf and reload db settings
8. test login of zabbix_tester to zabbix_test db and list schemas search_path. Should be '"$user",public' - psql -U zabbix_tester -d zabbix_test / SHOW search_path;
9. load schema, images,data 'cat schema.sql | psql -U zabbix_tester -d zabbix_test' .....
10. edit zabbix_server.conf db options and then try to run zabbix_server:
/etc/init.d/zabbix-server start && tail -f /var/log/zabbix/zabbix_server.log

Starting Zabbix server: [ OK ]
8076:20140220:081033.089 IPMI monitoring: YES
8076:20140220:081033.089 WEB monitoring: YES
8076:20140220:081033.089 VMware monitoring: YES
8076:20140220:081033.089 Jabber notifications: NO
8076:20140220:081033.089 Ez Texting notifications: YES
8076:20140220:081033.089 ODBC: NO
8076:20140220:081033.089 SSH2 support: YES
8076:20140220:081033.089 IPv6 support: YES
8076:20140220:081033.089 ******************************
8076:20140220:081033.089 using configuration file: /etc/zabbix_server.conf
8076:20140220:081033.110 [Z3005] query failed: [0] PGRES_FATAL_ERROR:ERROR: relation "dbversion" already exists
[create table dbversion (
mandatory integer default '0' not null,
optional integer default '0' not null
)]

Explicit searching for tables in schema 'public' will fail immediatelly.

Got same issue, here a patch which adds Schema support for PostgreSQL in

• zabbix server
• php UI

Dakol, thanks for your effort and this enablement patch. This is really issue and is necessary either to solve it or leave usage of postgresql for zabbix 2.2.x. I'll try your patch ASAP.

This issue will always arise for zabbix 2.2.x, either for new installation or upgrade existing one.

@Tomas Pipo: is my patch working for you ?

Yes!, thank you for this diff. I hope, that it'll be included in next versions soon.

Implemented in svn://svn.zabbix.com/branches/dev/ZBX-7794

The DBSchema parameter in server and proxy configurations now applies to PostgreSQL databases as well as IBM DB2.

(1) Frontend code must be adjusted accordingly.

kristsk RESOLVED in r45953, branch svn://svn.zabbix.com/branches/dev/ZBXNEXT-2254.

oleg.egorov CLOSED

Thank you very much for such fast implementation.
Ragards Tom

(2) It would be better not forcing CONFIG_SCHEMA to "public" by default to keep the changes to minimum

jurism Instead of settings a default value upon detecting an empty parameter it is supplied during checking for the existence of a table. RESOLVED.

wiper CLOSED

(3) SQL statements must be written in lowercase

jurism Corrected. RESOLVED.

wiper CLOSED

The development branch of this issue is now at svn://svn.zabbix.com/branches/dev/ZBXNEXT-2254

(4) DBfield_exists() also should check for schema name when working with postgresql databases.

jurism Filter added. RESOLVED.

wiper CLOSED, please review my changes in r45894

Successfully tested

Fix merged in 2.3.1 (trunk) r45899

(5) Documentation should be updated to show that in case PostgreSQL is used and $DB['SCHEMA'] in zabbix.conf.php is '' (empty string) default value 'public' is used for schema.

Also commands to make psql use specific schema when creating database schema and loading initial data are:

cat database/postgresql/schema.sql | PGOPTIONS='-c search_path=some_custom_schema' psql -d zabbix_database_name -h postgresql.server.host -U postgres_user
cat database/postgresql/images.sql | PGOPTIONS='-c search_path=some_custom_schema' psql -d zabbix_database_name -h postgresql.server.host -U postgres_user
cat database/postgresql/data.sql | PGOPTIONS='-c search_path=some_custom_schema' psql -d zabbix_database_name -h postgresql.server.host -U postgres_user

(6) Compiler gives a warning when server is compiled with MySQL:

db.c: In function ‘zbx_db_connect’:
db.c:212:6: warning: unused variable ‘rc’ [-Wunused-variable]
  int  rc, ret = ZBX_DB_OK;
      ^

jurism Variable moved to the correct part of code. Fixed in 2.3.1 (trunk) r45998.

asaveljevs Looks good. CLOSED.

subissue (1) should be tested.

(7) Coding styles:

• We don't use "!empty"
• Unnecessary spaces in

  ' AND table_schema=' . $schema .
  

  Before and after dots

• Please improve SQL query style

  		$tableExists = DBfetch(DBselect('SELECT 1 FROM information_schema.tables'.
  			' WHERE table_catalog='.zbx_dbstr($DB['DATABASE']).
  			' AND table_schema=' . $schema .
  			" AND table_name='dbversion'"));
  

kristsk RESOLVED in r46513.

oleg.egorov CLOSED

(8) Error message displaying in incorrect db schema
In installation:

pg_query() [<a href='function.pg-query'>function.pg-query</a>]: Query failed: ERROR: schema "public2" does not exist [setup.php:94 → CSetupWizard->__construct() → CSetupWizard->eventHandler() → CSetupWizard->checkConnection() → DBconnect() → DBexecute() → pg_query() in /var/www/oleg/ZBXNEXT-2254/frontends/php/include/db.inc.php:513]
Error in query [SET search_path = 'public2'] [ERROR: schema "public2" does not exist]
The frontend does not match Zabbix database.

In frontend:
Database error
The frontend does not match Zabbix database.

kristsk RESOLVED in r46513.

oleg.egorov CLOSED

TESTED (FRONTEND)

Implemented frontend and merged to 2.3.2 (trunk) in r46700.