Postgres on Heroku and dumping single table to dump file
You can dump a single table of data like so:
$ pg_dump --no-acl --no-owner -h [host ip].compute-1.amazonaws.com -U [user name] -t [table name] --data-only [database name] > table.dump
You can get all of the values needed with this:
$ heroku pg:credentials:url [DATABASE] -a [app_name]Connection info string: "dbname=[database name] host=[host ip].compute-1.amazonaws.com port=5432 user=[user name] password=[password] sslmode=require"Connection URL: postgres://[username]:[password]@[host ip].compute-1.amazonaws.com:5432/[database name]
This will prompt you for your password. Enter it, and you should then proceed to get a file table.dump
on your local drive.
You probably want to truncate the table on staging:
$ echo "truncate [table];" | heroku pg:psql [DATABASE] -a staging_app
With that file, you can use psql
with the Connection URL:
output of a new call to pg:credentials
for the staging app and restore just that table.
$ psql "[pasted postgres:// from pg:credentials:url of staging app]" < table.dumpSETSET............$
@catsbys answer
I needed to add the port as well
pg_dump --no-acl --no-owner -h [host ip].compute-1.amazonaws.com -p [port] -U [user name] -t [table name] --data-only [database name] > table.dump