Migrating to PostgreSQL has many benefits – lower license costs, great community support, robust SQL support, powerful stored procedure languages, NoSQL key/value, document and full-text database capabilities, and many more. If you’re moving from another relational database the migration can be fairly simple. Moving from a NoSQL database may require more application code changes but the benefit of proven reliability and never losing your data is worth it!
To help make your migration to PostgreSQL smoother, follow these 5 Tips:
- Know all the users of your database: You are focused on the major application(s), the public facing web app or your internal system. But what about Sally in accounting with that spreadsheet that updates every day? And Fred in development who built that reporting dashboard for the senior executives? Make sure you don’t strand them
- Spring Cleaning: Even if you use source control for your database objects, developers don’t have access to production, and you carefully manage promotions every database builds up objects that you don’t actually need any more. Snapshot tables from restores, test schemas, functions that are no longer used, they are have a tendency to hang around in databases. If you can drop them before migrating it’s one (or many) fewer things to test!
- Archiving: We like big databases. We like to keep all the data. But do you really need to? And do you really want to migrate all of it? If you can archive (or delete) a few years of history, now’s the time!
- Plan for Testing: Testing a migrated database and all the applications is the most time consuming part of migrating. Make sure you talk with the application owners to find out what they need to be able to test the application against the new database. And make sure they have the time and resources to actually test thoroughly before the go-live date!
- Test your Plan: You’ve figured out how to do backups and your failover strategy is well thought out. Right? Make sure you actually try out your recovery plan one time before you go live. There’s nothing like taking downtime or losing data to sour everyone on the new database.