Postgresql – Copy postgis layer from S3 to Heroku

awsherokupostgispostgresqlrestore

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?

Best 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. =)

Related Question