Oh my god thank you–this is the only thing that worked for me!
My process was this:
-
heroku pg:backups:capture --a <my-app> -
heroku pg:backups:download--a <my-app> -
Use pgAdmin restore with these settings: include CREATE DATABASE statement, Clean before restore. Not sure how necessary, but seemed to minimize my errors. On running this, I got a bunch of errors saying:
pg_restore: error: could not execute query: ERROR: role "xxxxxxxxxxxxx" does not exist
Command was: ALTER TABLE public.strapi_permission OWNER TO xxxxxxxxxxxxx;
So it makes sense that we need to alter something about permissions!
Then I ran your SQL like so:
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO strapi; GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO strapi;
And it worked like a charm! Seriously a lifesaver–I now have absolutely identical environments and data across my local machine, staging, and prod, which will really help with testing and troubleshooting