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?
Add a comment
|
1 Answer
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. =)
