RAKE INSPECTION · WHAT TO CHECK BEFORE RETURNING IT
Common Mistakes in Scheme Implementation and Administration
Six Common Pitfalls Teams Run Into When Porting a Schema to a Production PostgreSQL Environment
1. Discrepancies Between the DBML and the Actual Database
2. We modified init.sql but did not recreate the volume
We drew the relationships in dbdiagram.io, but forgot to include the FOREIGN KEY in the SQL. This can be fixed by checking the reverse ER diagram in DBeaver.
We fixed the script in /docker-entrypoint-initdb.d/ and restarted it. It won't run on a volume that isn't empty—you need to run `docker compose down -v`.
3. We forgot to run the GRANT USAGE command on the schema and sequence
4. ON DELETE CASCADE on purchases
We granted GRANT SELECT and INSERT ON TABLE, but forgot to grant USAGE ON SCHEMA, USAGE, and SELECT ON ALL SEQUENCES—the service crashes during an insert.
We've implemented cascading deletion for purchases or financial transactions—deleting an item clears the payment history.
5. Attempt to load a -Fc dump via psql
6. Port 0.0.0.0:5432 and running under Postgres
We extracted the binary pg_dump -Fc archive and fed it into psql < file.dump. The text parser will return an error—you need pg_restore!
We published the container port on all interfaces and specified the superuser in the backend configuration instead of 127.0.0.1 and the game_app role.