0

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?

bumped to the homepage by Community yesterday

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

0

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, privacy policy and cookie policy

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