I have a dump of a postgis layer (layer.dump), which I am trying to add to my heroku database (mydatabase). The postgis layer is stored on S3 (https://s3.amazonaws.com/layer.dump). I would like to add the layer to to the heroku database and previously used heroku pgbackups:restore DATABASE 'https://s3.amazonaws.com/layer.dump'. However, the new heroku pg:backups restore 'https://s3.amazonaws.com/layer.dump' DATABASE deletes all data from the target database before restoring the backup (https://devcenter.heroku.com/articles/heroku-postgres-backups). Is there still a way to only restore a single table and leave the remaining tables in the database untouched?

1 Answer 1


According to the link to a Heroku discussion here: Howto: Restore a single table to an existing database, the way your're doing it is always a destructive action.

A better solution proposed is to dump the table locally, and import the data remotely.

You could do a single dump of the table using a command like this:

pg_dump -U <myuser> <mydb> -t <mytable> -f <my_table>.sql 

And then try to restore it like this:

psql -U <myuser> <mydb> -h <my_heroku_url> -f <my_table>.sql

If you need a more intricate restore, I would recommend using pg_dump from the originating database with the -Fc flag, like this:

pg_dump -U <myuser> <mydb> -Fc -f <my_db>.dump

And then using the pg_restore utility to restore just the parts you need.

Further documentation for pg_restore is here: http://www.postgresql.org/docs/9.4/static/app-pgrestore.html

And for pg_dump: http://www.postgresql.org/docs/9.4/static/app-pgdump.html

Hope that helps. =)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.