Whether we want a copy of our database as a backup or we need to transition some records to a new database, this can be accomplished with a few simple commands.
Here is an example of creating a dump file from a Heroku Postgres database containing the records of a posts table and a posts category table. The dump file is then restored to either a local development database or a live production database on Heroku.
View the Heroku addons by entering the command:
heroku addons
Output:
Add-on Plan Price State
──────────────────────────────────────────────── ───────── ───── ───────
heroku-postgresql (postgresql-databasename-12345) hobby-dev free created
└─ as DATABASE
The table above shows add-ons and the attachments to the current app (appname-12345) or other apps.
Get the database credentials by entering the command:
heroku pg:credentials:url DATABASE_URL --app appname-12345
Output:
Connection information for default credential.
Connection info string:
"dbname=heroku_db_name host=heroku_db_host.compute-1.amazonaws.com port=5432 user=heroku_db_user password=heroku_db_password sslmode=require"
Connection URL:
postgres://heroku_db_user:heroku_db_password@heroku_db_host.compute-1.amazonaws.com:5432/heroku_db_name
This information can also be found in the Heroku dashboard.
Get a dump file containing records for the tables blog_post and blog_postcategory by entering the command:
pg_dump -Fc --no-acl --no-owner -h heroku_db_host.compute-1.amazonaws.com -U heroku_db_user --dbname=heroku_db_name --table=blog_post --table=blog_postcategory --data-only > posts.dump
Enter the password from the credentials when prompted.
Copy the posts.dump file to the root directory of the target project.
Connect to the target app's database psql terminal:
heroku pg:psql --app appname-67890
Or connect to a local database psql terminal:
psql -U postgres -h localhost -W local_db_name
Enter the local database password when prompted.
Clear the tables from the target database using the psql terminal. This will remove all rows from the set of tables so be careful not to delete anything that you do not intend to.
Run the psql command:
TRUNCATE blog_post, blog_postcategory CASCADE;
Exit the psql terminal.
Restore the posts.dump file to the target database using the command line:
pg_restore --verbose --clean --no-acl --no-owner -h heroku_db_host.compute-1.amazonaws.com -U heroku_db_user -d heroku_db_name posts.dump
To restore the posts.dump file to a local database, enter the command:
pg_restore --verbose --clean --no-acl --no-owner -h localhost -U postgres -d local_db_name posts.dump
Here are a couple of resources that provide more detail:
https://devcenter.heroku.com/articles/heroku-postgres-import-export
https://www.postgresql.org/docs/current/app-pgdump.html
That's it. You can use a dump file to backup your database or transfer records to a new database.
Postgresql Icon and Heroku Icon by Icon Mafia on Iconscout