I know, this gets asked a lot, and this is NOT a request for our esteemed developers to implement it. There are very good reasons why MySQL-like databases are the supported database, and that's not likely to change anytime soon (the GitHub issues list is .. daunting, and most of those are of far higher importance).
But, I wanted to attempt a conversion just to see how much work was needed, and recorded my findings in the event some enterprising contributors wanted to help sand off some sharp corners and make a future conversion easier.
I used a tool to convert the schema and spent about 12 hours editing the source to convert MySQLisms to PostgreSQLisms, and afterward had the GUI largely working (graph display, at least).
A conversion was certainly very achievable in a bounded time.
The improvements that would help the most with any future conversion are, interestingly, the ones that would best future-proof Cacti from any changes in MySQL -- removing the need for compatibility sql_modes. In particular:
- ALLOW_INVALID_DATES / NO_ZERO_DATE / NO_ZERO_IN_DATE -- '0000-00-00 00:00:00' is used as a sentinel in a few places, mostly in the host monitoring code and the poller. During my conversion I tried changing this to '-Infinity' which is the PostgreSQL equivalent, but it turns out the poller does things like UNIX_TIMESTAMP('0000-00-00 00:00:00') which is implicitly changed to UNIX_TIMESTAMP(0), but emits a warning. One suggestion from stackoverflow was to use a valid but impossible date, like 1000-10-01. Modern MySQL also allows NULL in DATETIME fields, and I don't think you have to worry about people using .NET connectors, which don't support NULL date/time fields, to poke around the Cacti internals. (Or do you?)
- STRICT / ONLY_FULL_GROUP_BY -- I found a couple of queries that use implicit GROUP BY members, they are (mostly) easily fixed by using SELECT DISTINCT instead.
- ANSI_QUOTES -- This isn't really a "compatibility" mode, but this is surprisingly easy to do by hand, and may survive an automated conversion too. There are only 2 places, both in lib/poller.php, that use backticks in the shell sense (run this command and return the output) and shouldn't be rewritten. The bigger problem was using double quotes for strings... I don't want to see enabled="on" for some time after fixing it in the hundreds of places that appears.
The more involved conversions are INSERT .. ON DUPLICATE KEY UPDATE ("upsert") and REPLACE INTO queries. PostgreSQL has an equivalent upsert construct, and REPLACE INTO can be translated to a transactional DELETE and INSERT. It's certainly possible to automate those rewrites.
Other miscellaneous issues: There is one use of archaic INSERT .. SET syntax in the SNMP agent. There is also some unsafe assumptions about DEFAULT values in a few places (inserting NULL into a NOT NULL column replaces it with the DEFAULT value on MySQL, but throws a constraint violation on PostgreSQL) and one place in the MIB cache where it inserts a value on a AUTO_INCREMENT column that it doesn't need to. Oh, and watch out for places in poller that wants to CREATE TABLE.
I did find a bug in the MIB cache code in update() where an INSERT is missing a required field ... guess that code path doesn't get exercised very much.
Finally, another thread mentioned that database changes affect plugins. No easy way to address it painlessly if 3rd party code can directly access your internals. Might just have to slot those changes for Cacti 2.0.
Thanks for listening, and I hope this helps someone down the road.
