Prod DB Migration

[bh2smith]

1. Run migrations on prodDB (with ADMIN user)

docker run --add-host=host.docker.internal:host-gateway \
  --rm ghcr.io/cowprotocol/solver-rewards-db-migration \ 
  -url=jdbc:postgresql://$POSTGRES_HOST/$POSTGRES_DB \
  -user=$ADMIN_USER \
  -password=$ADMIN_PASSWORD \
  migrate

2. Update DB_URL in Tenderly Deployment (using R/W USER): This will start populating from next settlement.

3. Perform Export/Import on the relevant tables:

   • internalized_imbalances
   • settlement_simulations
   • settlements
   • tx_receipts

4. At this point, the prodDB is expected to be fully "synced", although we may want to check for missing data before proceeding with step 4. This can be achieved most simply by running the backfill script for the current date... Alternatively we could query the settlements table with the following Gap Detection Queries

   • Dune
   • LocalDB select count(*) from settlements where block_number between {start_block} and {end_block};

5. Update WAREHOUSE_URL in dune-sync project with using READONLY_USER. This will need to be a Hotfix PR on the infrastructure project.

   • Done in https://github.com/cowprotocol/infrastructure/pull/436

Additional Notes

• After step 1, we will not see any new data appearing in Dune until step 4 has been completed.

• Even if we don't bother with step 4, we can still recover from gaps later by backfilling (say the next day), then we would have to empty the Dune AWS Bucket and resync (from dune-sync project:

  python -m src.scripts.empty_bucket --sync-table internal_imbalance