golang-migrate command "freezes" during migrations, takes too much time to migrate (10 minutes for 10 rows)

Question

when running the migrate on VPS the command freezes. After that I can abort, reboot the VPS, and see that the migration version is one higher, but the DB is dirty. I have no idea how to debug this! The same happens during down migrations.

The command I used and the "freeze frame":

ssh -t myusername'@'111.111.11.11' 'migrate -path ~/migrations -database $DSN -verbose down 1 && sudo systemctl restart api'
2024/03/02 16:09:51 Start buffering 7/d add_user_settings
2024/03/02 16:09:51 Read and execute 7/d add_user_settings

After migrating down by 1 the version is by 1 lower, but also dirty according to schema_migrations;

How do I debug it, how can I see what goes wrong? Locally everything works well.

Ubuntu Postgres 14, golang-migrate 4.17.0 (installing newest version today did not help)

I tried GPT3, it gave generic answers. Googled a lot, but found nothing.

UPDATE,

it finished after 11 minutes on an empty database:

    xxx@xxx-Mac-mini xxxxusrnmee % make prodmigdownone
ssh -t 'xxxxxxuser'@'1111.111.11' 'migrate -path ~/migrations -database $DSN -verbose down 1 && sudo systemctl restart api'
2024/03/02 16:09:51 Start buffering 7/d add_user_settings
2024/03/02 16:09:51 Read and execute 7/d add_user_settings
2024/03/02 16:20:58 Finished 7/d add_user_settings (read 4.772845ms, ran 11m7.775711064s)
2024/03/02 16:20:58 Finished after 11m7.783364562s
2024/03/02 16:20:58 Closing source and database
[sudo] password for xxxxxxxuser: 
Connection to 111.111.54.111 closed.

UPDATE 2

Indeed all I needed is to wait. Which makes no sens, since an empty DB with about 5 rows in users table would take 7 minutes to migrate up adding a new column. Locally it is instant (with the same amount of records). Also in the past all migrations have been instant on the same VPS.

Answer 1

I would tackle this methodically. I'm guessing there is a bottleneck somewhere.

• Monitor resource usage on the VPS while you're performing the migration.
• Forgive my ignorance of golang-migrate, but perhaps test your SSH connection bandwidth. Something like;

ssh remote_host cat /dev/zero | pv | cat > /dev/null

If you can isolate where the bottleneck is, you can start to look for more answers.

Perhaps log an issue, and see if those closer to the project can provide insight: https://github.com/golang-migrate/migrate/issues

Good luck!