To be of any help to people having issues on Heroku Hobby and Postgres heres what i do to add new fields to existing content types with data in them.
First i download the backup of db from Heroku and put it in my local Postgres
heroku pg:backups:capture --app my-app-name
heroku pg:backups:download backupid --app my-app-name
pg_restore -d local_dbname latest.dump
After that i open the local strapi connected on my local postgre and add all the fields i need to collections. After that i dump the local db, reset heroku db and import new dump.
pg_dump -Fc --no-acl --no-owner -h localhost -U username dbname > mydb.dump
heroku pg:reset --app my-app-name
pg_restore --verbose --no-acl --no-owner -U herokudb_username -h herokudb_location -p 5432 -d herokudb_name < mydb.dump
I connect my local strapi to remote postgres just to be sure everything working.
If you ever get an error like duplicate key value violates unique constraint its an error due to dump (Postgres error not strapi), just manualy increment the key in the error
//Duplicate id key in admin_permissions table
SELECT SETVAL((SELECT PG_GET_SERIAL_SEQUENCE('"admin_permissions"', 'id')), (SELECT MAX(id) FROM admin_permissions) + 1, FALSE);
Then push my changes to heroku and open the app. Everything is looking working so far.
Strapi Version:4.1.11
pg:8.6.0
Postgres:14.0.2