If you set things up appropriately, you can rewind/replay your DB to a given point-in-time. So you have a 2nd Moodle install "shadowing" your main installation -- from a special admin webpage, you can order the shadow moodle to be rewound to any time from 5 minutes ago to 30 days ago, with granularity down to seconds/miliseconds.
So if a teacher calls saying they have *just* deleted a quiz he shouldn't have, you rewind the shadow Moodle install, export the course/quiz to a moodle backup file, and either restore it or email it to the teacher.
Something like a having your own personal Moodle Tardis.
Depending on HW, traffic and time of the day, the "rewind" operation takes 5-20 minutes. How many days of rewind-ability do you keep around is configurable - naturally, this feature eats hard drives for breakfast on a busy Moodle setup, and more days mean more HD
The only caveat is that it would not cover - or rewind - files held in Moodledata. There's a plan I've drafted with Petr for smarter file storage using a content-addressable strategy. Once that's in place, it will be able to magically "rewind" Moodledata too.
I really think the sysadmins in this forum are all thinking ... darn, wish I'd be using/knowing/migrating to postgres...
Do you have a list of caveats/specificities of postgres, because if moodle runs better on postgres, I'm sure there are some admins out there willing to learn how to manage it, to run a better moodle (I'm sure I would want to.)...
Sorry, I could not control my enthousiasm.
Not many downsides (that I'm aware of!):
- Contrib modules may have SQL errors/oddities that MySQL accepts. From v1.7 onwards, thanks to XMLDB and the new dmllib, this is rather unlikely. Still, it can happen.
- The default PostgreSQL install usually needs a bit of tuning, mainly around telling PostgreSQL how much memory to use (it assumes it can only use something really small, like 256MB). Pg 8.3, due Real Soon Now is supposed to be a bit more autotuning in that regard. Still, expect to do a bit of tuning and testing.
- Disable autovacuum and run it from cron.
- For small/medium sites, it may be slower than MySQL.
- Persistent connections are more important than under MySQL -- this makes it impractical for bulk hosting.
On the other hand
- Migration is relatively easy. I think I've posted some notes and links to scripts in the past.
- Never ever gets index or data corruption. No repair tables.
- Very good scalability, specially with large sites and Moodle v1.9.
- Cool tools like PITR
- If you want to run a national election, or the DNS backend of your country... you have all the DB you're going to need
> Disable autovacuum and run it from cron.
Oh, why? I was under the impression that autovacuum was relatively good (not that i've ever looked into it in detail).
> Migration is relatively easy. I think I've posted some notes and links to scripts in the past.
It is? Would be interesting to see them! The thought scares me! Although I guess it should 'just work' if you were to do it with the XMLDB layer.
We are traditionally a postgres shop, but I went with mysql for our moodle hosting, mostly due to migrating a large number of existing moodles from mysql hosting and the fear of such a migration ( that was pre XMLDB and we'd had v.bad recent experiences with drupal).
I think we'd like to move to postgres one day (if only so my colleagues dont kill me when they hit CTRL-C on the mysql console to quit a query), but we do use features Mysql Replication, which i've not really seen a winning alternative in postgres yet. I'd never really thought about the persistent connections problem before now either.
I'm sure the MySQL-to-Pg scripts are posted / linked somewhere, the only "tricky" bit was re-setting the sequences. We have a newer one that uses XMLDB to do it across any DB implementations that Moodle supports, and any direction. But that's newish and still unpublished.
In terms of replication - does PITR not meet your needs? I tend to find MySQL replication... more configurable, but slow and somewhat flaky
I have managed to migrate from MySQL to Postgres during my 1.5 >> 1.6 upgrade (so it was a little bit complicated due to UTF8 migration). IIRC I was trying to use one of those scripts caught somewhere in the web. But finally I just made a mysqldump (single inserts with column names!), opened it in my favourite editor and performed several regexp search and replace to get correct SQL syntax. Then I just changed DB info in config.php, let Moodle to create new and empty Postgres tables and imported data from modified dump.
p.s. I like the idea of a repository based moodledata - great solution for backups as well
Morning, i would like to do two parallel sites, i did it, but the replication tool, is "me" making export from a site and import from the other, regulary. Now my problem is that all these two sites are in an hosting server on the web. how to automate replication ? So, i did'nt find nothing possible to do with phpmydmin for mysql, and nor with phppgadmin for postgre.
am i in error?
hav i to do a mine stand alone server, on a mine machine with static ip,dns, etc?
may on dedicated server on the web too.
I do not find any documentation on phppgadmin !
Can you help me to understand, please ?
Thanks in advance, EDT
How about a php "dedicated" page on moodle to reade data from a db and write it on the other ?
Actually - over the xmas break the office has been mostly quiet, and I've had time to implement this end-to-end.
- A nice web UI to rewind things that also shows a few stats. Needs CSS love though.
- The database is "rewindable" to any timestamp, down to the second (or microsecond, if we care to extend things a bit).
- It covers moodledata as well -- with snapshots taken at configurable intervals (every hour is recommended), so we pick the snapshot that is at or just before the time requested.
- Extremely cheap performance-wise - the DB snapshotting and transaction log is virtually cost-free, the moodledata snapshots have a bit of disk-io cost.
- It is fairly space efficient, my estimate is that for a reasonable-traffic moodle, the overhead for keeping 30 days worth of rewind data is around 50%.
- Storage can be on NFS so it could be on a 'remote' and is replication-friendly (to keep a warm-spare around very efficiently).
The moodledata side would be my 'performance cost concern' as our Moodles are getting more and more loaded up with huge numbers of media files.
Having said that, we are now storing data on ZFS and doing hourly snapshots (which basically cost nothing performance wise), so would be able to plug-in to the .zfs snapshot directory structure.
ZFS is quite close to what I am doing, with a possibly lower cost in performance, but higher disk footprint (unless it's doing magic compression/diffing behind the scenes like git does).
I don't know much [anything] about how git does its packing (know any good overviews? Google fails me!).
But in the case of moodledata (/big media files) I assume it can't really help too much?
Edit: Except git would do single instancing would it?
Don't have any good readable overviews of the git packing format, sorry the git documentation is pretty technical.