Skip to content

Instantly share code, notes, and snippets.

@kosyfrances
Last active July 18, 2024 17:39
Show Gist options
  • Save kosyfrances/c7ec4cb7ef6fe9d4e0e6 to your computer and use it in GitHub Desktop.
Save kosyfrances/c7ec4cb7ef6fe9d4e0e6 to your computer and use it in GitHub Desktop.
To copy heroku database from one app to another and from local to heroku

To copy database from one heroku app to another -

heroku pg:backups capture [database_name]
heroku pg:backups restore $(heroku pg:backups public-url --app source_app) DATABASE_URL --app target_app

You can refer to https://devcenter.heroku.com/articles/heroku-postgres-backups for more information.

To copy database from local to heroku - Dump your local database in compressed format using the open source pg_dump tool: PGPASSWORD=mypassword pg_dump -Fc --no-acl --no-owner -h localhost -U myuser mydb > mydb.dump where myuser is your database username and mydb is the database name. In order for PG Backups to access and import your dump file you will need to upload it somewhere with an HTTP-accessible URL. I used cloudinary, but you can use aws or anyone you prefer. heroku pg:backups restore 'the dump url on cloudinary or aws' DATABASE_URL e.g: heroku pg:backups restore 'https://s3.amazonaws.com/me/items/3H0q/mydb.dump' DATABASE_URL

To access heroku database from command line - heroku pg:psql --app app_name DATABASE_URL

@braynm
Copy link

braynm commented Oct 8, 2019

Hi, This works too.

NOTE: Please create a backup before doing operation below.

 # Get DATABASE_SOURCE
 > heroku pg:backups:url b001 --app souce-app 

 # Start backup
 > heroku pg:backups:restore 'DATABASE_SOURCE' --app target-app

Source

@jpstokes
Copy link

jpstokes commented Oct 17, 2019

I tried a number of suggested solutions and nothing worked. After not finding anything that actually works, I came up with the following and it worked like a charm! Hopefully, this helps someone else:

heroku pg:backups:restore source-app::b005 --app destination-app

b005 is the id of the backup for the source-app.

@gsum
Copy link

gsum commented Oct 24, 2019

I tried a number of suggested solutions and nothing worked. After not finding anything that actually works, I came up with the following and it worked like a charm! Hopefully, this helps someone else:

heroku pg:backups:restore source-app::b005 --app destination-app

b005 is the id of the backup for the source-app.

Thanks this works for me too.

@areeba-arshad
Copy link

This works for me as well, i follow these steps
1- i too backup of my test app's DB
2- Then call the above stated command

heroku pg:backups:restore test::b001 --app stage

3- now for my stage app the DB is updated and show the exact mirror schema and data just like in my test app's DB appear.

Problem: but there is a text appear against my test app on the heroku pipeline dashboard saying "Promote to stage" and when clicking on that button it gave me this error
C1

C2

C3

@drnic
Copy link

drnic commented Jul 14, 2021

I tried a number of suggested solutions and nothing worked. After not finding anything that actually works, I came up with the following and it worked like a charm! Hopefully, this helps someone else:

heroku pg:backups:restore source-app::b005 --app destination-app

b005 is the id of the backup for the source-app.

You're a champion.

@Wokoro
Copy link

Wokoro commented Oct 23, 2021

For a complete overide do;

heroku pg:copy source_project:source_database_name target_database_name --app target_app

@thekennysong
Copy link

@Wokoro getting Unknown database error

@Wokoro
Copy link

Wokoro commented Feb 11, 2022

@thekennysong, what error are you having?

@dzar-aker
Copy link

you need two colons between the project source and the source DB name:

heroku pg:copy source_project::source_database_name target_database_name --app target_app

@coolaj86
Copy link

Just because I landed here during search while I was trying to figure all this out between "Heroku Postgres" and "Native Postgres":

How to backup and restore for years at a time for just $6/month rather than $200+/month:
https://github.com/therootcompany/pg-xzbackup.sh

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment