Migrate DB to Postgres

[dchoi27]

We're already getting a consistent number of operational problems from using FaunaDB for Web3.Stoarge, which doesn't bode well at the current scale. This issue tracks the things we need to do to migrate to Postgres, which we (and most others) engineers know better and will likely be more reliable.

Plan

• Define the database schema in SQL Define the database schema in SQL #439
• Database Setup, Client and test environment feat: postgres setup #508
• Rewire API endpoints to use Postgres Rewire API endpoints to use Postgres #441
• Rewire cron jobs to use Postgres Rewire cron jobs to use Postgres #520
• Add a read-only mode to the api. Existing users can query for existing uploads, and requests go to Fauna. Add a read-only mode for maintenance #452
• Visualise read-only mode in the website (unfortunate, but is a useful thing to have for now and any future major maintenance). Add a read-only mode maintenance banner to website #453
• Prod + Staging infra setup Setup infrastructure for Postgres DB #541
• Sync fauna data to postgres from a known point in time in the background. Migrate data from fauna to Postgres #440
• Inform web3.storage users about upcoming maintenance window
• Enable read-only mode and sync the diff between what we have in postgres and that moment.
• Verify (need to define how) that the data in postgres is complete and accurate
• Flip the switches so reads and writes go to postgres, and disable read-only mode.
• Celebrate?!

[Gozala]

Plan of action:

• Define the database schema in SQL #439
• Migrate data from fauna to Postgres #440
• Rewire API endpoints to use Postgres #441

[olizilla]

To add some more nuance here, I see the migration going like this:

• Define the database schema in SQL #439
• Rewire API endpoints to use Postgres #441
• Add a read-only mode to the api and visualise it in the website (unfortunate, but is a useful thing to have for now and any future major maintenance). Existing users can query for existing uploads, and requests go to Fauna. Add a read-only mode for maintenance #452
• Sync fauna data to postgres from a known point in time in the background (we need to investigate how long that takes, and what errors we could hit) this is captured in Migrate data from fauna to Postgres #440, but we need to add more detail.
• Enable read-only mode and sync the diff between what we have in postgres and that moment.
• Verify (need to define how) that the data in postgres is complete and accurate, and then flip the switches so reads and writes go to postgres, and disable read-only mode.
• Celebrate?!

[vasco-santos]

Testing Plan for DB migration validation:

1. Website

• Register -> logout -> login Flow with email
• Register -> logout -> login Flow with github
• Login with user previously registered
• Create two auth keys
• Delete an auth key previously created and see if deleted is set
• Upload a small file and confirm used storage increase
• Re-upload the same file twice and confirm used storage does not increase
• Rename upload
• Confirm upload list has expected content
• Upload a large file (partials) and confirm used storage increase
• Delete a file and confirm it does not appear in file listing
• Upload file previously deleted

2. W3

• Fail to upload CAR with a deleted auth key
• Fail to upload file with a deleted auth key
• Upload CAR with a valid auth key
• Upload file with a valid auth key
• Upload CAR/file with a custom name
• List uploaded files
• Get status for uploaded files and confirm content expectations

3. Test Deals

[rafaelramalho19]

Staging sucessfully tested:

• Fail to upload CAR with a deleted auth key
• Fail to upload file with a deleted auth key
• Fail to list uploaded files with a deleted auth key
• Upload CAR with a valid auth key
• Upload file with a valid auth key
• Upload CAR/file with a custom name
• List uploaded files
• Get status for uploaded files and confirm content expectations

(Listing with deleted auth key could have a better error)

[atopal]

Looks like we can close this :)