0

This is the kind of question that is liable to get "closed" - perhaps with pointers to this SO thread. Quite apart from the fact that many of the links in that thread no longer work I am posting this question because I have some very specific questions

Here is how I currently use MariaDB

  • A DB with around 15 tables - no triggers etc
  • Accessed via PHP PDO. Mostly simple INSESRT/UPDATE/SELECT/DELETE statements. No complex JOINS or the like
  • Administered via Adminer

Finally, I should also mention that all data going into the database are encrypted + base 64 encoded. This is all done in PHP so the only data seen by the DB are alphanumeric strings (+ the usual bits added by B64).

Whilst I never expect such moves to go completely without a hitch I would like to have an idea of what I might be letting myself in for here. Given the above does the move (except the actual server install) sound like a job to be done in an hour or two or will I end up spending far more time debugging my SQL?

Finally, how will this impact resource use on the server? Is PostgreSQL a more demanding beast than MariaDB which will entail throwing more resources at the server?

S-Man
  • 22,521
  • 7
  • 40
  • 63
DroidOS
  • 8,530
  • 16
  • 99
  • 171
  • 2
    Expect testing to last more than an hour, unless you are using really really simple queries. The fast part will be translating the tables, as you get errors if something doesn't fit. But for your queries, there can be many more or less subtle differences. They may generate an error (e.g. missing function, different names or function usage) or just give you unexpected results (e.g. postgres uses case-sensitive string comparison; or autocasting types might or might not work (like mysql uses 1 and 0 for booleans, while postgres has a boolean type)). And it all might depend on the data/situation. – Solarflare Jul 20 '17 at 11:14
  • Thanks. All character content in my databases is typically encrypted and then base64 encoded (lower case). I assume that given this at least case sensitivity issues are unlikely to arise? – DroidOS Jul 20 '17 at 13:38
  • Yes, I guess that might take care of the case-sensitivity, although "typically" isn't "always". But my list was far away from complete (although the case-sensitive comparison is often a bigger problem than it seems to be in your case). I unfortunately cannot tell you how long it will take you to change your database. In a perfect scenario, your code might run perfectly without touching a single query. But even then, I would plan more than an hour to verify that your scenario is a perfect one. It's not done by not getting errors, as there can be other effects (like the case sensitive example). – Solarflare Jul 20 '17 at 14:18
  • Is your data encrypted by the database server or by the app? If by the app, no issue. If by the database, then you must check to see if Postgres or an extension can perform that exact same encryption/decryption. – Basil Bourque Jul 20 '17 at 19:46
  • @DroidOS Post your clarifications and details as edits to the Question rather than as Comments. – Basil Bourque Jul 20 '17 at 19:47
  • Point taken - question edited as suggested – DroidOS Jul 20 '17 at 20:17

0 Answers0