Postgres and Heroku backups

0

Dump

To dump and download a Heroku Postgres db
heroku pg:backups:capture
heroku pg:backups:download

To dump directly from your Heroku database
pg_dump --host=<host_name> --port=<port> --username=<username> --dbname=<dbname> > output.sql

heroku config:get DATABASE_URL
gives
postgres://<username>:<password>@<host_name>:<port>/<dbname>
e.g.
pg_dump --host=ec2xxxx.compute-1.amazonaws.com --port=5432 --username=xxxxxx --dbname=xxxxxx > output.sql

To dump into compressed binary format add -Fc
pg_dump -Fc

To dump to a local db
pg_dump -Fc --no-acl --no-owner -h localhost -U username xxxxxx_development > mydb.dump

To dump without creating tables add --data-only
pg_dump --data-only

To dump specific tables only
pg_dump --column-inserts --data-only --table=<table> <database>

Restore

To restore sql file
psql lukeko_development < output.sql

On your local computer
pg_restore --verbose --clean --no-acl --no-owner -h localhost -U username -d xxxxxx_development latest.dump

On Heroku
heroku pg:backups:restore b101 DATABASE_URL
or
heroku pg:backups:restore <public_url> DATABASE_URL

To convert dump to sql
pg_restore my_dump_file.dump > sql_statements.sql

To restore from sql file
psql -d my_local_database -f output.sql

  • 0 posts
  • 0 subtopics
  • 10 months ago by vince