If you haven’t yet deployed with Heroku, you may want to check out our previous post where we deployed our (Rails) app to Heroku.
If you’re running an app on Heroku and you’ve come across this issue:
PG::InsufficientPrivilege: ERROR: permission denied for tableThen you have probably come to a row limit on your hobby-dev postgres resource. You can confirm it using the following command:
heroku pg:info
...
Rows: 19504/10000 (Write access revoked) - refreshingYou will need to do several things:
- Create a new database with more rows
- Backup old database
- Restore backup in the new database
- Promote the new database to be in use by the app
Let’s get started, first off create a new database, you can either do this in UI or using terminal.
Command in terminal is quicker and is covered below.
If using the UI, navigate to the resources tab in your Heroku dashbaoard:
You can click ‘Find more add-ons’
Locate Heroku Postgres, you can search for it in the box on the top right
Find the ‘Hobby-basic’ plan – or whichever suits your needs
And now you can click install add-on.
If using Terminal commands you can simply open your terminal and execute:
heroku addons:create heroku-postgresql:hobby-basic -a YOUR_APP_NAME
Creating heroku-postgresql:hobby-basic on ⬢ yazii... $9/month
Database has been created and is available
! This database is empty. If upgrading, you can transfer
! data from another database with pg:copy
Created postgresql-corrugated-10516 as HEROKU_POSTGRESQL_SILVER_URL
Use heroku addons:docs heroku-postgresql to view documentationYou don’t need to add -a YOUR_APP_NAME if you’re in the directory of your app.
That’s it, resource is created and is ready for use.
The next step is to start backing up your data. You may want to set maintenance mode on. This is to prevent writes occurring while and after the backup is generated as those will not be moved to the new database.
heroku maintenance:onFor the next steps you will need the resource names, you can get them with:
heroku pg:info
=== DATABASE_URL
Plan: Hobby-dev
Status: Available
Connections: 0/20
PG Version: 12.4
Created: 2020-08-14 10:55 UTC
Data Size: 76.8 MB
Tables: 15
Rows: 19201/10000 (Write access revoked) - refreshing
Fork/Follow: Unsupported
Rollback: Unsupported
Continuous Protection: Off
Region: Europe
Add-on: postgresql-adjacent-85022
=== HEROKU_POSTGRESQL_SILVER_URL
Plan: Hobby-basic
Status: Available
Connections: 0/20
PG Version: 12.4
Created: 2020-10-12 07:41 UTC
Data Size: 7.9 MB
Tables: 0
Rows: 0/10000000 (In compliance)
Fork/Follow: Unsupported
Rollback: Unsupported
Continuous Protection: Off
Region: Europe
Add-on: postgresql-corrugated-10516you care about the name of the add-on here
At this point, you will want to ‘capture’ and ‘restore’ the database from hobby-dev to hobby-basic.
heroku pg:backups:capture postgresql-adjacent-85022
...
Backing up DATABASE to b001... donereplacing postgresql-adjacent-85022 with your hobby-dev Postgres add-on.
When this is done, which can take a few minutes, run the restore on your hobby-basic Postgres add-on.
heroku pg:backups:restore b001 postgresql-corrugated-1051
...
Restoring... doneReplace b001 with the ID that was provided after running pg:backups:capture
Replace postgresql-corrugated-1051 with the hobby-basic Postgres add-on.
Once this is complete, you will want to promote the new database for use.
heroku pg:promote HEROKU_POSTGRESQL_SILVER_URL
Ensuring an alternate alias for existing DATABASE_URL... HEROKU_POSTGRESQL_CYAN_URL
Promoting postgresql-corrugated-10516 to DATABASE_URL on ⬢ yazii... doneReplace HEROKU_POSTGRESQL_SILVER_URL with the desired name as provided by heroku pg:info.
This completes the transfer and all that’s left is to disable maintenance mode:
heroku maintenance:offGive your app a test and you should no longer have the InsufficientPrivilege issue.