galenmaly/lighterpack

Migrate from Mongo to Postgres

Opened this issue · 1 comments

galenmaly commented

MongoDB got the job done for a while, but as we move to implement new features having a standard DB with tables will make life much easier.

Implementation

Table Schema

Only two tables for now:

user

  • user_id (uuid)
  • username (text)
  • password_hash (text)
  • registration_date (datetime)
  • library (json)

list_external_id

  • list_external_id (text)
  • user_id (UUID)

Technology

Use knex for a query builder

Migration

Given how a server migration is imminent, the plan is to install postgres only on the new server, and have the web server still running on the old server, talking to postgres on the new server (in the same data center).

This migration intentionally has downtime to avoid a much more complicated and time-consuming zero-downtime migration.

Preparation

  • Install postgres on the new server and configure to allow connections from localhost and the old server (including setting up an encrypted (ssl?) connection)
  • Write script that can import a mongodump file to postgres
  • Modify server/views.js and server/endpoints.js to talk to postgres instead of mongo in a postgres branch
  • Create a database migrations folder with sql files named by date 2019-10-06-users-and-ids.sql
  • Run through the execution steps with the beta site

Execution steps

  1. Merge the postgres branch to `master
  2. Open terminals to new web server and old web server.
  3. Update config/local.json on the old server with connection values to the DB
  4. Open SFTP connection from new server to old server
  5. Disable auto-restart of the lighterpack service on the old server. TODO: remember how to do this
  6. stop lighterpack
  7. mongodump
  8. SFTP transfer the mongodump'd file to the new web server
  9. Run the import script to populate the postgres db
  10. git pull on the old server
  11. start lighterpack
  12. Perform manual testing of logging in, saving data, and viewing a list
  13. Re-enable auto-restart of the lighterpack service on the old server TODO: remember how to do this
colinmxs commented

This seems like a waste of time. Let's add features!

Edit: To elaborate, I'm having a hard time understanding the necessity of changing DB tech. If there aren't cost, performance, or scalability concerns, moving from mongo to postgres seems like a high-effort/low-payoff move. Given that Galen has limited availability to work on things/complete PRs/etc, I'd vote that we address the data/schema concerns within the application instead.

Anyway, just my two cents, and I'll definitely support whichever direction we end up chasing!

  • 👍7