Murmur & Postgresql

[thorbadour]

Hi all,

I have been running a murmur instance for a couple of weeks now and it's working very well with about ~40 people online.

But I'm also running several other services on that same server that are all using Postgres as database and I have a complete incremental backup scheme running for Postgres.

I'd like to have the murmur database in that backup scheme as well, since to backup the sqlite database I have to turn off the murmur server everytime I want to make a backup. Which is kinda nasty since the murmur server is being used 24/7 by users all over the world.

Therefor a migration to Postgres would be awesome.

(Correct me if the above information is incorrect but the murmur wiki tells me to turn off murmur before creating a backup)

I migrated my existing sqlite database to postgres using pgloader and I validated the data, and the data from the tables same to match (only the slog table is way way to large for me to manually run over ;) )

I configured the murmur.ini with the following db settings (yes unsave, this setup is on my staging server):

database=murmur
dbDriver=QPSQL
dbUsername=murmur
#dbPassword=
dbHost=localhost
dbPort=5432
#dbPrefix=murmur_
#dbOpts=

 

From the logs I can see that murmur is able to connect to the server, so far so good!

But the logs also give me these errors:

Jun 01 19:56:26 rage murmurd[7316]: 2015-06-01 19:56:26.936 Successfully switched to uid 122

Jun 01 19:56:26 rage postgres[491]: ERROR: syntax error at or near "CREATE" at character 25

Jun 01 19:56:26 rage postgres[491]: STATEMENT: PREPARE qpsqlpstmt_1 AS CREATE TABLE IF NOT EXISTS `meta`(`keystring` varchar(255) PRIMARY KEY, `value` varchar(255)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

Jun 01 19:56:26 rage postgres[491]: ERROR: syntax error at or near "CREATE" at character 25

Jun 01 19:56:26 rage postgres[491]: STATEMENT: PREPARE qpsqlpstmt_2 AS CREATE TABLE IF NOT EXISTS `meta`(`keystring` varchar(255) PRIMARY KEY, `value` varchar(255)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

Jun 01 19:56:26 rage murmurd[7316]: 2015-06-01 19:56:26.962 SQL Prepare Error [CREATE TABLE IF NOT EXISTS `meta`(`keystring` varchar(255) PRIMARY KEY, `value` varchar(255)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin]: ERROR: syntax error at or near "CREATE"

Jun 01 19:56:26 rage murmurd[7316]: LINE 1: PREPARE qpsqlpstmt_2 AS CREATE TABLE IF NOT EXISTS `meta`(`k...

Jun 01 19:56:26 rage murmurd[7316]: ^

Jun 01 19:56:26 rage murmurd[7316]: (42601) QPSQL: Unable to prepare statement

 

Now this would seem like a perfectly valid MYSQL query, using InnoDB as engine and all. But Postgres? not so much.

Can anyone point me into the right direction?

Whom can I contact about this?

Has anyone else got some experience with setting up Murmur with Postgresql?

Many many thanks for a reply.

Cheers,

thorbadour

[fwaggle]

I don't think it'll work unless you're willing to get your hands dirty. Our database code quite literally looks like multiples of this:

 

if (Meta::mp.qsDBDriver == "QSQLITE")
	SQLDO("CREATE TABLE IF NOT EXISTS `%1meta` (`keystring` TEXT PRIMARY KEY, `value` TEXT)");
else
	SQLDO("CREATE TABLE IF NOT EXISTS `%1meta`(`keystring` varchar(255) PRIMARY KEY, `value` varchar(255)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin");

 

... to accomplish MySQL support. In other words, PGSQL support would require someone to hack it in, the same way MySQL support was added, because unfortunately QSQL doesn't do the level of abstraction we'd need to make all our statements database agnostic. I seem to recall that PGSQL support might have been a WIP thing at one point, I know it was on my radar at one time as I'm a bit of a Postgres fanboy, but I do know that I certainly never did any tangible work towards it.

I do know that I take nightly backups of my sqlite databases, and never really had one that was corrupt because it was half-written at the time (I did have one that was unavailable because I messed up the backup script and backed up the wrong file). I think there's an API in sqlite for taking backups, which we should arguably make use of (and it's my knee-jerk reaction to say that it should be done in SIGHUP, because that's what we use for flushing out and re-opening log files) but I don't think that's there either. I think the "shut it down before making backups" is more of a "here be dragons" thing rather than a result of an actual "shot my own foot off" story someone had, but I could be wrong on that.

I think the Mumble developers would be concerned about potential bit-rot if you hacked together PGSQL support, as I believe that was one of the concerns around MySQL support as well (which was solved by the fact that one of the larger commercial hosts are the ones who took ownership of MySQL support in Murmur), but I wouldn't mind seeing PGSQL support in Murmur... just not enough to write it and take ownership of the maintenance myself. :)

Ninja-edit: It might be that were those if statements reversed (ie mysql-specific queries inside an "if mysql" block, the sqlite one being the else block), it might work out of the box - it's been so long I don't know how close to standard SQL sqlite and pgsql are?

[thorbadour]

Thanks for your reply and I was almost expecting I would have to get my hands dirty ;)

http://sourceforge.net/p/mumble/patches/368/ has a patch for postgres but that patch is still open ended, so it never showed up in murmur.

Although it seems to be like an (almost?) complete patch. I'd have to dive deep(er) into the murmur code to validate that.

I agree that that process should start with checking if the qsDBDriver variables matches either QPSQL or QMYSQL first and default fallback to SQLITE, which was something I expected.

sqlite and pgsql are a very close match, but there are some small differences between the two.

The following post on stackoverflow, the 1st comment has an excellent description what would need to change in statements for them to work in postgres.

http://stackoverflow.com/questions/4581727/convert-sqlite-sql-dump-file-to-postgresql

Do you happen to have more information about that potential bit-rot part? I'm curious about that since I'm also using Postgresql for a handful of applications and never experienced such a thing.

I'm also somewhat of a Postgresql fanboy ;)

My guess would be that the people that took over the MySQL part implemented the "prepared statements" part that I see flying by in the logs. That would be one measure to prevent bit-rot.

Thank you for any additional information.

[fwaggle]

Do you happen to have more information about that potential bit-rot part? I'm curious about that since I'm also using Postgresql for a handful of applications and never experienced such a thing.

 

"Bit-rot" just being if you wrote all the code out, it got committed, then you disappeared. Who would maintain it? Potential bugs would creep in and it would introduce additional work to the devs at no additional benefit for them.

It's not a problem if you're planning on using it for a while, because you can make sure no regression bugs occur.

[thorbadour]

Aah, gotcha.

I do intend, well I am! using it for a while and will continue using it for quite some time to come.

I'll see what I can do, no rush though ;-)

Thanks a lot for the information fwaggle