Skip Navigation
Show nav
Heroku Dev Center
  • Get Started
  • Documentation
  • Changelog
  • Search
  • Get Started
    • Node.js
    • Ruby on Rails
    • Ruby
    • Python
    • Java
    • PHP
    • Go
    • Scala
    • Clojure
  • Documentation
  • Changelog
  • More
    Additional Resources
    • Home
    • Elements
    • Products
    • Pricing
    • Careers
    • Help
    • Status
    • Events
    • Podcasts
    • Compliance Center
    Heroku Blog

    Heroku Blog

    Find out what's new with Heroku on our blog.

    Visit Blog
  • Log inorSign up
View categories

Categories

  • Heroku Architecture
    • Dynos (app containers)
    • Stacks (operating system images)
    • Networking & DNS
    • Platform Policies
    • Platform Principles
  • Command Line
  • Deployment
    • Deploying with Git
    • Deploying with Docker
    • Deployment Integrations
  • Continuous Delivery
    • Continuous Integration
  • Language Support
    • Node.js
    • Ruby
      • Working with Bundler
      • Rails Support
    • Python
      • Background Jobs in Python
      • Working with Django
    • Java
      • Working with Maven
      • Java Database Operations
      • Working with the Play Framework
      • Working with Spring Boot
      • Java Advanced Topics
    • PHP
    • Go
      • Go Dependency Management
    • Scala
    • Clojure
  • Databases & Data Management
    • Heroku Postgres
      • Postgres Basics
      • Postgres Performance
      • Postgres Data Transfer & Preservation
      • Postgres Availability
      • Postgres Special Topics
    • Heroku Redis
    • Apache Kafka on Heroku
    • Other Data Stores
  • Monitoring & Metrics
    • Logging
  • App Performance
  • Add-ons
    • All Add-ons
  • Collaboration
  • Security
    • App Security
    • Identities & Authentication
    • Compliance
  • Heroku Enterprise
    • Private Spaces
      • Infrastructure Networking
    • Enterprise Accounts
    • Enterprise Teams
    • Heroku Connect (Salesforce sync)
    • Single Sign-on (SSO)
  • Patterns & Best Practices
  • Extending Heroku
    • Platform API
    • App Webhooks
    • Heroku Labs
    • Building Add-ons
      • Add-on Development Tasks
      • Add-on APIs
      • Add-on Guidelines & Requirements
    • Building CLI Plugins
    • Developing Buildpacks
    • Dev Center
  • Accounts & Billing
  • Troubleshooting & Support
  • Databases & Data Management
  • Heroku Postgres
  • Postgres Data Transfer & Preservation
  • Importing and Exporting Heroku Postgres Databases

Importing and Exporting Heroku Postgres Databases

English — 日本語に切り替える

Last updated 23 January 2020

Table of Contents

  • Export
  • Import

On the surface, PG Backups provides a way to capture regular backups of your Heroku Postgres database. However, because of its general-purpose architecture and use of standard PostgreSQL utilities, it is also a useful tool capable of exporting to or importing from external PostgreSQL databases.

An alternative to using the dump and restore method of import/export if you have a Postgres instance on your local machine is to use the pg:push and pg:pull CLI commands to automate the process.

Export

PG Backups uses the native pg_dump PostgreSQL tool to create its backup files, making it trivial to export to other PostgreSQL installations. Note that the resulting backup file uses the custom format option in pg_dump. As compared to the plain-text format, the custom format options results in backup files that can be much smaller.

In general, PGBackups are intended for moderately loaded databases up to 20 GB. Contention for the I/O, memory and CPU needed for backing up a larger database becomes prohibitive at a moderate load and the longer run time increases the chance of an error that will end your backup capture prematurely. For databases that are larger than 20 GB, physical backups and continuous protection are recommended.

Download backup

To export the data from your Heroku Postgres database, create a new backup and download it.

$ heroku pg:backups:capture
$ heroku pg:backups:download

Restore to local database

Load the dump into your local database using the pg_restore tool. If objects exist in a local copy of the database already, you might run into inconsistencies when doing a pg_restore.

This will usually generate some warnings, due to differences between your Heroku database and a local database, but they are generally safe to ignore.

$ pg_restore --verbose --clean --no-acl --no-owner -h localhost -U myuser -d mydb latest.dump

If you are using an old version of pg_restore, you may see an error such as pg_restore: [archiver] unsupported version (1.13) in file header when you try to run pg_restore. Please make sure that you are using pg_restore version 11, 10.3, 9.6.8, 9.5.12, 9.4.17, and 9.3.22 or up. You can check the version of your pg_restore by running pg_restore --version.

 

You can optionally use the --jobs <number of jobs> flag with pg_restore to parallelise the restore of the dump. Only the custom and directory archive formats are supported with this option. More on this can be found in the Postgres documentation.

Import

PG Backups can be used as a convenient tool to import database dumps from other sources into your Heroku Postgres database.

If you are importing data as part of the initialization of a new application you will need to first create and configure the app on Heroku before performing the import.

Create dump file

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

Import to Heroku Postgres

In order for PG Backups to access and import your dump file you will need to upload it somewhere with an HTTP-accessible URL. We recommend using Amazon S3 with a signed url.

Note that the pg:backups restore command drops any tables and other database objects before recreating them.

Generate a signed URL using the aws console:

$ aws s3 presign s3://your-bucket-address/your-object

Use the raw file URL in the pg:backups restore command:

$ heroku pg:backups:restore '<SIGNED URL>' DATABASE_URL

DATABASE_URL represents the HEROKU_POSTGRESQL_COLOR_URL of the database you wish to restore to. You must specify a database configuration variable to restore the database.

If you’re using a Unix-like operating system be sure to use single quotes around the temporary S3 URL, because it might contain ampersands and other characters that will confuse your shell. If you’re running Windows, you must use double-quotes.

When you have completed the import process, delete the dump file from its storage location if it’s no longer needed.

Keep reading

  • Postgres Data Transfer & Preservation

Feedback

Log in to submit feedback.

SQLite on Heroku Migrating from MySQL to Postgres on Heroku

Information & Support

  • Getting Started
  • Documentation
  • Changelog
  • Compliance Center
  • Training & Education
  • Blog
  • Podcasts
  • Support Channels
  • Status

Language Reference

  • Node.js
  • Ruby
  • Java
  • PHP
  • Python
  • Go
  • Scala
  • Clojure

Other Resources

  • Careers
  • Elements
  • Products
  • Pricing

Subscribe to our monthly newsletter

Your email address:

  • RSS
    • Dev Center Articles
    • Dev Center Changelog
    • Heroku Blog
    • Heroku News Blog
    • Heroku Engineering Blog
  • Heroku Podcasts
  • Twitter
    • Dev Center Articles
    • Dev Center Changelog
    • Heroku
    • Heroku Status
  • Facebook
  • Instagram
  • Github
  • LinkedIn
  • YouTube
Heroku is acompany

 © Salesforce.com

  • heroku.com
  • Terms of Service
  • Privacy
  • Cookies