PostgreSQL support

[eizedev]

Hi,

our default database backend system is pgsql, so we use it for all icinga related components. It causes additional effort, but so far pgsql works quite well with all icinga related components.

So it would be nice if support for pgsql could be also added here.

(i have removed some information from the issue template, because it is not a general problem or a bug)

Thank you

Greetings René

Expected Behavior

PGSQL should also be supported as a database backend, just as mysql is already supported.

Current Behavior

Currently only mysql/mariadb is supported

[durzo]

Any progress on this?

[mvrgotiks]

It would be really nice to have this one, our system is already running using PostgreSQL backend. Does anyone know if pgsql schema is only thing missing/required to have the x509 module working with pg backend? If so, maybe its worth a shot to use db converter?

Marko

[lippserd]

We're about to add the missing PostgreSQL support to our modules. At moment we're working on the Reporting modules. This one will be next.

[mvrgotiks]

That is just fantastic news.

Could you give us an estimate, simply for planning and informing my management (they like nice graphs and counters).

thank you, Marko

[deucalion]

Any news?

If I may add... if it's almost done, adding a pull request with the unfinished code and letting others finish it may perhaps be helpful? Thanks!

[mvrgotiks]

We're about to add the missing PostgreSQL support to our modules. At moment we're working on the Reporting modules. This one will be next.

What about the update give above, 6months ago? Come on Icinga gurus, I am very happy with Icinga so far (500hosts or almost 2000services), but what is the point of supporting PostgreSQL and MariaDB, if all these enhancements are going to be added only for MariaDB. We can work with existing checks but if Icinga features list is gonna suffer severely based on initially choosen DB backend, its not really a choice, and it should be mentioneed in bold in the installation or overview.

Have you considered what @deucalion suggested? Is there a way we can help?

[lippserd]

Hi all,

Please see PR #85. Feedback and tests welcome!

All the best, Eric

[mattboston]

I'm getting the following error when I do the import source steps using a PostgreSQL database.

SQLSTATE[42601]: Syntax error: 7 ERROR:  syntax error at or near "SEPARATOR"
LINE 1: ...stname AS host_name, GROUP_CONCAT(DISTINCT t.port SEPARATOR ...
                                                             ^ (Connection.php:402)

#0 /usr/share/icinga-php/ipl/vendor/ipl/sql/src/Connection.php(402): PDOStatement->execute(Array)
#1 /usr/share/icinga-php/ipl/vendor/ipl/sql/src/Connection.php(418): ipl\Sql\Connection->prepexec('SELECT t.ip AS ...', Array)
#2 /usr/share/icingaweb2/modules/x509/library/X509/ProvidedHook/HostsImportSource.php(27): ipl\Sql\Connection->select(Object(ipl\Sql\Select))
#3 /usr/share/icingaweb2/modules/director/library/Director/Web/Table/ImportsourceHookTable.php(100): Icinga\Module\X509\ProvidedHook\HostsImportSource->fetchData()
#4 /usr/share/icingaweb2/modules/incubator/vendor/gipfl/icingaweb2/src/Table/SimpleQueryBasedTable.php(29): Icinga\Module\Director\Web\Table\ImportsourceHookTable->prepareQuery()
#5 /usr/share/icingaweb2/modules/incubator/vendor/gipfl/icingaweb2/src/Table/SimpleQueryBasedTable.php(15): gipfl\IcingaWeb2\Table\SimpleQueryBasedTable->getQuery()
#6 /usr/share/icingaweb2/modules/incubator/vendor/gipfl/icingaweb2/src/Table/QueryBasedTable.php(56): gipfl\IcingaWeb2\Table\SimpleQueryBasedTable->getPaginationAdapter()
#7 /usr/share/icingaweb2/modules/incubator/vendor/gipfl/icingaweb2/src/Table/QueryBasedTable.php(255): gipfl\IcingaWeb2\Table\QueryBasedTable->getPaginator(Object(gipfl\IcingaWeb2\Url))
#8 /usr/share/icingaweb2/modules/director/application/controllers/ImportsourceController.php(162): gipfl\IcingaWeb2\Table\QueryBasedTable->renderTo(Object(Icinga\Module\Director\Controllers\ImportsourceController))
#9 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action.php(507): Icinga\Module\Director\Controllers\ImportsourceController->previewAction()
#10 /usr/share/php/Icinga/Web/Controller/Dispatcher.php(76): Zend_Controller_Action->dispatch('previewAction')
#11 /usr/share/icingaweb2/library/vendor/Zend/Controller/Front.php(937): Icinga\Web\Controller\Dispatcher->dispatch(Object(Icinga\Web\Request), Object(Icinga\Web\Response))
#12 /usr/share/php/Icinga/Application/Web.php(304): Zend_Controller_Front->dispatch(Object(Icinga\Web\Request), Object(Icinga\Web\Response))
#13 /usr/share/php/Icinga/Application/webrouter.php(107): Icinga\Application\Web->dispatch()
#14 /usr/share/icingaweb2/public/index.php(4): require_once('/usr/share/php/...')
#15 {main}