Migrating from 0.1.x to 0.2.x

[JSmith-Aura]

2.x contains a bug fix that requires a manual database migration:

To do this connect to the phatcrack database instance:

docker exec -it -u postgres phatcrack-db-1 psql -U phatcrack

Then complete this change:

BEGIN;

-- Create temporary table with unique rows
CREATE TEMP TABLE unique_temp_potfile AS
SELECT DISTINCT ON (hash, plaintext_hex, hash_type) *
FROM potfile_entries
ORDER BY hash, plaintext_hex, hash_type, id;

-- Delete original rows
DELETE FROM potfile_entries;

-- Copy the rows back
INSERT INTO potfile_entries SELECT * FROM unique_temp_potfile;

DROP TABLE unique_temp_potfile;

COMMIT;

An issue has been identified which stops postgres from building an index for the potfiles which will be resolved soon. This may cause the following errors to be emitted:

2024/03/25 14:11:09 /app/api/internal/db/db.go:87 ERROR: index row size 2784 exceeds btree version 4 maximum 2704 for index "idx_uniq" (SQLSTATE 54000)
[28.572ms] [rows:0] CREATE UNIQUE INDEX IF NOT EXISTS "idx_uniq" ON "potfile_entries" ("hash","plaintext_hex","hash_type")